Mysql查看执行计划explain关键字Extra字段的每个类型原理

MySQL的 EXPLAIN 命令中 Extra 字段提供了关于 MySQL 如何执行查询的额外重要信息，这些信息揭示了优化器在解析和执行查询时的具体行为和优化策略。理解这些类型对于诊断性能问题（特别是慢查询）至关重要。

以下是常见 Extra 类型及其原理的详细解释：

🚨 关键性能警示（通常需要优化）

  1. Using filesort

    • 原理: MySQL 无法利用索引直接获取排好序的结果集。它必须执行一个额外的排序步骤，将符合 WHERE 条件的行读入内存（或磁盘，如果结果集太大）进行排序。

    • 触发场景: ORDER BY 或 GROUP BY（隐式排序）涉及的列没有合适的索引，或者索引顺序无法满足排序要求。

      • 示例：SELECT * FROM table ORDER BY b且b无索引。

    • 优化方向: 为 ORDER BY/GROUP BY 的列创建合适的索引。考虑复合索引的顺序，确保其能直接满足排序需求。如果排序量非常大且内存不足 (sort_buffer_size)，会导致昂贵的磁盘临时文件排序，性能急剧下降。

  2. Using temporary

    • 原理: MySQL 需要创建一张内部临时表来保存查询的中间结果，以便完成某些操作（如去重、排序、分组、UNION 等）。临时表可能存储在内存（MEMORY 引擎）或磁盘（MyISAM 或 InnoDB 引擎）上。

    • 触发场景:

      • GROUP BY 和 ORDER BY 子句中的列不同。

        • 示例：SELECT COUNT(*) FROM table GROUP BY c且c无索引。

      • 包含 DISTINCT 且无法用索引优化。

      • 某些 UNION 查询。

      • 一些派生表（子查询在 FROM 子句中）。

      • 某些 JOIN 优化策略（如 BNL）。

    • 优化方向: 优化查询语句，特别是 GROUP BY 和 ORDER BY 尽量使用相同的列和顺序。为 GROUP BY 列创建索引。避免不必要的 DISTINCT。增大 tmp_table_size 和 max_heap_table_size 可以减少临时表落盘的概率，但根本是减少临时表的产生。

  3. Using intersect/union（索引交集/并集），复合索引的作用，为什么不能只建立单列索引

    • 原理：合并多个索引范围的结果。
      • 示例：SELECT * FROM table WHERE a=1 OR b=2且a,b均有索引。
    • 优化：可尝试创建复合索引替代多个单列索引。

📍 索引使用相关信息（通常较好，但也需分析）

  1. Using index (覆盖索引 Covering Index)

    • 原理: 查询所需的所有列都包含在使用的索引中（即索引"覆盖"了查询）。这意味着引擎层仅通过扫描索引就能获取所需数据，无需回表（访问主键索引或数据行）。这是非常高效的访问方式。

    • 触发场景: SELECT 的列列表、WHERE 的过滤条件、JOIN 的连接条件都只涉及某个索引中的列。

      • 组合索引(a,b,c)支持查询SELECT a,b FROM table WHERE a=1，因所有字段均在索引中。

    • 优化方向: 尽量设计覆盖索引来满足高频查询。注意索引列的维护成本。

  2. Using where

    • 原理: 虽使用索引定位数据，但需回表后用WHERE条件进一步过滤。存储引擎返回的行（通过索引扫描或全表扫描）需要在 Server 层 进行额外的过滤检查，以满足 WHERE 子句中的条件。这表示索引未能完全过滤掉所有不符合条件的行。

    • 触发场景:

      • WHERE 条件中使用了索引列，但索引是范围扫描（如 >, <, BETWEEN, LIKE 'prefix%'），范围扫描后的行可能不满足所有条件。

      • WHERE 条件中包含了未被索引覆盖的列。

        • 索引(a)支持SELECT * FROM table WHERE a>1 AND b=2，但b不在索引中，需回表过滤。

      • WHERE 条件使用了函数或表达式作用于索引列（可能导致索引失效）。

      • 全表扫描时，所有过滤都在 Server 层完成。

    • 优化方向: 检查 WHERE 条件，确保索引被有效利用。考虑创建更合适的索引（覆盖更多条件列或使用索引条件下推）。避免在索引列上使用函数或计算。，或调整查询减少回表。

  3. Using index condition (索引条件下推 Index Condition Pushdown - ICP)

    • 原理: 这是对 Using where 的一种优化。对于复合索引，WHERE 条件中包含了索引列但无法完全作为索引前缀来使用（即只用了索引的一部分列进行查找），剩余部分的索引列条件（WHERE 子句中）会被"下推"到存储引擎层进行过滤。存储引擎在读取索引时就应用这些条件，过滤掉更多不符合条件的行，减少回表次数或传递给 Server 层的行数。

    • 触发场景: 使用了复合索引，WHERE 条件包含了索引中的列（不一定是前缀列），且存储引擎支持 ICP（InnoDB 和 MyISAM 支持）。

      • 示例：索引(a,b)支持SELECT * FROM table WHERE a=1 AND b LIKE 'x%'，先通过a=1定位索引，再在存储引擎层用b LIKE 'x%'过滤。

    • 优化方向: 设计良好的复合索引。确保 ICP 被启用（默认开启）。ICP 显著减少了不必要的回表和数据传输。

  4. Select tables optimized away

    • 原理：聚合函数（如MIN/MAX）或GROUP BY直接通过索引获取结果，无需扫描表。
      • 示例：SELECT MIN(a) FROM table且a有索引。
    • 优化：已高效，确保索引维护。

🔗 Join 操作相关信息

  1. 连接缓冲区 Using join buffer (Block Nested Loop) / Using join buffer (Batched Key Access) / Using join buffer (hash join)

    • 原理: 当进行 JOIN 操作（尤其是没有合适索引驱动时），MySQL 会使用一个连接缓冲区 (join_buffer) 来优化。

      • BNL (Block Nested Loop): 将驱动表（第一个表）的一部分行缓存在 join_buffer 中，然后遍历被驱动表（第二个表），将 join_buffer 中的每一块与被驱动表的每一行进行比较。减少了对驱动表的访问次数。

      • BKA (Batched Key Access): 一种更高级的优化，通常需要 MRR。它缓存驱动表关联键的多行，然后批量地向被驱动表的索引发起请求（通常是主键或唯一键），利用 MRR 的排序特性减少磁盘随机 I/O。需要 mrr=on 和 batched_key_access=on（或优化器自动选择）。

      • hash join (MySQL 8.0.18+): 将驱动表（较小的表）在 join_buffer 中构建一个哈希表，然后扫描被驱动表，对每一行计算哈希值并在哈希表中查找匹配项。对于等值连接 (=) 且没有高效索引可用时非常高效。

    • 触发场景: JOIN 时被驱动表缺少有效的索引来匹配 ON/USING 条件。优化器认为使用连接缓冲区比多次随机访问被驱动表更高效。

      • 示例：SELECT * FROM a JOIN b ON a.x=b.y且x,y无索引。

    • 优化方向: 为被驱动表的连接字段添加索引是根本解决方案。增大 join_buffer_size 可以提升 BNL/BKA 性能（但消耗内存）。哈希连接在 MySQL 8.0 后是首选。

  2. Not exists

    • 原理: MySQL 对 LEFT JOIN 查询应用了一种优化。当执行 LEFT JOIN 并指定 WHERE table2.column IS NULL 时（查找在 table1 中存在但在 table2 中不存在的行），一旦在 table2 中找到任何一行匹配 table1 的当前行，优化器就知道该 table1 行不符合 IS NULL 条件，会立即停止在 table2 中查找该行的更多匹配项。

    • 触发场景: SELECT ... FROM t1 LEFT JOIN t2 ON ... WHERE t2.key_column IS NULL。且 t2.key_column 定义为 NOT NULL。

    • 优化方向: 这种优化通常是好的，表明优化器有效地利用了 LEFT JOIN ... IS NULL 模式。

  3. Select tables optimized away / Impossible HAVING / Impossible WHERE / No matching min/max row / unique row not found

    • 原理: 这些表明优化器在查询优化阶段就确定了某些部分的结果。

      • Select tables optimized away: 对于 MIN()/MAX() 查询，如果存在覆盖该列的索引（通常是索引的最左或最右端值），引擎可以直接从索引中读取极值而无需扫描表或索引行。

      • Impossible HAVING/Impossible WHERE: HAVING 或 WHERE 条件永远不可能为真（如 1=0）。

      • No matching min/max row: MIN()/MAX() 函数应用于一个空结果集或 WHERE 条件不可能满足。

      • unique row not found: 尝试访问一个根据唯一键（主键或唯一索引）应存在但实际不存在的行（如 SELECT ... FROM tbl_name WHERE primary_key=non_existent_value）。

    • 触发场景: 查询包含聚合函数且索引可用，或查询条件逻辑上不可能满足。

    • 优化方向: 这些通常是优化器高效工作的标志，不需要额外优化。

  4. Start temporary, End temporary

    • 原理: 用于优化 IN 或 EXISTS 子查询的 Duplicate Weedout 策略。MySQL 创建一个内部临时表来记录子查询返回的重复主键（或唯一键）。Start temporary 表示开始为该 SELECT 填充临时表，End temporary 表示开始使用该临时表进行去重。

    • 触发场景: 优化器选择使用 Duplicate Weedout 策略来处理 IN/EXISTS 子查询，且子查询可能返回重复行。

    • 优化方向: 理解子查询执行策略。有时重写为 JOIN 可能更高效。

  5. Range checked for each record

    • 原理：JOIN操作中，需逐行检查范围条件，通常因索引缺失。
      • 示例：SELECT * FROM a JOIN b ON a.x BETWEEN b.y AND b.z且无有效索引。
    • 优化：为JOIN条件字段添加索引。

🔗 其他操作相关信息

 

  1. Using MRR（多范围读取）

 

    • 原理：优化多范围查询的I/O效率，按索引顺序读取数据。
      • 示例：SELECT * FROM table WHERE id IN (1,3,5)。
    • 优化：通常无需干预，确保索引有效。

  2. Impossible WHERE/HAVING

    • 原理：WHERE/HAVING条件矛盾，无匹配数据。
      • 示例：SELECT * FROM table WHERE 1=0。
    • 优化：检查条件逻辑或数据一致性。

  3. No matching min/max row

    • 原理：MIN/MAX查询无符合条件的数据。
      • 示例：SELECT MAX(a) FROM table WHERE a < 0且表中a均大于0。
    • 优化：验证数据分布或调整查询条件。

  4. Distinct/FirstMatch

    • 原理：优化器提前终止扫描或去重。
      • 示例：子查询中使用EXISTS替代IN。
    • 优化：检查子查询结构，必要时重写为JOIN。

  5. Full scan on NULL key

    • 原理：子查询中NULL值导致全表扫描。
      • 示例：SELECT * FROM a WHERE b IN (SELECT c FROM b WHERE d IS NULL)且d允许NULL。
    • 优化：添加NOT NULL约束或调整查询逻辑。

  6. Zero limit

    • 原理：LIMIT 0导致无结果返回，通常用于语法检查。
    • 优化：确认业务逻辑是否需要此类查询。

📌 总结与关键点

  • Using filesort 和 Using temporary 通常是性能瓶颈的红色警报，需要优先优化。

  • Using index (覆盖索引) 是最理想的访问方式之一。

  • 避免Using where 或 Using join buffer 表示 JOIN 可能因缺少索引而效率较低。

  • Not exists 是 LEFT JOIN ... IS NULL 查询的有效优化。

  • 其他类型（如 Impossible WHERE）通常表示优化器做了早期优化。

  • Extra 信息需要结合 type (访问类型) 和 key (使用的索引) 等字段一起分析才能全面理解查询执行计划。

  • MySQL 版本不同，支持的 Extra 类型和优化策略会有差异（特别是 5.6, 5.7, 8.0 之间）。

通过仔细分析 EXPLAIN 输出中的 Extra 字段，你可以深入了解 MySQL 执行查询的内部机制，识别潜在的性能问题，并指导索引优化和查询重写。💪🏻

posted @ 2025-06-29 10:23  飘来荡去evo  阅读(57)  评论(0)    收藏  举报