3、索引优化策略：复合索引设计、覆盖索引、索引下推、索引失效场景

索引这东西，说白了就是数据库的「目录」。但很多人建索引就是一股脑加，结果该快的没快，反而拖慢了写入。我这些年踩过的坑不少，今天把这四个核心点掰开揉碎讲清楚。

3.1 复合索引设计：最左前缀原则

复合索引，就是把多个字段揉成一个索引。但有个铁律——最左前缀原则。什么意思？你建了 (a, b, c) 的索引，查询条件必须从 a 开始，跳过了 a 直接查 b，索引就废了。

我在项目中遇到过好几次这种问题。有一次审批流查待办，条件是按 approver_id 和 status 查，结果 DBA 建了 (status, approver_id) 的索引。看起来没问题对吧？但实际查询里，approver_id 是必传的，status 反而有时候不传。这就导致索引没被充分利用。

正确的做法是：把区分度高的、经常作为等值条件的字段放左边。

-- 错误示范：status 经常不传，却放在最左边
CREATE INDEX idx_wrong ON approval (status, approver_id, create_time);

-- 正确示范：approver_id 必传且区分度高，放最左边
CREATE INDEX idx_right ON approval (approver_id, status, create_time);

3.2 覆盖索引：让查询「不走表」

覆盖索引，就是索引里已经包含了查询需要的所有字段。这样数据库直接读索引就完事了，不用回表查数据行。你想想看，少了一次磁盘 I/O，速度能不快吗？

我记得有一次优化审批流列表页，页面要展示 id、title、status、create_time 四个字段。原来的查询走了索引，但还要回表拿 title 字段。我建议把 title 也加到索引里，改成复合索引 (approver_id, status, create_time, title)。结果查询时间从 120ms 降到了 8ms。

-- 原查询：需要回表
SELECT id, title, status, create_time 
FROM approval 
WHERE approver_id = 1001 AND status = 'PENDING';

-- 覆盖索引：索引包含所有查询字段
CREATE INDEX idx_cover ON approval (approver_id, status, create_time, id, title);
-- 注意：id 是主键，InnoDB 会自动包含，所以实际只需加 title

3.3 索引下推：MySQL 5.6 后的隐藏福利

索引下推（Index Condition Pushdown，ICP），是 MySQL 5.6 引入的优化。说白了，就是把 where 条件判断「下推」到存储引擎层，在索引遍历时就过滤掉不满足条件的行，减少回表次数。

举个例子。你有索引 (zipcode, last_name)，查询条件是 zipcode = '100001' AND last_name LIKE '%张%'。没有 ICP 时，数据库会先根据 zipcode 找到所有记录，然后回表，再在 server 层过滤 last_name。有 ICP 时，在存储引擎层就判断 last_name 是否匹配，不匹配的直接跳过，不回表。

我曾经在一个报表查询里，用 ICP 把扫描行数从 50 万降到了 3 万。效果立竿见影。

-- 查看是否开启 ICP
SHOW VARIABLES LIKE 'optimizer_switch'\G
-- 输出中看到 index_condition_pushdown=on 就对了

-- 强制关闭 ICP（一般不推荐）
SET optimizer_switch = 'index_condition_pushdown=off';

3.4 索引失效场景：避坑指南

这部分我吃过不少亏。索引建好了，查询却不用，白忙活一场。下面这几个场景，我建议你背下来。

场景	错误写法	正确写法	原因
隐式类型转换	WHERE user_id = '1001'（user_id 是 int）	WHERE user_id = 1001	字符串转数字，索引失效
函数操作	WHERE DATE(create_time) = '2024-01-01'	WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'	函数让索引列变成计算值
模糊匹配前缀	WHERE name LIKE '%张三'	WHERE name LIKE '张三%'	前模糊无法利用 B+ 树顺序
OR 条件	WHERE status = 'A' OR status = 'B'	WHERE status IN ('A', 'B')	OR 可能导致全表扫描
不等于/ NOT IN	WHERE status != 'CLOSED'	考虑用 status IN ('PENDING', 'APPROVED')	不等于通常不走索引

我曾经接手过一个审批流系统，每天几百万条数据，查询慢得要命。一查，发现开发在 create_time 上用了 DATE() 函数，索引完全没生效。改掉之后，查询从 3 秒降到了 50 毫秒。嗯，有时候问题就这么简单。

3.5 知识体系总览

下面这张图，把本章四个核心知识点串起来了。你可以把它当作索引优化的「检查清单」。

这张图把四个知识点串成了一个整体。你写 SQL 时，可以对照着检查：复合索引设计对了吗？能不能用覆盖索引？ICP 有没有生效？有没有踩到索引失效的坑？