MySQL索引优化实战：从原理到调优

“为什么加了索引还是慢？”

这个问题我被问过无数次。索引不是万能药，用不好反而是负担。这篇从原理讲起，说说索引优化的实战经验。

索引的本质：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从好到差：

system > const > eq_ref > ref > range > index > ALL

总结

索引优化的核心：

1. 理解B+树，知道索引怎么存、怎么查
2. 善用覆盖索引，避免回表
3. 遵循最左前缀，注意联合索引顺序
4. 避免索引失效，函数、类型转换、%开头的LIKE
5. 用EXPLAIN分析，看type、key、rows、Extra

记住：索引是空间换时间。写多读少的场景，索引可能是负担；读多写少的场景，索引是救命稻草。

有问题评论区聊。