MySQL执行计划 使用EXPLAIN优化查询 EXPLAIN输出格式 扩展EXPLAIN输出格式 EXPLAIN 语法 复制代码 {EXPLAIN | DESCRIBE | DESC} tbl_name [col_name | wild] {EXPLAIN | DESCRIBE | DESC} [explain_type] {explainable_stmt | FOR CONNECTION connection_id} explain_type: { EXTENDED | PARTITIONS | FORMAT = format_name } format_name: { TRADITIONAL | JSON } explainable_stmt: { SELECT statement | DELETE statement | INSERT statement | REPLACE statement | UPDATE statement } 复制代码 1. 使用EXPLAIN优化查询 EXPLAIN语句提供有关MySQL如何执行语句的信息： EXPLAIN适用于SELECT，DELETE，INSERT，REPLACE和UPDATE语句。 当EXPLAIN与可解释的语句一起使用时，MySQL会显示优化器中有关语句执行计划的信息。也就是说，MySQL解释了它将如何处理语句，包括有关表如何连接以及以何种顺序连接的信息。 当EXPLAIN与FOR CONNECTION connection_id将显示connection_id执行的语句的执行计划。 对于SELECT语句，EXPLAIN会生成可以使用SHOW WARNINGS显示的其他执行计划信息。 EXPLAIN对于检查涉及分区表的查询很有用。 FORMAT选项可用于选择输出格式。 TRADITIONAL以表格格式显示输出。如果没有显示定义FORMAT选项，则默认值为TRADITIONAL。 JSON格式以JSON格式显示信息。 在EXPLAIN的帮助下，可以看到应该向表添加索引的位置，以便通过使用索引查找行来更快地执行语句。还可以使用EXPLAIN来检查优化程序是否以最佳顺序连接表。要提示优化器使用与SELECT语句中命名表的顺序相对应的连接顺序，请使用SELECT STRAIGHT_JOIN而不是SELECT来开始语句。 （请参见“SELECT语法”。）但是，STRAIGHT_JOIN可能会阻止使用索引，因为它会禁用半连接转换。请参见“使用半连接转换优化子查询，派生表和视图引用”。 优化器trace有时可以提供与EXPLAIN的信息互补的信息。但是，优化程序跟踪格式和内容可能会在不同版本之间发生变化。有关详细信息，请参阅MySQL内部：跟踪优化程序。 如果在您认为应该使用索引时遇到问题，请运行ANALYZE TABLE以更新可能影响优化程序所做选择的表统计信息，例如key的基数。请参见“ANALYZE TABLE语法”。 注意 EXPLAIN还可用于获取有关表中列的信息。 EXPLAIN tbl_name与DESCRIBE tbl_name和SHOW COLUMNS FROM tbl_name同义。 2. EXPLAIN输出格式 EXPLAIN语句提供有关MySQL如何执行语句的信息。 EXPLAIN适用于SELECT，DELETE，INSERT，REPLACE和UPDATE语句。 EXPLAIN为SELECT语句中使用的每个表返回一行信息。 它按照MySQL在处理语句时读取它们的顺序列出输出中的表。 MySQL使用嵌套循环连接方法解析所有连接。 这意味着MySQL从第一个表中读取一行，然后在第二个表，第三个表中找到匹配的行，依此类推。 处理完所有表后，MySQL会通过表列表输出所选列和回溯，直到找到有更多匹配行的表。 从该表中读取下一行，并继续下一个表。 EXPLAIN输出包括分区信息。 此外，对于SELECT语句，EXPLAIN生成扩展信息，可以使用EXPLAIN后的SHOW WARNINGS显示. 【注意】 在较旧的MySQL版本中，使用EXPLAIN PARTITIONS和EXPLAIN EXTENDED生成分区和扩展信息。 这些语法仍然可以向后兼容，但默认情况下现在启用分区和扩展输出，因此PARTITIONS和EXTENDED关键字是多余的并且已弃用。 它们的使用会导致警告，并且在将来的MySQL版本中它们将从EXPLAIN语法中删除。 您不能在同一个EXPLAIN语句中一起使用已弃用的PARTITIONS和EXTENDED关键字。 此外，这些关键字都不能与FORMAT选项一起使用。 EXPLAIN输出列 EXPLAIN join 类型 EXPLAIN Extra 信息 2.1 EXPLAIN输出列 复制代码 mysql> explain select * from employees.t1 where t1.emp_no in (select emp_no from employees.salaries); +----+--------------+-------------+------------+--------+----------------+------------+---------+---------------------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+-------------+------------+--------+----------------+------------+---------+---------------------+---------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 2837194 | 100.00 | Using where | | 1 | SIMPLE | | NULL | eq_ref | | | 4 | employees.t1.emp_no | 1 | 100.00 | NULL | | 2 | MATERIALIZED | salaries | NULL | index | PRIMARY,emp_no | emp_no | 4 | NULL | 2838426 | 100.00 | Using index | +----+--------------+-------------+------------+--------+----------------+------------+---------+---------------------+---------+----------+-------------+ 3 rows in set, 1 warning (0.00 sec) 复制代码 EXPLAIN的每个输出行都提供有关一个表的信息。如下表： Column JSON Name Meaning id select_id 查询序列号 select_type None 查询类型 table table_name 输出行所引用的表 partitions partitions 匹配的分区 type access_type 连接使用的类型 possible_keys possible_keys 指出 MySQL 能在该表中使用哪些索引有助于查询。如果为空，说明没有可用的索引 key key 实际选择的索引 key_len key_length 使用的索引的长度。在不损失精确性的情况下，长度越短越好 ref ref 显示索引的哪一列被使用了 rows rows MYSQL 认为必须检查的用来返回请求数据的行数 filtered filtered 按表条件过滤行的百分比 Extra None 附加信息 id(JSON Name: select_id) MySQL Query Optimizer 选定的执行计划中查询的序列号。表示查询中执行 select 子句或操作表的顺序， id 值越大优先级越高，越先被执行。 id 相同，执行顺序由上至下。 select_type (JSON name: none) SELECT的类型，可以是下表中列出的任何类型。 select_type Value JSON Name Meaning SIMPLE None 简单的SELECT（不使用UNION或子查询） PRIMARY None 最外层的SELECT UNION None UNION 中的第二个或随后的 select 查询， 不依赖于外部查询的结果集 DEPENDENT UNION dependent (true) UNION中的第二个或随后的SELECT语句，依赖于外部查询 UNION RESULT union_result UNION 查询的结果集 SUBQUERY None 子查询中的第一个SELECT查询，不依赖于外部查询的结果集 DEPENDENT SUBQUERY dependent (true) 子查询中的第一个SELECT，依赖于外部查询的结果集 DERIVED None 用于 from 子句里有子查询的情况。 MySQL会递归执行这些子查询，把结果放在临时表里 MATERIALIZED materialized_from_subquery 物化子查询 UNCACHEABLE SUBQUERY cacheable (false) 结果集不能被缓存的子查询，必须重新为外层查询的每一行进行评估 UNCACHEABLE UNION cacheable (false) UNION 中的第二个或随后的 select 查询，属于不可缓存的子查询 　　DEPENDENT通常表示使用相关子查询。 DEPENDENT SUBQUERY评估与UNCACHEABLE SUBQUERY评估不同。 对于DEPENDENT SUBQUERY，子查询仅针对来自其外部上下文的变量的每组不同值重新评估一次。 对于UNCACHEABLE SUBQUERY，将为外部上下文的每一行重新评估子查询。 　　子查询的可缓存性与查询缓存中查询结果的缓存不同（详见“查询缓存如何操作”）。 查询执行期间发生子查询缓存，而查询缓存仅在查询执行完成后用于存储结果。 使用EXPLAIN指定FORMAT = JSON时，输出没有直接等同于select_type的单个属性; query_block属性对应于给定的SELECT。 可以使用与刚显示的大多数SELECT子查询类型等效的属性（示例为MATERIALIZED的materialized_from_subquery），并在适当时显示。 SIMPLE或PRIMARY没有JSON等价物。 非SELECT语句的select_type值显示受影响表的语句类型。 例如，对于DELETE语句，select_type是DELETE。 table (JSON name: table_name) 输出行引用的表的名称。这也可以是以下值之一： ：该行指的是id值为M和N的行的并集。 ：该行引用id值为N的行的派生表结果。例如，派生表可能来自FROM子句中的子查询。 ：该行引用id值为N的行的具体化子查询的结果 partitions (JSON name: partitions) 记录将与查询匹配的分区。对于非分区表，该值为NULL。 type (JSON name: access_type) 连接类型。用于描述不同的连接类型 possible_keys (JSON name: possible_keys) possible_keys指出 MySQL 能在该表中使用哪些索引有助查询。如果为空，说明没有可用的索引 key (JSON name: key) key列表示MySQL实际决定使用的key（索引）。如果MySQL决定使用其中一个possible_keys索引来查找行，那么该索引将被列为key value。 key可能会命名possible_keys值中不存在的索引。如果所有possible_keys索引都不适合查找行，则会发生这种情况，但查询选择的所有列都是其他索引的列。也就是说，命名索引覆盖了所选列，因此虽然它不用于确定要检索的行，但索引扫描比数据行扫描更有效。 对于InnoDB，即使查询还选择主键，辅助索引也可能覆盖所选列，因为InnoDB将主键值与每个辅助索引一起存储。如果key为NULL，则MySQL找不到用于更有效地执行查询的索引。 要强制MySQL使用或忽略possible_keys列中列出的索引，请在查询中使用FORCE INDEX，USE INDEX或IGNORE INDEX。请参考“索引hint”。 对于MyISAM表，运行ANALYZE TABLE可帮助优化器选择更好的索引。对于MyISAM表，myisamchk --analyze也是如此。 key_len (JSON name: key_length) key_len列使用的索引的长度。在不损失精确性的情况下，长度越短越好 ref (JSON name: ref) ref列显示哪些列或常量与key列中指定的索引进行比较，以从表中选择行。 如果值为func，则使用的值是某个函数的结果。 要查看哪个函数，请使用EXPLAIN之后的SHOW WARNINGS来查看扩展的EXPLAIN输出。 该函数实际上可能是算术运算符等运算符。 rows (JSON name: rows) rows列指示MySQL认为必须检查以执行查询的行数。 对于InnoDB表，此数字是估计值，可能并不总是准确的。 filtered (JSON name: filtered) filtered列指示将按表条件筛选的表行的估计百分比。 最大值为100，这意味着不会对行进行过滤。 值从100开始减少表示过滤量增加。 rows显示已检查的估计行数，rows×filtered显示将与下表连接的行数。 例如，如果行为1000且过滤为50.00（50％），则使用下表连接的行数为1000×50％= 500。 Extra (JSON name: none) 此列包含有关MySQL如何解析查询的其他信息。 有关不同值的说明，请参阅EXPLAIN Extra 信息。 2.2 EXPLAIN join 类型 EXPLAIN输出的type列描述了表的连接方式。以下列表描述了从最佳类型到最差类型的连接类型： system 表仅有一行(=系统表)。这是 const 连接类型的一个特例。 const 该表最多只有一个匹配行，在查询开头读取。 因为只有一行，所以优化器的其余部分可以将此行中列的值视为常量。 const表非常快，因为它们只读一次。 当将PRIMARY KEY或UNIQUE索引的所有部分与常量值进行比较时，将使用const。 在以下查询中，tbl_name可用作const表： 复制代码 SELECT * FROM tbl_name WHERE primary_key=1; SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2; 复制代码 eq_ref 除 const 类型外最好的可能实现的连接类型。它用在一个索引的所有部分被连接使用并且索引是 UNIQUE 或 PRIMARY KEY， 对于每个索引键，表中只有一条记录与之匹配 复制代码 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 连接不能基于关键字选择单个行，可能查找到多个符合条件的行。叫做 ref 是因为索引要跟某个参考值相比较。这个参考值或者是一个常数，或者是来自一个表里的多表查询的结果值 ref可用于使用=或<=>运算符进行比较的索引列。 在以下示例中，MySQL可以使用ref join来处理ref_table： 复制代码 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 使用FULLTEXT索引执行连接。 ref_or_null 这种连接类型与ref类似，但附加说明MySQL会对包含NULL值的行进行额外搜索。 此连接类型优化最常用于解析子查询。 在以下示例中，MySQL可以使用ref_or_null连接来处理ref_table： 复制代码 SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL; 复制代码 index_merge 此连接类型表示使用了索引合并优化。 在这种情况下，输出行中的key列包含使用的索引列表，key_len包含所用索引的最长键部分列表。详见 index merge optimization 复制代码 root@localhost [test] 09:51:37> explain select * from a force index(idx_1,idx_2) where c1=2 or c2=1; +----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+ | 1 | SIMPLE | a | NULL | index_merge | idx_2,idx_1 | idx_1,idx_2 | 5,5 | NULL | 2 | 100.00 | Using union(idx_1,idx_2); Using where | +----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+ 1 row in set, 1 warning (0.00 sec) 复制代码 unique_subquery 此类型替换以下形式的某些IN子查询的eq_ref： value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery只是一个索引查找函数，它可以完全替换子查询以提高效率。 range 仅检索给定范围内的行，使用索引选择行。 输出行中的键列指示使用哪个索引。 key_len包含使用的最长密钥部分。 对于此类型，ref列为NULL。 使用=，<>，>，>=，<，<=，IS NULL，<=>，BETWEEN，LIKE或IN（）运算符中的任何一个将键列与常量进行比较时，可以使用范围： 复制代码 SELECT * FROM tbl_name WHERE key_column = 10; SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20; SELECT * FROM tbl_name WHERE key_column IN (10,20,30); SELECT * FROM tbl_name WHERE key_part1 = 10 AND key_part2 IN (10,20,30); 复制代码 index 全表扫描，只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序，但是开销仍然非常大。 如果索引是查询的覆盖索引，并且可用于满足表中所需的所有数据，则仅扫描索引树。 在这种情况下，Extra列显示Using index。 仅索引扫描通常比ALL快，因为索引的大小通常小于表数据。 使用索引中的读取执行全表扫描，以按索引顺序查找数据行。 使用索引不会出现在Extra列中。 当查询仅使用属于单个索引的列时，MySQL可以使用此连接类型。 ALL 对表中的每行进行全表扫描。 2.3 EXPLAIN Extra 信息 EXPLAIN输出的Extra列包含有关MySQL如何解析查询的其他信息。 以下列表说明了此列中可能出现的值。 每个项目还指示JSON格式的输出，该属性显示Extra值。 对于其中一些，有一个特定的属性。 其他显示为消息属性的文本。 Child of 'table' pushed join@1 (JSON: message text) 此表在连接中被引用为表的子节点，可以将其下推到NDB内核。 仅当启用了 pushed-down连接时，才适用于NDB群集。 const row not found (JSON property: const_row_not_found) 对于诸如SELECT ... FROM tbl_name之类的查询，该表为空。 Deleting all rows (JSON property: message) 对于DELETE，某些存储引擎（如MyISAM）支持一种处理程序方法，该方法以简单快捷的方式删除所有表行。 如果引擎使用此优化，则会显示此附加值。 Distinct (JSON property: distinct) MySQL正在寻找不同的值，因此它在找到第一个匹配行后停止为当前行组合搜索更多行。 FirstMatch(tbl_name) (JSON property: first_match) 半连接FirstMatch连接快捷方式 Full scan on NULL key (JSON property: message) 当优化程序无法使用 index-lookup 访问时，子查询优化将作为回退策略发生。 Impossible HAVING (JSON property: message) HAVING子句始终为false，无法查询任何行。 Impossible WHERE (JSON property: message) WHERE子句始终为false，无法查询任何行。 Impossible WHERE noticed after reading const tables (JSON property: message) MySQL已经读取了所有const（和system）表，并注意到WHERE子句始终为false。 LooseScan(m..n) (JSON property: message) 使用半连接LooseScan策略。 m和n是关键部件号。 No matching min/max row (JSON property: message) 没有行满足查询的条件，例如SELECT MIN（...）FROM ... WHERE条件。 No matching rows after partition pruning (JSON property: message) 对于DELETE或UPDATE，优化器在分区修剪后未发现任何要删除或更新的内容。 它与SELECT语句的Impossible WHERE的含义相似。 No tables used (JSON property: message) 查询语句没有制定from子句，或者from dual子句。 对于INSERT或REPLACE语句，EXPLAIN在没有SELECT部分​​时显示该值。例如，它出现在EXPLAIN INSERT INTO VALUES（10）中，因为它等同于EXPLAIN INSERT INTO SELECT 101 FROM DUAL。 Not exists (JSON property: message) MySQL能够对查询执行LEFT JOIN优化，并且在找到与LEFT JOIN条件匹配的行之后，不会检查此表中针对上一行组合的更多行。 以下是可以通过以下方式优化的查询类型的示例： 复制代码 SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.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中实际匹配多少行。 Plan isn't ready yet (JSON property: none) 当优化器尚未完成为在命名连接中执行的语句创建执行计划时，EXPLAIN FOR CONNECTION会出现此值。 如果执行计划输出包含多行，则其中的任何一个或全部都可以具有此Extra值，具体取决于优化程序在确定完整执行计划时的进度。 Range checked for each record (index map: N) (JSON property: message) MySQL发现没有好的索引可以使用，但发现在前面的表的列值已知后可能会使用某些索引。 对于上表中的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来检索行。 这不是很快，但比执行没有索引的连接更快。 Scanned N databases (JSON property: message) 这表示在处理INFORMATION_SCHEMA表的查询时服务器执行的目录扫描数。 N的值可以是0,1或all。 Select tables optimized away (JSON property: message) 使用某些聚合函数来访问存在索引的某个字段时，优化器会通过索引直接一次定位到所需要的数据行完成整个查询，例如MIN()\MAX()，这种也是比较好的结果之一 Skip_open_table, Open_frm_only, Open_full_table (JSON property: message) 这些值表示适用于INFORMATION_SCHEMA表的查询的文件打开优化。 Skip_open_table：不需要打开表文件。通过扫描数据库目录，该信息已在查询中可用。 Open_frm_only：只需要打开表的.frm文件。 Open_full_table：未经优化的信息查找。必须打开.frm，.MYD和.MYI文件。 Start temporary, End temporary (JSON property: message) 这表示临时表用于半连接Duplicate Weedout策略。 unique row not found (JSON property: message) 对于诸如SELECT ... FROM tbl_name之类的查询，没有行满足表上UNIQUE索引或PRIMARY KEY的条件。 Using filesort (JSON property: using_filesort) 表示 MySQL 会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。 MySQL 中无法利用索引完成的排序操作称为“文件排序” Using index (JSON property: using_index) 仅使用索引树中的信息从表中检索列信息，而不必另外寻找读取实际行。 当查询仅使用属于单个索引的列时，可以使用此策略。 对于具有用户定义的聚簇索引的InnoDB表，即使Extra列中不存在使用索引，也可以使用该索引。 如果type是index并且key是PRIMARY，则会出现这种情况。 Using index condition (JSON property: using_index_condition) 会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行。 Using index for group-by (JSON property: using_index_for_group_by) 与Using index table访问方法类似，Using group for group-by表示MySQL找到了一个索引，可用于检索GROUP BY或DISTINCT查询的所有列，而无需对实际表进行任何额外的磁盘访问。 此外，索引以最有效的方式使用，因此对于每个组，只读取少数索引条目。 有关详细信息，请参考“GROUP BY优化”。 Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access) (JSON property: using_join_buffer) 将联接中的表分成几部分读入连接缓冲区，然后从缓冲区中使用它们的行来与当前表执行连接。 （Block Nested Loop）表示使用块嵌套循环算法，（Batched Key Access）表示使用批量密钥访问算法。 也就是说，来自EXPLAIN输出前一行的表中的键