“为什么加了索引还是慢？”
这个问题我被问过无数次。索引不是万能药，用不好反而是负担。这篇从原理讲起，说说索引优化的实战经验。
索引的本质：B+树
MySQL的InnoDB索引用的是B+树，理解这个结构才能理解索引的行为。
[根节点: 50] / \ [20, 35] [70, 85] / | \ / | \ [数据] [数据] [数据] [数据] [数据] [数据] ↓ ↓ ↓ ↓ ↓ ↓ 叶子节点包含完整数据行（聚簇索引） 或主键值（二级索引）关键特点：
- 叶子节点存数据，非叶子节点只存索引
- 叶子节点有序且双向链接，范围查询很快
- 树高度通常3-4层，千万级数据也只需3-4次IO
聚簇索引 vs 二级索引
聚簇索引（主键索引）
数据按主键顺序存储，主键索引的叶子节点就是数据本身。
-- 主键查询，直接定位到数据SELECT*FROMusersWHEREid=100;-- 只需要查聚簇索引，一次搞定二级索引（普通索引）
叶子节点存的是主键值，查到后还要回表查聚簇索引。
-- 假设name上有索引SELECT*FROMusersWHEREname='张三';-- 执行过程：-- 1. 在name索引上找到name='张三'对应的主键id-- 2. 拿着id去聚簇索引找完整数据-- 这个过程叫"回表"回表是性能杀手。能避免就避免。
覆盖索引：干掉回表
如果查询的列都在索引里，就不用回表了。
-- 原SQL，需要回表SELECTid,name,ageFROMusersWHEREname='张三';-- 如果只有name索引，要回表取age-- 优化：建联合索引CREATEINDEXidx_name_ageONusers(name,age);-- 现在查询的列(id, name, age)都在索引里了-- id是主键，二级索引叶子节点自带-- name, age在联合索引里-- 不用回表，直接返回EXPLAIN看到Using index就是覆盖索引：
EXPLAINSELECTid,name,ageFROMusersWHEREname='张三';-- Extra: Using index ← 覆盖索引，没回表联合索引的最左前缀原则
联合索引(a, b, c)的结构：
先按a排序 a相同的按b排序 b相同的按c排序所以：
-- 能用上索引WHEREa=1WHEREa=1ANDb=2WHEREa=1ANDb=2ANDc=3WHEREa=1ANDc=3-- 只用到a（c用不上，因为跳过了b）-- 用不上索引WHEREb=2-- 跳过了aWHEREc=3-- 跳过了a和bWHEREb=2ANDc=3-- 跳过了a范围查询会截断
-- 索引 (a, b, c)WHEREa=1ANDb>10ANDc=3-- a用等值查询 ✓-- b用范围查询 ✓-- c用不上！因为b是范围查询，后面的列无法使用索引所以等值查询的列放前面，范围查询的列放后面：
-- 差：(status, create_time, user_id)WHEREstatus=1ANDcreate_time>'2024-01-01'ANDuser_id=100-- create_time是范围，user_id用不上-- 好：(status, user_id, create_time)WHEREstatus=1ANDuser_id=100ANDcreate_time>'2024-01-01'-- 三个列都能用上索引失效的常见场景
1. 对索引列做运算
-- 失效SELECT*FROMordersWHEREYEAR(create_time)=2024;-- 优化SELECT*FROMordersWHEREcreate_time>='2024-01-01'ANDcreate_time<'2025-01-01';2. 隐式类型转换
-- phone是varchar类型-- 失效：数字会转成字符串，导致全表扫描SELECT*FROMusersWHEREphone=13800138000;-- 正确SELECT*FROMusersWHEREphone='13800138000';3. LIKE以%开头
-- 失效SELECT*FROMusersWHEREnameLIKE'%张';-- 能用索引SELECT*FROMusersWHEREnameLIKE'张%';4. OR连接的条件
-- 如果name没索引，整个查询都不走索引SELECT*FROMusersWHEREid=1ORname='张三';-- 优化1：给name加索引-- 优化2：改成UNIONSELECT*FROMusersWHEREid=1UNIONSELECT*FROMusersWHEREname='张三';5. NOT IN、NOT EXISTS、!=
-- 可能不走索引（优化器判断）SELECT*FROMusersWHEREstatus!=0;SELECT*FROMusersWHEREidNOTIN(1,2,3);-- 如果status大部分是0，可以改成SELECT*FROMusersWHEREstatusIN(1,2,3);6. IS NULL / IS NOT NULL
-- 看数据分布，NULL值多可能不走索引SELECT*FROMusersWHEREdeleted_atISNULL;索引设计原则
1. 选择区分度高的列
-- 区分度 = COUNT(DISTINCT col) / COUNT(*)-- 性别：区分度约0.5，不适合单独建索引-- 手机号：区分度接近1，适合建索引-- 状态：区分度低，但如果经常查某个状态的少量数据，也可以建2. 联合索引顺序
1. 等值查询的列放前面 2. 区分度高的列放前面 3. 排序的列考虑放进去-- 常见查询SELECT*FROMordersWHEREuser_id=?ANDstatus=?ORDERBYcreate_timeDESC;-- 索引设计CREATEINDEXidx_user_status_timeONorders(user_id,status,create_time);-- user_id区分度高，放前面-- status等值查询-- create_time用于排序，放最后3. 避免冗余索引
-- 已有 (a, b, c)-- 不需要再建 (a) 或 (a, b)，联合索引已经覆盖-- 但可能需要 (b) 或 (c)，如果单独查询这些列4. 控制索引数量
索引不是越多越好：
- 占用磁盘空间
- 插入/更新/删除时要维护索引，影响写性能
- 一般一张表不超过5-6个索引
实战案例
案例1：订单列表查询
-- 需求：查某用户某状态的订单，按时间倒序SELECT*FROMordersWHEREuser_id=123ANDstatus=1ORDERBYcreate_timeDESCLIMIT20;方案1：单列索引
CREATEINDEXidx_user_idONorders(user_id);-- 能用上，但要回表过滤status，再排序方案2：联合索引
CREATEINDEXidx_user_status_timeONorders(user_id,status,create_time);-- 完美：-- 1. user_id和status用于过滤-- 2. create_time已经有序，不需要额外排序-- 3. 如果只查id，还是覆盖索引案例2：分页深度优化
-- 原SQL：深分页很慢SELECT*FROMordersORDERBYidLIMIT1000000,20;-- 要扫描100万+20行-- 优化：用上一页最后的IDSELECT*FROMordersWHEREid>1000000ORDERBYidLIMIT20;-- 直接定位到id>1000000，只扫描20行案例3：统计查询优化
-- 原SQLSELECTCOUNT(*)FROMordersWHEREstatus=1;-- 如果status区分度低，可能全表扫描-- 优化1：建索引CREATEINDEXidx_statusONorders(status);-- 优化2：如果经常统计，用汇总表-- 定时任务更新CREATETABLEorder_stats(statusINT,cntINT,updated_atDATETIME);EXPLAIN怎么看
EXPLAINSELECT*FROMordersWHEREuser_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
记住：索引是空间换时间。写多读少的场景，索引可能是负担；读多写少的场景，索引是救命稻草。
有问题评论区聊。
