hi,你好!欢迎访问本站!登录
本站由网站地图腾讯云宝塔系统阿里云强势驱动
当前位置:首页 - 教程 - 杂谈 - 正文 君子好学,自强不息!

数据库优化 - SQL优化

2019-11-18杂谈搜奇网25°c
A+ A-

前面一篇文章从实例的角度举行数据库优化,经由历程设置一些参数让数据库机能到达最优。然则一些“不好”的SQL也会致使数据库查询变慢,影响营业流程。本文从SQL角度举行数据库优化,提拔SQL运转效力。

推断题目SQL

推断SQL是不是有题目时能够经由历程两个表象举行推断:

  • 体系级别表象
    • CPU斲丧严峻
    • IO守候严峻
    • 页面相应时刻太长
    • 运用的日记涌现超时等毛病

能够运用sar敕令,top敕令检察当前体系状况。

也能够经由历程Prometheus、Grafana等监控东西视察体系状况。(感兴趣的能够翻看我之前的文章)

  • SQL语句表象
    • 冗杂
    • 实行时刻太长
    • 从全表扫描猎取数据
    • 实行计划中的rows、cost很大

冗杂的SQL都好明白,一段SQL太长浏览性肯定会差,而且涌现题目的频次肯定会更高。更进一步推断SQL题目就得从实行计划入手,以下所示:

实行计划通知我们本次查询走了全表扫描Type=ALL,rows很大(9950400)基础能够推断这是一段"有滋味"的SQL。

猎取题目SQL

差别数据库有差别的猎取要领,以下为现在主流数据库的慢查询SQL猎取东西

  • MySQL
    • 慢查询日记
    • 测试东西loadrunner
    • Percona公司的ptquery等东西
  • Oracle
    • AWR报告
    • 测试东西loadrunner等
    • 相干内部视图如v$sql、v$session_wait等
    • GRID CONTROL监控东西
  • 达梦数据库
    • AWR报告
    • 测试东西loadrunner等
    • 达梦机能监控东西(dem)
    • 相干内部视图如v$sql、v$session_wait等

SQL编写技能

SQL编写有以下几个通用的技能:

• 合理运用索引

索引少了查询慢;索引多了占用空间大,实行增编削语句的时刻须要动态保护索引,影响机能
选择率高(反复值少)且被where频仍援用须要竖立B树索引;平常join列须要竖立索引;庞杂文档范例查询采纳全文索引效力更好;索引的竖立要在查询和DML机能之间获得均衡;复合索引建立时要注意基于非前导列查询的状况

• 运用UNION ALL替换UNION

UNION ALL的实行效力比UNION高,UNION实行时须要排重;UNION须要对数据举行排序

• 防止select * 写法

实行SQL时优化器须要将 * 转成详细的列;每次查询都要回表,不能走掩盖索引。

• JOIN字段发起竖立索引

平常JOIN字段都提早加上索引

• 防止庞杂SQL语句

提拔可浏览性;防止慢查询的几率;能够转换成多个短查询,用营业端处置惩罚

• 防止where 1=1写法

• 防止order by rand()相似写法

RAND()致使数据列被屡次扫描

SQL优化

实行计划

完成SQL优化一定要先读实行计划,实行计划会通知你哪些地方效力低,那里能够须要优化。我们以MYSQL为例,看看实行计划是什么。(每一个数据库的实行计划都不一样,须要自行相识)
explain sql

字段 诠释
id 每一个被自力实行的操纵标识,标识对象被操纵的递次,id值越大,先被实行,假如雷同,实行递次从上到下
select_type 查询中每一个select 字句的范例
table 被操纵的对象称号,通常是表名,但有其他花样
partitions 婚配的分区信息(关于非分区表值为NULL)
type 衔接操纵的范例
possible_keys 能够用到的索引
key 优化器现实运用的索引(最主要的列) 从最好到最差的衔接范例为consteq_regrefrangeindexALL。当涌现ALL时示意当前SQL涌现了“坏滋味”
key_len 被优化器选定的索引键长度,单元是字节
ref 示意本行被操纵对象的参照对象,无参照对象为NULL
rows 查询实行所扫描的元组个数(关于innodb,此值为估计值)
filtered 前提表上数据被过滤的元组个数百分比
extra 实行计划的主要补充信息,当此列涌现Using filesort , Using temporary 字样时就要警惕了,极能够SQL语句须要优化

接下来我们用一段现实优化案例来申明SQL优化的历程及优化技能。

优化案例

  • 表构造
    CREATE TABLE `a` ( `id` int(11) NOT NULLAUTO_INCREMENT, `seller_id` bigint(20) DEFAULT NULL, `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `gmt_create` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `b` ( `id` int(11) NOT NULLAUTO_INCREMENT, `seller_name` varchar(100) DEFAULT NULL, `user_id` varchar(50) DEFAULT NULL, `user_name` varchar(100) DEFAULT NULL, `sales` bigint(20) DEFAULT NULL, `gmt_create` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `c` ( `id` int(11) NOT NULLAUTO_INCREMENT, `user_id` varchar(50) DEFAULT NULL, `order_id` varchar(100) DEFAULT NULL, `state` bigint(20) DEFAULT NULL, `gmt_create` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) );

  • 三张表关联,查询当前用户在当前时刻前后10个小时的定单状况,并依据定单建立时刻升序分列,详细SQL以下
    select a.seller_id, a.seller_name, b.user_name, c.state from a, b, c where a.seller_name = b.seller_name and b.user_id = c.user_id and c.user_id = 17 and a.gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE) AND DATE_ADD(NOW(), INTERVAL 600 MINUTE) order by a.gmt_create;

  • 检察数据量

  • 原实行时刻

  • 原实行计划

  • 开端优化思绪
    1. SQL中 where前提字段范例要跟表构造一致,表中user_id 为varchar(50)范例,现实SQL用的int范例,存在隐式转换,也未增添索引。将b和c表user_id 字段改成int范例。
    2. 因存在b表和c表关联,将b和c表user_id建立索引
    3. 因存在a表和b表关联,将a和b表seller_name字段建立索引
    4. 应用复合索引消弭暂时表和排序
  • 开端优化SQL
    alter table b modify `user_id` int(10) DEFAULT NULL; alter table c modify `user_id` int(10) DEFAULT NULL; alter table c add index `idx_user_id`(`user_id`); alter table b add index `idx_user_id_sell_name`(`user_id`,`seller_name`); alter table a add index `idx_sellname_gmt_sellid`(`gmt_create`,`seller_name`,`seller_id`);

  • 检察优化后实行时刻

  • 检察优化后实行计划

  • 检察warnings信息

  • 继承优化
    alter table a modify "gmt_create" datetime DEFAULT NULL;

  • 检察实行时刻

  • 检察实行计划

  • 优化总结
    1. 检察实行计划 explain
    2. 假如有告警信息,检察告警信息 show warnings;
    3. 检察SQL触及的表构造和索引信息
    4. 依据实行计划,思索能够的优化点
    5. 根据能够的优化点实行表构造变动、增添索引、SQL改写等操纵
    6. 检察优化后的实行时刻和实行计划
    7. 假如优化结果不明显,反复第四步操纵

      请关注个人民众号:JAVA日知录

  选择打赏方式
微信赞助

打赏

QQ钱包

打赏

支付宝赞助

打赏

  移步手机端
数据库优化 - SQL优化

1、打开你手机的二维码扫描APP
2、扫描左则的二维码
3、点击扫描获得的网址
4、可以在手机端阅读此文章
未定义标签

本文来源:搜奇网

本文地址:https://www.sou7.cn/282024.html

关注我们:微信搜索“搜奇网”添加我为好友

版权声明: 本文仅代表作者个人观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。请记住本站网址https://www.sou7.cn/搜奇网。

发表评论

选填

必填

必填

选填

请拖动滑块解锁
>>