SQL 调优之查询调优

sql 语句性能分析

1、看 sql 语句执行时间

2、看 sql 的执行计划

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

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

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;
Field	Type	Null	Key	Default	Extra
USERID	int(10) unsigned	NO	PRI	NULL	auto_increment
USERNAME	varchar(10)	NO		NULL
PASSSWORD	varchar(30)	NO		NULL
GROUPNAME	varchar(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上

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

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...）

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

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

总结
从上面这个例子我们可以看出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执行计划：

第二条执行计划：

仔细观察会发现，索引只使用了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
执行计划：

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

解决办法就是建立联合索引：
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

联合索引使用的原理

但是为什么能解决这个问题呢，下面我们再看一个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
执行计划为：

这里执行的步骤是，先从索引树中，按时间升序取出前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...