explain分析慢SQL

简介

大部分数据库都可以使用explain sql分析SQL执行计划，这里记录一下mysql的explain使用。

列解释

使用方式：

//查看slow-log.log获取慢sql
explain sql;
//如explain select * from test;

列	意义
id	每一个select对应一行；连接查询对应的多个select的id相同，子查询id不同。
table	访问的表名，如果是物化派生表，则表名为，N对应生成物化表的查询id
select_type	代表了在相同id的多个select之间，各个select扮演的角色；对于包含union,union all或者子查询的大查询，最左边的就是查询就是PRIMARY;如果通过物化派生表的方式执行派生表的查询，那么派生表对应的子查询就是DERIVED；
type	访问表的方法； ref表示普通二级索引等值匹配查询； const表示主键或者不允许存储NULL的唯一二级索引与常数进行等值匹配； eq_ref表示连接查询时，被驱动表是通过主键或者不为NULL的唯一二级索引进行等值匹配的方式进行访问； index表示可以使用索引覆盖； ALL表示全表扫描。
key_len	索引列的最大宽度
ref	当访问方法是ref,const,eq_ref等时，ref列展示的就是进行等值匹配的值是什么
rows	估计需要读取的行数；对于全表扫描，就是估计表行数；对于索引扫描，就是扫描索引记录行数。
filtered	驱动表扇出比例，也就是驱动表中估计满足条件的比例；对于全表扫描，就是满足所有条件的记录数；对于索引扫描就是满足其他搜索条件的记录数。
extra	说明额外信息。 using index表示使用索引覆盖执行查询； using index condition表示发生了索引条件下推，如果有些搜索条件中出现了索引列，但是不能充当边界条件来形成索引区间（如like '%xx'），mysql将在索引扫描的时候直接在存储引擎层判断，减少回表； using where表示某个搜索条件需要在server层判断； using temporary表示使用临时表完成去重排序等功能，如执行中包含distinct,group by,union而且不能有效利用索引来完成查询； using filesort表示使用内存或者磁盘排序； FirstMatch(tbname)表示使用首次匹配，一种最原始的执行半连接查询的方式。 start temporary, end temporary表示将in转化为duplicate weedout的半连接时的驱动表和被驱动表

索引优化查询

对于出现在where条件中表的列或者group by中的列，可以尝试建立索引优化查询；如果一个表有多个列出现在where中，可以考虑在这几列上建立联合索引；如果select只取表中较少的列可以考虑建立覆盖索引。

//先更新表的统计数据，更新前后可能导致执行计划不同
analyze table tbname
//查看sql执行计划
explain select ...;
//重点观察：type为ALL类型的查询可能是缺少索引;
//rows较高的列可能是缺少索引或者索引唯一性不好，可以考虑建立联合索引；

//查看表上已经存在的索引，避免重复建立索引
show indexes from tb;
//cardinality列代表了索引的唯一性，越大越好

//在表tb的列colx建立索引前应该先分析索引的唯一性
select colx,count(*) from tb group by colx;

//建立索引
create index idname on tbnabme(col1,col2...);

//重新查看执行计划，检查执行计划是否使用了新建立的索引
explain select ....;

//直接执行sql，查看是否真的有性能提升
//有时候建立索引反而导致性能下降，mysql的执行计划都无法正确判断是否应该走索引
//所以实践是检验真理的唯一标准
select ...

建立索引并不一定就能起到优化查询的效果。全表扫描读取的数据量大，是顺序IO；通过普通索引查询读取的数据少，但回表(type为ref)是随机IO。当普通索引筛选出的数据量少到一定阈值之后，才会优于全表扫描。这个阈值是很难确定的（数据库的执行计划是如何判断成本的呢？）。联合索引可以更准确的找到数据，但是联合索引占用的存储空间比单列索引大，所以也不一定能提高查找效率。建立索引的时候需要重点考虑索引唯一性，索引大小，回表产生的随机IO。
建立索引会导致数据更新操作变慢（update,insert,delete），因为这些操作需要维护索引，同时索引导致数据库占用更多的存储空间。