Knowledge/1 使用/数据库/EXPLAIN.md

## 解读 EXPLAIN 结果的关键字段

以 MySQL 为例，重要的列包括：

| 列名                | 说明      | 优化关注点                                                     |
| ----------------- | ------- | --------------------------------------------------------- |
| **type**          | 访问类型    | 最好到最差：system > const > eq_ref > ref > range > index > ALL |
| **key**           | 实际使用的索引 | 检查是否使用了预期索引                                               |
| **rows**          | 预计扫描行数  | 数值越小越好                                                    |
| **Extra**         | 额外信息    | 注意 Using filesort, Using temporary                        |
| **possible_keys** | 可能使用的索引 | 数据库考虑使用的索引列表                                              |

# MySQL EXPLAIN 中 type 字段的详细解释

`type` 列显示 MySQL 如何查找表中的行，从最优到最差排序如下：

## 一、性能最好的类型

### **1. system**
- **解释**：表只有一行（相当于系统表）
- **出现场景**：
  - 表中只有一条记录（MyISAM、MEMORY表）
  - 衍生表（派生表）只有一行数据
- **示例**：
  ```sql
  -- 假设表只有一行数据
  EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE id=1) AS derived_table;
  ```

### **2. const**
- **解释**：通过索引一次就找到了，用于 primary key 或 unique key 的等值查询
- **特点**：
  - 查询速度非常快
  - 只会读取一次
  - 返回最多一条记录
- **示例**：
  ```sql
  EXPLAIN SELECT * FROM users WHERE id = 1;
  -- id 是主键
  ```

## 二、性能优秀的类型

### **3. eq_ref**
- **解释**：使用唯一索引进行关联查询
- **出现场景**：
  - 关联查询中，被驱动表使用主键或唯一索引进行等值匹配
  - 每个索引键值在表中只有一条记录匹配
- **示例**：
  ```sql
  EXPLAIN SELECT * 
  FROM orders 
  JOIN users ON orders.user_id = users.id;  -- users.id 是主键
  ```
- **注意**：这是除了 system 和 const 之外最好的连接类型

### **4. ref**
- **解释**：使用非唯一索引进行等值查询
- **出现场景**：
  - 使用普通索引（非唯一索引）进行等值查询
  - 可能返回多行数据
- **示例**：
  ```sql
  EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
  -- email 字段有普通索引，但不是唯一索引
  ```

### **5. fulltext**
- **解释**：使用全文索引
- **出现场景**：
  - 查询使用了 `MATCH() ... AGAINST()` 语法
  - 表中建立了全文索引
- **示例**：
  ```sql
  EXPLAIN SELECT * FROM articles 
  WHERE MATCH(title, content) AGAINST('MySQL tutorial');
  ```

## 三、性能中等的类型

### **6. ref_or_null**
- **解释**：类似于 ref，但额外搜索包含 NULL 值的行
- **出现场景**：
  - 等值查询条件中包含 OR ... IS NULL
- **示例**：
  ```sql
  EXPLAIN SELECT * FROM users 
  WHERE email = 'test@example.com' OR email IS NULL;
  ```

### **7. index_merge**
- **解释**：使用了索引合并优化
- **出现场景**：
  - 查询条件使用多个索引，MySQL 会合并这些索引的结果
  - 常见于 OR 条件的查询
- **示例**：
  ```sql
  EXPLAIN SELECT * FROM users 
  WHERE name = 'John' OR age = 30;
  -- name 和 age 分别有索引
  ```

### **8. unique_subquery**
- **解释**：在子查询中使用了唯一索引
- **出现场景**：
  - 子查询中使用主键或唯一索引
  - 通常用于 IN 子查询优化
- **示例**：
  ```sql
  EXPLAIN SELECT * FROM orders 
  WHERE user_id IN (
    SELECT id FROM users WHERE status = 'active'
  );
  -- 如果 users.id 是主键，子查询会使用 unique_subquery
  ```

### **9. index_subquery**
- **解释**：类似于 unique_subquery，但用于非唯一索引
- **出现场景**：
  - 子查询中使用普通索引
- **示例**：
  ```sql
  EXPLAIN SELECT * FROM orders 
  WHERE product_id IN (
    SELECT id FROM products WHERE category = 'electronics'
  );
  -- 如果 products.id 是普通索引
  ```

## 四、性能需要优化的类型

### **10. range**
- **解释**：使用索引进行范围查询
- **出现场景**：
  - `BETWEEN`、`>`、`<`、`>=`、`<=`、`IN()`、`LIKE 'prefix%'` 等操作
  - 只检索给定范围的行
- **示例**：
  ```sql
  EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;
  EXPLAIN SELECT * FROM users WHERE last_name LIKE 'Smith%';
  EXPLAIN SELECT * FROM users WHERE id IN (1, 2, 3);
  ```

### **11. index**
- **解释**：全索引扫描
- **两种场景**：
  1. **覆盖索引**：查询的列都包含在索引中，不需要回表
     ```sql
     EXPLAIN SELECT id, name FROM users WHERE name LIKE 'A%';
     -- 如果 (id, name) 是复合索引
     ```
  2. **索引排序**：使用索引进行全表扫描，但按索引顺序
     ```sql
     EXPLAIN SELECT * FROM users ORDER BY id;
     -- 如果 id 是主键
     ```
- **特点**：比 ALL 快，因为索引通常比表数据小

## 五、需要避免的性能问题类型

### **12. ALL**
- **解释**：全表扫描
- **出现场景**：
  - 没有使用索引
  - 索引失效
  - 表数据量很小，全表扫描更快
- **示例**：
  ```sql
  EXPLAIN SELECT * FROM users WHERE phone = '123456789';
  -- phone 字段没有索引
  ```

## 六、性能对比图表

```
性能从高到低：
system > const > eq_ref > ref > fulltext > ref_or_null > 
index_merge > unique_subquery > index_subquery > range > index > ALL
```

### 视觉化性能对比：

```
查询类型          | 性能 | 扫描方式
-----------------|------|--------
system          | ⭐⭐⭐⭐⭐ | 单行，常量
const           | ⭐⭐⭐⭐⭐ | 主键/唯一索引查找
eq_ref          | ⭐⭐⭐⭐  | 唯一索引关联
ref             | ⭐⭐⭐   | 非唯一索引查找
range           | ⭐⭐    | 索引范围扫描
index           | ⭐     | 全索引扫描
ALL             | 🚫    | 全表扫描
```

## 七、实际优化建议

### 1. **理想情况**：至少达到 **range** 级别
```sql
-- 目标：使 type 达到 range 或更好
EXPLAIN SELECT * FROM logs WHERE created_at > '2023-01-01';
-- 为 created_at 添加索引
CREATE INDEX idx_created_at ON logs(created_at);
```

### 2. **避免 ALL 类型**
```sql
-- 优化前：type = ALL（全表扫描）
EXPLAIN SELECT * FROM products WHERE description LIKE '%discount%';

-- 优化方案1：添加全文索引（对于文本搜索）
CREATE FULLTEXT INDEX idx_description ON products(description);

-- 优化方案2：使用更精确的条件
EXPLAIN SELECT * FROM products 
WHERE tag = 'discount'  -- 添加 tag 字段并建立索引
AND description LIKE '%discount%';
```

### 3. **索引覆盖优化**
```sql
-- 优化前：可能使用 index 或 range
EXPLAIN SELECT id, name, email FROM users 
WHERE email LIKE 'a%';

-- 优化后：创建覆盖索引
CREATE INDEX idx_email_name ON users(email, name);
-- 现在查询只扫描索引，不需要回表
```

## 八、常见问题排查

### 问题1：为什么使用了索引还是慢？
```sql
EXPLAIN SELECT * FROM orders 
WHERE status = 'shipped' AND created_at > '2023-01-01';
-- type 可能是 range 或 ref，但：
-- 1. 检查选择性：如果 status='shipped' 占 90% 的数据，索引效果差
-- 2. 考虑创建复合索引：CREATE INDEX idx_status_created ON orders(status, created_at)
```

### 问题2：为什么没使用我期望的索引？
```sql
-- 强制使用索引查看效果
EXPLAIN SELECT * FROM users USE INDEX (idx_email) 
WHERE email LIKE 'a%';

-- 对比不使用索引
EXPLAIN SELECT * FROM users IGNORE INDEX (idx_email) 
WHERE email LIKE 'a%';
```

记住：**type 只是性能评估的一个维度**，还需要结合 `rows`（扫描行数）、`Extra`（额外信息）等字段综合判断查询性能。