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 KEYUNIQUE 索引与常量值进行比较时,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 KEYUNIQUE 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,则使用的值是某个函数的结果。

参考:

posted @ 2024-01-05 15:11  LARRY1024  阅读(278)  评论(0编辑  收藏  举报