MySQL中EXPLAIN详解
EXPLAIN作为MySQL的性能分析神器，读懂其结果是很有必要的，然而我在各种搜索引擎上竟然找不到特别完整的解读。都是只有重点，没有细节（例如type的取值不全、Extra缺乏完整的介绍等）。文章目录...
EXPLAIN作为MySQL的性能分析神器，读懂其结果是很有必要的，然而我在各种搜索引擎上竟然找不到特别完整的解读。都是只有重点，没有细节（例如type的取值不全、Extra缺乏完整的介绍等）。
EXPLAIN使用
借助于EXPLAIN，可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT。
如果由于使用不正确的索引出现了问题，应运行ANALYZE TABLE更新表的统计（例如关键字集的势），这样会影响优化器进行的选择。
还可以知道优化器是否以一个最佳次序联接表。为了强制优化器让一个SELECT语句按照表命名顺序的联接次序，语句应以STRAIGHT_JOIN而不只是SELECT开头。
EXPLAIN为用于SELECT语句中的每个表返回一行信息。表以它们在处理查询过程中将被MySQL读入的顺序被列出。MySQL用一遍扫描多次联接（single-sweep multi-join）的方式解决所有联接。这意味着MySQL从第一个表中读一行，然后找到在第二个表中的一个匹配行，然后在第3个表中等等。当所有的表处理完后，它输出选中的列并且返回表清单直到找到一个有更多的匹配行的表。从该表读入下一行并继续处理下一个表。
当使用EXTENDED关键字时，EXPLAIN产生附加信息，可以用SHOW WARNINGS浏览。该信息显示优化器限定SELECT语句中的表和列名，重写并且执行优化规则后SELECT语句是什么样子，并且还可能包括优化过程的其它注解。
用法示例
EXPLAIN
SELECT
T.DEPT_CODE,
A.ORG_NAME DEPT_NAME,
T.ALARM_TIME,
T.STATUS,
B.DICT_NAME EXCEPTION_TYPE,
T.ALARM_USER,
1 NUMBER,
T.WEIGHT,
T.ABNORMAL_STATUS,
( CASE T.ABNORMAL_STATUS WHEN 0 THEN '未处置' ELSE '已处置' END ) AS DEAL_STATUS,
IFNULL( C.HANDLE_CONTENT, '' ) HANDLE_CONTENT
FROM
t_mw_waste_material T
JOIN t_sys_organization A ON A.ORG_CODE = T.DEPT_CODE
JOIN t_sys_dictionary B ON T.STATUS = B.DICT_CODE
JOIN t_mw_message_push C ON T.BAR_CODE = C.BAR_CODE
AND B.DICT_TYPE = '废弃物状态'
AND A.ORG_TYPE = 2
AND A.ORG_LEVEL = 5
AND A.IS_DELETED = 0
运行结果
输出结果字段说明
| 字段 | 含义 |
|---|---|
| id | 该语句的唯一标识 |
| select_type | 查询类型 |
| table | 表名 |
| partitions | 匹配的分区 |
| type | 联接类型 |
| possible_keys | 可能的索引选择 |
| key | 实际选择的索引 |
| key_len | 索引的长度 |
| ref | 索引的哪一列被引用了 |
| rows | 估计要扫描的行 |
| filtered | 表示符合查询条件的数据百分比 |
| Extra | 附加信息 |
字段结果解读
id
该语句的唯一标识。如果explain的结果包括多个id值，则数字越大越先执行；而对于相同id的行，则表示从上往下依次执行。
select_type
查询类型，有如下几种取值：
| 查询类型 | 作用 |
|---|---|
| SIMPLE | 简单查询（未使用UNION或子查询） |
| PRIMARY | 最外层的查询 |
| UNION | 在UNION中的第二个和随后的SELECT被标记为UNION。如果UNION被FROM子句中的子查询包含，那么它的第一个SELECT会被标记为DERIVED。 |
| DEPENDENT UNION | UNION中的第二个或后面的查询，依赖了外面的查询 |
| UNION RESULT | UNION的结果 |
| SUBQUERY | 子查询中的第一个 SELECT |
| DEPENDENT SUBQUERY | 子查询中的第一个 SELECT，依赖了外面的查询 |
| DERIVED | 用来表示包含在FROM子句的子查询中的SELECT，MySQL会递归执行并将结果放到一个临时表中。MySQL内部将其称为是Derived table（派生表），因为该临时表是从子查询派生出来的 |
| DEPENDENT DERIVED | 派生表，依赖了其他的表 |
| MATERIALIZED | 物化子查询 |
| UNCACHEABLE SUBQUERY | 子查询，结果无法缓存，必须针对外部查询的每一行重新评估 |
| UNCACHEABLE UNION | UNION属于UNCACHEABLE SUBQUERY的第二个或后面的查询 |
table
表示当前这一行正在访问哪张表，如果SQL定义了别名，则展示表的别名
partitions
当前查询匹配记录的分区。对于未分区的表，返回null
type
连接类型，有如下几种取值，性能从好到坏排序 如下：
- system：该表只有一行（相当于系统表），system是const类型的特例
- const：针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可
- eq_ref：当使用了索引的全部组成部分，并且索引是PRIMARY KEY或UNIQUE NOT NULL 才会使用该类型，性能仅次于system及const。
--多表关联查询，单行匹配
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
--多表关联查询，联合索引，多行匹配
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
- ref：当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行，性能也是不错的。
-- 根据索引（非主键，非唯一索引），匹配到多行
SELECT * FROM ref_table WHERE key_column=expr;
-- 多表关联查询，单个索引，多行匹配
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
-- 多表关联查询，联合索引，多行匹配
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
- fulltext：全文索引
- ref_or_null：该类型类似于ref，但是MySQL会额外搜索哪些行包含了NULL。这种类型常见于解析子查询
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;
- index_merge：此类型表示使用了索引合并优化，表示一个查询里面用到了多个索引
- unique_subquery：该类型和eq_ref类似，但是使用了IN查询，且子查询是主键或者唯一索引。例如：
value IN (SELECT primary_key FROM single_table WHERE some_expr)
- index_subquery：和unique_subquery类似，只是子查询使用的是非唯一索引
value IN (SELECT key_column FROM single_table WHERE some_expr)
- range：范围扫描，表示检索了指定范围的行，主要用于有限制的索引扫描。比较常见的范围扫描是带有BETWEEN子句或WHERE子句里有>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN()等操作符。
SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20;
SELECT * FROM tbl_name WHERE key_column IN (10,20,30);
- index：全索引扫描，和ALL类似，只不过index是全盘扫描了索引的数据。当查询仅使用索引中的一部分列时，可使用此类型。有两种场景会触发：
如果索引是查询的覆盖索引，并且索引查询的数据就可以满足查询中所需的所有数据，则只扫描索引树。此时，explain的Extra 列的结果是Using index。index通常比ALL快，因为索引的大小通常小于表数据。
按索引的顺序来查找数据行，执行了全表扫描。此时，explain的Extra列的结果不会出现Uses index。
- ALL：全表扫描，性能最差。
possible_keys
展示当前查询可以使用哪些索引，这一列的数据是在优化过程的早期创建的，因此有些索引可能对于后续优化过程是没用的。
key
表示MySQL实际选择的索引
key_len
索引使用的字节数。由于存储格式，当字段允许为NULL时，key_len比不允许为空时大1字节。
ref
表示将哪个字段或常量和key列所使用的字段进行比较。
如果ref是一个函数，则使用的值是函数的结果。要想查看是哪个函数，可在EXPLAIN语句之后紧跟一个SHOW WARNING语句。
rows
MySQL估算会扫描的行数，数值越小越好。
filtered
表示符合查询条件的数据百分比，最大100。用rows × filtered可获得和下一张表连接的行数。例如rows = 1000，filtered = 50%，则和下一张表连接的行数是500。
Extra
展示有关本次查询的附加信息，取值如下：
- Child of ‘table’ pushed join@1
此值只会在NDB Cluster下出现。
- const row not found
例如查询语句SELECT … FROM tbl_name，而表是空的
- Deleting all rows
对于DELETE语句，某些引擎（例如MyISAM）支持以一种简单而快速的方式删除所有的数据，如果使用了这种优化，则显示此值
- Distinct
查找distinct值，当找到第一个匹配的行后，将停止为当前行组合搜索更多行
- FirstMatch(tbl_name)
当前使用了半连接FirstMatch策略
- Full scan on NULL key
子查询中的一种优化方式，在无法通过索引访问null值的时候使用
- Impossible HAVING
HAVING子句始终为false，不会命中任何行
- Impossible WHERE
WHERE子句始终为false，不会命中任何行
- Impossible WHERE noticed after reading const tables
MySQL已经读取了所有const（或system）表，并发现WHERE子句始终为false
- LooseScan(m…n)
当前使用了半连接LooseScan策略
- No matching min/max row
没有任何能满足例如 SELECT MIN(…) FROM … WHERE condition 中的condition的行
- no matching row in const table
对于关联查询，存在一个空表，或者没有行能够满足唯一索引条件
- No matching rows after partition pruning
对于DELETE或UPDATE语句，优化器在partition pruning（分区修剪）之后，找不到要delete或update的内容
- No tables used
当此查询没有FROM子句或拥有FROM DUAL子句时出现。例如：explain select 1
- Not exists
MySQL能对LEFT JOIN优化，在找到符合LEFT JOIN的行后，不会为上一行组合中检查此表中的更多行。
- Plan isn’t ready yet
使用了EXPLAIN FOR CONNECTION，当优化器尚未完成为在指定连接中为执行的语句创建执行计划时， 就会出现此值。
- Range checked for each record (index map: N)
MySQL没有找到合适的索引去使用，但是去检查是否可以使用range或index_merge来检索行时，会出现此提示。index map N索引的编号从1开始，按照与表的SHOW INDEX所示相同的顺序。 索引映射值N是指示哪些索引是候选的位掩码值。 例如0x19（二进制11001）的值意味着将考虑索引1、4和5。
- Recursive
出现了递归查询。详见 “WITH (Common Table Expressions)”
- Rematerialize
用得很少，使用类似如下SQL时，会展示Rematerialize
- Scanned N databases
表示在处理INFORMATION_SCHEMA表的查询时，扫描了几个目录，N的取值可以是0，1或者all。详见 “Optimizing INFORMATION_SCHEMA Queries”
- Select tables optimized away
优化器确定：①最多返回1行；②要产生该行的数据，要读取一组确定的行，时会出现此提示。一般在用某些聚合函数访问存在索引的某个字段时，优化器会通过索引直接一次定位到所需要的数据行完成整个查询时展示，例如下面这条SQL。
- Skip_open_table， Open_frm_only， Open_full_table
这些值表示适用于INFORMATION_SCHEMA表查询的文件打开优化；
- Start temporary, End temporary
表示临时表使用Duplicate Weedout策略
- unique row not found
对于形如 SELECT … FROM tbl_name 的查询，但没有行能够满足唯一索引或主键查询的条件
- Using filesort
当Query 中包含 ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。数据较少时从内存排序，否则从磁盘排序。Explain不会显示的告诉客户端用哪种排序。官方解释：“MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行”
- Using index
仅使用索引树中的信息从表中检索列信息，而不必进行其他查找以读取实际行。当查询仅使用属于单个索引的列时，可以使用此策略。
- Using index condition
表示先按条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行。通过这种方式，除非有必要，否则索引信息将可以延迟“下推”读取整个行的数据。详见 “Index Condition Pushdown Optimization” 。
Using index for group-by
- List item
数据访问和 Using index 一样，所需数据只须要读取索引，当Query 中使用GROUP BY或DISTINCT 子句时，如果分组字段也在索引中，Extra中的信息就会是 Using index for group-by。详见 “GROUP BY Optimization”
– name字段有索引
explain SELECT name FROM t1 group by name
Using index for skip scan
表示使用了Skip Scan。详见 Skip Scan Range Access Method
- Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)
使用Block Nested Loop或Batched Key Access算法提高join的性能。详见 https://www.cnblogs.com/chenpingzhao/p/6720531.html
- Using MRR
使用了Multi-Range Read优化策略。详见 “Multi-Range Read Optimization”
- Using sort_union(…), Using union(…), Using intersect(…)
这些指示索引扫描如何合并为index_merge连接类型。详见 “Index Merge Optimization” 。
- Using temporary
为了解决该查询，MySQL需要创建一个临时表来保存结果。如果查询包含不同列的GROUP BY和 ORDER BY子句，通常会发生这种情况。
-- name无索引
explain SELECT name FROM t1 group by name
- Using where
如果我们不是读取表的所有数据，或者不是仅仅通过索引就可以获取所有需要的数据，则会出现using where信息
explain SELECT * FROM t1 where id > 5
- Using where with pushed condition
仅用于NDB
- Zero limit
该查询有一个limit 0子句，不能选择任何行
估计查询性能
多数情况下，你可以通过计算磁盘的搜索次数来估算查询性能。对于比较小的表，通常可以在一次磁盘搜索中找到行（因为索引可能已经被缓存了），而对于更大的表，你可以使用B-tree索引进行估算：你需要进行多少次查找才能找到行：log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1
在MySQL中，index_block_length通常是1024字节，数据指针一般是4字节。比方说，有一个500,000的表，key是3字节，那么根据计算公式 log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 次搜索。
该索引将需要500,000 * 7 * 3/2 = 5.2MB的存储空间（假设典型的索引缓存的填充率是2/3），因此你可以在内存中存放更多索引，可能只要一到两个调用就可以找到想要的行了。
但是，对于写操作，你需要四个搜索请求来查找在何处放置新的索引值，然后通常需要2次搜索来更新索引并写入行。
前面的讨论并不意味着你的应用性能会因为log N而缓慢下降。只要内容被OS或MySQL服务器缓存，随着表的变大，只会稍微变慢。在数据量变得太大而无法缓存后，将会变慢很多，直到你的应用程序受到磁盘搜索约束（按照log N增长）。为了避免这种情况，可以根据数据的增长而增加key的。对于MyISAM表，key的缓存大小由名为key_buffer_size的系统变量控制。
为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。
更多推荐
9
0- 0
已为社区贡献28条内容
所有评论(0)