深入理解 MySQL EXPLAIN 的七种 type 类型

掌握 MySQL 的 EXPLAIN 命令是数据库性能优化的必备技能，而其中的 type 字段更是分析查询性能的关键指标。

本文将带你深入理解七种 type 类型的含义、使用场景和优化策略。

一、什么是 EXPLAIN 的 type 字段？

在 MySQL 中，EXPLAIN 命令用于分析 SQL 查询的执行计划。其中的 type 字段表示 MySQL 决定如何查找表中的行，它直接反映了查询的性能特征。

理解不同类型的访问方式，对于 SQL 优化至关重要。

二、七种 type 类型详解

1. system ★★

★★★（最优）

特点：

• 表只有一行数据（系统表）

• 是 const 类型的特例，性能最佳

示例场景：

-- 查询系统表中的单行数据
EXPLAIN SELECT * FROM mysql.proxies_priv WHERE user = 'root';

输出特征：

• type: system

• rows: 1

优化建议：这已经是最高效的访问方式，无需进一步优化。

2. const ★★★★★

特点：

• 通过主键或唯一索引的等值查询

• 最多返回一条记录

• 查询条件必须是常量值

示例场景：

-- 主键等值查询
EXPLAIN SELECT * FROM users WHERE id = 1;


-- 唯一索引等值查询
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

输出特征：

• type: const

• rows: 1

• key: 使用的索引名

优化建议：确保查询使用主键或唯一索引，这是理想的查询方式。

3. eq_ref ★★★★☆

特点：

• 多表连接时，使用主键或唯一索引作为关联条件

• 对于前表的每一行，后表只返回一条匹配记录

• 通常出现在高性能的 JOIN 操作中

示例场景：

-- orders.user_id 是 users.id 的外键，且 users.id 是主键
EXPLAIN SELECT * FROM orders JOIN users ON orders.user_id = users.id;

输出特征：

• type: eq_ref

• key: 使用的索引名

• 通常与 const 或 ref 一起出现在 JOIN 查询中

  
  

优化建议：确保连接条件使用主键或唯一索引，检查外键关系是否正确建立。

4. ref ★★★☆☆

特点：

• 使用普通索引的等值查询

• 可能返回多行记录

• 性能优于全表扫描，但不如 const 和 eq_ref

示例场景：

输出特征：

• type: ref

• key: 使用的索引名

• rows: 预计扫描行数

  
  

优化建议：

• 为频繁查询的条件添加合适的索引

• 考虑使用覆盖索引减少回表操作

• 注意索引的选择性，高选择性列更适合建索引

5. range ★★☆☆☆

特点：

• 使用索引进行范围扫描

• 常见于 BETWEEN、>、<、IN 等范围操作

• 只检索给定范围内的行

示例场景：

-- age 列有索引
EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;
EXPLAIN SELECT * FROM users WHERE id IN (1, 2, 3);

输出特征：

• type: range

• key: 使用的索引名

• rows: 范围扫描的行数估计

  
  

优化建议：

• 注意范围查询后的列索引会失效

• 在联合索引中，将范围查询列放在最后

• 考虑使用覆盖索引优化查询

6. index ★★☆☆☆

特点：

• 全索引扫描（比全表扫描好）

• 只扫描索引树，不扫描数据行

• 常见于覆盖索引查询或需要索引排序的场景

示例场景：

-- 索引覆盖查询（status 有索引）
EXPLAIN SELECT status FROM users;


-- 使用索引但需要排序
EXPLAIN SELECT id FROM users ORDER BY id;

输出特征：

• type: index

• key: 使用的索引名

• Extra: Using index（如果使用覆盖索引）

优化建议：

• 考虑是否真的需要所有索引数据

• 对于大表，全索引扫描仍然很耗资源

• 评估是否可以通过添加 LIMIT 限制结果集

7. ALL ☆☆☆☆☆（最差）

特点：

• 全表扫描

• 性能最差，尤其是大表

• 表示没有使用任何索引

示例场景：

-- 没有为 address 列创建索引
EXPLAIN SELECT * FROM users WHERE address LIKE '%北京%';


-- 没有合适的索引可用
EXPLAIN SELECT * FROM users WHERE created_at > '2023-01-01';

输出特征：

• type: ALL

• key: NULL

• rows: 表的总行数

• Extra: Using where

  
  

优化建议：

• 必须为查询条件添加合适的索引

• 考虑使用覆盖索引

• 限制返回的数据量（添加 LIMIT）

• 评估是否需要所有列，避免 SELECT *

三、性能对比总结表

四、实战优化案例

问题查询：

EXPLAIN SELECT * FROM orders WHERE customer_id = 100 AND order_date > '2023-01-01'

 假设当前 type 显示为 ALL，表示全表扫描。

优化步骤：

1.添加联合索引：

ALTER TABLE orders ADD INDEX idx_customer_date(customer_id, order_date);

2.验证优化效果：

EXPLAIN SELECT * FROM orders 
WHERE customer_id = 100 AND order_date > '2023-01-01';

现在 type 应该变为 range。

3.进一步优化（使用覆盖索引）：

EXPLAIN SELECT order_id, order_date FROM orders 
WHERE customer_id = 100 AND order_date > '2023-01-01';

如果只需要部分列，可以考虑使用覆盖索引。

阅读原文：原文链接​