陈晨辰 · 2020年01月10日

SQL 调优之查询调优

sql 语句性能分析

1、看 sql 语句执行时间
file

2、看 sql 的执行计划
file

3、查看 sql 的执行中各个环节耗时时间
file

4、查看mysql的执行进程,处理锁表的情况,命令 show PROCESSLIST, state 为LOCKED,说明产生锁表,ID为进程id,直接执行kill ID,就可以停止这个进程;
file

MySQL整个查询执行过程:

1、客户端同数据库服务层建立TCP连接。
2、客户端向MySQL服务器发送一条查询请求。
3、连接线程接收到SQL语句之后,将语句交给SQL语句解析模块进行语法分析和语义分析。
4、先看查询缓存中是否有结果,如果有结果可以直接返回给客户端。
5、如果查询缓存中没有结果,就需要真的查询数据库引擎层了,于是发给SQL优化器,进行查询的优化,生成相应的执行计划。
6、MySQL根据执行计划,调用存储引擎的API来执行查询
7、使用存储引擎查询时,先打开表,如果需要的话获取相应的锁。 查询缓存页中有没有相应的数据,如果有则可以直接返回,如果没有就要从磁盘上去读取。
8、当在磁盘中找到相应的数据之后,则会加载到缓存中来,从而使得后面的查询更加高效,由于内存有限,多采用变通的LRU表来管理缓存页,保证缓存的都是经常访问的数据。
9、最后,获取数据后返回给客户端,关闭连接,释放连接线程。

Procedure Analyse优化表结构

PROCEDURE ANALYSE() ,在优化表结构时可以辅助参考分析语句。通过分析select查询结果对现有的表的每一列给出优化的建议。

利用此语句,MySQL 帮你去分析你的字段和其实际的数据,并会给你一些有用的建议。
【只有表中有实际的数据,这些建议才会变得有用,因为要做一些大的决定是需要有数据作为基础的。】

PROCEDURE ANALYSE的语法如下:

SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]])
max_elements:指定每列非重复值的最大值,当超过这个值的时候,MySQL不会推荐enum类型。(默认值256)
max_memory (默认值8192)analyse()为每列找出所有非重复值所采用的最大内存大小。
执行返回中的Optimal_fieldtype列是mysql建议采用的列。

样例程序

mysql> DESC user_account;
FieldTypeNullKeyDefaultExtra
USERIDint(10) unsignedNOPRINULLauto_increment
USERNAMEvarchar(10)NO NULL
PASSSWORDvarchar(30)NO NULL
GROUPNAMEvarchar(10)YES NULL

rows in set (0.00 sec)

mysql> select * from user_account PROCEDURE ANALYSE(1)\G;
1. row **
Field_name: ibatis.user_account.USERID
Min_value: 1
Max_value: 103
Min_length: 1
Max_length: 3
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 51.7500
Std: 50.2562
Optimal_fieldtype: TINYINT(3) UNSIGNED NOT NULL
2. row **
Field_name: ibatis.user_account.USERNAME
Min_value: dfsa
Max_value: LMEADORS
.........................................................

从第一行输出我们可以看到analyze分析ibatis.user_account.USERID列
最小值1,最大值103,最小长度1,最大长度3,字符串或0的个数为0;值为空的字段数为0;平均每个字段的值为51.7500
并给出了该字段的优化建议:建议将该字段的数据类型改成TINYINT(3) UNSIGNED NOT NULL。

查询语句出现sending data耗时解决

执行SHOW PROFILES 查出Query_ID后在执行 show profile for query Query_ID ,或者是show processlist,查看查询所耗时资源
得知查询到语句耗时主要集中在 sending data上
file

解决步骤:
1、查询资料需要开启查询缓存,执行命令查看开启情况 show variables like '%query_cache%',No则是未开启,改配置文件设置 query_cache_type=YES后重启
file

2、查看buffer 执行 show variables like 'innodb_buffer_pool%',可以看出innodb_buffer_pool_size只有8M大小
这里设置成3G,SET GLOBAL innodb_buffer_pool_size = 3221225472,然后重启mysql(参考:https://www.cnblogs.com/wanbi...
file

3、查看表索引,show index from table_name发现cardinality的值为1,表的cardinality(可以翻译为“散列程度”),它表示某个索引对应的列包含多少个不同的值。如果cardinality大大少于数据的实际散列程度,那么索引就基本失效了。

使用命令:analyze table table_name修复索引,如果修复失败,查看是否因为新添加的字段设置了相同的默认值
file

总结
从上面这个例子我们可以看出analyze能根据目前表中的数据情况给出优化建议。当数据库在生产环境运行一定时间以后,开发或是DBA能参考analyze的分析结果来对表结构做出一定的优化。

where条件排序字段以及limit使用索引

背景

我们先来看2条sql

第一条:
select * from acct_trans_log WHERE acct_id = 1000000000009000757 order by create_time desc limit 0,10

第二条:
select * from acct_trans_log WHERE acct_id = 1000000000009003061 order by create_time desc limit 0,10

表的索引及数据总情况:
索引:acct_id,create_time分别是单列索引,数据库总数据为500w

通过acct_id过滤出来的结果集在1w条左右
查询结果:第一条要5.018s,第二条0.016s
为什么会是这样的结果呢?第一,acct_id和create_time都有索引,不应该出现5s查询时间这么慢啊

那么先来看执行计划
第一条sql执行计划:
file
第二条执行计划:
file

仔细观察会发现,索引只使用了idx_create_time,没有用到idx_acct_id

这能解释第一条sql很慢,因为where查询未用到索引,那么第二条为什么这么快?
看起来匪夷所思,其实搞清楚mysql查询的原理之后,其实很简单

我们来看这2条sql查询,都用到了where order by limit
当有limit存在时,查询的顺序就有可能发生变化,这时并不是从数据库中先通过where过滤再排序再limit
因为如果这样的话,从500万数据中通过where过滤就不会是5s了。

此时的执行顺序是,先根据idx_create_time索引树,从最右侧叶子节点,反序取出n条,然后逐条去跟where条件匹配
若匹配上,则得出一条数据,直至取满10条为止,为什么第二条sql要快,因为运气好,刚好时间倒序的前几条就全部满足了。

搞清楚原理之后,我们了解了为什么第一条慢,第二条快的原因,但是问题又来了
为什么mysql不用idx_acct_id索引,这是一个问题,因为这样的话,我们的建立的索引基本失效了,在此类sql下
查询效率将会是相当低

因为通过acct_id过滤出来的结果集比较大,有上万条,mysql认为按时间排序如果不用索引,将会是filesort,这样会很慢,而又不能2个索引都用上,所以选择了idx_create_time。

为什么mysql只用一个索引

这里为什么不能2个索引都用上,可能很多人也不知道为什么,其实道理很简单,每个索引在数据库中都是一个索引树,其数据节点存储了指向实际

数据的指针,如果用一个索引来查询,其原理就是从索引树上去检索,并获得这些指针,然后去取出数据,试想,如果你通过一个索引,得到过滤后的指针,这时,你的另一个条件索引如果再过滤一遍,将得到2组指针的集合,如果这时候取交集,未必就很快,因为如果每个集合都很大的话,取交集的时候,等于扫描2个集合,效率会很低,所以没法用2个索引。当然有时候mysql会考虑临时建立一个联合索引,将2个索引联合起来用,但是并不是每种情况都能奏效,同样的道理,用一个索引检索出结果集之后,排序时,也无法用上另一个索引了。

实际上用索引idx_acct_id大多数情况还是要比用索引idx_create_time要快,我们举个例子:
select * from acct_trans_log force index(idx_acct_id) WHERE acct_id = 1000000000009000757 order by create_time desc limit 0,10
耗时:0.057s
可以看出改情况用idx_acct_id索引是比较快的,那么是不是这样就可以了呢,排序未用上索引,始终是有隐患的。

联合索引,让两个字段都用上索引

我们来看下一条sql:
select * from acct_trans_log force index(idx_acct_id) WHERE acct_id = 3095 order by create_time desc limit 0,10
耗时: 1.999s
执行计划:
file

该sql通过acct_id过滤出来的结果集有100万条,因此排序将会耗时较高,所幸这里只是取出前10条最大的然后排序
查询概况,我们发现时间基本消耗在排序上,其实这是内存排序,对内存消耗是很高的。
file

解决办法就是建立联合索引:
alter table acct_trans_log add index idx_acct_id_create_time(acct_id,create_time)
然后执行sql:
select * from acct_trans_log WHERE acct_id = 3095 order by create_time desc limit 0,10
耗时: 0.016s
file

联合索引使用的原理

但是为什么能解决这个问题呢,下面我们再看一个sql:
select * from acct_trans_log force index(idx_acct_id_create_time) WHERE acct_id in(3095,1000000000009000757) order by create_time desc limit 0,10
耗时:1.391s
索引还是用idx_acct_id_create_time,时间居然慢下来了。看执行计划,排序用到了filesort,也就是说,排序未用到索引。

那么我们还是来看看,索引排序的原理,我们先来看一个sql:
select * from acct_trans_log ORDER BY create_time limit 0,100
耗时:0.029s
执行计划为:
file
这里执行的步骤是,先从索引树中,按时间升序取出前100条,因为索引是排好序的,直接左序遍历即可了
因此,这里mysql并没有做排序动作,如果想降序,则右序遍历索引树,取出100条即可,查询固然快,

那么联合索引的时候,是怎样的呢?
select * from acct_trans_log WHERE acct_id = 3095 order by create_time desc limit 0,10
使用组合索引:idx_acct_id_create_time
这个时候,因为acct_id是联合索引的前缀,因此可以很快实行检索,

如果sql是
select * from acct_trans_log WHERE acct_id = 3095
出来的数据是按如下逻辑排序的
3095+time1
3095+time2
3095+time3

默认是升序的,也就是说,次sql相当于
select * from acct_trans_log WHERE acct_id = 3095 order by create_time
他们是等效的。

如果我们把条件换成order by create_time desc limit 0,10呢
这时候,应该从idx_acct_id_create_time树右边叶子节点倒序遍历,取出前10条即可
因为数据的前缀都是3095,后缀是时间升序。那么我们倒序遍历出的数据,刚好满足order by create_time desc
因此也无需排序。

那么语句:
select * from acct_trans_log force index(idx_acct_id_create_time) WHERE acct_id in(3095,1000000000009000757) order by create_time desc limit 0,10
为什么排序无法用索引呢?
我们先分析下索引的排序规则
已知:id1<id2<id3... time1<time2<time3....
查询结果集排序如下:
id1+time1
id1+time2
id1+time3
id2+time1
id2+time2
id2+time3

索引出来的默认排序是这样的,id是有序的,时间是无序的,因为有2个id,优先按id排序,时间就是乱的了,
这样排序将会用filesort,这就是慢的原因,也是排序没有用到索引的原因。

查询计划使用以及使用说明
table:显示这一行数据是关于哪张表的
type:显示使用了何种类型,从最好到最差的连接类型为const,eq_ref,ref,range,index,all
possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引
key:实际使用的索引,如果为null,则没有使用索引。
key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好
ref:显示索引的哪一列被使用了,如果可能的话,是一个常数
rows:mysql认为必须检查的用来返回请求数据的行数

参考:
1、Navicat常用功能:https://blog.csdn.net/littlex...
2、MySQL查询过程剖析:https://blog.csdn.net/z_ryan/...
3、分析诊断工具之Procedure Analyse:https://www.cnblogs.com/duanx...
4、sending data耗时解决:https://www.cnblogs.com/yaoxi...、https://www.cnblogs.com/rwxwsblog/p/5684213.html
5、where条件排序字段以及limit使用索引:https://www.cnblogs.com/tangy...

推荐阅读
关注数
2
文章数
95
目录
极术微信服务号
关注极术微信号
实时接收点赞提醒和评论通知
安谋科技学堂公众号
关注安谋科技学堂
实时获取安谋科技及 Arm 教学资源
安谋科技招聘公众号
关注安谋科技招聘
实时获取安谋科技中国职位信息