MySQL高级性能优化7---Explain分析

Explain

1.什么是Explain

• 其实就是模拟优化器执行SQL语句，从而让我们知道MYSQL是如何处理我们写的sql语句，进而分析查询语句和表结构的性能瓶颈。

2.作用

1. 查询表的读取顺序
2. 查看哪些索引可以使用以及实际使用
3. 查询每张表有多少行被优化器执行
4. 查看表之间的引用

3.实战分析

首先看看在sql前加上explain后的执行效果

1.id分析

• select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
• 值的三种情况
  • id相同
    • 执行顺序由上到下
  • id不同
    • id值越大，优先级越高，所以执行顺序从大到小
  • id同时存在相同和不同
    • 相同分为一组，顺序由上到下
    • 不同的，id值越大，越先执行

2.select_type分析

• 查询类型，主要用于区分普通查询，联合查询，子查询等复杂查询
• 结果值
  • SIMPLE
    • 简单查询不包括任何子查询，union查询
  • PRIMARY
    • 查询中包含任何复杂的子查询(包含衍生查询)，最外层都被标记成primary
  • SUBQUERY
    • 查询中包含子查询
  • DERIVED
    • 衍生查询，结果记录是一张临时表
  • UNION
    • 使用union查询
  • UNION RESULT
    • 两个union合并的结果集

3.table分析

• 显示这一行的数据是关于哪个表的

4.partitions分析

• 如果查询是基于分区表的话，会显示查询访问的分区

5.type分析

• 对表是进行什么样的扫描
• 结果值（最好到最差）
  • system
    • 表中有一行记录(系统表)，这是const类型的特例。平时不会出现
  • const
    • const用于比较primary或者unique索引，直接查询主键或者唯一 索引，因为只匹配一行数据，所以很快
  • eq_ref
    • 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用语主键或者唯一索引扫描。
  • ref
    • 非唯一性索引扫描，返回匹配某个单独值的行
  • range
    • 范围查询
  • index
    • 通过索引查询
  • ALL
    • 全表扫描
• 要求
  • 一般来说，保证查询至少达到range级别
  • 最好能达到ref

6.possible_keys分析

• 可能会使用到的索引

7.key分析

• 实际用到的索引

8.key_len分析

• 表示索引使用的字节数，可通过该列计算查询中使用的索引长度

9.ref分析

• 索引是否被引入到，到底引用了哪几个值的索引

10.rows分析

• 大致估算找到所需记录所需要读取的行数

11.filtered分析

• 满足查询的记录数量占扫描表数据的百分比
• 值越大越好，但并不一定准确

12.Extra分析

• 返回值分析
  • Using filesort
    • 说明mysql会对数据使用一个外部的索引排序
    • 不是按照表内索引顺序进行
    • Mysql中无法利用索引完成排序操作称为“文件排序”
  • Using temporary
    • 使用了临时表保存中间结果，Mysql在对查询结果排序时，使用了临时表
    • 常用于排序orderby，和分组查询group by
  • Using index
    • where判断字段都是索引
  • Using where
    • 使用了where条件
  • Using join buffer
    • 使用了连接缓存
  • impossible where
    • where子句的值总是false，不能用来获取任何元素

知是行之始，行是知之成