通过一次慢SQL优化来看EXPLAIN

前言

前段时间接手了一个公司内部使用的API调用统计的项目，最近突然频繁的收到慢SQL的告警，正好最近在学习这块的内容，来练把手吧。

实操

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

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

SHOW INDEX FROM API_RESULT;

API —— 用于记录api信息(数据量为2000+)
DESC API;

SHOW INDEX FROM API;

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

照着套路来：
EXPLAIN SELECT SUM(CALL_TIMES) AS TCALLTIMES, SUM(SUCCESS_TIMES) AS TSUCCESSTIMES, SUM(ERROR_TIMES) AS TERRORTIMES FROM API_RESULT AR JOIN API A ON A.API_NAME = AR.API WHERE AR.COUNT_DATE = '2019-08-22' AND A.FREE = '2';

先来解释下各列的意思，如果对这块比较熟悉的话可以直接跳过
id
每一个select关键字都会被分配一个唯一的id，我们这次是只有一个select，所以只被分配了一个id，虽然是一个关联查询，但是这两个表被分配到的id是一样的。同时需要注意的是，虽然id相同，但顺序是有说法的，在连接查询中，首先出现的表是驱动表。比如在我们这个SQL中，因为采用了内连接所以由查询优化器去选择一个驱动表¹，MySQL是选择了api表去作为一个驱动表，api_result为被驱动表，所以api表是排在第一位。至于为什么会选择api表作为驱动表，我们等下再说。

table
没啥好说的，就是表名啦。因为我们这次是连接查询了两个表，所以就有两个表名。

select_type
这个有很多值，这里就只说几个我常见的:
——SIMPLE: 查询语句中不包含UNION或者子查询的查询都算作是SIMPLE类型
——PRIMARY : 对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的select_type值就是PRIMARY
——UNION : 对于包含UNION或者UNION ALL的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的select_type值就是UNION
——UNION RESULT : MySQL选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT
——DERIVED : 对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的select_type就是DERIVED
——MATERIALIZED : 当查询优化器在执行包含子查询的语句时，选择将子查询物化²之后与外层查询进行连接查询时，该子查询对应的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. 物化是指含子查询的查询语句中，将子查询结果集中的记录保存到临时表的过程 ↩