第 18 章 索引优化与覆盖索引

📖 本篇简介：索引是 MySQL 性能的命脉，但"建了索引"和"索引真的生效"是两回事。本篇假设你已经会 CREATE INDEX、能用 EXPLAIN 看执行计划。学完之后，你将能讲清最左前缀原则、识别哪些写法会让索引失效、利用覆盖索引消灭回表、用索引下推和冗余索引治理把索引数瘦身。本示例基于 MySQL 8.0（InnoDB）。

目录

• 1 索引回顾
• 2 最左前缀原则
• 3 索引失效场景
• 4 覆盖索引
• 5 索引下推 ICP
• 6 冗余与重复索引治理
• 7 索引优化速查表
• 8 常见坑与注意事项
• 9 练习题

1 索引回顾

InnoDB 的索引默认是 B+Tree 结构，分两类：

• 主键索引（聚簇索引）：叶子节点直接存整行数据。
• 二级索引（普通索引）：叶子节点存的是主键值，查二级索引找到主键后，还要再回主键索引取整行，这叫回表。

B+Tree 的特点决定了索引擅长"等值查找"和"范围查找"：树是有序的，从根节点一路往下二分定位，复杂度稳定在 O(log N)。这也是为什么索引对 <、>、BETWEEN 也有效——有序结构天然支持范围扫描。

-- 本示例基于 MySQL 8.0 InnoDB
-- 建一张用户表
CREATE TABLE user (
    id        INT PRIMARY KEY,          -- 聚簇索引
    name      VARCHAR(32),
    age       INT,
    city      VARCHAR(32),
    KEY idx_age_city (age, city)         -- 联合二级索引
) ENGINE = InnoDB;

理解"回表"是后面所有优化的基础：二级索引查到主键 → 拿主键再去聚簇索引查整行，多一次树查找，就是成本。

2 最左前缀原则

联合索引 (age, city) 是按 age 排好序、age 相同再按 city 排序。这就引出最左前缀：查询必须从索引最左列开始，才能用上索引。

| 查询条件 | 能否走 idx_age_city |

|---|---|

| WHERE age = 20 | 能（用了第 1 列） |

| WHERE age = 20 AND city = '北京' | 能（两列都用） |

| WHERE city = '北京' | 不能（跳过了第 1 列） |

| WHERE age > 20 AND city = '北京' | 部分（age 用范围，city 用不上） |

| WHERE age = 20 ORDER BY city | 能，还能利用索引有序性避免 filesort |

-- 能走索引
EXPLAIN SELECT * FROM user WHERE age = 20 AND city = '北京';

-- 不走索引：跳过最左列 age
EXPLAIN SELECT * FROM user WHERE city = '北京';

经验：建联合索引时，把等值查询列放左边、范围查询列放右边；把高频查询列放左边。

为什么范围列不能放中间？因为 B+Tree 是先按第一列排序、第一列相同再按第二列排序。一旦中间某列用了范围（>、<），后面的列在索引里就不再有序，后续列的等值条件只能逐行判断，用不上索引。这就是"范围列之后索引失效"的根因。

3 索引失效场景

即使建了索引，下面这些写法也会让索引失效：

| 失效写法 | 例子 | 原因 |

|---|---|---|

| 对索引列做函数运算 | WHERE DATE(create_time)='2026-10-07' | B+Tree 存的是原值，函数后值无序 |

| 对索引列做运算 | WHERE age + 1 = 21 | 同上 |

| 隐式类型转换 | WHERE phone = 13800000000（phone 是字符串） | 相当于套了函数 |

| 前导模糊查询 | WHERE name LIKE '%张' | B+Tree 无法定位前缀 |

| OR 两边有一列无索引 | WHERE a = 1 OR b = 2（b 无索引） | 整体退化为全表扫 |

| 违反最左前缀 | 见上一节 | - |

| != / NOT IN | WHERE status != 1 | 大部分情况优化器选全表扫 |

这些失效场景的共同点是：破坏了 B+Tree 索引列的有序性。一旦你在索引列上套了函数、做了运算、或者用前导模糊，索引就无法定位，只能退化成全表扫。理解这一点，比死记规则更重要。

-- 失效：对索引列套函数
SELECT * FROM user WHERE DATE(create_time) = '2026-10-07';
-- 改写成范围查询，就能走索引：
SELECT * FROM user
WHERE create_time >= '2026-10-07' AND create_time < '2026-10-08';

-- 失效：前导模糊
SELECT * FROM user WHERE name LIKE '%张%';
-- 前缀模糊可以走索引：
SELECT * FROM user WHERE name LIKE '张%';

-- 失效：隐式类型转换（phone 是 VARCHAR）
SELECT * FROM user WHERE phone = 13800000000;   -- 写错
SELECT * FROM user WHERE phone = '13800000000'; -- 正确

4 覆盖索引

覆盖索引（Covering Index）：查询需要的列全部都在二级索引里，不需要回表。EXPLAIN 的 Extra 列会显示 Using index。

-- 索引 idx_age_city(age, city)
-- 这条只查 age 和 city，二级索引里就有，不用回表
EXPLAIN SELECT age, city FROM user WHERE age = 20;
-- Extra: Using index（覆盖索引，性能极好）

-- 这条还要查 name，二级索引里没有 name，必须回表
EXPLAIN SELECT age, city, name FROM user WHERE age = 20;
-- Extra: 没有 Using index，需要回表

优化思路：把高频查询的 SELECT 列也加进联合索引，让索引"覆盖"整条查询。

-- 高频查询：按 age 查 city 和 name
-- 把索引扩成 (age, city, name)，这样 SELECT age,city,name 就是覆盖索引
ALTER TABLE user ADD INDEX idx_age_city_name (age, city, name);

EXPLAIN SELECT age, city, name FROM user WHERE age = 20;
-- Extra: Using index，无回表

覆盖索引的代价是索引变大、写入变慢，要在"读多写少"的表上用。

判断要不要做覆盖索引，先看这条查询 QPS 高不高：如果是首页这种每秒上千次的查询，把它做成覆盖索引非常值；如果是一年跑一次的报表，没必要为它专门扩索引。覆盖索引是用写入成本换读取成本，只在高频读的场景下划算。

一句话总结：覆盖索引 = 把"回表"这步省掉，让查询全程待在二级索引里。判断标准就是"SELECT 的列是不是都在索引里"。

5 索引下推 ICP

索引下推（Index Condition Pushdown，ICP）：MySQL 5.6 引入，把 WHERE 里能用索引列判断的条件下推到存储引擎层过滤，减少回表次数。

没有 ICP 时：联合索引 (age, city)，WHERE age=20 AND city LIKE '%京%'——age 走索引，找到一批 age=20 的行后全部回表，再在 server 层过滤 city。

有了 ICP：在引擎层就用 city LIKE 先过滤一遍，只把满足 city 的行回表，IO 大幅减少。

-- ICP 生效时，Extra 会显示 Using index condition
EXPLAIN SELECT * FROM user
WHERE age = 20 AND city LIKE '%京%';
-- Extra: Using index condition（ICP 开启）

-- 查看 ICP 是否开启（默认 ON）
SHOW VARIABLES LIKE 'optimizer_switch';
-- 在输出里找 index_condition_pushdown=on

5.1 ICP 的直观收益

假设有联合索引 (age, city)，表里有 100 万行，age=20 的有 10 万行，其中 city='北京' 的只有 1000 行：

• 没有 ICP：定位 age=20 后，10 万行全部回表，再在 server 层筛出 1000 行——回表 10 万次。
• 有 ICP：在引擎层就用 city='北京' 筛掉 9.9 万行，只回表 1000 次。

回表次数降了 100 倍，这就是 ICP 的价值。MySQL 5.6 之后默认开启，一般不用手动管。

6 冗余与重复索引治理

索引不是越多越好。每多一个索引，INSERT/UPDATE/DELETE 都要多维护一棵 B+Tree，写性能下降，还占磁盘。

• 重复索引：PRIMARY KEY(id) 和 KEY(id) 同时存在，后者完全多余。
• 冗余索引：KEY(a) 和 KEY(a,b) 同时存在，前者被后者的最左前缀覆盖，前者冗余。

-- 查看一张表上所有索引
SHOW INDEX FROM user;

-- 删除冗余索引 idx_a（已经被 idx_ab 覆盖）
ALTER TABLE user DROP INDEX idx_a;

-- 删除重复索引
ALTER TABLE user DROP INDEX idx_id;  -- 主键已经是 id

治理经验：

1. 上线前 review 索引，避免重复建；

2. 定期跑 sys.schema_unused_indexes（sys 库）找出从没被用到的索引；

3. 业务字段变更后，回头清理不再需要的索引。

-- MySQL 8.0 sys 库：查从未被使用过的索引
SELECT * FROM sys.schema_unused_indexes
WHERE table_schema = 'shop';

7 索引优化速查表

| 场景 | 优化手段 |

|---|---|

| WHERE 经常按 a=? AND b=? | 建联合索引 (a,b) |

| 查询只查 a,b 两列 | 联合索引 (a,b) 天然覆盖 |

| SELECT 还要 c | 扩成 (a,b,c) 做覆盖索引 |

| ORDER BY 经常排 c | 把 c 也放进联合索引尾部 |

| LIKE '%xx' | 普通索引无效，考虑全文索引 |

| 索引列套函数 | 改写为范围查询或函数索引 |

| (a) 和 (a,b) 同时存在 | 删 (a)，它冗余 |

| Extra 关键字 | 含义 | 是否好事 |

|---|---|---|

| Using index | 覆盖索引，无回表 | 好事 |

| Using index condition | 索引下推 ICP | 好事 |

| Using filesort | 排序没走索引 | 要优化 |

| Using temporary | 临时表 | 要优化 |

| 失效写法 | 改写成 |

|---|---|

| DATE(t)='2026-10-07' | t >= '2026-10-07' AND t < '2026-10-08' |

| name LIKE '%张' | name LIKE '张%' 或全文索引 |

| phone = 138...（字符串列） | phone = '138...' |

7.1 索引的维护成本

索引不是建完就不管了。每次 INSERT/UPDATE/DELETE，InnoDB 都要同步维护所有相关 B+Tree：

| 操作 | 维护哪些索引 |

|---|---|

| INSERT | 每棵索引都要插入一个叶子节点，可能触发页分裂 |

| DELETE | 每棵索引都要标记删除，purge 后真正清理 |

| UPDATE 索引列 | 相当于先删旧值再插新值 |

所以一张表索引越多，写越慢。经验值：单表索引数控制在 5 个以内，超过就要 review。

7.2 索引统计信息

优化器选索引靠的是统计信息（每个索引的基数 cardinality）。统计信息过期会导致选错索引。

-- 手动更新索引统计信息
ANALYZE TABLE user;

-- 查看某个索引的基数（SHOW INDEX 里的 Cardinality）
SHOW INDEX FROM user;

Cardinality 表示索引列上不同值的大概数量，越接近总行数说明区分度越高、索引越有效。如果 Cardinality 明显失真（比如表里有 100 万行，Cardinality 显示 100），跑一次 ANALYZE TABLE。

7.3 前缀索引

对很长的字符串列（如 VARCHAR(255)），整列建索引又大又慢。可以只索引前 N 个字符：

-- 对 email 列只索引前 10 个字符
ALTER TABLE user ADD INDEX idx_email_prefix (email(10));

前缀索引省空间，但不能用于覆盖索引，也不能用于 ORDER BY。适合区分度足够的长字符串列。

7.4 函数索引（MySQL 8.0）

8.0 支持对表达式建索引，正好解决"索引列套函数失效"的问题：

-- 想按 DATE(create_time) 查询又走索引，建函数索引
ALTER TABLE orders ADD INDEX idx_create_date ( (DATE(create_time)) );

-- 这样下面的查询就能走函数索引
SELECT * FROM orders WHERE DATE(create_time) = '2026-10-07';

函数索引是 8.0 的大杀器，既保留了函数写法的可读性，又不牺牲索引。

7.5 高频列 vs 高频查询

建索引的本质是"为高频查询买单"。下面这张表帮你判断该不该建：

| 情况 | 建议 |

|---|---|

| 该列每天被 WHERE/JOIN 命中上万次 | 建 |

| 该列只在后台报表偶尔查一次 | 不建 |

| 该列区分度极低（gender） | 单独建意义不大 |

| 该列被 ORDER BY 频繁用到 | 建，或放进联合索引尾部 |

| 该表写多读少（日志表） | 少建索引，写入优先 |

7.6 覆盖索引设计实战

假设高频查询是：按 user_id 查 status 和 amount，要求不回表。

-- 原始查询
SELECT user_id, status, amount FROM orders WHERE user_id = 1001;

-- 设计覆盖索引：把查询涉及的列全部放进联合索引
ALTER TABLE orders ADD INDEX idx_user_status_amount (user_id, status, amount);

-- 此时 EXPLAIN 的 Extra 出现 Using index，全程在索引里完成，零回表
EXPLAIN SELECT user_id, status, amount FROM orders WHERE user_id = 1001;

注意：覆盖索引里主键会被 InnoDB 二级索引自动带上，所以不需要额外把 id 加进去。

7.7 索引下推与覆盖索引的组合

ICP 和覆盖索引经常一起出现。一条好的优化 SQL，往往是：联合索引 + 覆盖索引 + ICP，三者叠加，让查询全程不碰主键聚簇索引。

-- 索引 (age, city, name)
-- WHERE age=20 AND city LIKE '%京%'，SELECT age,city,name
-- 引擎层用 age 定位、用 city LIKE 下推过滤、需要的列索引里全有
-- 结果：Using index condition; Using index（双重利好）
EXPLAIN SELECT age, city, name FROM user
WHERE age = 20 AND city LIKE '%京%';

7.8 索引设计的决策顺序

拿到一个新需求，按下面顺序决定怎么建索引：

1. 先看 WHERE/JOIN/ORDER BY/GROUP BY 用到了哪些列；

2. 把等值列按频率排序，高频的放联合索引左边；

3. 范围列放最后；

4. 看 SELECT 取了哪些列，能加进索引就加，做成覆盖索引；

5. 建完用 EXPLAIN 验证，确认 type/key/Extra 符合预期；

6. 上线后定期查 sys.schema_unused_indexes，清理没用的索引。

这六步走完，基本能保证索引设计既有效又不冗余。索引设计不是一次性工作，业务变了、查询模式变了，索引也要跟着 review 和调整。

学完本篇，你应该能对着一条慢 SQL，说出它为什么没走索引、该建什么联合索引、要不要做成覆盖索引。下一篇慢查询分析会把这些工具串成一套完整的治理流程。

8 常见坑与注意事项

坑 1：以为索引建得越多越好。

每个索引都占空间、拖慢写入。一张表上 5~6 个索引就该警惕，超过 10 个一定要 review。

坑 2：联合索引列顺序随便排。

把低频列放左边，高频列放右边，结果高频查询用不上索引。原则：等值列优先、高频列优先、排序列放最后。

坑 3：索引列上做运算/函数。

WHERE age+1=21、WHERE DATE(t)=... 都会让索引失效。改写时把运算挪到等号右边。

-- 错
SELECT * FROM user WHERE age + 1 = 21;
-- 对
SELECT * FROM user WHERE age = 20;

坑 4：SELECT * 导致覆盖索引失效。

就算建了覆盖索引，只要你 SELECT * 把所有列都取出来，二级索引里没有的列就得回表，覆盖索引白建。业务里只查需要的列。

坑 5：联合索引 (a,b) 建了，查询只按 b 查，却奇怪为什么没走索引。

这是违反最左前缀，不是 bug。要么把索引顺序调整为 (b,a)，要么改查询加上 a。

坑 6：索引建在低区分度列上。

比如 gender 只有男/女两个值，建索引意义不大——扫索引再回表比全表扫还慢。区分度低的列不适合单独建索引，可以和高频列组成联合索引。

坑 7：索引建完从不维护。

表大量 DELETE/UPDATE 后，索引会产生碎片，统计信息也会过期，优化器选错索引。定期 ANALYZE TABLE，大表碎片严重时 OPTIMIZE TABLE 重建索引。

-- 更新索引统计信息
ANALYZE TABLE user;
-- 重建表和索引（会锁表，业务低峰做）
OPTIMIZE TABLE user;

9 练习题

下面这些题覆盖最左前缀、覆盖索引、索引失效等核心点，建议先自己作答再对答案。

练习 1：联合索引 (a, b, c)，下面哪些查询能走索引？

① WHERE a=1 ② WHERE b=2 ③ WHERE a=1 AND c=3 ④ WHERE a=1 AND b=2 AND c=3

<details>

<summary>参考答案</summary>

① 能（用 a）；② 不能（跳过最左列 a）；③ 部分能（只用 a，c 用不上，因为中间跳了 b）；④ 能（三列全用）。

</details>

练习 2：什么是覆盖索引？它在 EXPLAIN 里怎么体现？

<details>

<summary>参考答案</summary>

覆盖索引指查询需要的列全部包含在二级索引里，不需要回表查聚簇索引。EXPLAIN 的 Extra 列会显示 Using index。它能显著减少回表 IO，是常用优化手段。

</details>

练习 3：WHERE DATE(create_time) = '2026-10-07' 为什么索引失效？怎么改写？

<details>

<summary>参考答案</summary>

对索引列 create_time 套了函数 DATE()，B+Tree 里存的是原始时间值，函数计算后的值无序，无法定位。改写为范围查询：

WHERE create_time >= '2026-10-07' AND create_time < '2026-10-08'

这样就能走 create_time 上的索引。

</details>

练习 4：表里已经有索引 idx_ab(a, b)，再建 idx_a(a) 有必要吗？

<details>

<summary>参考答案</summary>

没必要。idx_ab 的最左前缀已经覆盖了只按 a 查询的场景，idx_a 是冗余索引，白白增加写入维护成本。应该删掉 idx_a。

</details>

练习 5：Extra 显示 Using index condition，这是什么意思？好事还是坏事？

<details>

<summary>参考答案</summary>

这是索引下推（ICP）生效的标志。存储引擎层先用索引里的字段过滤一遍，只把满足条件的行回表，减少了回表次数，是好事，说明 MySQL 8.0 默认开启的优化在起作用。

</details>

练习 6：用 Python + sqlite3 验证：对一个有索引的列做函数运算，是否会让索引失效？

<details>

<summary>参考答案</summary>

import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE t(id INT PRIMARY KEY, ts TEXT)")
conn.execute("CREATE INDEX idx_ts ON t(ts)")
conn.executemany("INSERT INTO t VALUES (?,?)", [(i, f"2026-10-0{i%9}") for i in range(20)])
# 直接等值：走索引
for r in conn.execute("EXPLAIN QUERY PLAN SELECT * FROM t WHERE ts='2026-10-01'"):
    print(r)
# 套函数：不走索引
for r in conn.execute("EXPLAIN QUERY PLAN SELECT * FROM t WHERE substr(ts,1,7)='2026-10'"):
    print(r)

预期输出：第一条 USING INDEX idx_ts，第二条 SCAN t。证明对索引列套函数会让索引失效。

</details>

练习 7：什么是冗余索引？举个例子，怎么治理？

<details>

<summary>参考答案</summary>

冗余索引指已有索引被另一个索引的最左前缀完全覆盖。例如已有联合索引 (a,b)，再建单列索引 (a) 就是冗余——只按 a 查询时 (a,b) 的最左前缀已经能用上。治理：用 SHOW INDEX 或 sys.schema_redundant_indexes 找出冗余索引，ALTER TABLE ... DROP INDEX 删掉，减少写入维护成本。

</details>

练习 8：MySQL 8.0 的函数索引解决了什么痛点？

<details>

<summary>参考答案</summary>

以前对索引列套函数（如 DATE(create_time)）会让索引失效，必须改写成范围查询，可读性差。8.0 的函数索引允许直接对表达式建索引：ADD INDEX idx((DATE(create_time)))，这样 WHERE DATE(create_time)=... 也能走索引，兼顾可读性和性能。

</details>

练习 9：一张订单表 orders(id, user_id, status, amount, create_time)，高频查询是"按 user_id 查最近 10 条已支付订单，按 create_time 倒序"。怎么设计索引？

<details>

<summary>参考答案</summary>

高频查询条件是 user_id=? AND status=? ORDER BY create_time DESC。设计联合索引 (user_id, status, create_time)：等值列 user_id、status 放前面，排序列 create_time 放最后。这样既能用索引定位用户+已支付，又能利用索引有序性避免 filesort。如果 SELECT 只取 id/amount，可进一步把 amount 加进索引做覆盖索引。

</details>

本篇小结：索引优化的核心就是三句话——让高频查询走对索引（最左前缀）、让查询尽量不回表（覆盖索引）、让索引数保持精简（冗余治理）。把这三点刻进脑子，日常调优就不会跑偏。

>

配套工具：每次建完索引，务必用 EXPLAIN 验证 type/key/Extra 是否符合预期，别建完就万事大吉。

>

常见误区：以为索引建得越多越好，实则每个索引都会拖慢写入，上线前务必 review 索引清单。

>

最后一句：索引是给查询服务的，不是越多越聪明，合适才最重要。

下一篇预告：第 19 章 慢查询分析与优化