mysql索引解析

mysql explain 

 

# MySQL EXPLAIN 中的 type 字段详解

`type` 字段是 **EXPLAIN 输出中最重要的指标之一**，它表示 MySQL 如何查找表中的行，从最优到最差排序。

## 📊 **type 类型（性能从优到劣）**

### **1. system ★最优★**
- **说明**：表中只有一行数据（系统表）
- **场景**：MyISAM/ MEMORY 引擎的特例
```sql
-- 示例：只有一条记录的系统表
EXPLAIN SELECT * FROM mysql.proc WHERE name = 'procedure_name';
```

### **2. const ★超高效★**
- **说明**：通过主键或唯一索引一次就找到
- **场景**：WHERE 条件使用主键或唯一索引的等值查询
```sql
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
UNIQUE KEY uk_email (email)
);

-- type: const
EXPLAIN SELECT * FROM users WHERE id = 1;
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
```

### **3. eq_ref ★高效★**
- **说明**：关联查询时，使用主键或唯一索引进行关联
- **场景**：多表 JOIN，被驱动表使用主键或唯一索引
```sql
-- orders 表有主键 id
-- order_items 表有外键 order_id
EXPLAIN SELECT * FROM orders o
JOIN order_items i ON o.id = i.order_id;
-- 对于 order_items 表的访问 type 为 eq_ref
```

### **4. ref ★高效★**
- **说明**：使用非唯一索引进行等值查询
- **场景**：WHERE 条件使用普通索引的等值查询
```sql
CREATE INDEX idx_department ON employees(department_id);

-- type: ref
EXPLAIN SELECT * FROM employees WHERE department_id = 10;
```

### **5. ref_or_null ★较高效★**
- **说明**：类似于 ref，但包含对 NULL 值的搜索
- **场景**：WHERE 条件使用索引，但包含 IS NULL
```sql
CREATE INDEX idx_department ON employees(department_id);

-- type: ref_or_null
EXPLAIN SELECT * FROM employees
WHERE department_id = 10 OR department_id IS NULL;
```

### **6. range ★中等★**
- **说明**：使用索引进行范围扫描
- **场景**：BETWEEN、>、<、IN、LIKE 'prefix%' 等
```sql
CREATE INDEX idx_salary ON employees(salary);
CREATE INDEX idx_name ON employees(name);

-- type: range
EXPLAIN SELECT * FROM employees WHERE salary > 5000;
EXPLAIN SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000;
EXPLAIN SELECT * FROM employees WHERE name LIKE '张%';
EXPLAIN SELECT * FROM employees WHERE id IN (1, 2, 3);
```

### **7. index ★较低效★**
- **说明**：全索引扫描（扫描整个索引树）
- **场景**：查询只需要索引列，但需要扫描全部索引
```sql
CREATE INDEX idx_status ON orders(status);

-- type: index（需要扫描整个索引）
EXPLAIN SELECT status FROM orders ORDER BY status;

-- type: index（覆盖索引，但无 WHERE 条件）
EXPLAIN SELECT id FROM products;
```

### **8. ALL ★最差★**
- **说明**：全表扫描
- **场景**：没有可用索引，或数据量小优化器认为全表更快
```sql
-- type: ALL（无索引或索引失效）
EXPLAIN SELECT * FROM users WHERE name LIKE '%张%';
EXPLAIN SELECT * FROM logs WHERE YEAR(create_time) = 2024;
```

## 📈 **type 性能对比图**

```
性能优劣：system > const > eq_ref > ref > ref_or_null > range > index > ALL
★★★★★ ★★★★ ★★★★ ★★★ ★★★ ★★ ★ ✗
```

## 🔍 **实际案例对比**

### **案例1：不同查询条件的 type**
```sql
-- 表结构
CREATE TABLE employee (
id INT PRIMARY KEY,
emp_no VARCHAR(20) UNIQUE,
name VARCHAR(50),
department_id INT,
salary DECIMAL(10,2),
INDEX idx_dept (department_id),
INDEX idx_salary (salary)
);

-- 1. const（最优）
EXPLAIN SELECT * FROM employee WHERE id = 100;
EXPLAIN SELECT * FROM employee WHERE emp_no = 'EMP001';

-- 2. ref（高效）
EXPLAIN SELECT * FROM employee WHERE department_id = 5;

-- 3. range（中等）
EXPLAIN SELECT * FROM employee WHERE salary > 10000;
EXPLAIN SELECT * FROM employee WHERE id IN (100, 101, 102);

-- 4. index（较低效）
EXPLAIN SELECT department_id FROM employee ORDER BY department_id;

-- 5. ALL（最差）
EXPLAIN SELECT * FROM employee WHERE name = '张三'; -- name无索引
```

### **案例2：JOIN 查询的 type**
```sql
-- 多表查询分析
EXPLAIN
SELECT e.name, d.department_name
FROM employee e
JOIN department d ON e.department_id = d.id -- d.id是主键，对d表：eq_ref
WHERE e.salary > 5000; -- 对e表：range（如果salary有索引）或ALL（无索引）
```

### **案例3：覆盖索引的影响**
```sql
CREATE INDEX idx_cover ON employee(department_id, name);

-- type: ref（使用索引）
EXPLAIN SELECT department_id, name FROM employee
WHERE department_id = 5;

-- type: index（覆盖索引，不需要回表）
EXPLAIN SELECT department_id FROM employee;

-- type: ALL（需要回表查询其他列）
EXPLAIN SELECT * FROM employee WHERE department_id = 5;
```

## 📋 **优化建议对照表**

| type 类型 | 优化建议 | 紧急程度 |
|-----------|----------|----------|
| **ALL** | 必须优化！添加合适索引 | ⚠️⚠️⚠️ 紧急 |
| **index** | 考虑优化，全索引扫描 | ⚠️⚠️ 中等 |
| **range** | 基本可接受，可进一步优化 | ⚠️ 一般 |
| **ref/eq_ref** | 良好，保持当前方案 | ✅ 良好 |
| **const/system** | 最优，无需优化 | ✅✅ 优秀 |

## 💡 **实战优化示例**

### **优化前：ALL 全表扫描**
```sql
-- 原始查询（type: ALL）
EXPLAIN SELECT * FROM orders
WHERE user_id = 100 AND DATE(create_time) = '2024-01-19';

-- 问题：DATE()函数导致索引失效
```

### **优化后：range**
```sql
-- 1. 重写查询，避免函数
EXPLAIN SELECT * FROM orders
WHERE user_id = 100
AND create_time >= '2024-01-19 00:00:00'
AND create_time < '2024-01-20 00:00:00';

-- 2. 添加复合索引
CREATE INDEX idx_user_create ON orders(user_id, create_time);

-- 优化后 type: range
```

## 🎯 **type 判断技巧**

1. **const/eq_ref**：使用了主键或唯一索引的等值查询
2. **ref**：使用了普通索引的等值查询
3. **range**：使用了索引的范围查询
4. **index**：扫描了整个索引树
5. **ALL**：扫描了整个数据表

## 📝 **总结要点**

1. **目标**：尽量让 type 保持在 **range** 及以上（ref、eq_ref、const）
2. **底线**：避免出现 **ALL**（全表扫描）
3. **技巧**：通过添加合适索引、优化查询条件来改进 type
4. **例外**：小表（<1000行）可能直接全表扫描更快

理解 `type` 字段是 SQL 性能优化的关键第一步，它能快速定位查询的性能瓶颈所在。