第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

常见数据结构对比：

为什么不用哈希表？

-- 哈希表适合等值查询
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特点：

1. 所有数据都存储在叶子节点
2. 叶子节点之间有指针连接（双向链表）
3. 非叶子节点只存储索引，不存储数据
4. 树的高度很低（通常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);