【MySQL笔记】执行计划（explain）

mysql中的explain命令可以查看sql语句是否使用了索引，用了什么索引，有没有全表扫描，帮助我们优化查询语句

查看索引

SHOW INDEX FROM 表名 

EXPLAIN

explain出来的信息有10列，主要介绍type、key、Extra这几个字段

id

select 查询的序列号，表示查询中执行 select 子句或者操作表的顺序（id相同，执行顺序从上到下；id不同，值越大越先执行），单表查询，只有一条记录，多表查询会有多条记录。

select_type

表示 SELECT 的类型，常见取值有 SIMPLE（简单表，即不适用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等

key

sql语句实际执行时使用的索引列，有时候mysql可能会选择优化效果不是最好的索引，这时我们可以在select语句中使用force index(indexName)强制mysql使用指定索引，或使用ignore index(indexName)强制mysql忽略指定索引
force案例
强制查询使用ix_UserID索引

EXPLAIN select * from order_master force index(`ix_UserID`) where userid=5

使用了ix_UserID索引
ignore案例
强制查询不要使用ix_UserID索引

EXPLAIN select * from order_master ignore index(`ix_UserID`) where userid=5

没有使用索引，type是all，全表扫描

key_length

通过该列可以计算出使用到的索引占得字节数。在不适用精度的情况下长度越小越好

type

访问类型，表示数据库引擎查找表的方式，常见的type类型如下：

• all
  全表扫描，效率最低

EXPLAIN select * from user_info;
EXPLAIN select email from user_info; -- email未加索引

• index
  全索引扫描，表示sql语句将会把整颗二级索引树全部扫描一遍，效率比all高一些。一般SELECT子句中查询字段为索引字段，且无WHERE子句时，type会为index。如下：

EXPLAIN select ID from user_info;

• range
  部分索引扫描，当查询为区间查询，且WHERE子句中查询字段为索引字段时，type为range

EXPLAIN select * from user_info where id>11;

• ref
  where子句中，操作符为‘=’，且where字段为非唯一索引的单表查询或联表查询

EXPLAIN select * from order_master where userid=2 --userid是非唯一索引

• eq_ref
  where子句中，操作符为‘=’，且where字段为唯一索引的联表查询

EXPLAIN select * from user_info u,order_master o where u.id=o.userid 

• const
  where子句中，操作符为‘=’，且字段为唯一索引的单表查询，此时最多会匹配的一行

EXPLAIN select * from user_info where id=2;

综上，单从type字段考虑效率，const > ref_eq > ref > range > index > all

但是，我们不能仅根据type去判断两条sql的执行速度，例如type是range的查询不一定比type是index的全表查询速度快，因为type是index时不需要回表查询，而type是range时，有可能需要回表查询

Extra

extra列会包含一些十分重要的信息，我们可以根据这些信息进行sql优化

• using index
  sql没有where子句，使用覆盖索引，不需要回表查询即可拿到结果
• using where
  需要回表查询且没有使用到下推索引
• using index && using where
  sql语句有where查询条件，且使用覆盖索引，不需要回表查询即可拿到结果
• using index condition
  使用了索引，但是需要回表查询
• using index condition && using where
  需要回表查询
• using filesort
  语句中存在order by，无法利用索引排序，消耗性能
• using temporary
  建立了临时表来保存中间结果，查询完成之后又要把临时表删除，会影响性能，需尽快优化（常见于排序order by、分组查询group by）
• impossible hwere
  where子句的值总是false，永远查不到数据，逻辑问题尽快修改

rows

根据表统计信息及索引选用情况，大致估算出找到所需记录，需要读取的行数，越小越好

show profile

show profile用来分析当前会话中语句执行的资源消耗情况，可以用于sql的调优
在使用explain查看执行计划后如果还看不出为什么sql执行慢的原因，此时可以使用show profile，可以更细粒度的查看sql的执行过程，诸如IO，上下文切换，CPU，Memory等等。根据这些开销进一步分析当前SQL瓶颈从而进行优化与调整。
默认情况下，处于关闭状态，并保存最近15次的运行结果

查看当前是否开启：

SHOW VARIABLES LIKE 'profiling'

开启：

SET profiling=ON

查看profile：（最近15条）

SHOW PROFILES

诊断sql语法：

SHOW PROFILE [type [, type] ... ]  
    [FOR QUERY n]  
    [LIMIT row_count [OFFSET offset]]  

type:
ALL --显示所有的开销信息
| BLOCK IO --显示块IO相关开销
| CONTEXT SWITCHES --上下文切换相关开销
| CPU --显示CPU相关开销信息
| IPC --显示发送和接收相关开销信息
| MEMORY --显示内存相关开销信息
| PAGE FAULTS --显示页面错误相关开销信息
| SOURCE --显示和Source_function，Source_file，Source_line相关的开销信息
| SWAPS --显示交换次数相关开销的信息

案例、诊断sql,查看cpu、io开销：

SHOW PROFILE cpu,block io for query QueryID　　　　# QueryID可从SHOW PROFILES中查到