mysql调优：执行计划、optimizer_trace 查看执行流程、调优

SQL执行计划

1、如何查看sql的执行计划

在需要执行的查询SQL前添加一个关键字“EXPLAIN”

EXPLAIN中的列

（1）id

1、标识select所属的行，sql语句中有多少个select就有多少个id，并且id的顺序是按照select出现的顺序增长的

2、id越大，越先执行

3、id相同，从上往下执行

4、id为NUll的最后执行

  (2) select_type

显示对应行是简单还是复杂select，SIMPLE值表明没有子查询或Union，如果有子查询，那么最外层标记为Primary

  (3) table

显示当前行的数据来自于哪一张表

（4）type

访问类型，结果值从好到坏依次是：NULL>system>const>eq_ref>ref>range>index>ALL

一般来说，保证查询至少能到达range级别，最好能达到ref。

（1）system 表中只有一行数据（系统表）

（2）const 通过索引一次就能找到的数据，比如primary key 和union key，主键在where条件中，就能将查询转换成一个常量。比如：student表中id是主键

（3）eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

（4）ref 非唯一性索引扫描，返回匹配某个单独值得所有行 比如：student表中的age是一个普通索引

（5）range

1、只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引

2、一般就是where条件中出现“>”、“<”、“between”、“in”等条件

3、这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

(6) All 全表扫描，不使用任何条件或索引。比如：student表中score字段是没有设置索引的。

2.optimizer_trace查看执行流程

Optimizer Trace 是MySQL 5.6.3里新加的一个特性，可以把MySQL Optimizer的决策和执行过程输出成文本，结果为JSON格式，兼顾了程序分析和阅读的便利。

利用performance_schema库里面的session_status来统计innodb读取行数
利用performance_schema库里面的optimizer_trace来查看语句执行的详细信息

使用如下步骤来执行

#0. 如果前面有开启 optimizer_trace 则先关闭
SET optimizer_trace="enabled=off";

#1. 开启 optimizer_trace
SET optimizer_trace='enabled=on';

#2. 记录现在执行目标 sql 之前已经读取的行数
select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';

#3. 执行我们需要执行的 sql
todo

#4. 查询 optimizer_trace 详情
select trace from `information_schema`.`optimizer_trace`\G;

#5. 记录现在执行目标 sql 之后读取的行数
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

#6. 读取的行数
select @b-@a;

considered_execution_plans遍历所有可能执行的顺序路径，以及选择原因。由此可知sql执行速度和sql里join的顺序是无关的，因为优化器会遍历估算可能的执行时间，选择cost最优的。

3.调优思路

join的优化

小表join大表

join替代子查询

参考:

https://segmentfault.com/a/1190000018136007 Mysql 使用 optimizer_trace 查看执行流程，分析、验证优化思路

https://www.jianshu.com/p/8db16b21910e count(*)会造成全表扫描吗

https://www.jianshu.com/p/5ea620b4bf50 explain详解

https://zhuanlan.zhihu.com/p/351174217 一次SQL查询优化原理分析（900W+数据，从17s到300ms）

https://zhuanlan.zhihu.com/p/355302417 mysql单表2000w为何查询性能下降

https://dev.mysql.com/doc/internals/en/optimizer-tracing.html 官方文档