深入理解mysql的explain命令

2023-12-14 12:36:11

1 基础

全网最全 | MySQL EXPLAIN 完全解读

1.1 MySQL中EXPLAIN命令提供的字段包括：

  • id：查询的标识符。
  • select_type：查询的类型（如SIMPLE, PRIMARY, SUBQUERY等）。
  • table：查询的是哪个表。
  • partitions：查询中涉及的分区。
  • type：join类型（如ALL, index, range等）。
  • possible_keys：可能用于此查询的索引。
  • key：实际使用的索引。
  • key_len：使用的索引的长度。
  • ref：哪些列或常量被用于查找索引列上的值。
  • rows：估计要检查的行数。
  • filtered：按表条件过滤的行百分比。
  • Extra：关于查询执行的额外信息。

2 关于id字段的疑惑

2.1 id越大越先执行吗？（不完全对）

记住，id字段更多地表示查询中的逻辑顺序，而不一定完全代表物理执行顺序。在涉及复杂查询，尤其是嵌套子查询和UNION的情况下，理解id字段对于分析查询性能至关重要。

在MySQL的EXPLAIN命令中，id字段表示查询中各个SELECT子句的执行顺序或层级。理解id字段有时可能会有些复杂，但以下是一些基本的规则和示例：

  1. 相同的id：具有相同id值的行表示它们是同一层级的查询部分，通常是因为JOIN操作或UNION。

  2. 递增的id：通常，id值较小的SELECT会先执行，id值较大的后执行。但在涉及子查询的情况下，子查询（较大的id）实际上会先执行，因为它们的结果通常需要被外层查询（较小的id）所使用。

  3. 子查询：子查询的id通常大于主查询的id。子查询先执行，因为外层查询依赖它们的结果。

  4. UNION：在UNION中，第一个SELECT的id最小，随后的SELECT``id逐渐增大。

示例

  • 简单的JOIN查询：

    EXPLAIN SELECT * FROM table1 JOIN table2 ON table1.id = table2.id;

    这里可能会看到两行输出，每行的id都是1，表示这是一个单一层级的查询。

  • 包含子查询：

    EXPLAIN SELECT * FROM (SELECT * FROM table1) AS subquery JOIN table2 ON subquery.id = table2.id;

    这里可能会看到两行输出，子查询SELECT * FROM table1的id可能是2，而外层的JOIN查询的id可能是1。尽管id值较大，子查询实际上会先执行。

  • UNION查询：

    EXPLAIN SELECT * FROM table1 UNION SELECT * FROM table2;

    在这种情况下，第一个SELECT的id可能是1，第二个SELECT的id可能是2。

3 关于select_type字段的疑惑

3.1 select_type有哪些字段值

在MySQL的EXPLAIN命令输出中，select_type字段用于描述SELECT语句的类型。以下是select_type可能的值及其含义：

  1. SIMPLE：简单的SELECT，不涉及子查询或UNION。

  2. PRIMARY：查询中最外层的SELECT。

  3. SUBQUERY：SELECT中的子查询。

  4. DEPENDENT SUBQUERY：子查询中的SELECT，其结果依赖于外部查询。

  5. UNION：UNION操作中的第二个或后续的SELECT。

  6. DEPENDENT UNION：UNION中的第二个或后续的SELECT，其结果依赖于外部查询。

  7. UNION RESULT：UNION查询的结果。

  8. DERIVED：派生表的SELECT，例如，出现在FROM子句中的子查询。

这些不同的select_type值有助于理解复杂查询的执行过程和性能表现。

3.2 针对select_type的字段值，你能各给一个例子吗

当然，以下是select_type字段的各种值及其相应的SQL查询示例：

  1. SIMPLE：

    SELECT * FROM table1 WHERE column1 = 'value';

    这是一个没有子查询或UNION的简单查询。

  2. PRIMARY：

    SELECT * FROM table1 WHERE column1 IN (SELECT column1 FROM table2);

    这里，最外层的SELECT是PRIMARY。

  3. SUBQUERY：

    SELECT * FROM table1 WHERE column1 IN (SELECT column1 FROM table2);

    这里，(SELECT column1 FROM table2)是一个SUBQUERY。

  4. DEPENDENT SUBQUERY：

    SELECT * FROM table1 WHERE column1 IN (SELECT column1 FROM table2 WHERE table2.column2 = table1.column2);

    子查询依赖于外部查询的结果。

  5. UNION：

    SELECT * FROM table1 UNION SELECT * FROM table2;

    第二个SELECT（和任何后续的SELECT）被视为UNION。

  6. DEPENDENT UNION：
    这种情况较为复杂，通常涉及到外部查询影响UNION部分的子查询。

  7. UNION RESULT：

    SELECT * FROM (SELECT * FROM table1 UNION SELECT * FROM table2) AS union_result;

    这里，UNION的结果被作为派生表处理。

  8. DERIVED：

    SELECT * FROM (SELECT * FROM table1) AS derived_table;

    这里，内部的SELECT是一个派生表查询。

这些例子展示了不同select_type值在实际查询中的应用。理解这些类型有助于分析和优化复杂的SQL查询。

4 索引type字段

4.1 explain的type字段有哪些？

在MySQL的EXPLAIN命令输出中，type字段是一个非常重要的指标，它显示了MySQL决定使用哪种方式来访问表。以下是type字段可能的值，按性能从好到坏排序：

  1. system：表只有一行数据（等同于系统表），是const类型的一个特例。
  2. const：表示通过索引一次就找到了，适用于主键或唯一索引的等值查询，const 查询速度非常快，因为最多只返回一行数据。
  3. eq_ref：一对一关系，唯一索引扫描，用于主键或唯一索引的关联查询。
  4. ref：非唯一索引扫描，返回匹配某个单个值的所有行。
  5. fulltext：全文索引。
  6. ref_or_null：类似ref，但是MySQL会额外搜索包含NULL值的行。
  7. index_merge：表示使用了索引合并优化。
  8. unique_subquery：在IN子句中用到的唯一索引查询，该类型和eq_ref类似，但是使用了IN查询，且子查询是主键或者唯一索引。
  9. index_subquery：在IN子句中用到的非唯一索引查询，和unique_subquery类似，只是子查询使用的是非唯一索引。
  10. range：只检索给定范围的行，使用一个索引来选择行。范围扫描，表示检索了指定范围的行，主要用于有限制的索引扫描。比较常见的范围扫描是带有BETWEEN子句或WHERE子句里有>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN()等操作符。
  11. index：全索引扫描。
  12. all： 查询条件没有索引，需要全表扫描
    index：全索引扫描，和ALL类似，只不过index是全盘扫描了索引的数据。当查询仅使用索引中的一部分列时，可使用此类型。有两种场景会触发：
  • 如果索引是查询的覆盖索引，并且索引查询的数据就可以满足查询中所需的所有数据，则只扫描索引树。此时，explain的Extra 列的结果是Using index。index通常比ALL快，因为索引的大小通常小于表数据。
  • 按索引的顺序来查找数据行，执行了全表扫描。此时，explain的Extra列的结果不会出现Uses index
  1. ALL：全表扫描，性能最差。

4.2 以下是针对MySQL EXPLAIN中type字段的每个值的SQL查询示例：

当然，以下是针对MySQL EXPLAIN中type字段的每个值的SQL查询示例：

  1. system：
    -- 假设table1只有一行数据
SELECT * FROM table1;
  2. const：
    -- 假设id为主键
SELECT * FROM table WHERE id = 1;
  3. eq_ref：
    -- 假设table2的id是table1的外键
SELECT * FROM table1 JOIN table2 ON table1.id = table2.id;
  4. ref：
    -- 假设column是非唯一索引
SELECT * FROM table WHERE column = 'value';
  5. fulltext：
    -- 假设对column列有全文索引
SELECT * FROM table WHERE MATCH(column) AGAINST('text');
  6. ref_or_null：
    -- 假设column是非唯一索引，且可能包含NULL
SELECT * FROM table WHERE column = 'value' OR column IS NULL;
  7. index_merge：
    -- 假设对column1和column2有单独的索引
SELECT * FROM table WHERE column1 = 'value1' OR column2 = 'value2';
  8. unique_subquery：
    -- 假设id为唯一索引
SELECT * FROM table WHERE id IN (SELECT id FROM table2 WHERE column = 'value');
  9. index_subquery：
    -- 假设column为非唯一索引
SELECT * FROM table WHERE column IN (SELECT column FROM table2 WHERE column2 = 'value');
  10. range：
    -- 假设对column有索引
SELECT * FROM table WHERE column BETWEEN 'value1' AND 'value2';
  11. index：
    -- 假设对column有索引
SELECT column FROM table;
  12. ALL：
    -- 无索引的全表扫描
SELECT * FROM table;

这些示例演示了如何在不同情况下生成特定的type值，帮助理解EXPLAIN输出中的这些类型。

4.3 如何理解这里的index，

index：全索引扫描，和ALL类似，只不过index是全盘扫描了索引的数据。当查询仅使用索引中的一部分列时，可使用此类型。有两种场景会触发：

  • 如果索引是查询的覆盖索引，并且索引查询的数据就可以满足查询中所需的所有数据，则只扫描索引树。此时，explain的Extra 列的结果是Using index。index通常比ALL快，因为索引的大小通常小于表数据。
  • 按索引的顺序来查找数据行，执行了全表扫描。此时，explain的Extra列的结果不会出现Uses index。

当然，这里是两个触发index查询类型的MySQL示例：

  1. 使用覆盖索引的查询：

    • 假设user表有一个覆盖索引idx_name_age，包含name和age列。
    • 查询只涉及到这两列，因此MySQL可以只扫描索引而不是整个表：
      SELECT name, age FROM user USE INDEX (idx_name_age);
    • 这个查询中，EXPLAIN的结果可能会在Extra列显示Using index。

  2. 按索引顺序的全表扫描：

    • 假设user表有一个索引idx_age在age列上。
    • 这个查询虽然使用了age索引，但需要返回表中的其他未索引列，导致全表扫描：
      SELECT name, age FROM user ORDER BY age;
    • 在这种情况下，EXPLAIN的Extra列不会显示Using index，因为它需要访问表中未被索引的数据。

在第一个例子中，查询可以完全通过索引来满足，而在第二个例子中，虽然查询利用了索引的顺序，但仍然需要访问表中的额外数据。这两种情况都会触发index类型的查询，但由于不同的使用方式，它们在性能上可能有所差异。

5 一些其他的结果列

5.1 possible_keys中有哪些值

展示当前查询可以使用哪些索引，这一列的数据是在优化过程的早期创建的，因此有些索引可能对于后续优化过程是没用的。

5.1.1 key

表示MySQL实际选择的索引

5.1.2 key_len

索引使用的字节数。由于存储格式，当字段允许为NULL时，key_len比不允许为空时大1字节。
key_len计算公式： https://www.cnblogs.com/gomysql/p/4004244.html
在这里插入图片描述

5.1.3 ref

表示将哪个字段或常量和key列所使用的字段进行比较。
如果ref是一个函数，则使用的值是函数的结果。要想查看是哪个函数，可在EXPLAIN语句之后紧跟一个SHOW WARNING语句。

5.1.4 rows

MySQL估算会扫描的行数，数值越小越好。

5.1.5 filtered

表示符合查询条件的数据百分比，最大100。用rows × filtered可获得和下一张表连接的行数。例如rows = 1000，filtered = 50%，则和下一张表连接的行数是500。

TIPS
在MySQL 5.7之前，想要显示此字段需使用explain extended命令；
MySQL.5.7及更高版本，explain默认就会展示filtered

6 extra列

6.1 比较关键的几个信息字段

在MySQL的EXPLAIN命令输出中，Extra列提供了关于查询执行的附加信息，这些信息有助于理解MySQL是如何处理查询的。以下是一些关键的Extra字段及其含义的详细介绍：

  1. Using index：这表示查询仅通过索引来获取所需的数据，而无需额外读取表中的数据行。这通常发生在覆盖索引的情况下，即查询的所有字段都包含在索引中。

  2. Using where：这表示MySQL服务器在从存储引擎接收到数据行后，使用了额外的WHERE条件来进一步过滤结果。

  3. Using index condition：这种情况下，查询的某些列可能未被完全覆盖在索引中。MySQL会首先使用索引来过滤数据，然后使用WHERE子句中的其余条件进一步筛选这些已过滤的数据行。

  4. Using temporary：这表示MySQL为了处理查询，需要创建临时表。这种情况常见于需要对结果进行排序或分组的查询，如使用DISTINCT、GROUP BY或某些JOIN操作。

  5. Using filesort：MySQL使用了外部排序方法来对结果进行排序，而不是直接通过索引排序。当数据量较小时，排序可能在内存中完成，但较大数据量可能需要磁盘排序。出现这种情况时，通常考虑使用索引来优化排序操作。

  6. Select tables optimized away：这通常发生在使用聚合函数（如MAX()或MIN()）查询索引列时。MySQL优化器能够直接从索引中获取所需的最大或最小值，而无需实际扫描表中的行。

这些Extra字段值提供了查询优化和性能调整的重要线索。通过理解这些值的含义，可以更好地分析查询效率，并在必要时进行相应的优化。

6.2 分别给我举一个例子

当然，这里是MySQL EXPLAIN命令中Extra列关键字段的示例：

  1. Using index：

    -- 假设在name字段上有索引
EXPLAIN SELECT name FROM users WHERE name = 'Alice';
-- 这个查询可能显示Using index，因为它只涉及到索引字段

  2. Using where：

     EXPLAIN SELECT * FROM users WHERE age > 25 AND name = 'Alice';
 -- 假设在name字段上有索引，但没有在age字段上。这里，MySQL会使用索引找到所有名为'Alice'的行，然后在返回的结果上应用`age > 25`的条件。

  3. Using index condition：（索引下推技术）

    -- 假设在（name，age)字段上有联合索引，
EXPLAIN SELECT name, age FROM users WHERE name LIKE 'A%' AND age > 18;
-- 这个查询可能显示Using index condition

  4. Using temporary：

    -- 假设name字段没有索引
EXPLAIN SELECT DISTINCT name FROM users;
-- 这个查询可能显示Using temporary，因为需要创建临时表来处理DISTINCT

  5. Using filesort：

    -- 假设name字段没有索引
EXPLAIN SELECT * FROM users ORDER BY name;
-- 这个查询可能显示Using filesort，因为需要对结果进行排序

  6. Select tables optimized away：

    -- 假设id字段是主键
EXPLAIN SELECT MIN(id) FROM users;
-- 这个查询可能显示Select tables optimized away，因为可以直接从索引中获得最小值

这些示例展示了不同Extra字段值的典型应用场景。通过这些示例，您可以更好地理解EXPLAIN输出中这些值的含义及其对查询优化的指导意义。

6.3 对于index级别以上的是不是都会导致extra的using index？

在MySQL中，EXPLAIN命令的type字段描述了表的访问类型，而Extra字段则提供了关于查询执行的额外信息。type字段中index级别以上的访问类型并不总是导致Extra字段显示Using index。Using index在Extra字段中出现的具体情况取决于查询是否满足某些特定条件：

  1. Using index出现在Extra字段中的条件是：

    • 查询操作仅涉及索引中的列，即是所谓的“覆盖索引”。即使查询类型是index（全索引扫描），但如果查询的列不完全在索引中，Using index就不会出现。

  2. 对于type字段中不同级别的类型，Using index的出现情况如下：

    • const、eq_ref：这些类型通常表示索引被有效利用，但是否使用Using index取决于查询的列是否完全由索引覆盖。
    • ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range：这些类型中的Using index出现也同样依赖于查询是否为覆盖索引的情况。
    • index：尽管表示全索引扫描，但Using index仅当查询列完全由索引覆盖时才会出现。
    • ALL：此类型通常表示全表扫描，不会出现Using index，因为它不依赖于索引来检索数据。

因此，并非type字段中的index级别以上的访问类型都会导致Extra字段出现Using index。是否出现Using index取决于查询是否能够仅通过索引来获取所需数据，而不需要访问表的数据行。

文章来源:https://blog.csdn.net/yxg520s/article/details/134842092
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。