1. EXPLAIN详解本文基于MySQL 8.0编写理论支持MySQL 5.0及更高版本。2. EXPLAIN使用2.1 explain分析SQL的执行计划{EXPLAIN | DESCRIBE | DESC} tbl_name [col_name | wild] {EXPLAIN | DESCRIBE | DESC} [explain_type] {explainable_stmt | FOR CONNECTION connection_id} {EXPLAIN | DESCRIBE | DESC} ANALYZE select_statement explain_type: { FORMAT format_name } format_name: { TRADITIONAL | JSON | TREE } explainable_stmt: { SELECT statement | TABLE statement | DELETE statement | INSERT statement | REPLACE statement | UPDATE statement }2.2 示例EXPLAIN format TRADITIONAL json SELECT tt.TicketNumber, tt.TimeIn, tt.ProjectReference, tt.EstimatedShipDate, tt.ActualShipDate, tt.ClientID, tt.ServiceCodes, tt.RepetitiveID, tt.CurrentProcess, tt.CurrentDPPerson, tt.RecordVolume, tt.DPPrinted, et.COUNTRY, et_1.COUNTRY, do.CUSTNAME FROM tt, et, et AS et_1, do WHERE tt.SubmitTime IS NULL AND tt.ActualPC et.EMPLOYID AND tt.AssignedPC et_1.EMPLOYID AND tt.ClientID do.CUSTNMBR;2.3 结果输出展示3. 结果解读id该语句的唯一标识。如果explain的结果包括多个id值则数字越大越先执行而对于相同id的行则表示从上往下依次执行。select_type查询类型有如下几种取值table表示当前这一行正在访问哪张表如果SQL定义了别名则展示表的别名partitions当前查询匹配记录的分区。对于未分区的表返回nulltype连接类型有如下几种取值性能从好到坏排序如下◦system该表只有一行相当于系统表system是const类型的特例◦const针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可◦eq_ref当使用了索引的全部组成部分并且索引是PRIMARY KEY或UNIQUE NOT NULL 才会使用该类型性能仅次于system及const。-- 多表关联查询单行匹配 SELECT * FROM ref_table,other_table WHERE ref_table.key_columnother_table.column; -- 多表关联查询联合索引多行匹配 SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1other_table.column AND ref_table.key_column_part21;◦ref当满足索引的最左前缀规则或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行性能也是不错的。-- 根据索引非主键非唯一索引匹配到多行 SELECT * FROM ref_table WHERE key_columnexpr; -- 多表关联查询单个索引多行匹配 SELECT * FROM ref_table,other_table WHERE ref_table.key_columnother_table.column; -- 多表关联查询联合索引多行匹配 SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1other_table.column AND ref_table.key_column_part21;TIPS最左前缀原则指的是索引按照最左优先的方式匹配索引。比如创建了一个组合索引(column1, column2, column3)那么如果查询条件是 WHERE column1 1、WHERE column1 1 AND column2 2、WHERE column1 1 AND column2 2 AND column3 3 都可以使用该索引 WHERE column1 2、WHERE column1 1 AND column3 3就无法匹配该索引。◦fulltext全文索引ref\_or\_null该类型类似于ref但是MySQL会额外搜索哪些行包含了NULL。这种类型常见于解析子查询SELECT * FROM ref_table WHERE key_columnexpr 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字节。◦key_len计算公式https://www.cnblogs.com/gomysql/p/4004244.htmlref表示将哪个字段或常量和key列所使用的字段进行比较。如果ref是一个函数则使用的值是函数的结果。要想查看是哪个函数可在EXPLAIN语句之后紧跟一个SHOW WARNING语句。rowsMySQL估算会扫描的行数数值越小越好。filtered表示符合查询条件的数据百分比最大100。用rows × filtered可获得和下一张表连接的行数。例如rows 1000filtered 50%则和下一张表连接的行数是500。TIPS在MySQL 5.7之前想要显示此字段需使用explain extended命令 MySQL.5.7及更高版本explain默认就会展示filteredExtra展示有关本次查询的附加信息取值如下◦Child of ‘table’ pushed join1此值只会在NDB Cluster下出现。◦const row not found例如查询语句SELECT … FROM tbl_name而表是空的◦Deleting all rows对于DELETE语句某些引擎例如MyISAM支持以一种简单而快速的方式删除所有的数据如果使用了这种优化则显示此值◦Distinct查找distinct值当找到第一个匹配的行后将停止为当前行组合搜索更多行◦FirstMatch(tbl_name)当前使用了半连接FirstMatch策略详见 https://mariadb.com/kb/en/firstmatch-strategy/ 翻译 https://www.cnblogs.com/abclife/p/10895624.html◦Full scan on NULL key子查询中的一种优化方式在无法通过索引访问null值的时候使用◦Impossible HAVINGHAVING子句始终为false不会命中任何行◦Impossible WHEREWHERE子句始终为false不会命中任何行◦Impossible WHERE noticed after reading const tablesMySQL已经读取了所有const或system表并发现WHERE子句始终为false◦LooseScan(m…n)当前使用了半连接LooseScan策略详见 https://mariadb.com/kb/en/loosescan-strategy/ 翻译 http://www.javacoder.cn/?p39◦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 existsMySQL能对LEFT JOIN优化在找到符合LEFT JOIN的行后不会为上一行组合中检查此表中的更多行。例如SELECT * FROM t1 LEFT JOIN t2 ON t1.idt2.id WHERE t2.id IS NULL;◦假设t2.id定义成了NOT NULL 此时MySQL会扫描t1并使用t1.id的值查找t2中的行。 如果MySQL在t2中找到一个匹配的行它会知道t2.id永远不会为NULL并且不会扫描t2中具有相同id值的其余行。也就是说对于t1中的每一行MySQL只需要在t2中只执行一次查找而不考虑在t2中实际匹配的行数。◦在MySQL 8.0.17及更高版本中如果出现此提示还可表示形如 NOT IN (subquery) 或 NOT EXISTS (subquery) 的WHERE条件已经在内部转换为反连接。这将删除子查询并将其表放入最顶层的查询计划中从而改进查询的开销。通过合并半连接和反联接优化器可以更加自由地对执行计划中的表重新排序在某些情况下可让查询提速。你可以通过在EXPLAIN语句后紧跟一个SHOW WARNING语句并分析结果中的Message列从而查看何时对该查询执行了反联接转换。◦Note两表关联只返回主表的数据并且只返回主表与子表没关联上的数据这种连接就叫反连接Plan isn’t ready yet使用了EXPLAIN FOR CONNECTION当优化器尚未完成为在指定连接中为执行的语句创建执行计划时 就会出现此值。MySQL没有找到合适的索引去使用但是去检查是否可以使用range或index_merge来检索行时会出现此提示。index map N索引的编号从1开始按照与表的SHOW INDEX所示相同的顺序。 索引映射值N是指示哪些索引是候选的位掩码值。 例如0x19二进制11001的值意味着将考虑索引1、4和5。Plan isn’t ready yet使用了EXPLAIN FOR CONNECTION当优化器尚未完成为在指定连接中为执行的语句创建执行计划时 就会出现此值。◦MySQL没有找到合适的索引去使用但是去检查是否可以使用range或index_merge来检索行时会出现此提示。index map N索引的编号从1开始按照与表的SHOW INDEX所示相同的顺序。 索引映射值N是指示哪些索引是候选的位掩码值。 例如0x19二进制11001的值意味着将考虑索引1、4和5。◦示例下面例子中name是varchar类型但是条件给出整数型涉及到隐式转换。图中t2也没有用到索引是因为查询之前我将t2中name字段排序规则改为utf8_bin导致的链接字段排序规则不匹配。explain select a.* from t1 a left join t2 b on t1.name t2.name where t2.name 2;结果Recursive出现了递归查询。详见“WITH (Common Table Expressions)”Rematerialize用得很少使用类似如下SQL时会展示RematerializeSELECT ... FROM t, LATERAL (derived table that refers to t) AS dt ...Scanned N databases表示在处理INFORMATION\_SCHEMA表的查询时扫描了几个目录N的取值可以是01或者all。详见 “Optimizing INFORMATION\_SCHEMA Queries”Select tables optimized away优化器确定①最多返回1行②要产生该行的数据要读取一组确定的行时会出现此提示。一般在用某些聚合函数访问存在索引的某个字段时优化器会通过索引直接一次定位到所需要的数据行完成整个查询时展示例如下面这条SQL。explain select min(id) from t1;Skip\_open\_table Open\_frm\_only Open\_full\_table这些值表示适用于INFORMATION_SCHEMA表查询的文件打开优化◦Skip\_open\_table无需打开表文件信息已经通过扫描数据字典获得◦Open\_frm\_only仅需要读取数据字典以获取表信息◦Open\_full\_table未优化的信息查找。表信息必须从数据字典以及表文件中读取Start temporary, End temporary表示临时表使用Duplicate Weedout策略详见 https://mariadb.com/kb/en/duplicateweedout-strategy/ 翻译 https://www.cnblogs.com/abclife/p/10895531.htmlunique row not found对于形如 SELECT … FROM tbl_name 的查询但没有行能够满足唯一索引或主键查询的条件Using filesort当Query 中包含 ORDER BY 操作而且无法利用索引完成排序操作的时候MySQL Query Optimizer 不得不选择相应的排序算法来实现。数据较少时从内存排序否则从磁盘排序。Explain不会显示的告诉客户端用哪种排序。官方解释“MySQL需要额外的一次传递以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序并按排序顺序检索行”Using index仅使用索引树中的信息从表中检索列信息而不必进行其他查找以读取实际行。当查询仅使用属于单个索引的列时可以使用此策略。例如explain SELECT id FROM tUsing index condition表示先按条件过滤索引过滤完索引后找到所有符合索引条件的数据行随后用 WHERE 子句中的其他条件去过滤这些数据行。通过这种方式除非有必要否则索引信息将可以延迟“下推”读取整个行的数据。详见 “Index Condition Pushdown Optimization” 。例如MySQL分成了Server层和引擎层下推指的是将请求交给引擎层处理。理解这个功能可创建所以INDEX (zipcode, lastname, firstname)并分别用如下指令SET optimizer_switch index_condition_pushdownoff; SET optimizer_switch index_condition_pushdownon;开或者关闭索引条件下推并对比explain SELECT * FROM people WHERE zipcode95054 AND lastname LIKE %etrunia% AND address LIKE %Main Street%;的执行结果。index condition pushdown从MySQL 5.6开始支持是MySQL针对特定场景的优化机制感兴趣的可以看下 https://blog.51cto.com/lee90/2060449Using index for group-by数据访问和 Using index 一样所需数据只须要读取索引当Query 中使用GROUP BY或DISTINCT 子句时如果分组字段也在索引中Extra中的信息就会是 Using index for group-by。详见 “GROUP BY Optimization”-- name字段有索引 explain SELECT name FROM t1 group by nameUsing index for skip scan表示使用了Skip Scan。详见 Skip Scan Range Access MethodUsing 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.htmlUsing 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 nameUsing where如果我们不是读取表的所有数据或者不是仅仅通过索引就可以获取所有需要的数据则会出现using where信息explain SELECT * FROM t1 where id 5Using where with pushed condition仅用于NDBZero limit该查询有一个limit 0子句不能选择任何行explain SELECT name FROM resource_template limit 04. 扩展的EXPLAINEXPLAIN可产生额外的扩展信息可通过在EXPLAIN语句后紧跟一条SHOW WARNING语句查看扩展信息。TIPS在MySQL 8.0.12及更高版本扩展信息可用于SELECT、DELETE、INSERT、REPLACE、UPDATE语句在MySQL 8.0.12之前扩展信息仅适用于SELECT语句 在MySQL 5.6及更低版本需使用EXPLAIN EXTENDED xxx语句而从MySQL 5.7开始无需添加EXTENDED关键词。使用示例mysql EXPLAIN SELECT t1.a, t1.a IN (SELECT t2.a FROM t2) FROM t1\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t1 type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 4 filtered: 100.00 Extra: Using index *************************** 2. row *************************** id: 2 select_type: SUBQUERY table: t2 type: index possible_keys: a key: a key_len: 5 ref: NULL rows: 3 filtered: 100.00 Extra: Using index 2 rows in set, 1 warning (0.00 sec) mysql SHOW WARNINGS\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select test.t1.a AS a, in_optimizer(test.t1.a,test.t1.a in ( materialize (/* select#2 */ select test.t2.a from test.t2 where 1 having 1 ), primary_index_lookup(test.t1.a in temporary table on auto_key where ((test.t1.a materialized-subquery.a))))) AS t1.a IN (SELECT t2.a FROM t2) from test.t1 1 row in set (0.00 sec)由于SHOW WARNING的结果并不一定是一个有效SQL也不一定能够执行因为里面包含了很多特殊标记。特殊标记取值如下◦auto_key自动生成的临时表key◦cache(expr)表达式例如标量子查询执行了一次并且将值保存在了内存中以备以后使用。对于包括多个值的结果可能会创建临时表你将会看到 temporary table 的字样◦exists(query fragment)子查询被转换为 EXISTS◦in_optimizer(query fragment)这是一个内部优化器对象对用户没有任何意义◦index_lookup(query fragment)使用索引查找来处理查询片段从而找到合格的行◦if(condition, expr1, expr2)如果条件是true则取expr1否则取expr2◦is\_not\_null_test(expr)验证表达式不为NULL的测试◦materialize(query fragment)使用子查询实现◦materialized-subquery.col\_name在内部物化临时表中对col\_name的引用以保存子查询的结果◦primary\_index\_lookup(query fragment)使用主键来处理查询片段从而找到合格的行◦ref\_null\_helper(expr)这是一个内部优化器对象对用户没有任何意义◦/* select#N */ select_stmtSELECT与非扩展的EXPLAIN输出中idN的那行关联◦outer\_tables semi join (inner\_tables)半连接操作。inner_tables展示未拉出的表。详见 “Optimizing Subqueries, Derived Tables, and View References with Semijoin Transformations”◦temporary table表示创建了内部临时表而缓存中间结果当某些表是const或system类型时这些表中的列所涉及的表达式将由优化器尽早评估并且不属于所显示语句的一部分。但是当使用FORMATJSON时某些const表的访问将显示为ref。5. 估计查询性能多数情况下你可以通过计算磁盘的搜索次数来估算查询性能。对于比较小的表通常可以在一次磁盘搜索中找到行因为索引可能已经被缓存了而对于更大的表你可以使用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/(34)) 1 4 次搜索。该索引将需要500,00073/2 5.2MB的存储空间假设典型的索引缓存的填充率是2/3因此你可以在内存中存放更多索引可能只要一到两个调用就可以找到想要的行了。但是对于写操作你需要四个搜索请求来查找在何处放置新的索引值然后通常需要2次搜索来更新索引并写入行。前面的讨论并不意味着你的应用性能会因为log N而缓慢下降。只要内容被OS或MySQL服务器缓存随着表的变大只会稍微变慢。在数据量变得太大而无法缓存后将会变慢很多直到你的应用程序受到磁盘搜索约束按照log N增长。为了避免这种情况可以根据数据的增长而增加key的。对于MyISAM表key的缓存大小由名为key\_buffer\_size的系统变量控制详见Section 5.1.1, “Configuring the Server”6. 参考文档EXPLAIN Output FormatEXPLAIN StatementExtended EXPLAIN Output FormatEstimating Query PerformanceMySQL中explain执行计划中额外信息字段(Extra)详解explain参数详解最官方的 mysql explain type 字段解读What does eq_ref and ref types mean in MySQL explain面试官不会看 Explain执行计划简历敢写 SQL 优化作者京东物流 柳宏