执行计划(EXPLAIN)分析SQL

什么是执行计划?

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句, 从而知道MySQL是如何处理你的SQL语句的. 分析你的查询语句或是表结构的性能瓶颈.

执行计划的作用

1.确认表的执行顺序.
2.哪些索引可以使用? 哪些索引被具体使用?
3.每张表被扫表查询的行数等.

执行计划语法

在查询sql语句前面加上EXPLAIN即可. 例: explain select * from User

执行计划包含的信息:

image.png

1. ID列

描述select查询的执行序号, 包含一组数字. 表示查询中执行select子句或操作表的顺序.

根据ID数值分以下情况:

• Id值相同, 执行顺序自上而下.
• Id值不同, 如果是子查询, Id值会顺序递增, Id值越大, 执行的优先级越高.
• 同时存在上面两种情况. 根据Id值优先级执行, 相同的值仍会自上而下执行.

2. select_type列

查询的类型.(主要用于区别普通查询, 联合查询,子查询等复杂的查询)
具体类型:

• SIMPLE: 简单的select查询,不包含子查询或者UNION.
• PRIMARY: 查询中若包含任何复杂的子部分,最外层的则会被标记.
• SUBQUERY: 在select或where列表中包含了子查询.
• DEPENDENT SUBQUERY: 在select或where列表中包含了子查询, 取决于外部查询.
• UNCACHEABLE SUBQUERY: 结果集无法缓存的子查询，必须重新评估外部查询的每一行.
• DERIVED: 衍生表(FROM子句中的子查询),MySql会递归执行这些子查询,把结果放到临时表里.
• UNION: 若第二个select出现在UNION之后,则被标记为UNION.
• DEPENDENT UNION: 若第二个select出现在UNION之后, 取决于外部查询.
• UNCACHEABLE UNION: UNION中第二个或之后的SELECT，属于无法缓存的子查询.
• UNION RESULT: 从UNION表获取结果的select.

3. table列

显示这一行的数据是关于哪张表的.

4. partitions列

该列显示的为分区表命中的分区情况. 非分区表该字段为空(null).

5. type

按照最好到最差的连接类型依次为：system, const, eq_ref, ref, fulltext, ref_or_null, index_merge, unique_subquery, index_subquery, range, index, ALL
除了ALL之外, 其他的type都可以使用到索引, 除了index_merge之外, 其他的type只可以用到一个索引.

• system: 表中只有一行数据或者是空表, 这是const类型的一个特例. 且只能用于myisam和memory表. 如果是Innodb引擎表, type列在这个情况通常都是all或者index.
• const: 最多只有一行记录匹配. 当联合主键或唯一索引的所有字段跟常量值比较时, join类型为const. 其他数据库也叫做唯一索引扫描.
• eq_ref: 多表join时, 对于来自前面表的每一行, 在当前表中只能找到一行. 这是除了system和const之外最好的类型. 当主键或唯一非NULL索引的所有字段都被用作join联接时会使用此类型.
  可用于使用’='操作符作比较的索引列. 比较的值可以是常量, 也可以是使用在此表之前读取的表的列的表达式.
• ref: 对于来自前面表的每一行, 在此表的索引中可以匹配到多行. 若联接只用到索引的最左前缀或索引不是主键或唯一索引时, 使用ref类型(也就是说, 此联接能够匹配多行记录). ref可用于使用’=‘或’<=>'操作符作比较的索引列.
  eq_ref相对于ref区别就是它使用的唯一索引, 即主键或唯一索引, 而ref使用的是非唯一索引或者普通索引. eq_ref只能找到一行, 而ref能找到多行.
• fulltext: 使用全文索引的时候是这个类型. 要注意, 全文索引的优先级很高, 若全文索引和普通索引同时存在时, mysql不管代价, 优先选择使用全文索引
• ref_or_null: 跟ref类型类似, 只是增加了null值的比较. 实际用的不多.
• index_merge: 表示查询使用了两个以上的索引, 最后取交集或者并集, 常见and, or的条件使用了不同的索引, 官方排序这个在ref_or_null之后, 但是实际上由于要读取多个索引, 性能可能大部分时间都不如range
• unique_subquery: 用于where中的in形式子查询, 子查询返回不重复值唯一值, 可以完全替换子查询, 效率更高.
• index_subquery: 该联接类型类似于unique_subquery. 适用于非唯一索引, 可以返回重复值.
• range: 索引范围查询, 常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中.
• index: 索引全表扫描, 把索引从头到尾扫一遍. 这里包含两种情况:
  一种是查询使用了覆盖索引, 那么它只需要扫描索引就可以获得数据, 这个效率要比全表扫描要快, 因为索引通常比数据表小, 而且还能避免二次查询. 在extra中显示Using index, 反之, 如果在索引上进行全表扫描, 没有Using index的提示.
• ALL: 全表扫描, 性能最差.
  我们常用到的级别: system>const>eq_ref>ref>range>index>all. 日常开发中我们要保证最少能达到range级别,最好能达到ref级别.

6. partitions_keys

显示了MySQL在查找当前表中数据的时候可能使用到的索引, 实际意义不大

7. key

显示了MySQL在实际查找数据时决定使用的索引, 如果该字段值为NULL, 则表明没有使用索引

8. key_len

• Key_len表示索引中使用的字节数, 可通过该列计算查询中使用的索引的长度. 在不损失精确性的情况下, 长度越短越好
• key_len显示的值为索引字段的最大可能长度, 并非实际使用长度, 即key_len是根据表定义计算而得, 不是通过表内检索出的
• key_len表示索引使用的字节数,根据这个值,就可以判断索引使用情况,特别是在组合索引的时候,判断所有的索引字段是否都被查询用到
  char和varchar跟字符编码也有密切的联系. latin1占用1个字节 gbk占用2个字节 utf8占用3个字节. （不同字符编码占用的存储空间不同）
如果字段允许为NULL, 则需要额外增加一个字节

9. ref

如果是使用的常数等值查询,这里会显示const,如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段,如果是条件使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能显示为func
列值一般为: 数据库.表.列,const

10. rows

这是mysql估算的需要扫描的行数(不是精确值). 这个值非常直观显示 SQL的效率好坏, 原则上rows越少越好

11. filtered

这个字段表示存储引擎返回的数据在server层过滤后, 剩下多少满足查询的记录数量的比例, 注意是百分比, 不是具体记录数(新特性)

12. extra

包含不适合在其他列中显示但十分重要的额外信息

• Using filesort: MySQL需要对获取的数据进行额外的一次排序操作, 无法通过索引的排序完成. 通常发生在有ORDER BY子句的语句当中.*出现这种情况一般是可以优化的
• Using temporary: MySQL需要创建临时表来存放查询结果集. 通常发生在有GROUP BY或ORDER BY子句的语句当中.
  *以上两种情况都应该注意,都是可以根据场景进行优化的. 例: Using temporary可以考虑使用联合索引
• using index: 仅查询索引树就可以获取到所需要的数据行, 而不需要读取表中实际的数据行. 通常适用于select字段就是查询使用索引的一部分, 即使用了覆盖索引.*这种情况无需优化
• Using index condition: 显示采用了Index Condition Pushdown(ICP)特性通过索引去表中获取数据.
  关于ICP特性可以参考官方文档:Index Condition Pushdown Optimization
  简单说法如下:
1.如果开启ICP特性, 部分where条件部分可以下推到存储引擎通过索引进行过滤, ICP可以减少存储引擎访问基表的次数
2.如果没有开启ICP特性, 则存储引擎根据索引需要直接访问基表获取数据并返回给server层进行where条件的过滤
• Using where: 显示MySQL通过索引条件定位之后还需要返回表中获得所需要的数据.
• Impossible WHERE: where子句的值总是false, 永远不可能根据条件获取到元素.
• Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access): 表示使用了连接缓存, 不需要做什么处理.

执行计划记录完结