2、mysql工具explain trace

explain用于显示mysql对sql语句的执行计划，如语句的分解情况，是否使用了索引，表关联使用何种算法，如何排序等。 通过mysql的执行计划，结合mysql数据结构可以分析出该sql执行效率，然后进行优化。

explain [extended] sql 5.7之前 extended用于展示partitions和filtered属性，5.7后自动加上了。

explain各列的意义

id

用户sql语句中拆分出的select编号，编号越大优先级越高

select_type

• • simple，简单查询 select * from t;
  • primary，存在多查询时最外层查询 select (select...) from t;
  • subquery，select中的子查询 select (select...) from t;
  • derived，派生查询，会产生临时表，from中的子查询 select * from (select...) t;
  • union，union查询中的后半部门查询 select ... union select...

table

查询所在的表，查询临时表为derived

partition

存在分区时，所在分区

type

关联表类型或访问类型，按最优排序 system>const>eq_ref>ref>range>index>ALL; 一般要达到range以上

1、system，const，常量查询，直接给主键指定一个常量，如id=1，因此只会读取一次，速度很快；system为const的特例，效率更高。

select * from t where id=1;

2、eq_ref，关联查询时，关联条件为主键或唯一索引，结果最多返回一条。

select * from t1 left join t2 on t2.id = t1.index_t2

3、ref， 不使用唯一索引，用普通索引或唯一索引前缀，可能有多行返回

select * from t where index = 1;

4、range，通过索引进行的范围查询，通常为利用索引进行in < > between条件查询

select * from t where index>2;

5、index，扫描非聚集索引，因为是整个树扫描，所以一般从非聚集索引的叶子节点进行全体遍历，速度较慢

select index from t;

6、ALL，全表扫描，对聚集索引的叶子节点全体遍历，需要增加索引优化

select * from t;

possible_keys

预计要使用的索引，若实际没使用，则是经过mysq内部的优化判断，不使用索引反而更快。若为null，则需要创建索引进行优化。

key

实际使用的索引

key_len

使用索引的字节长度，可以推算出在联合索引中实际都使用了哪些列

ref

查询中索引使用的值，常量或字段名

rows

查询需要扫描的行数

filtered

百分比值，估算与前一个id表的连接行数，rows*filtered/100

Extra

类型众多，结果不是很精确，仅供参考

Using index 使用覆盖索引，通过索引可直接查询到结果，不需回表查询。

select index from t;

Using where 使用where条件查询结果，查询的列未被索引覆盖

select * from t where no_index = 1;

Using index condition 查询的列不完全被索引覆盖，存在前导列查询

select * from t where index like 'QQ%'

Using temporary 使用了临时表，需要进行优化

select distinct no_index from t;

Using filesort 使用了文件排序，因排序的值不是索引，所以数据是无序的，需要排序，mysql根据数据大小载入内存或硬盘，进行数据排序；需要优化

select * from t order by no_index;

trace

explain实际并未执行语句， 只是mysql的执行计划，实际执行时还会结合实际数据或环境做变动。若想要看的mysql实际的执行策略，需要使用trace跟踪工具。

trace需要手动开启，因为会输出大量执行过程，对性能影响很大，只适合调试使用。

trace工具主要用于查看mysql是否使用索引，使用哪个索引的成本分析和选择，方面开发人员对sql进行优化。

开启：set session optimizer_trace='enabled=on',end_markers_in_json=on;

执行查询语句后执行 select * from information_schema.OPTIMIZER_TRACE; 得到结果：

steps步骤

• join_preparation 准备阶段
  • expanded_query展开的sql
• join_optimization 优化阶段
  • condition_processing 条件处理
  • table_dependencies 表依赖
  • rows_estimation 预估查询成本
    • table_scan.rows 全表扫描行数
    • table_scan.cost 全表扫描成本
    • potential_range_indexes 查询使用的索引情况
      • index.usable 是否可用
    • analyzing_range_alternatives 分析各索引使用成本
      • rowid_ordered 使用该索引获取的记录是否按照主键排序
      • index_only 是否使用覆盖索引
      • rows 索引扫描行数
      • cost 索引使用成本
      • chosen 是否选择使用该索引
  • best_access_path 最优访问路径
    • considered_access_paths 最终选择的访问路径
      • access_type 访问类型 scan全表
      • cost
      • chosen
• join_execution 执行阶段