90 lines
2.3 KiB
Markdown
90 lines
2.3 KiB
Markdown
---
|
||
title: "MySQL 索引优化实战：从 EXPLAIN 到索引设计"
|
||
date: "2026-05-24"
|
||
category: "数据库"
|
||
tags: ["MySQL", "索引", "性能优化"]
|
||
excerpt: "深入讲解 B+ 树索引结构、覆盖索引、索引下推、联合索引最左前缀原则。"
|
||
---
|
||
|
||
## 一、B+ 树索引结构
|
||
|
||
### 1.1 为什么选择 B+ 树
|
||
|
||
MySQL InnoDB 存储引擎选择 B+ 树作为索引结构，原因如下：
|
||
|
||
- 所有数据存储在叶子节点，非叶子节点只存键值
|
||
- 叶子节点通过双向链表连接，支持范围查询
|
||
- 树的高度通常在 2-4 层，IO 次数可控
|
||
|
||
### 1.2 聚簇索引 vs 二级索引
|
||
|
||
**聚簇索引**：叶子节点存储完整行数据，一个表只能有一个。
|
||
|
||
**二级索引**：叶子节点存储主键值，查询需要回表。
|
||
|
||
```sql
|
||
-- 查看索引使用情况
|
||
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
|
||
```
|
||
|
||
## 二、EXPLAIN 分析
|
||
|
||
### 2.1 关键字段解读
|
||
|
||
| 字段 | 含义 | 期望值 |
|
||
|------|------|--------|
|
||
| type | 访问类型 | const > ref > range > index > ALL |
|
||
| key | 实际使用的索引 | 不应为 NULL |
|
||
| rows | 扫描行数估计 | 越小越好 |
|
||
| Extra | 额外信息 | Using index 表示覆盖索引 |
|
||
|
||
### 2.2 常见优化案例
|
||
|
||
```sql
|
||
-- 案例1：避免索引失效
|
||
-- ❌ 函数导致索引失效
|
||
SELECT * FROM orders WHERE DATE(created_at) = '2026-01-01';
|
||
|
||
-- ✅ 范围查询
|
||
SELECT * FROM orders
|
||
WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02';
|
||
```
|
||
|
||
## 三、索引设计原则
|
||
|
||
### 3.1 最左前缀原则
|
||
|
||
联合索引 `(a, b, c)` 相当于创建了三个索引：
|
||
- `(a)`
|
||
- `(a, b)`
|
||
- `(a, b, c)`
|
||
|
||
```sql
|
||
CREATE INDEX idx_name_age_city ON users(name, age, city);
|
||
|
||
-- 可以走索引
|
||
SELECT * FROM users WHERE name = '张三';
|
||
SELECT * FROM users WHERE name = '张三' AND age = 25;
|
||
|
||
-- 不能走索引（跳过了 name）
|
||
SELECT * FROM users WHERE age = 25;
|
||
```
|
||
|
||
### 3.2 覆盖索引
|
||
|
||
查询的所有列都包含在索引中，避免回表：
|
||
|
||
```sql
|
||
CREATE INDEX idx_email_name ON users(email, name);
|
||
|
||
-- Using index（覆盖索引）
|
||
SELECT email, name FROM users WHERE email = 'test@example.com';
|
||
```
|
||
|
||
## 四、总结
|
||
|
||
- 索引不是越多越好，每个索引都会增加写操作成本
|
||
- 定期分析慢查询日志
|
||
- 使用 `EXPLAIN` 验证索引是否被正确使用
|
||
- 联合索引遵循最左前缀原则
|