索引原理
2026/1/15大约 4 分钟
索引原理
什么是索引
索引是帮助 MySQL 高效获取数据的数据结构，类似于书的目录。
索引的数据结构
B+ 树
MySQL InnoDB 使用 B+ 树作为索引结构。
B+ 树的特点
- 非叶子节点只存储索引，不存储数据
- 叶子节点存储所有数据，并通过指针连接
- 所有查询都要到叶子节点，查询效率稳定
为什么用 B+ 树而不是 B 树？
| 特性 | B 树 | B+ 树 |
|---|---|---|
| 数据存储 | 所有节点 | 仅叶子节点 |
| 范围查询 | 需要中序遍历 | 叶子节点链表遍历 |
| 磁盘 IO | 较多 | 较少 |
| 查询效率 | 不稳定 | 稳定 |
为什么不用 Hash 索引？
- Hash 索引不支持范围查询
- Hash 索引不支持排序
- Hash 索引不支持最左前缀匹配
- 存在 Hash 冲突
索引类型
按数据结构分
| 类型 | 说明 |
|---|---|
| B+ 树索引 | 最常用，支持范围查询 |
| Hash 索引 | 等值查询快，不支持范围 |
| 全文索引 | 用于全文搜索 |
| R-Tree 索引 | 用于地理空间数据 |
按物理存储分
聚簇索引（主键索引）
- 叶子节点存储完整的行数据
- 一个表只能有一个聚簇索引
- InnoDB 按主键组织数据
-- 主键索引
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50)
);非聚簇索引（二级索引）
- 叶子节点存储主键值
- 需要回表查询完整数据
-- 二级索引
CREATE INDEX idx_name ON users(name);
-- 查询过程：
-- 1. 在 idx_name 索引中找到 name 对应的主键
-- 2. 根据主键在聚簇索引中找到完整数据（回表）按字段特性分
-- 主键索引
ALTER TABLE users ADD PRIMARY KEY (id);
-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);
-- 普通索引
CREATE INDEX idx_name ON users(name);
-- 前缀索引
CREATE INDEX idx_name ON users(name(10));
-- 联合索引
CREATE INDEX idx_name_age ON users(name, age);联合索引
最左前缀原则
联合索引 (a, b, c) 相当于创建了三个索引：
(a)(a, b)(a, b, c)
-- 创建联合索引
CREATE INDEX idx_abc ON t(a, b, c);
-- 能使用索引
SELECT * FROM t WHERE a = 1;
SELECT * FROM t WHERE a = 1 AND b = 2;
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3;
SELECT * FROM t WHERE a = 1 AND c = 3; -- 只用到 a
-- 不能使用索引
SELECT * FROM t WHERE b = 2;
SELECT * FROM t WHERE c = 3;
SELECT * FROM t WHERE b = 2 AND c = 3;索引下推（ICP）
MySQL 5.6 引入，在索引遍历过程中过滤数据。
-- 联合索引 (name, age)
SELECT * FROM users WHERE name LIKE '张%' AND age = 20;
-- 无 ICP：先根据 name 回表，再过滤 age
-- 有 ICP：在索引中直接过滤 age，减少回表次数覆盖索引
查询的字段都在索引中，无需回表。
-- 联合索引 (name, age)
SELECT name, age FROM users WHERE name = '张三';
-- 覆盖索引，无需回表
SELECT * FROM users WHERE name = '张三';
-- 需要回表索引失效场景
1. 对索引列使用函数
-- 索引失效
SELECT * FROM users WHERE YEAR(create_time) = 2024;
-- 优化
SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';2. 对索引列进行运算
-- 索引失效
SELECT * FROM users WHERE id + 1 = 10;
-- 优化
SELECT * FROM users WHERE id = 9;3. 类型隐式转换
-- phone 是 VARCHAR 类型
-- 索引失效（字符串转数字）
SELECT * FROM users WHERE phone = 13800138000;
-- 优化
SELECT * FROM users WHERE phone = '13800138000';4. LIKE 以 % 开头
-- 索引失效
SELECT * FROM users WHERE name LIKE '%张';
-- 可以使用索引
SELECT * FROM users WHERE name LIKE '张%';5. OR 条件
-- 如果 age 没有索引，整个查询不走索引
SELECT * FROM users WHERE name = '张三' OR age = 20;6. NOT IN / NOT EXISTS
-- 可能不走索引
SELECT * FROM users WHERE id NOT IN (1, 2, 3);7. 联合索引不满足最左前缀
-- 索引 (a, b, c)
-- 索引失效
SELECT * FROM t WHERE b = 1;索引优化建议
1. 选择合适的列建索引
- WHERE 条件中的列
- JOIN 关联的列
- ORDER BY / GROUP BY 的列
2. 选择区分度高的列
-- 查看列的区分度
SELECT COUNT(DISTINCT column) / COUNT(*) FROM table;
-- 区分度越接近 1 越好3. 使用前缀索引
-- 对于长字符串，使用前缀索引
CREATE INDEX idx_name ON users(name(10));
-- 选择合适的前缀长度
SELECT COUNT(DISTINCT LEFT(name, 10)) / COUNT(*) FROM users;4. 避免冗余索引
-- 冗余索引
INDEX (a)
INDEX (a, b) -- 已包含 (a)
-- 保留 (a, b) 即可5. 控制索引数量
- 索引会占用磁盘空间
- 索引会降低写入性能
- 建议单表索引不超过 5 个
索引监控
-- 查看索引使用情况
SHOW INDEX FROM table_name;
-- 查看索引统计信息
SHOW TABLE STATUS LIKE 'table_name';
-- 分析表
ANALYZE TABLE table_name;