MySQL性能调优（四）Query优化--explain Que

要想写出高效的query语句，就一定要对query语句进行分析
主要使用explain和profiling两个命令完成分析的工作

1、explain的用法

explain的用法就是在Query前加上 explain关键字即可，例如

explain select * from tb;

返回结果如下：

explain返回
其中的字段含义解释：
1、id
id为查询序列号
id越大的越优先执行，如果id相等，依次执行
2、select_type
select_type分为以下几类：
(1) SIMPLE：除了子查询和union之外的所有查询
(2) PRIMARY：子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY
(3) UNION：union语句中第二个select开始的后面所有select，第一个select为PRIMARY
(4) DEPENDENT UNION：子查询的union查询，union中第二个select语句后面的所有select
(5) UNION RESULT：union中的合并结果
(6) SUBQUERY：子查询的第一个select，结果不依赖于外部查询的结果集
(7) DEPENDENT SUBQUERY：子查询中的第一个select，结果依赖于外部查询的结果集
(8) UNCACHEABLE SUBQUERY：结果集无法缓存的的子查询
3、table
语句作用的表名称
4、partitions
表示所匹配的分区，5.7以前不显示此项，如要显示，需使用explain partitions命令
5、type
对表的访问方式，主要有以下几类
（1）all：全表扫描
（2）const：读常量，只读一次
（3）eq_ref：最多只有一条匹配结果，一般是通过主键或唯一索引来访问
（4）index：全索引扫描
（5）index_merge：查询中使用连个或更多索引
（6）index_subquery：子查询中返回的结果集是一个索引，不是主键或唯一索引
（7）range：索引范围扫描
（8）ref：jion语句中被驱动表索引引用查询
（9）ref_or_null：在ref的基础上增加空值的查询
（10）system：查询系统表
（11）unique_subquery：子查询中返回的结果集是主键或者唯一索引
性能排序是：
system > const > eq_ref > ref > range > index > all
6、possible_keys
查询中可以利用的索引，提示可以使用哪个索引来优化查询，如果为null，表示没有索引可利用
7、key
使用的索引
8、key_len
使用索引的键长度
9、ref
是通过常量（const）还是通过某个表的字段来过滤的
10、rows
结果集记录条数
11、filetered
按表条件过滤的行的百分比，5.7以前需要使用 explain extended命令显示，默认不显示
如果行数是1000，过滤比是50（50%），那么过滤后的行数就是1000*50% = 500
12、Extra
常用的类型有以下几种
（1）distinct：在select部分使用了distinc关键字
（2）no tables used：不带from字句的查询或者From dual查询
（3）using filesort：排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中
（4）using index：索引覆盖，查询时不需要回表查询，直接通过索引就可以获取查询的数据。
（5）using join buffer（block nested loop），using join buffer（batched key accss）：5.6.x之后的版本优化关联查询的BNL，BKA特性。主要是减少内表的循环数量以及比较顺序地扫描查询。
（5）using temporary：必须使用临时表，常见于order by 和 group by语句中
（6）using where：不是读取表中的所有数据，或者不仅通过索引获取所需数据时，会出现
（7）select tables optimized away：使用聚合函数访问存在索引的某个字段

2、profiling的用法

profiling可以定位一条query的性能瓶颈在哪里？可以看出CPU计算太多还是操作IO次数太多，从而针对具体的问题优化。
profile使用方法
1、开启profiling参数
命令是：

set profiling = 1;

开启profiling
2、执行query
例如：执行如下query

select usename,count(*) from tb group by usename;

select结果
3、查看profiling概要信息
执行命令

show profiles;

可以看到刚才执行的query记录和耗时情况

show profiles
4、查看query执行的详细情况
执行命令获取具体某条query的具体cpu和IO操作情况

show profile cpu,block io for query 1;

结果：

show profile 具体某条query的结果

根据上面的表格的情况，可以很清楚的看到每条query使用cpu和IO操作的情况

3、实例分析

---

参考资料：
1、《MySQL性能调优与架构设计》
2、https://www.cnblogs.com/tufujie/p/9413852.html
3、https://www.jianshu.com/p/73f2c8448722
4、https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-output-columns