EXPLAIN 输出字段详解

原创于 2025-09-23 22:43:31 发布 · 1.1k 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #java #spring #后端

假设我们执行一个简单的 EXPLAIN，会返回一个包含多列的表。以下是这些列的含义，按照重要性从高到低排序：

1. `id`（查询标识符）

含义：SELECT 查询的序列号。表示查询中执行 SELECT 子句或操作表的顺序。
解读：
- id 相同：执行顺序由上至下。例如在连接查询（JOIN）中，所有表的 id 都是 1，执行顺序就是 EXPLAIN 输出中表的排列顺序。
- id 不同：如果是子查询，id 的序号会递增。id 值越大，优先级越高，越先执行。
- id 为 NULL：通常表示这是一个结果集，不需要查询（例如由 UNION 合并的结果）。

2. `select_type`（查询类型）

含义：说明了 SELECT 的类型，主要用于区分普通查询、子查询、联合查询等复杂查询。
常见值：
- SIMPLE：最简单的 SELECT，不包含子查询或 UNION。
- PRIMARY：查询中若包含任何复杂的子部分，最外层的 SELECT 被标记为 PRIMARY。
- SUBQUERY：在 SELECT 或 WHERE 列表中包含了子查询。
- DERIVED：在 FROM 列表中包含的子查询被标记为 DERIVED（衍生表）。MySQL 会递归执行这些子查询，把结果放在临时表中。
- UNION：UNION 中的第二个或后面的 SELECT 语句。
- UNION RESULT：从 UNION 表获取结果的 SELECT。

3. `table`（访问的表）

含义：显示这一步访问的是哪张表。
解读：
- 可以是具体的表名。
- 也可以是 <derivedN>，其中 N 是 id 值，表示这个结果来源于 id=N 的衍生查询。
- 或者是 <unionM,N>，表示来源于 id 为 M 和 N 的 UNION 结果。

4. `partitions`（匹配的分区）

含义：查询匹配到的分区。如果表没有被分区，则该值为 NULL。这对于分区表优化很重要。

5. `type`（访问类型）【极其重要】

对于Type字段
- NULL:
  - 含义： 表示查询不需要访问任何表或索引。这通常发生在优化阶段即可完成计算。
  - 场景：
    - 从索引列中选取最小值或最大值（如 SELECT MIN(key) FROM table 且 key 有索引）。
    - 在索引列上使用 COUNT(*) (当 MyISAM 表且查询不带 WHERE 子句时，有时也直接可得)。
  - 性能： 最优！ 因为它完全不涉及读取表数据页（data pages）。
- system:
  - 含义： const 类型的一个特例。表明查询只返回一行数据。
  - 场景：出现在两种特殊情况：
    - 表本身只有一行数据（比如系统表 mysql.proxies_priv）。
    - const 查询的结果恰好只有一行。
  - 性能： 性能非常好，等同于 const，因为它也只读取一行。
- const:
  - 含义： 表示通过主键（Primary Key）或唯一索引（Unique Index）进行等值查询时，至多返回一行数据。这里实际上也只有做出了where的限制才会有
  - 场景：
    - SELECT * FROM table WHERE primary_key = 'value';
    - SELECT * FROM table WHERE unique_column = 'value';
  - 性能： 非常快！ MySQL 能够直接将常量值转换为表中的一个特定行引用，只读一行且非常高效。是优化器追求的访问类型。
- eq_ref: 相当于联合查询的唯一索引或主键
  - 含义： 这是多表连接（JOIN）中性能最好的连接方式。当一个表的每一行，在另一个表中根据唯一索引（或主键）进行等值匹配时，只找到一行与之对应。
  - 场景：
    
    通常在多表JOIN的驱动表被指定为PRIMARY KEY或UNIQUE NOT NULL索引列进行等值查找时出现。
    - 例如：SELECT * FROM a JOIN b ON a.id = b.id;（其中 b.id 是主键或唯一非空键）
    - 对于驱动表 a 中的每一行，MySQL 只需在 b 表中精确查找一次。
  - 性能： 非常高效，接近 const。是 JOIN 操作的理想类型。
- ref: 非唯一索引这个也是通过where做出限制的但是可能有多个相同索引所以耗时比唯一索引多
  - 含义： 使用普通索引（非唯一索引）进行等值比较（=）。查找时可能返回多个匹配的行。如果是 JOIN，则对于驱动表（前一个表）的每一行，需要在当前表中使用索引查找匹配的行。
  - 场景：
    - SELECT * FROM table WHERE index_column = 'value'; (普通索引)
    - SELECT a.* FROM a JOIN b ON a.non_unique_key = b.key;
  - 性能： 良好到较好。 性能取决于匹配的行数。匹配行少则快，匹配行多则慢于 eq_ref 或 const。比全表扫描好很多。
- range:
  - 含义： 表示使用了索引来检索给定范围的行。扫描范围起始点和终止点都是确定的。
  - 场景：
    
    索引列上使用了范围操作符，如：
    - = (匹配范围值，如索引是前缀索引)
    - > / >=
    - < / <=
    - BETWEEN
    - IN() (在某些情况下被视为范围扫描)
    - LIKE 'prefix%' (在索引字符串上使用常量前缀)
  - 性能： 中等。 比全表扫描好，但仍然需要扫描索引中一个范围内的条目。扫描范围越小，性能越好。
- index: 对于有索引的无论使唯一还是非唯一没有做出where限制的
  - 含义： 索引扫描（Index Scan）。MySQL 扫描了整个索引树（Index Tree）来查找所需的数据。这不需要全表扫描文件（Data File），但需要遍历索引文件。它可能是在覆盖索引（Covering Index）查询（即查询列都在索引中）时的一种优化选择（Using index），但也不一定总是如此。
  - 场景：
    - 查询只需要访问索引中的列（覆盖索引），即使没有 WHERE 子句（此时 key 是 PRIMARY 或其他索引）。
    - SELECT indexed_column FROM table;
    - 执行 ORDER BY primary_key 之类的操作时。
    - 如果查询条件无法有效过滤行，但结果集恰好能用索引覆盖，优化器可能选择 index 扫描避免回表。
  - 性能： 相对较差。 虽然避免了读取表数据文件（除非需要回表查询不在索引中的列），但需要扫描整个索引树（索引文件通常比数据文件小，但仍然是全量索引扫描）。通常比 ALL 类型好，但不如 ref 和 range 高效。
- ALL:
  - 含义： 全表扫描（Full Table Scan）。MySQL 从头到尾读取表中的每一行，以找到匹配的行。这是最低效的访问方式。
  - 场景：
    - 表没有适合查询条件的索引。
    - 索引选择性太差（例如查询条件是性别），导致优化器判断全表扫描更快。
    - 查询使用了在索引字段开头的模糊查询，如 LIKE '%suffix' 或 LIKE '%infix%'。
    - 对非索引字段排序（且无法用索引覆盖排序）。
  - 性能： 最差！ 对于大表来说，这会导致大量的 I/O 操作，性能急剧下降。是 SQL 优化的重点目标，需要尝试添加索引、优化查询条件或重写查询语句来避免。
目标：查询优化至少要做到 range 级别，最好能达到 ref。

6. `possible_keys`（可能使用的索引）

含义：显示查询可能使用哪些索引来查找。如果为 NULL，则表示没有可能的索引。

7. `key`（实际使用的索引）

含义：显示查询实际决定使用的索引。如果为 NULL，则表示没有使用索引。
注意：possible_keys 列出了可选的索引，但 MySQL 可能出于查询效率的考虑，选择了一个不在 possible_keys 中的索引，或者选择全表扫描。

8. `key_len`（使用的索引长度）

含义：表示索引中使用的字节数。可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。
解读：可以判断索引是否被完全使用。例如，一个复合索引 (a, b, c)，如果查询只用了 a 和 b，key_len 就是前两列的长度之和。

9. `ref`（索引的引用）

含义：显示索引的哪一列被使用了。它显示的是与 key 列指定的索引进行比较的列或常量。
常见值：
- const：常量值。
- 库名.表名.列名：来自另一个表的列。

10. `rows`（预估需要扫描的行数）【非常重要】

含义：MySQL 认为它必须检查以执行查询的行数的估计值。这是一个预估值，不是精确值。
解读：这个值越小越好。它是衡量查询效率的关键指标。

11. `filtered`（按条件过滤的百分比）

含义：表示存储引擎返回的数据在服务器层过滤后，剩下多少满足查询的记录数量的比例。注意是百分比，不是具体记录数。
解读：rows * filtered / 100 可以估算出将要与下一张表连接的行数。这个值越大越好。

12. `Extra`（额外信息）【非常重要】

含义：包含不适合在其他列中显示的额外信息，但非常重要。
常见值（好坏都有）：
- Using index：表示查询使用了覆盖索引，性能非常好。这意味着数据可以直接从索引中获取，而不需要读取数据行。
- Using where：表示 MySQL 服务器在存储引擎检索行后进行了二次过滤。这意味着索引可能没有被完全利用。
- Using temporary：表示 MySQL 需要使用临时表来存储结果集，常见于排序（ORDER BY）和分组查询（GROUP BY），性能较差。
- Using filesort：MySQL 无法利用索引完成的排序操作称为“文件排序”。这通常发生在 ORDER BY 子句上，性能较差。
- Using join buffer (Block Nested Loop)：表示连接查询时，被驱动表没有使用索引，需要用到连接缓冲区。
- Impossible WHERE：WHERE 子句的值总是 false，无法获取任何行（例如 WHERE 1=0）。