通过一次慢SQL优化来看EXPLAIN

数据库小白一枚，正在不断学习积累知识，现将学习到的知识记录一下，也是将我的所得分享给大家！而今天这篇文章《通过一次慢SQL优化来看EXPLAIN》带大家来了解一下通过一次慢SQL优化来看EXPLAIN，希望对大家的知识积累有所帮助，从而弥补自己的不足，助力实战开发！

实操

这条慢SQL主要涉及了两个表。

API_RESULT —— 用于统计api调用结果(数据量为6000W+)

1，MySQL是选择了api表去作为一个驱动表，api_result为被驱动表，所以api表是排在第一位。至于为什么会选择api表作为驱动表，我们等下再说。
2之后与外层查询进行连接查询时，该子查询对应的select_type属性就是MATERIALIZED
possible_key

就是执行中可能会用到的索引，需要注意的是并不是可能用到的索引越多越好，更多的索引意味着查询优化器计算查询成本时需要花费的时间也越多
key

就是实际使用到的索引
key_len

查询优化器最终查询时使用的索引的长度。它是由这三个部分构成的：
1.对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值。
2.如果该索引列可以存储NULL值，则key_len比不可以存储NULL值时多1个字节。
3.对于变长字段来说，都会有2个字节的空间来存储该变长列的实际长度。
比如我们这个SQL，最终使用了idx_api_skey_ip_appkey这个索引，其中api的字段类型为varchar(80)，数据库使用的字符集是utf8，最大是占三个字节，又因为它是一个变长字段，80 * 3 + 2 = 242，那么就可以看出，实际查询中只使用到了idx_api_skey_ip_appkey这个索引的api列

type

对表的访问方法
——

system

: 当一个表的存储引擎的统计数据是精确的，比如MyISAM，且表中只有一条数据的时候，那么就会使用system
——

const

: 当使用主键索引或者唯一索引进行常数值匹配的时候，为const，需要注意的是，当是联合索引时，要求是要对所有索引字段进行等值常数匹配，否则会为ref
——

eq_ref

: 在连接查询的时候，如果被驱动表是通过主键或者唯一索引进等值匹配时为eq_ref,当是联合索引时，同const一致
——

ref

: 当通过索引进行等值匹配时，就是ref，联合索引不需要对所有字段进行等值匹配
——

ref_or_null

: 对索引列进行等值查找的同时有 OR IS NULL时访问方法为ref_or_null
——

index_merge

: 一般情况一个sql只能走一个索引，但在一些特殊情况下，可能会产生索引合并的情况，这时候就会为index_merge
——

unique_subquery

: 针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，就是unique_subquery
——

index_subquery

: 和unique_subquery差不多，只不过子查询使用的索引变成普通索引而已
——

range

: 如果查询中使用到了范围查询，且查询列为索引列，那么可能会使用到range
——

index

: 可以使用索引覆盖，但需要扫描全部的索引记录时，比如有一个联合索引a,b, select a from table where b = ?;这个时候就是index
——

all

: 喜提全表扫描一次

ref

当查询为对索引列进行等值匹配时，与索引列匹配的是什么东西，const-常数列，func-函数......

rows

使用该执行计划，该表预计扫描行数

extra

说一些比较常见的:
——

No tables used

: 当查询语句没有from的时候提示
——

Impossible WHERE

: 查询条件永远为false的时候提示，比如id列不允许为null，查询时使用id is null 就会出现
——

Using index

: 可以使用索引覆盖时提示
——

Using index condition

: 搜索条件中虽然出现了索引列，但却不能使用到索引，比如a为索引，a > 'a' and a like '%zz' 这种
——

Using where

: 使用全表扫描，同时含有where子句 或者 用到索引但where子句中包含非索引列字段的查找
——

Using filesort

: 在排序时不能使用索引列直接进行排序，需要依赖内存或者磁盘去进行排序处理，那么会提示这个
——

Using temporary

: 查询过程中会用到临时表
需要注意的是，

Using index condition

和

Using where

在我自己测试中发现，和MySQL的版本有关系，5.6和5.7的版本中有不同；另外，当查询中出现

Using filesort

，

Using temporary

这两个的时候我们可能需要多注意一点，都是比较耗性能的查询，应该尽可能的去优化它

回归到正题，来看这次这个慢SQL的优化，我们可以看出，本次查询选择了以api表作为驱动表，api_result表去作为一个被驱动表。其实一开始看到这个SQL我的预想是认为应该以api_result表作为一个驱动表，那么就可以使用到index_2这个索引，然后再以api_name去连接api表可以使用到api表的index_2索引，所以我尝试加了一个force index(Index_2)，测试下:

DESC SELECT SUM(CALL_TIMES) AS TCALLTIMES,SUM(SUCCESS_TIMES) AS TSUCCESSTIMES,SUM(ERROR_TIMES) AS TERRORTIMES FROM API_RESULT AR FORCE INDEX(INDEX_2) JOIN API A ON A.API_NAME = AR.API WHERE AR.COUNT_DATE='2019-08-22' AND A.FREE = '2';

测试查询很快，但是正常是不建议直接在SQL上加上force index去强制走一个索引的，那还有什么方式去优化它呢？
很明显问题的关键点在于，为什么会选择api表作为一个驱动表？
搜索了一下，原来在内连接的时候，查询优化器会优先选择数据量较少的表作为一个驱动表。
那么一个想法是，我们能不能把api_result表的数据量尽可能的压缩呢？答案是可以做到的，因为本来这次的sql就是要SUM所有的调用次数，那么我们可以先把api_result表的数据先根据api分组统计出来,再根据api连接到api中:

 SELECT SUM(AR.TCALLTIMES), SUM(AR.TSUCCESSTIMES), SUM(AR.TERRORTIMES) FROM API A JOIN (SELECT API,SUM(CALL_TIMES) AS TCALLTIMES, SUM(SUCCESS_TIMES) AS TSUCCESSTIMES, SUM(ERROR_TIMES) AS TERRORTIMES FROM API_RESULT WHERE COUNT_DATE = '2019-08-22' GROUP BY API ORDER BY NULL) AR ON AR.API = A.API_NAME WHERE A.FREE = '2';

因为API表过滤掉FREE = '2'这个条件后只有400+条记录，而API_RESULT统计出来有2300+条，所以依然还是会选择API表作为驱动表，可以看出API_RESULT使用了想要的INDEX_2索引，通过一个临时表去与API表作连接，执行时间在0.5s左右，比之前的700s左右快了很多，暂时没有更好的想法，如果想要更好的性能，实际应该重新对API_RESULT表进行一个分表处理了。

参考文献

本文参考了掘金小册《MySQL 是怎样运行的：从根儿上理解 MySQL》—— 小孩子4919，个人觉得是一本很不错的小册，讲的通俗易懂，适合像我这样的小白学习，有兴趣的小伙伴可以去看下

---

1. inner join 与 left | right join 不同，内连接因为需要的是两个表都存在的数据，所以驱动表是可以互换的，由查询优化器去选择一个驱动表，而left join 则是左边表为驱动表，右边表为被驱动表，right join 与left join正好相反 ↩
2. 物化是指含子查询的查询语句中，将子查询结果集中的记录保存到临时表的过程 ↩

到这里，我们也就讲完了《通过一次慢SQL优化来看EXPLAIN》的内容了。个人认为，基础知识的学习和巩固，是为了更好的将其运用到项目中，欢迎关注golang学习网公众号，带你了解更多关于mysql的知识点！