深入理解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中,第一个SELECTid最小,随后的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 table1id可能是2,而外层的JOIN查询的id可能是1。尽管id值较大,子查询实际上会先执行。

  • UNION查询

    EXPLAIN SELECT * FROM table1 UNION SELECT * FROM table2;
    

    在这种情况下,第一个SELECTid可能是1,第二个SELECTid可能是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 EXPLAINtype字段的每个值的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,包含nameage列。
    • 查询只涉及到这两列,因此MySQL可以只扫描索引而不是整个表:
      SELECT name, age FROM user USE INDEX (idx_name_age);
      
    • 这个查询中,EXPLAIN的结果可能会在Extra列显示Using index
  2. 按索引顺序的全表扫描

    • 假设user表有一个索引idx_ageage列上。
    • 这个查询虽然使用了age索引,但需要返回表中的其他未索引列,导致全表扫描:
      SELECT name, age FROM user ORDER BY age;
      
    • 在这种情况下,EXPLAINExtra列不会显示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为了处理查询,需要创建临时表。这种情况常见于需要对结果进行排序或分组的查询,如使用DISTINCTGROUP 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 indexUsing indexExtra字段中出现的具体情况取决于查询是否满足某些特定条件:

  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
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。