如何查看MySQL执行计划

200次阅读

共计 5982 个字符，预计需要花费 15 分钟才能阅读完成。

介绍下怎么查看 MySQL 执行计划前，我们先来看个后面会提到的名词解释：

覆盖索引：MySQL 可以利用索引返回 select 列表中的字段，而不必根据索引再次读取数据文件包含所有满足查询需要的数据的索引称为覆盖索引（Covering Index）如果要使用覆盖索引，一定要注意 select 列表中只取出需要的列，不可 select *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降

EXPLAIN 查看执行计划的一些局限：

EXPLAIN 不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
EXPLAIN 不考虑各种 Cache
EXPLAIN 不能显示 MySQL 在执行查询时所作的优化工作
部分统计信息是估算的，并非精确值
EXPALIN 只能解释 SELECT 操作，其他操作要重写为 SELECT 后查看执行计划

*************************** 1. row ***************************
id: 1 <br> select_type: SIMPLE
table: t1
type: ref
possible_keys: idx_customer_no
key: idx_customer_no
key_len: 99
ref: const
rows: 1
Extra: Using index condition; Using where 1 row in set (0.00 sec)

id：是一组数字，表示查询中执行 select 子句或操作表的顺序。如果 id 相同，则执行顺序从上至下；如果是子查询，id 的序列号会递增，id 越大则优先级越高，越先会被执行。id 如果相同，则可以认为是一组，从上往下顺序执行，所有组中，id 越高，优先级越高，越容易执行。

select_type: 取值有 simple,primary,subquery,derived,union,unionresult

simple: 表示查询中不包含子查询或者 union
primary: 当查询中包含任何复杂的子部分，最外层的查询被标记成 primary
subquery: 在 select 或 where 列表中包含了子查询，则子查询被标记成 subquery
derived: 在 from 的列表中包含的子查询被标记成 derived
union: 若第二个 select 出现在 union 后，则被标记成 union, 若 union 在 from 子句的子查询中，外层的 select 被标记成 derived
unionresult：从 union 表获取结果的 select 被标记成 union result

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

type: 访问类型，表示在表中找到所需行的方式，常见的类型有 all,index,range,ref,eq_ref,const,system,null 性能从左至右由差至好。

ALL：即 full table scan，mysql 将遍历全表来找到所需要的行
index:full index scan，只遍历索引树
range：表示索引范围扫描，对索引的扫描开始于一点，返回匹配值域的行，常见于 between，<,> 的查询
ref：为非唯一性索引扫描，返回匹配某个单独值的所有行，常见于非唯一索引即唯一索引的非唯一前缀进行的查找。
eq_ref: 表示唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或者唯一索引扫描。
const,system 表示当对查询部分进行优化，并转化成一个常量时，使用这些类型访问。比如将主键置于 where 列表中，mysql 就能把该查询置成一个常量。system 是 const 的一个特例，当查询表中只有一行的情况下使用的是 system。null 表示在执行语句中，不用查表或索引。

possiblekey: 表示能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引被列出，但不一定被查询使用。

key：表示查询时使用的索引。若查询中使用了覆盖索引，则该索引仅出现在 key 中。

key_len: 表示索引所使用的字节数，可以通过该列结算查询中使用的索引长度

ref：表示上述表的链接匹配条件，即哪些列或常量可被用于查找索引列上的值。

rows：表示根据 mysql 表统计信息及索引选用情况，估算找到所需记录要读取的行数。

extra：表示不在其他列并且也很重要的额外信息。

using index 表示相应的 select 中使用了覆盖索引。
usingwhere 表示存储引擎搜到记录后进行了后过滤（POST-FILTER）, 如果查询未能使用索引，usingwhere 的作用只是提示我们 mysql 要用 where 条件过滤 Z 结果集。
using temporay 表示临时表来存储结果集，常见于排序和分组查询。
using filesort mysql 中无法用索引完成的排序成为文件排序

extra 值参考列表

distinct：当 mysql 找到第一条匹配的结果值时，就停止该值的查询，然后继续该列其他值的查询。

not exists：在左连接中，优化器可以通过改变原有的查询组合而使用的优化方法。当发现一个匹配的行之后，不再为前面的行继续检索，可以部分减少数据访问的次数。例如，表 t1、t2，其中 t2.id 为 not null，对于 SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL; 由于 t2.id 非空，所以只可能是 t1 中有，而 t2 中没有，所以其结果相当于求差。left join 原本是要两边 join，现在 Mysql 优化只需要依照 t1.id 在 t2 中找到一次 t2.id 即可跳出。

const row not found：涉及到的表为空表，里面没有数据。

Full scan on NULL key：是优化器对子查询的一种优化方式，无法通过索引访问 NULL 值的时候会做此优化。

Impossible Having： Having 子句总是 false 而不能选择任何列。例如 having 1=0

Impossible WHERE： Where 子句总是 false 而不能选择任何列。例如 where 1=0

Impossible WHERE noticed after reading const tables： mysql 通过读取“const/system tables”，发现 Where 子句为 false。也就是说：在 where 子句中 false 条件对应的表应该是 const/system tables。这个并不是 mysql 通过统计信息做出的，而是真的去实际访问一遍数据后才得出的结论。当对某个表指定了主键或者非空唯一索引上的等值条件，一个 query 最多只可能命中一个结果，mysql 在 explain 之前会优先根据这一条件查找对应记录，并用记录的实际值替换 query 中所有用到来自该表属性的地方。例如：select * from a,b where a.id = 1 and b.name = a.name 执行过程如下：先根据 a.id = 1 找到一条记录(1, ‘name1’)，然后将 b.name 换成 ’name1’，然后通过 a.name = ‘name1’ 查找，发现没有命中记录，最终返回“Impossible WHERE noticed after reading const tables”。

No matching min/max row：没有行满足如下的查询条件。例如：EXPLAIN SELECT MIN(actor_id) FROM actor WHERE actor_id > 3(只有两条记录)actor_id 为唯一性索引时，会显示“No matching min/max row”，否则会显示“using where”。

no matching row in const table：对一个有 join 的查询，包含一个空表或者没有数据满足一个唯一索引条件。

No tables used：查询没有 From 子句，或者有一个 From Dual(dual：虚拟表，是为了满足 select…from… 习惯)子句。例如：EXPLAIN SELECT VERSION()

Range checked for each record (index map: N)： Mysql 发现没有好的 index，但发现如果进一步获取下一张 join 表的列的值后，某些 index 可以通过 range 等使用。Mysql 没找到合适的可用的索引。取代的办法是，对于前一个表的每一个行连接，它会做一个检验以决定该使用哪个索引（如果有的话），并且使用这个索引来从表里取得记录。这个过程不会很快，但总比没有任何索引时做表连接来得快。

Select tables optimized away：当我们使用某些聚合函数来访问存在索引的某个字段时，优化器会通过索引直接一次定位到所需要的数据行完成整个查询。在使用某些聚合函数如 min,max 的 query，直接访问存储结构 (B 树或者 B + 树) 的最左侧叶子节点或者最右侧叶子节点即可，这些可以通过 index 解决。Select count(*) from table(不包含 where 等子句)，MyISAM 保存了记录的总数，可以直接返回结果，而 Innodb 需要全表扫描。Query 中不能有 group by 操作。

unique row not found：对于 SELECT … FROM tbl_name，没有行满足 unique index 或者 primary key。从表中查询 id 不存在的一个值会显示 Impossible WHERE noticed after reading const tables。

Using filesort：指 Mysql 将用外部排序而不是按照 index 顺序排列结果。数据较少时从内存排序，否则从磁盘排序。Explain 不会显示的告诉客户端用哪种排序。

Using index：表示 Mysql 使用覆盖索引避免全表扫描，不需要再到表中进行二次查找数据。注意不要和 type 中的 index 类型混淆。

Using index for group-by：类似 Using index，所需数据只需要读取索引，当 query 中有 group by 或 distinct 子句时，如果分组字段也在索引中，extra 就会显示该值。

Using temporary： Mysql 将创建一个临时表来容纳中间结果。在 group by 和 order by 的时，如果有必要的话。例如 group by 一个非键列，优化器会创建一个临时表，有个按照 group by 条件构建的 unique key，然后对于每条查询结果（忽略 group by），尝试 insert 到临时表中，如果由于 unique key 导致 insert 失败，则已有的记录就相应的 updated。例如，name 上没有索引，SELECT name,COUNT(*) FROM product GROUP BY name，为了排序，Mysql 就需要创建临时表。此时一般还会显示 using filesort。

Using where：表示 Mysql 将对 storage engine 提取的结果进行过滤。例如，price 没有 index，SELECT * FROM product WHERE price=1300.00。有许多 where 的条件由于包含了 index 中的列，在查找的时候就可以过滤，所以不是所有带 where 子句的查询会显示 Using where。Using join buffer：5.1.18 版本以后才有的值。join 的返��列可以从 buffer 中获取，与当前表 join。例如：explain select * from t1,t2 where t1.col < 10 and t2.col < 10

Scanned N databases：指在处理 information_schema 查询时，有多少目录需要扫描。例如：EXPLAIN SELECT TABLE_NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES 网上说这个查询会显示 Scanned all databases，我试了下 extra 列是空。Skip_open_table, Open_frm_only, Open_trigger_only, Open_full_table：指示从 information_schema 查询信息时有关文件开启的优化。Skip_open_table：表信息已经获得，不需要打开。Open_frm_only：只打开.frm 文件。Open_trigger_only：只打开.trg 文件。Open_full_table：没有优化。.frm，.myd 和.myi 文件都打开。

Using sort_union(…), Using union(…), Using intersect(…)：都出现在 index_merge 读取类型中。Using sort_union：用两个或者两个以上的 key 提取数据，但优化器无法确保每个 key 会提取到一个自然排好序的结果，所以为了排除多余的数据，需要额外的处理。例如，customer 的 state，（lname，fname）是 key，但 lname 不是 key，SELECT COUNT(*) FROM customer WHERE (lname =‘Jones’) OR (state =‘UT’)，由于 lname 上面没有 key，所以使用（lname，fname），使得结果可能不按照顺序，优化器需要额外的一些工作。Using union：用两个或者两个以上的 key 提取数据，分别取得结果是已排序，通过合并就可以获得正确结果。例如，customer 中的 state 和（lname，fname）是 key，SELECT COUNT(state) FROM customer WHERE (lname =‘Jones’ AND fname=’John’) OR (state =‘UT’)。Using intersect：用两个或者两个以上的 key 提取数据，分别取得结果是已排序，通过求交就可以获得正确结果。例如，customer 中的 state 和（lname，fname）是 key，SELECT COUNT(state) FROM customer WHERE (lname =‘Jones’ AND fname=’John’) AND (state =‘UT’)。

Using where with pushed condition：仅用在 ndb 上。Mysql Cluster 用 Condition Pushdown 优化改善非索引字段和常量之间的直接比较。condition 被 pushed down 到 cluster 的数据节点，并在所有数据节点同时估算，把不合条件的列剔除避免网络传输。

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-08/134772.htm

正文完

星哥玩云-微信公众号