MySQL 查询执行计划
查询计划
根据表、列、索引的详细信息以及子句中的 WHERE 条件不同,MySQL 优化器会考虑多种技术来有效地执行 SQL 查询中涉及的查找。
例如,
-
对大表进行查询可能无需读取所有行;
-
涉及多个表的联接查询可能不需要比较每个行组合。
优化器选择的执行最高效查询的操作集称为“查询执行计划”(query execution plan),也称为 EXPLAIN 计划。
通常情况下,EXPLAIN 返回的查询计划是被较好优化的执行计划,我们可以通过改进SQL 语句或者索引技术,来优化慢查询。
EXPLAIN 语句提供了有关 MySQL 如何执行语句的信息,EXPLAIN 语句适用于 SELECT、 DELETE、 INSERT、 REPLACE 和 UPDATE语句。
查询计划输出格式
EXPLAIN 返回语句中使用的每个表的一行信息 SELECT。它按照 MySQL 在处理语句时读取表的顺序列出了输出中的表。这意味着MySQL从第一个表中读取一行,然后在第二个表中找到匹配的行,然后在第三个表中找到匹配的行,依此类推。当所有表都处理完毕后,MySQL 输出选定的列并回溯表列表,直到找到有更多匹配行的表。从此表中读取下一行,并继续处理下一个表。
解释输出列
EXPLAIN 语句输出列的格式:
列 | JSON | 含义 |
---|---|---|
id | select_id | SELECT 标识符 |
select_type | / | SELECT 类型 |
table | table_name | 输出记录的表 |
partitions | partitions | 匹配的分区 |
type | access_type | 连接类型 |
possible_keys | possible_keys | 可能会选择的索引 |
key | key | 实际选择的索引 |
key_len | key_length | 所选索引的长度 |
ref | ref | 与索引比较的列 |
rows | rows | 估计要检查的行数 |
filtered | filtered | 按表条件过滤的行的百分比 |
Extra | / | 附加信息 |
列格式
id
标识符SELECT。SELECT这是查询中的序列号 。NULL如果该行引用其他行的并集结果,则该值可以是。在本例中,该 table列显示一个值 ,表示该行引用值为 和 的行的并集。 <unionM,N>idMN
select_type
SELECT 的类型,可以是下表中显示的任何类型。JSON 格式将类型EXPLAIN公开 SELECT为 a 的属性 query_block,除非它是 SIMPLE或PRIMARY。表中还显示了 JSON 名称(如果适用)。
select_type 的值 | 含义 |
---|---|
SIMPLE | 简单的 SELECT 查询,没有使用 UNION 或者 子查询 |
PRIMARY | 最外层的 SELECT |
UNION | UNION 查询中的第二次或者更晚的 SELECT 语句 |
DEPENDENT UNION | UNION 查询中的第二次或者更晚的 SELECT 语句, 取决于外层查询 |
UNION RESULT | UNION 查询的结果 |
SUBQUERY | 子查询中的第一个 SELECT |
DEPENDENT SUBQUERY | 子查询中的第一个 SELECT,依赖于外部查询 |
DERIVED | 派生表 |
DEPENDENT DERIVED | 被派生表依赖的另一个表 |
MATERIALIZED | 物化的子查询 |
UNCACHEABLE SUBQUERY | 无法缓存结果且必须针对外部查询的每一行重新计算结果的子查询 |
UNCACHEABLE UNION | UNION 中属于不可缓存子查询的第二个或后续查询 |
type
EXPLAIN 输出的类型列描述了表的连接方式。下面的列表描述了连接类型,从最好的类型到最差的类型排序:
-
system
该表只有一行(通常是系统表),这是 const 连接类型的特例。。
-
const
该表最多有一个匹配行,该行在查询开始时读取。由于只有一行,因此该行中的列的值可以被优化器的其余部分视为常量。 const 表非常快,因为它们只被读取一次。
当我们使用 PRIMARY KEY 或 UNIQUE 索引与常量值进行比较时,MySQL 会使用 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
对于前一个表中的行的每个组合,都会从此表中读取一行。除了 system 和 const 类型之外,这是最好的连接类型。
当连接使用索引的所有部分并且索引是 PRIMARY KEY 或 UNIQUE NOT NULL 索引时,会使用 eq_ref。
eq_ref 可用于使用 = 运算符进行比较的索引列。比较值可以是常量或使用在此表之前读取的表中的列的表达式。在以下示例中,MySQL 会使用 eq_ref 连接来处理 ref_table:
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
对于前面表中的行的每个组合,都会从此表中读取具有匹配索引值的所有行。如果联接仅使用索引的最左边前缀或者索引不是 PRIMARY KEY 或 UNIQUE 索引,换句话说,如果联接无法根据索引的值选择单个行,就会使用 ref。如果使用的索引仅匹配几行,那么这是一个很好的连接类型。
ref 可用于使用 = 或 <=> 运算符进行比较的索引列。在以下示例中,MySQL 会使用 ref 连接来处理 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 包含所使用的索引的最长键部分的列表。
-
unique_subquery
此类型会替换以下形式的某些 IN 子查询的 eq_ref:
value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique_subquery 只是一个索引查找功能,完全替代了子查询,以获得更好的效率。
-
index_subquery
此连接类型类似于 unique_subquery. 它取代了 IN 子查询,但它适用于以下形式的子查询中的非唯一索引:
value IN (SELECT key_column FROM single_table WHERE some_expr)
-
range
使用索引来选择行,并且仅检索给定范围内的行。输出行中的 key 列会提示使用了哪个索引,key_len 列包含了所使用的最长的 key 部分。对于这种类型,ref 列为 NULL。
当索引列与常量进行比较时,如果使用了 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE 或 IN() 这些运算符,则 MySQL 会使用 range。例如:
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
index 连接类型与 ALL 相同,只是扫描索引树。这种情况有两种发生方式:
-
如果索引是查询的覆盖索引,并且可以用来满足表中所需的所有数据,则仅扫描索引树。
这种情况下,Extra 列会显示 Using index,只进行索引扫描通常比 ALL 更快,因为索引的大小通常小于表数据。
-
全表扫描是通过读取索引来按索引顺序查找数据行来执行的。 Uses index 不会出现在 Extra 列中。
当查询仅使用属于单个索引的列时,MySQL 会使用 index 联接类型。
-
-
ALL
对先前表中的每个行组合进行全表扫描。如果该表是第一个未标记为 const 的表,这通常不好,并且在所有其他情况下通常非常糟糕。
通常,我们可以通过添加索引来避免 ALL,这些索引允许根据早期表中的常量值或列值从表中检索行。
possible_keys
该possible_keys列指示 MySQL 可以选择从中查找该表中的行的索引。请注意,此列完全独立于 的输出中显示的表顺序 EXPLAIN。这意味着在实践中,某些键possible_keys可能无法用于生成的表顺序。
如果此列是NULL(或在 JSON 格式的输出中未定义),则没有相关索引。在这种情况下,您可以通过检查子句来WHERE 检查它是否引用某些适合建立索引的列,从而提高查询的性能。如果是这样,请创建适当的索引并再次检查查询 EXPLAIN。
key
该key列表示 MySQL 实际决定使用的键(索引)。如果 MySQL 决定使用其中一个possible_keys 索引来查找行,则该索引将被列为键值。
可能key会指定值中不存在的索引 possible_keys。如果没有possible_keys索引适合查找行,但查询选择的所有列都是其他索引的列,则可能会发生这种情况。也就是说,命名索引覆盖了选定的列,因此虽然它不用于确定要检索哪些行,但索引扫描比数据行扫描更有效。
对于InnoDB,即使查询还选择主键,辅助索引也可能覆盖所选列,因为InnoDB每个辅助索引都存储主键值。如果 key是NULL,MySQL 找不到可用于更有效地执行查询的索引。
要强制 MySQL 使用或忽略列中列出的索引 possible_keys,请 在查询中使用FORCE INDEX、USE INDEX或。
ref
该ref列显示将哪些列或常量与列中指定的索引进行比较 key以从表中选择行。如果值为func,则使用的值是某个函数的结果。
参考: