深入理解mysql的explain命令

news/2024/11/19 9:35:20/

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取决于查询是否能够仅通过索引来获取所需数据，而不需要访问表的数据行。


http://www.ppmy.cn/news/1260436.html

相关文章

很全面 影响无人机自动返航的因素总结

很全面 影响无人机自动返航的因素总结

在无人机技术不断成熟的今天&#xff0c;自主返航技术成为保障飞行安全的一种重要工具。无人机在多种情况下能够智能判断&#xff0c;主动实施返航动作&#xff0c;为用户提供更加可靠的飞行保障。以下是一些常见的无人机自动返航场景&#xff0c;让我们深入了解这项技术背后的…
阅读更多...
vue中的动画组件使用及如何在vue中使用animate.css

vue中的动画组件使用及如何在vue中使用animate.css

“< Transition >” 是一个内置组件&#xff0c;这意味着它在任意别的组件中都可以被使用&#xff0c;无需注册。它可以将进入和离开动画应用到通过默认插槽传递给它的元素或组件上。进入或离开可以由以下的条件之一触发&#xff1a; 由 v-if 所触发的切换由 v-show 所触…
阅读更多...
掌握Selenium4：详解各种定位方式

掌握Selenium4：详解各种定位方式

Selenium4中有多种元素定位方式&#xff0c;主要包括以下几种&#xff1a; 通过ID属性定位&#xff1a;根据元素的id属性进行定位。通过name属性定位&#xff1a;当元素没有id属性而有name属性时&#xff0c;可以使用name属性进行元素定位。通过class name定位&#xff1a;可以…
阅读更多...
短剧分销系统开发：短剧分销推广

短剧分销系统开发：短剧分销推广

近两年内&#xff0c;随着短剧的发展&#xff0c;节奏快、剧情紧凑的短剧逐渐成为了大众的“新宠”。短剧行业也凭着成本低、投资低、周期短的优势成为了众多创业者的首要选择&#xff0c;越来越多的专业团队加入到短剧的赛道中。 根据数据显示&#xff0c;我国短剧市场规模有…
阅读更多...
L0、Linux常用命令

L0、Linux常用命令

一、防火墙&#xff1a; 在 Linux 中&#xff0c;关闭防火墙可以使用不同的命令&#xff0c;这取决于你所使用的防火墙软件。在一些常见的 Linux 发行版中&#xff0c;防火墙可能是 iptables 或 firewalld两种&#xff1a; centos6使用iptables作为默认防火墙&#xff1b;cento…
阅读更多...
微信小程序怎么开通

微信小程序怎么开通

在当今数字化的世界里&#xff0c;微信小程序已经成为企业与用户进行交互的重要平台。它们不仅易于使用&#xff0c;而且可以提供丰富的功能和出色的用户体验。那么&#xff0c;你是否也想为自己的品牌或业务创建一个微信小程序呢&#xff1f;别急&#xff0c;今天我们就来分享…
阅读更多...
计算机图形图像技术（OpenCV核心功能、图像变换与图像平滑处理）

计算机图形图像技术（OpenCV核心功能、图像变换与图像平滑处理）

一、实验原理&#xff1a; 1、显示图像 void imshow(const string &name, InputArray image); ①功能&#xff1a;在指定窗口中显示图像。 ②参数&#xff1a;name为窗口的名字&#xff1b;image为待显示的图像。 ③说明&#xff1a;可显示彩色或灰度的字节图像和浮点数图…
阅读更多...
集成开发环境 PyCharm 的安装【侯小啾python基础领航计划 系列（二）】

集成开发环境 PyCharm 的安装【侯小啾python基础领航计划 系列（二）】

集成开发环境PyCharm的安装【侯小啾python基础领航计划 系列（二）】 大家好，我是博主侯小啾， 🌹꧔ꦿ🌹꧔ꦿ🌹꧔ꦿ🌹꧔ꦿ🌹꧔ꦿ🌹꧔ꦿ🌹꧔ꦿ🌹꧔ꦿ🌹꧔ꦿ🌹꧔ꦿ🌹꧔ꦿ🌹꧔ꦿ🌹꧔ꦿ🌹꧔ꦿ🌹꧔ꦿ🌹꧔ꦿ🌹꧔ꦿ🌹꧔ꦿ🌹꧔ꦿ🌹꧔ꦿ🌹꧔…
阅读更多...
最新文章

Copyright @ 2022~2023