MySQL 的EXPLAIN 计划 type 字段详细说明

在 MySQL 的 EXPLAIN 执行计划中，type 字段表示查询的访问类型，即 MySQL 在查询时使用的数据检索方法。不同的 type 值表示不同的查询效率，通常 system 是最优的，而 ALL 是最差的。以下是这些 type 值的详细说明及其排序：

system > const > eq_ref > ref > range > index > ALL

说明：
- 适用于 唯一索引（PRIMARY KEY 或 UNIQUE） 查询，并且查询条件是常量。
- MySQL 仅需查询一次，就能获取数据。
- 适用于 WHERE 子句中的主键或唯一索引等查询。

示例：

EXPLAIN SELECT * FROM employees WHERE emp_id = 100;

说明：
- eq_ref 用于 主键（PRIMARY KEY）或唯一索引（UNIQUE KEY） 的等值查询，通常出现在多表 JOIN 操作中。
- 对于每一行来自主表的数据，子表最多只会返回一条匹配记录。

示例：

EXPLAIN 
SELECT e.emp_id, d.dept_name 
FROM employees e 
JOIN departments d ON e.dept_id = d.dept_id;

特点：
- 每次查询只返回一行数据（因为 eq_ref 必须基于 PRIMARY KEY 或 UNIQUE 索引）。
- 查询优化效果非常好。
- 常见于 JOIN 语句中，被连接表的主键或唯一索引用于匹配主表的字段。

说明：
- ref 表示查询使用了非唯一索引（普通索引、非唯一的外键索引）。
- 适用于非唯一索引或 JOIN 操作中的索引匹配，可能会返回多行数据。

示例：

EXPLAIN SELECT * FROM employees WHERE department_id = 5;

特点：
- 使用索引查找，但不是唯一匹配（可能匹配多行）。
- 常用于 JOIN 操作，或者在 WHERE 子句中使用非唯一索引的查询。

示例：

EXPLAIN SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000;

特点：
- 适用于索引列上的范围查询。
- 扫描索引的部分数据，比全表扫描（ALL）高效。
- 适用于 >, <, BETWEEN, IN() 等操作符。

示例：

EXPLAIN SELECT idx_column FROM large_table;

示例：

EXPLAIN SELECT * FROM employees WHERE name = 'John Doe';

特点：
- 性能最差，因为需要扫描整张表。
- MySQL 可能会自动使用 Using filesort 或 Using temporary 进行排序。
- 如果 ALL 出现在大表查询中，通常需要优化索引。

避免 ALL（全表扫描）：
- 创建索引，提高查询效率。
- 使用 EXPLAIN 分析执行计划，确认索引是否被使用。
优化 range（范围扫描）：
- 适当调整索引，避免 MySQL 过度扫描数据范围。
- 使用 BETWEEN 或 IN() 查询时，确保索引列的数据分布合理。
提升 ref 和 eq_ref 查询：
- 使用外键关系，并确保 JOIN 语句涉及索引列。
- 采用 覆盖索引，减少回表查询的次数。
优化 index（全索引扫描）：
- 避免 SELECT *，只查询必要字段，减少数据量。
- 确保查询能利用索引，避免额外的 Using filesort 和 Using temporary。