索引 EXPLAIN 是 MySQL 中分析 SQL 执行计划的重要工具

EXPLAIN 是 MySQL 中分析 SQL 执行计划的重要工具，其输出的每个字段都反映了查询的执行细节。以下是 EXPLAIN 输出中各字段的含义（基于 MySQL 8.0 版本，按常用程度排序）：

1. id

• 含义：查询中每个操作的唯一标识，代表执行顺序。
• 规则：
  • id 相同：执行顺序由上至下（按表的连接顺序）。
  • id 不同：id 越大，优先级越高，越先执行（子查询的 id 通常更大）。
  • id 有相同也有不同：大 id 先执行，相同 id 按顺序执行。

2. select_type

• 含义：表示查询的类型（简单查询、子查询、联合查询等）。
• 常见值：
  • SIMPLE：简单查询（无子查询、无 UNION）。
  • PRIMARY：主查询（包含子查询时，最外层的查询）。
  • SUBQUERY：子查询（SELECT 或 WHERE 中的子查询，不依赖外层结果）。
  • DERIVED：派生表（FROM 后的子查询，会生成临时表）。
  • UNION：UNION 中的第二个及以后的查询。
  • UNION RESULT：UNION 结果的合并。

3. table

• 含义：当前行操作的表名（或临时表名，如 derived2 表示 id=2 的派生表）。

4. type

• 含义：表示表的访问方式（最核心的字段之一，反映查询效率）。
• 性能从好到差排序：
  • system：表中只有一行数据（如系统表），是 const 的特例。
  • const：通过主键或唯一索引精确匹配（只返回一行），速度极快。
  • eq_ref：多表连接时，被连接表通过主键或唯一索引匹配（每行只匹配一次）。
  • ref：非唯一索引的精确匹配（可能返回多行）。
  • range：索引范围查询（如 between、in、>, < 等）。
  • index：扫描全表索引（比 ALL 好，因为索引文件通常比数据文件小）。
  • ALL：全表扫描（最差，需优化）。
• 优化目标：至少达到 range 级别，最好是 ref 或 const。

5. possible_keys

• 含义：MySQL 优化器认为可能适用的索引（候选索引列表）。
• 注意：即使显示了索引，也不代表一定会使用（需结合 key 字段判断）。

6. key

• 含义：MySQL 实际使用的索引（如果为 NULL，表示未使用索引）。
• 分析：
  • 若 possible_keys 有值但 key 为 NULL：可能是索引选择性差（如字段值重复率高），优化器认为全表扫描更快。
  • 若 key 显示的索引不在 possible_keys 中：可能是优化器通过统计信息选择了更优的隐藏索引。

7. key_len

• 含义：实际使用的索引长度（字节数），反映索引被利用的程度。
• 作用：
  • 越长说明索引使用越充分（如联合索引中使用的字段越多，key_len 越大）。
  • 可用于判断联合索引是否被完全利用（如 key_len 等于所有字段长度之和，说明全字段使用）。

8. ref

• 含义：表示哪些值或字段被用来与 key 索引匹配。
• 示例：
  • const：使用常量匹配（如 where id = 1）。
  • db.table.column：使用其他表的字段匹配（如连接查询）。

9. rows

• 含义：MySQL 估计需要扫描的行数（非精确值）。
• 作用：数值越小，查询效率越高。可用于对比优化前后的效果（如加索引后 rows 大幅减少）。

10. Extra

• 含义：额外的执行细节（非常重要，包含优化关键信息）。
• 常见值：
  • Using index：使用了覆盖索引（查询的字段都在索引中，无需回表查数据），性能极佳。
  • Using where：使用了 WHERE 条件过滤，但未使用索引（可能是全表扫描后过滤）。
  • Using index condition：使用了索引下推（在存储引擎层用索引过滤部分条件，减少回表）。
  • Using temporary：创建了临时表（如 GROUP BY 或 DISTINCT 无合适索引时），性能差，需优化。
  • Using filesort：需要额外排序（如 ORDER BY 字段无索引），性能差，需优化。
  • Range checked for each record：没有合适的索引， MySQL 为每行数据检查是否有匹配的索引，效率极低。

总结

EXPLAIN 的核心字段是 type（访问方式）、key（实际索引）、rows（扫描行数）和 Extra（额外细节）。通过分析这些字段，可以判断查询是否高效、是否用到了合适的索引，从而针对性地优化 SQL 或索引设计。