执行计划概述

MySQL

接收一条Query后,会进行SQL解析,生成语法树,接下来会生成执行计划,选择最优的执行计划进行执行,对于一条SQL,可能有多种执行计划,观察和分析执行计划可以帮助开发人员以及DBA优化SQL.

With the help of EXPLAIN, you can see where

you should add indexes to tables so that the statement executes

faster by using indexes to find rows. You can also use EXPLAIN to

check whether the optimizer joins the tables in an optimal

order To give a hint to the optimizer to use a

join order corresponding to the order in which the tables are named

in a SELECT statement, begin the statement with SELECT

STRAIGHT_JOIN rather than just SELECT.

执行计划

EXPLAIN

SELECT …..

EXPLAIN

EXTENDED

SELECT ….. 将执行计划”反编译”成SELECT语句,执行SHOW WARNINGS可以得到被MySQL优化器优化的查询语句.

EXPLAIN

PARTITIONS

SELECT ….. 用于分区表的EXPLAIN.

DESC/DESCRIBE

SELECT …… MySQL中使用DESC和EXPLAIN同样的效果.

执行计划解释

执行计划包含如下信息

+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+

id:

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

id相同,执行顺序由上至下

子查询中,id的序号会递增,id越大优先级越高,越先被执行.

id如果相同,可以认为是一个group,从上往下顺序执行;在所有的group中,id值越大,优先级越高,越先被执行

select_type: 表示查询中每个SELECT子句的类型.

+------+--------------+

| id | select_type |

+------+--------------+

| 1 | SIMPLE |

| 2 | PRIMARY |

| 3 | SUBQUERY |

| 4 | DERIVED |

| 5 | UNION |

| 6 | UNION RESULT |

+------+--------------+

SIMPLE : 查询中不包含子查询或者UNION.

PRIMARY: 查询中包含任何复杂的子部分,最外层查询被标记为PRIMARY.

SUBQUERY: 在SELECT或者WHERE列表中包含了子查询,该子查询被标记为SUBQUERY.

DERIVED: 在FROM列表中包含的子查询被标记为DERIVED.

UNION:

若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,则被标记为DERIVED.

UNION RESULT 从UNION表获取结果的SELECT被标记为UNION RESULT.

type:

表示MySQL执行查询时是如何获取所需行的,又称”访问类型”

+-----+-------+-------+-----+--------+--------------+------+

| ALL | index | range | ref | eq_ref | const,system | NULL |

+-----+-------+-------+-----+--------+--------------+------+

从左至右,执行效率右最差到最好

ALL: Full Table Scan,MySQL扫描全表以找到匹配的行.

index: Full Index Scan,index与ALL的区别在于index只遍历索引树

range: 索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行.

ref: 非唯一性索引扫描,返回匹配某个值的所有.

eq_ref: 唯一性索引扫描,对于某个索引键,表中只有一条记录与之匹配,常见于主键或者唯一索引查询.

const,system:

当MySQL优化部分查询,并转换为一个常量时,使用这些类型访问.如将主键置与where列表中,MySQL就能将该查询替换为一个常量.需要注意的是system是const的特殊类型,当查询的表只有一行的情况下使用system.

NULL: MySQL在优化过程中分解语句,执行时不用访问表或者索引.

possible_keys:

MySQL能够使用那个索引找到匹配的行,查询涉及到字段上若存在索引,则该索引将被列出,但是不一定被查询使用到.

key:

MySQL在查询中实际使用的索引,若没有使用索引,将显示为NULL.若使用了覆盖索引,该索引仅出现在key列表中.

key_len:

表示索引中使用的字节数,可以通过该列计算查询中使用索引的长度.(key_len

显示的值是索引字段的最大可能长度,并非使用长度,key_len是跟进表定义计算得到的,而不是检索表的实际内容得到的)

ref:

表示上述表的连接匹配条件,既那些列或者常量用于查询索引列上的值.

row:

表示MySQL根据表的统计信息及索引选用情况,估算找到匹配行所需要读取的行数.

Extra:

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

Using index: 表示相应的select语句中使用了覆盖索引.

Using where: 表示MySQL服务器在存储引擎收到记录进行”后过滤”,如果查询未能使用索引,Using

where只是提醒我们MySL将用where子句进行结果过滤.

Using temorary: 表示MySQL将使用临时表来存储结果集,常见于分组和排序查询.

Using filesort: MySQL将无法使用索引进行排序的的操作称为”file sort”.

MySQL执行计划的局限性

EXPLAIN不能解释触发器,存储过程或者用户自定义函数.

EXPLAIN不考虑CACHE对Query的影响.

EXPLAIN是基于统计信息的,统计信息并非准确值,有些估算的.

EXPLAIN只能解释SELECT语句,其他类型的Query需要重写为SELECT后查看.

EXPLAIN有时需要真实的执行某些SQL,线上操作需谨慎.