第06章：索引原理与优化 ⭐⭐⭐⭐⭐
第06章：索引原理与优化 ⭐⭐⭐⭐⭐
索引是MySQL性能优化的核心，必须深入理解
6.1 索引的概念
6.1.1 什么是索引
索引是一种数据结构，用于帮助MySQL高效地查找数据。
类比：
- 没有索引：像在字典中逐页查找一个单词（全表扫描）
- 有索引：像使用字典的目录快速定位（索引查找）
6.1.2 索引的优缺点
优点：
- ✅ 大大加快数据查询速度
- ✅ 加速表与表之间的连接
- ✅ 减少分组和排序的时间
- ✅ 使用覆盖索引可以避免回表
缺点：
- ❌ 占用额外的磁盘空间
- ❌ 降低INSERT、UPDATE、DELETE的速度
- ❌ 需要维护成本
何时使用索引：
- ✅ WHERE条件字段
- ✅ ORDER BY字段
- ✅ GROUP BY字段
- ✅ JOIN的ON字段
- ✅ 经常查询的字段
何时不使用索引：
- ❌ 数据量很小的表（几百行）
- ❌ 频繁更新的字段
- ❌ 区分度很低的字段（如性别）
- ❌ WHERE条件中用不到的字段
6.2 索引的数据结构
6.2.1 为什么使用B+Tree
常见数据结构对比：
| 数据结构 | 查询时间复杂度 | 缺点 |
|---|---|---|
| 数组 | O(n) | 查询慢 |
| 链表 | O(n) | 查询慢 |
| 哈希表 | O(1) | 不支持范围查询、排序 |
| 二叉搜索树 | O(log n) | 可能退化为链表 |
| 平衡二叉树（AVL） | O(log n) | 树太高，磁盘I/O次数多 |
| 红黑树 | O(log n) | 树太高，磁盘I/O次数多 |
| B+Tree | O(log n) | ✅ 树矮、范围查询快 |
为什么不用哈希表？
-- 哈希表适合等值查询
SELECT * FROM users WHERE id = 100; -- ✅ 快
-- 但不支持范围查询
SELECT * FROM users WHERE id > 100; -- ❌ 慢
SELECT * FROM users ORDER BY id; -- ❌ 慢
为什么不用二叉树？
假设100万条数据：
- 二叉树高度：log₂(1000000) ≈ 20层
- 需要20次磁盘I/O
B+Tree（假设每个节点1000个key）：
- B+Tree高度：log₁₀₀₀(1000000) ≈ 3层
- 只需要3次磁盘I/O
6.2.2 B+Tree结构详解
B+Tree特点：
- 所有数据都存储在叶子节点
- 叶子节点之间有指针连接（双向链表）
- 非叶子节点只存储索引，不存储数据
- 树的高度很低（通常3-4层）
B+Tree示例：
[50, 100] ← 根节点（非叶子节点）
/ | \
/ | \
[10,20,30] [60,70] [110,120] ← 非叶子节点
/ | | \
/ | | \
[1-9][10-19][20-29][30-39] ... [110-119][120-129] ← 叶子节点（存储数据）
↔ ↔ ↔ ↔ ↔ ↔ ← 双向链表
查询过程：
-- 查询 id=25 的记录
SELECT * FROM users WHERE id = 25;
-- 查询过程：
-- 1. 从根节点开始：25 < 50，走左边
-- 2. 到达 [10,20,30]：20 < 25 < 30，走中间
-- 3. 到达叶子节点 [20-29]，找到 id=25 的数据
-- 总共3次磁盘I/O
范围查询：
-- 查询 id 在 20-60 之间的记录
SELECT * FROM users WHERE id BETWEEN 20 AND 60;
-- 查询过程：
-- 1. 找到 id=20 的叶子节点
-- 2. 通过叶子节点的链表指针，顺序扫描到 id=60
-- 非常高效！
6.2.3 InnoDB的B+Tree
InnoDB的两种索引：
1. 聚簇索引（Clustered Index）：
- 叶子节点存储完整的行数据
- 一个表只有一个聚簇索引
- 通常是主键索引
结构：
聚簇索引（主键索引）：
[50, 100]
/ | \
[10,20,30] [60,70] [110,120]
/ | | \
[id=1, [id=10, [id=20, [id=30,
name= name= name= name=
age= age= age= age=
...] ...] ...] ...]
↔ ↔ ↔ ↔
2. 二级索引（Secondary Index）：
- 叶子节点存储索引列的值 + 主键值
- 一个表可以有多个二级索引
结构：
二级索引（name索引）：
['李', '王']
/ | \
['张三','张四'] ['李四','李五'] ['王五','王六']
/ | \
[name='张三', [name='张四', [name='李四',
id=1] id=5] id=10]
↔ ↔ ↔
回表查询：
-- 使用二级索引查询
SELECT * FROM users WHERE name = '张三';
-- 查询过程：
-- 1. 在name索引中找到 name='张三'，得到 id=1
-- 2. 回到聚簇索引，根据 id=1 查找完整数据
-- 这就是"回表"
覆盖索引（避免回表）：
-- 创建联合索引
CREATE INDEX idx_name_age ON users(name, age);
-- 只查询索引中的字段，不需要回表
SELECT name, age FROM users WHERE name = '张三';
-- Extra: Using index（覆盖索引）
6.2.4 MyISAM的B+Tree
MyISAM的索引：
- 所有索引都是非聚簇索引
- 叶子节点存储索引列的值 + 数据文件的地址指针
- 主键索引和二级索引结构相同
结构：
MyISAM索引：
[50, 100]
/ | \
[10,20,30] [60,70] [110,120]
/ | | \
[id=1, [id=10, [id=20, [id=30,
addr] addr] addr] addr]
↔ ↔ ↔ ↔
addr: 数据文件中的物理地址
6.3 索引类型
6.3.1 按功能分类
1. 主键索引（PRIMARY KEY）
特点：
- 唯一且不能为NULL
- 一个表只能有一个主键
- InnoDB中是聚簇索引
创建：
-- 创建表时指定
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50)
);
-- 或
CREATE TABLE users (
id INT,
name VARCHAR(50),
PRIMARY KEY (id)
);
-- 添加主键
ALTER TABLE users ADD PRIMARY KEY (id);
-- 删除主键
ALTER TABLE users DROP PRIMARY KEY;
2. 唯一索引（UNIQUE）
特点：
- 列值必须唯一，但可以有NULL
- 一个表可以有多个唯一索引
创建：
-- 创建表时指定
CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE
);
-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);
-- 或
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);
-- 删除唯一索引
DROP INDEX idx_email ON users;
3. 普通索引（INDEX）
特点：
- 最基本的索引
- 没有任何限制
创建：
-- 创建索引
CREATE INDEX idx_name ON users(name);
-- 或
ALTER TABLE users ADD INDEX idx_name (name);
-- 删除索引
DROP INDEX idx_name ON users;
4. 全文索引（FULLTEXT）
特点：
- 用于全文搜索
- 只支持CHAR、VARCHAR、TEXT类型
- InnoDB在MySQL 5.6+支持
创建：
-- 创建全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);
-- 使用全文索引
SELECT * FROM articles
WHERE MATCH(content) AGAINST('MySQL' IN NATURAL LANGUAGE MODE);
6.3.2 按字段数量分类
1. 单列索引
CREATE INDEX idx_name ON users(name);
2. 联合索引（组合索引）⭐⭐⭐⭐⭐
创建：
-- 创建联合索引
CREATE INDEX idx_name_age_city ON users(name, age, city);
最左前缀原则：
-- 索引：idx_name_age_city (name, age, city)
-- ✅ 可以使用索引
WHERE name = '张三'
WHERE name = '张三' AND age = 20
WHERE name = '张三' AND age = 20 AND city = '北京'
WHERE name = '张三' AND city = '北京' -- 只使用name
-- ❌ 不能使用索引
WHERE age = 20
WHERE city = '北京'
WHERE age = 20 AND city = '北京'
索引顺序的选择：
-- 原则：区分度高的字段放前面
-- 假设：
-- name: 10000个不同值（区分度高）
-- age: 100个不同值（区分度中）
-- city: 10个不同值（区分度低）
-- 推荐顺序：
CREATE INDEX idx_name_age_city ON users(name, age, city);
-- 不推荐：
CREATE INDEX idx_city_age_name ON users(city, age, name);