MySQL执行计划学习

1.explain 的作用
工作中需要要写出好的sql或者想要提升sql的查询性能，我们通常的做法是加索引。那么加了索引后，我们如何知道加的索引生效了呢？真正执行每条sql时走的是哪个索引呢？(表字段建了多个索引，单sql执行时只会走一个索引) 索引效率如何呢？这就需要我们查询sql的执行计划，通过执行执行分析性能。不同数据生产厂商提供了不同的执行执行查询语句，如mysql，postgresql的explain ，oracle的explain for。这里学习下mysql的执行计划。

2.explain查询结果字段解读
执行如下语句，查看执行计划显示结果：

explain select * from user where id=1;

2.1 id
可以认为是执行explain后面的语句，所需要经过的步骤数。取值为null，1，2，3，4。。。取值越大越先执行，取值相同时依次执行。最后执行id为null的步骤(如最后union查询结果情况)。

2.2 selectType
查询类型：simple简单select语句，查询中不包含子查询或者union
primary：查询中包含子查询，最外层被标记为primary
subquery/materialized：subquery表示在select或者where列表中包含了=连接的子查询；materialized表示where后面in条件的子查询。
union：第二个select出现在union之后，则被标记为union
union result：从union表获取结果的select；

2.3 table
查询作用的表，或者表的别名，或union别的查询的结构，如<unionM,N>。

2.4 type
访问类型，sql查询优化中一个很重要的指标，结果值从好到坏依次是：
system -> constant -> eq_ref -> ref -> range -> index -> All
a. system: 表中只有一行记录，基本不会出现，可以忽略；
b. constant：表示通过索引一次就找到了，constant用于比较primary key或者unique索引。
这里的id为主键索引列字段，type为constant性能极好。
c. eq_ref: 唯一索引扫描，表中只有一条记录与之匹配，常见于主键或者唯一索引；
执行如下查询计划：

explain select * from users where id in(select userID from user-address where address=‘xxx’) \G

分析：explain后得到执行计划，由3部分组成：id=1，id=1，id=2.
1.首先执行id最大的步骤，即id=2步骤。select_type为materialized，即in后面的子查询。类型type为ref，即非唯一索引，即多个用户的地址是相同的。
2.然后执行第一个id=1的步骤，table为subquery2,把id=2步骤的结果全部列举出来。
3.执行执行第二个id=1的步骤，table为users表，type为eq_ref，key为主键。引用了子查询subquery2的userID结果。

d. ref： 非唯一性索引扫描，返回匹配某个单独值的所有行；

e. range：只检索给定范围的行，使用一个索引来选择行；(sql优化的最低标准)
f. index：Full Index Scan：索引全表扫描；
g. All：Full Table Scan，遍历全表以找到匹配的行；

2.5 possible_keys
查询过程中可能用到的索引

2.6 key
实际使用的索引，如果为null，则没有使用索引

2.7 rows
根据统计信息或者索引选用情况，大致估算出找到所需记录需要读取的行数。

2.8 filtered
表示返回结构行占需要读取行数的百分比，值越大越好。

3.其他额外信息–Extra
3.1 using filesort
mysql对数据使用一个外部的文件内容进行了排序，而不是按照表内的索引进行读取。用临时表排序性能是比较差的。

3.2 Using temporary
使用临时表保存中间结果，也就是说mysql对查询结果排序时使用了临时表，常见于order by或者group by

3.3 Using index
使用相应的select操作中使用了覆盖索引(Covering index)，避免了访问表的数据行，效率高。（覆盖索引直接命中后返回，无需访问叶子节点）

3.4 Using where
使用了where过滤条件

3.5 select tables optimized away
基于索引优化操作或者MyISAM存储引擎优化count(*)操作，不必等到执行阶段再进行计算，在查询计划生成阶段即可完成优化。

4 linux上慢查询MySQL服务检测配置
4.1.业务驱动，业务人员反馈
4.2.测试驱动
4.3.慢查询日志：
show variables like ‘slow_query_log’;查询慢查询开启状况
set global slow_query_log=on;开启慢查询
set global slow_query_log_file=’/var/lib/mysql/xxx.log’;慢查询日志路径
set global log_queries_not_using_indexs=on;记录未走索引的语句
set global long_query_time =0.1;(秒) sql语句执行超过0.1秒的就记录在慢查询日志中。

使用mysql自带的命令mysqldumslow可以分析慢查询日志文件：
#列举出慢查询日志xx-slow.log文件中平均耗时最高的前10条记录信息。

  mysqldumpslow -s at -t 10 /var/lib/mysql/xxx-slow.log

也可以使用第三方工具分析，如：mysqlsla，pt-query-digest；