explain的type几种类型详解

你说的 EXPLAIN 的 type，一般是指 MySQL 查询执行计划中 EXPLAIN 输出里的 type 列。它表示 MySQL 选择的连接类型，也就是访问数据表的方式，直接反映查询效率。

MySQL EXPLAIN 中 type 的几种常见类型详解（从最好到最差排序）

具体说明

1. system

• 表只有一条记录，MySQL 直接读取，没有任何扫描开销。

2. const

• 通过主键或唯一索引定位单条数据。
• 只查询一条记录，作为常量处理。

3. eq_ref

• 多表连接中，针对前一表的每条记录用唯一索引查找对应行。
• 常见于关联查询中的主键或唯一索引连接。

4. ref

• 用非唯一索引查找满足条件的多行。
• 返回多条记录。

5. range

• 执行索引范围扫描，查找某范围内的索引值。
• 典型场景如 WHERE col > 10。

6. index

• 遍历整个索引而不是数据表（注意 InnoDB 主键是聚簇索引，区别较大）。
• 可能比全表扫描稍好。

7. all

• 最差方式，执行全表扫描。
• 通常因为无索引可用。

总结

• 查询时尽量避免出现 all，保证有适当索引。
• const、eq_ref 和 ref 是理想访问方式，性能最好。
• range 也可以接受，但要注意范围大小。
• index 和 all 都是遍历，性能相对较差。

好的！这里帮你详细讲解 MySQL EXPLAIN 输出中其他几个关键字段的含义，以及它们对查询优化的帮助：

EXPLAIN 其他重要字段详解

如何用这些字段辅助优化？

• 确认索引是否被使用：看 possible_keys 和 key，若 possible_keys 有索引但 key 却是 NULL，考虑调整查询或索引。
• 减少扫描行数：rows 越少越好，表明访问数据少，索引命中率高。
• 覆盖索引优化：Extra 显示 Using index 说明查询只从索引里读取数据，不访问表数据，性能很好。
• 避免临时表和文件排序：Using temporary 和 Using filesort 通常是性能瓶颈，要尽量优化查询结构和索引，避免这些操作。

举例说明

EXPLAIN SELECT id, name FROM users WHERE age = 30;php50 Bytes© 菜鸟-创作你的创作

输出示例：

• possible_keys 有 age_index，表示这个索引可用。
• key 是 age_index，实际用到了该索引。
• type 是 ref，使用非唯一索引查找多行。
• rows 估计 100 行数据要读取。
• Extra 有 Using where，说明查询还需要额外过滤（age=30）。

好的！下面是一个指导你如何基于具体的 EXPLAIN 输出，逐步优化 SQL 查询的实战流程和建议。

基于 EXPLAIN 优化 SQL 查询的实战步骤

1. 观察 type 字段，尽量避免 ALL（全表扫描）

• 如果是 ALL，说明没有用到索引，优先考虑为查询涉及的字段添加合适索引。
• 对于范围查询（range）可以接受，但尽量缩小扫描范围。

2. 查看 key 字段，确认是否使用索引

• key = NULL 表示没用索引，需优化。
• 如果 possible_keys 有索引但未被使用，考虑：
  • 改写查询条件让索引生效。
  • 调整索引顺序，使用复合索引覆盖多个条件。

3. 注意 rows 字段，估计扫描行数应尽可能少

• 如果很大，说明扫描了大量数据，考虑加筛选条件或索引。
• 可以利用索引选择性高的列做条件过滤。

4. 关注 Extra 字段

• Using filesort 表示 MySQL 需要额外排序，尽量避免。
• Using temporary 表示使用临时表，可能是复杂的 GROUP BY 或 DISTINCT，需优化。
• Using index 表示覆盖索引，查询效率高。
• Using where 表示服务器端过滤，正常但越少越好。

5. 分析复合索引使用情况

• 复合索引中前导列必须用于查询条件才能用上索引。
• 多列索引能提高多条件过滤效率。

6. 避免在索引列上做函数、计算或类型转换

• 这些操作会导致索引失效，变成全表扫描。

优化案例示范

假设你的查询：

SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01' ORDER BY order_date DESC;php100 Bytes© 菜鸟-创作你的创作

• EXPLAIN 显示：
  • type 是 ALL，key 是 NULL，Extra 有 Using filesort。

优化建议：

• 为 (customer_id, order_date) 建复合索引：CREATE INDEX idx_customer_orderdate ON orders(customer_id, order_date);
• 复查 EXPLAIN，type 应变为 ref 或 range，key 显示使用该索引，Using filesort 消失。

额外工具和技巧

• 使用 ANALYZE TABLE table_name; 更新统计信息，帮助优化器更准确估计。
• 用 SHOW INDEX FROM table_name; 查看现有索引情况。
• 利用 EXPLAIN FORMAT=JSON 获得更详细执行计划。
• 结合慢查询日志定位瓶颈。
• https://www.52runoob.com/archives/5458