为什么加了索引还是慢？”
这个问题我被问过无数次。索引不是万能药，用不好反而是负担。这篇从原理讲起，说说索引优化的实战经验。
索引的本质：B+树
MySQL的InnoDB索引用的是B+树，理解这个结构才能理解索引的行为。
[根节点: 50]
/ \
[20, 35] [70, 85]
/ | \ / | \
[数据] [数据] [数据] [数据] [数据] [数据]
↓ ↓ ↓ ↓ ↓ ↓
叶子节点包含完整数据行（聚簇索引）
或主键值（二级索引）
关键特点：
- 叶子节点存数据，非叶子节点只存索引
- 叶子节点有序且双向链接，范围查询很快
- 树高度通常3-4层，千万级数据也只需3-4次IO
聚簇索引 vs 二级索引
聚簇索引（主键索引）
数据按主键顺序存储，主键索引的叶子节点就是数据本身。
-- 主键查询，直接定位到数据
SELECT * FROM users WHERE id = 100;
-- 只需要查聚簇索引，一次搞定
二级索引（普通索引）
叶子节点存的是主键值，查到后还要回表查聚簇索引。
-- 假设name上有索引
SELECT * FROM users WHERE name = '张三';
-- 执行过程：
-- 1. 在name索引上找到name='张三'对应的主键id
-- 2. 拿着id去聚簇索引找完整数据
-- 这个过程叫"回表"
回表是性能杀手。能避免就避免。
覆盖索引：干掉回表
如果查询的列都在索引里，就不用回表了。
-- 原SQL，需要回表
SELECT id, name, age FROM users WHERE name = '张三';
-- 如果只有name索引，要回表取age
-- 优化：建联合索引
CREATE INDEX idx_name_age ON users(name, age);
-- 现在查询的列(id, name, age)都在索引里了
-- id是主键，二级索引叶子节点自带
-- name, age在联合索引里
-- 不用回表，直接返回
EXPLAIN看到Using index就是覆盖索引：
EXPLAIN SELECT id, name, age FROM users WHERE name = '张三';
-- Extra: Using index ← 覆盖索引，没回表
联合索引的最左前缀原则
联合索引(a, b, c)的结构：
先按a排序
a相同的按b排序
b相同的按c排序
所以：
-- 能用上索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND c = 3 -- 只用到a（c用不上，因为跳过了b）
-- 用不上索引
WHERE b = 2 -- 跳过了a
WHERE c = 3 -- 跳过了a和b
WHERE b = 2 AND c = 3 -- 跳过了a
范围查询会截断
-- 索引 (a, b, c)
WHERE a = 1 AND b > 10 AND c = 3
-- a用等值查询 ✓
-- b用范围查询 ✓
-- c用不上！因为b是范围查询，后面的列无法使用索引
所以等值查询的列放前面，范围查询的列放后面：
-- 差：(status, create_time, user_id)
WHERE status = 1 AND create_time > '2024-01-01' AND user_id = 100
-- create_time是范围，user_id用不上
-- 好：(status, user_id, create_time)
WHERE status = 1 AND user_id = 100 AND create_time > '2024-01-01'
-- 三个列都能用上
索引失效的常见场景
1. 对索引列做运算
-- 失效
SELECT * FROM orders WHERE YEAR(create_time) = 2024;
-- 优化
SELECT * FROM orders
WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';
2. 隐式类型转换
-- phone是varchar类型
-- 失效：数字会转成字符串，导致全表扫描
SELECT * FROM users WHERE phone = 13800138000;
-- 正确
SELECT * FROM users WHERE phone = '13800138000';
3. LIKE以%开头
-- 失效
SELECT * FROM users WHERE name LIKE '%张';
-- 能用索引
SELECT * FROM users WHERE name LIKE '张%';
4. OR连接的条件
-- 如果name没索引，整个查询都不走索引
SELECT * FROM users WHERE id = 1 OR name = '张三';
-- 优化1：给name加索引
-- 优化2：改成UNION
SELECT * FROM users WHERE id = 1
UNION
SELECT * FROM users WHERE name = '张三';
5. NOT IN、NOT EXISTS、!=
-- 可能不走索引（优化器判断）
SELECT * FROM users WHERE status != 0;
SELECT * FROM users WHERE id NOT IN (1, 2, 3);
-- 如果status大部分是0，可以改成
SELECT * FROM users WHERE status IN (1, 2, 3);
6. IS NULL / IS NOT NULL
-- 看数据分布，NULL值多可能不走索引
SELECT * FROM users WHERE deleted_at IS NULL;
索引设计原则
1. 选择区分度高的列
-- 区分度 = COUNT(DISTINCT col) / COUNT(*)
-- 性别：区分度约0.5，不适合单独建索引
-- 手机号：区分度接近1，适合建索引
-- 状态：区分度低，但如果经常查某个状态的少量数据，也可以建
2. 联合索引顺序
1. 等值查询的列放前面
2. 区分度高的列放前面
3. 排序的列考虑放进去
-- 常见查询
SELECT * FROM orders WHERE user_id = ? AND status = ? ORDER BY create_time DESC;
-- 索引设计
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);
-- user_id区分度高，放前面
-- status等值查询
-- create_time用于排序，放最后
3. 避免冗余索引
-- 已有 (a, b, c)
-- 不需要再建 (a) 或 (a, b)，联合索引已经覆盖
-- 但可能需要 (b) 或 (c)，如果单独查询这些列
4. 控制索引数量
索引不是越多越好：
- 占用磁盘空间
- 插入/更新/删除时要维护索引，影响写性能
- 一般一张表不超过5-6个索引
实战案例
案例1：订单列表查询
-- 需求：查某用户某状态的订单，按时间倒序
SELECT * FROM orders
WHERE user_id = 123 AND status = 1
ORDER BY create_time DESC
LIMIT 20;
方案1：单列索引
CREATE INDEX idx_user_id ON orders(user_id);
-- 能用上，但要回表过滤status，再排序
方案2：联合索引
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);
-- 完美：
-- 1. user_id和status用于过滤
-- 2. create_time已经有序，不需要额外排序
-- 3. 如果只查id，还是覆盖索引
案例2：分页深度优化
-- 原SQL：深分页很慢
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- 要扫描100万+20行
-- 优化：用上一页最后的ID
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;
-- 直接定位到id>1000000，只扫描20行
案例3：统计查询优化
-- 原SQL
SELECT COUNT(*) FROM orders WHERE status = 1;
-- 如果status区分度低，可能全表扫描
-- 优化1：建索引
CREATE INDEX idx_status ON orders(status);
-- 优化2：如果经常统计，用汇总表
-- 定时任务更新
CREATE TABLE order_stats (
status INT,
cnt INT,
updated_at DATETIME
);
EXPLAIN怎么看
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
关键字段：
| 字段 | 含义 | 关注点 |
|---|---|---|
| type | 访问类型 | ALL=全表扫描（差），ref/range=索引扫描（好） |
| key | 实际用的索引 | NULL说明没用索引 |
| rows | 预估扫描行数 | 越小越好 |
| Extra | 额外信息 | Using index=覆盖索引，Using filesort=额外排序 |
type从好到差：
system > const > eq_ref > ref > range > index > ALL
总结
索引优化的核心：
- 理解B+树，知道索引怎么存、怎么查
- 善用覆盖索引，避免回表
- 遵循最左前缀，注意联合索引顺序
- 避免索引失效，函数、类型转换、%开头的LIKE
- 用EXPLAIN分析，看type、key、rows、Extra
记住：索引是空间换时间。写多读少的场景，索引可能是负担；读多写少的场景，索引是救命稻草。
