📖 本篇简介：上一章讲"有索引却用不上"，这一章讲"索引从一开始就建错了"。索引设计是个权衡：加索引提升查询，却拖慢写入、占用空间。本章汇总索引设计阶段最容易犯的 11 个错，覆盖区分度、冗余索引、过度索引、前缀索引、排序、外键、索引列长度与联合索引顺序。示例基于 MySQL 8.0。读完你能设计出"查询快、写入不拖、空间不浪费"的索引方案。
目录
- 坑 1：给区分度极低的列单独建索引
- 坑 2：冗余索引和重复索引
- 坑 3：过度索引拖慢写入
- 坑 4：长字符串列整列建索引
- 坑 5：没为排序建索引导致 filesort
- 坑 6：外键关联列没建索引
- 坑 7：把高频更新的列放进索引
- 坑 8：索引列顺序设计反了
- 坑 9：没为高频查询设计覆盖索引
- 坑 10：建索引不看执行计划
- 坑 11：把索引当万能药
- 坑 12：唯一索引只在数据库建、代码里不判重
- 速查表：索引设计决策速记
- 注意事项：索引上线前 Checklist
- 自测：你能避坑吗
坑 1：给区分度极低的列单独建索引
场景 / 错误示范：
-- 性别列只有男/女两种值，单独建索引
CREATE INDEX idx_gender ON user(gender);
-- 查询：WHERE gender = '男'
-- 命中表里 50% 的行，优化器干脆不用这个索引，全表扫
EXPLAIN SELECT * FROM user WHERE gender = '男';
-- type=ALL：索引建了等于白建，还白白拖慢每次写入
为什么是坑：索引的价值在于"快速缩小结果集"。一列只有两三种取值，无论怎么查都要捞回大半张表，走索引再回表的代价比直接全表扫还高，优化器会放弃这个索引。等于你花了维护成本建了个没人用的索引。
正确做法：
-- 低区分度列不单独建索引，而是和高区分度列组合成联合索引
CREATE INDEX idx_gender_age ON user(gender, age);
-- 此时 gender 是联合索引第一列，配合 age 一起过滤才有意义
-- 单纯按性别统计，本来就该走全表扫，别硬加索引
SELECT gender, COUNT(*) FROM user GROUP BY gender;
加分点：经验阈值——COUNT(DISTINCT col)/COUNT(*) 低于 10% 的列，单独建索引意义不大。区分度是索引设计的第一性原理。
坑 2：冗余索引和重复索引
场景 / 错误示范：
-- 已经有 (a,b)，又建了 (a)—— (a) 完全是冗余的
CREATE INDEX idx_a_b ON t(a, b);
CREATE INDEX idx_a ON t(a); -- 冗余！(a,b) 最左前缀已覆盖 a
-- 主键又在 a 上建了普通索引——重复
ALTER TABLE t ADD PRIMARY KEY(a);
ALTER TABLE t ADD INDEX idx_a2(a); -- 重复！主键索引已覆盖 a
为什么是坑：联合索引 (a,b) 已经能满足所有"按 a 查"的需求，再单独建 (a) 是纯浪费——每次写入都要多维护一棵没人用的索引树，占空间还拖慢 INSERT/UPDATE。重复索引同理。线上很多库积年累月，堆了大量没人用的冗余索引。
正确做法：
-- 用 sys 库找出冗余和重复索引（8.0 自带）
SELECT * FROM schema_redundant_indexes;
-- 确认无用后删掉
ALTER TABLE t DROP INDEX idx_a;
ALTER TABLE t DROP INDEX idx_a2;
加分点：定期巡检 schema_unused_indexes 和 schema_redundant_indexes，把从不被使用、或被更宽索引覆盖的索引清理掉。索引"做减法"和"做加法"同样重要。
坑 3：过度索引拖慢写入
场景 / 错误示范：
-- 一张表建了 10 个索引
CREATE TABLE order_t (
id BIGINT PRIMARY KEY,
a INT, b INT, c INT, d INT, e INT, f INT
);
CREATE INDEX i_a ON order_t(a);
CREATE INDEX i_b ON order_t(b);
CREATE INDEX i_c ON order_t(c);
CREATE INDEX i_d ON order_t(d);
CREATE INDEX i_e ON order_t(e);
-- ... 越来越多
-- 结果：每次 INSERT/UPDATE 都要同步维护 10 棵 B+ 树，写入 RT 飙升
为什么是坑：每多一个索引，写入时就要多维护一棵 B+ 树（定位、插入、可能页分裂）。索引越多，写放大越严重。读快了写慢了，写多读少的表尤其明显。索引不是免费的午餐，每个索引都在向写入"收税"。
正确做法：
-- 原则：只为真正高频、慢的查询建索引，宁缺毋滥
-- 写多读少的表（如流水表）索引要尽量少
-- 用 slow log + 实际查询模式反推需要哪几个索引，而不是"可能会用到就建"
-- 删索引也能提升写入：先看哪些索引从不被使用
SELECT * FROM sys.schema_unused_indexes;
加分点：经验法则——单表索引数量控制在 5 个以内，超出就要认真审视每个索引是否必要。读写比例决定索引预算。
坑 4：长字符串列整列建索引
场景 / 错误示范：
-- url 列很长，整列建索引
CREATE INDEX idx_url ON t(url); -- url 平均 200 字符
-- 索引又大又深，B+ 树层级多，查询和占空间都不划算
为什么是坑：InnoDB 二级索引有最大长度限制（767/3072 字节取决于行格式），长字符串整列建索引会让索引页能容纳的键值变少，B+ 树变高，查询要多走几层 IO。而且长字符串的前缀通常已经能区分大部分值，整列建是过度。
正确做法：
-- 用前缀索引，只索引前 N 个字符
CREATE INDEX idx_url_prefix ON t(url(20)); -- 只索引前 20 字符
-- 选 N 的依据：前缀区分度接近整列区分度即可
SELECT
COUNT(DISTINCT LEFT(url, 20)) / COUNT(*) AS sel20,
COUNT(DISTINCT url) / COUNT(*) AS sel_full
FROM t;
-- sel20 接近 sel_full 就够了
加分点：前缀索引节省空间、降低树高。代价是无法用它做覆盖索引和 ORDER BY url 排序——但对"按 url 前缀查找"这类场景完全够用。
坑 5：没为排序建索引导致 filesort
场景 / 错误示范：
-- 列表查询要按 create_time 排序，但没建对应索引
SELECT * FROM t WHERE user_id = 1 ORDER BY create_time DESC LIMIT 20;
EXPLAIN: Extra = "Using filesort"
-- 先按 user_id 查出一堆行，再在内存/磁盘上排序，数据量大就慢
为什么是坑：filesort 意味着 MySQL 拿到结果后要额外做一次排序。命中行少还行，一旦命中几万行，排序就要用排序缓冲，超出还要落临时磁盘文件，CPU 和 IO 都吃紧。排序是高频操作，却常常因为没建对索引而白白多花一次排序。
正确做法：
-- 建 (user_id, create_time) 联合索引，让排序直接用索引有序性
CREATE INDEX idx_uid_ct ON t(user_id, create_time);
EXPLAIN SELECT * FROM t WHERE user_id = 1 ORDER BY create_time DESC LIMIT 20;
-- Extra 里 filesort 消失，索引本身有序，取出就是排好序的
加分点："过滤列 + 排序列"组合成联合索引，是消除 filesort 的标准套路。深分页 + 排序的组合尤其要注意，见下一章。
坑 6：外键关联列没建索引
场景 / 错误示范：
-- 两张表用 user_id 关联，子表的 user_id 没建索引
CREATE TABLE order_t (
id BIGINT PRIMARY KEY,
user_id BIGINT, -- 没索引
amount DECIMAL(10,2)
);
SELECT * FROM user u JOIN order_t o ON u.id = o.user_id WHERE u.id = 1;
-- 被驱动表 order_t.user_id 无索引，每次 JOIN 都全表扫
为什么是坑：JOIN 时被驱动表的关联列如果没有索引，嵌套循环就要全表扫被驱动表，循环次数 = 外层行数 × 被驱动表行数，笛卡尔级放大。这是 JOIN 慢最常见的根因之一——关联列没索引。
正确做法：
-- 给被驱动表的关联列建索引
CREATE INDEX idx_uid ON order_t(user_id);
-- 之后 JOIN：外层每来一行，用 idx_uid 精准定位 order_t 中匹配行
EXPLAIN ...; -- 被驱动表 type=ref，不再是 ALL
加分点：建外键约束时 InnoDB 会自动给外键列建索引，但手写 JOIN 不一定走外键。凡是出现在 JOIN ON 里的列，都该检查有没有索引。
坑 7：把高频更新的列放进索引
场景 / 错误示范：
-- update_time 列每次更新行都变，却放进了好几个联合索引
CREATE INDEX idx_status_ut ON t(status, update_time);
CREATE INDEX idx_type_ut ON t(type, update_time);
-- 每次 UPDATE 改了 update_time，这两个索引都要同步调整位置
-- 写放大翻倍
为什么是坑：二级索引叶子按索引列值有序存储。若索引列的值被频繁更新，MySQL 就要在 B+ 树里把这条记录"摘下来、重新插回正确位置"，代价远大于更新普通列。把频繁变动的列塞进越多索引，写放大越严重。
正确做法：
-- 高频更新列（如 update_time）尽量少进索引，尤其别放联合索引前列
-- 若业务确实要按 update_time 排序，单独建一个，并接受这个写代价
CREATE INDEX idx_ct ON t(create_time); -- create_time 只写不改，适合进索引
-- update_time 这种易变列，非必要不进索引
加分点：索引列更新 = 索引记录的删除+插入。设计时要权衡"这个查询频率是否值得承担每行更新时的索引维护成本"。
坑 8：索引列顺序设计反了
场景 / 错误示范：
-- 业务查询：WHERE status=? ORDER BY create_time
-- 却建了 (create_time, status)
CREATE INDEX idx_ct_status ON t(create_time, status);
-- 结果：status 是等值、create_time 是范围/排序，顺序反了，排序仍要 filesort
EXPLAIN: Extra = Using filesort
为什么是坑：联合索引列顺序要配合查询模式。等值过滤列放前面、排序列放后面，索引才能既过滤又避免排序。顺序搞反，索引只能起到一半作用。很多人按"字母序"或"建表出现顺序"排索引列，这是拍脑袋。
正确做法：
-- 等值列在前，排序列在后
CREATE INDEX idx_status_ct ON t(status, create_time);
-- WHERE status=1 先定位，再利用索引里 create_time 的有序性取出，无需 filesort
EXPLAIN SELECT * FROM t WHERE status = 1 ORDER BY create_time DESC LIMIT 20;
加分点：排序+过滤的查询，把"等值过滤列 + 排序列"按这个顺序建联合索引，能同时解决过滤和排序两件事，是性价比极高的索引设计。
坑 9：没为高频查询设计覆盖索引
场景 / 错误示范：
-- 高频列表查询只取几列，却建了个单列索引导致大量回表
SELECT id, name, age FROM user WHERE city = '北京';
-- idx_city(city) 命中后，每行都要回主键取 name、age，回表成千上万次
为什么是坑：这是上一章坑 8 的"设计侧"版本。查询只需要几列，却只建了过滤列的索引，导致大量回表随机 IO。高频查询每天跑几千次，回表开销累积起来很可观。设计索引时只想着"过滤"，忘了"覆盖"。
正确做法：
-- 把高频查询需要返回的列一起放进联合索引，做成覆盖索引
CREATE INDEX idx_city_name_age ON user(city, name, age);
EXPLAIN SELECT id, name, age FROM user WHERE city = '北京';
-- Extra = Using index：覆盖索引，不回表
-- 注意：主键 id 默认隐含在二级索引里，不用显式加
加分点：覆盖索引是"用空间换时间"的经典手段。识别系统里 Top N 高频 SQL，把它们做成覆盖索引，往往能把慢查询直接消灭。
坑 10：建索引不看执行计划
场景 / 错误示范：
-- 凭感觉加索引，不加 EXPLAIN 验证
ALTER TABLE t ADD INDEX idx_xxx(col);
-- 加完上线，慢查询照旧——因为这条 SQL 根本没走这个新索引
为什么是坑：加索引之前不 EXPLAIN，你不知道这个索引能不能被目标 SQL 用上、会不会被优化器选中。可能加了一个对目标 SQL 完全没用的索引（违反最左前缀、或被更优索引替代），白白增加写入负担。"加了索引就快了"是错觉，必须验证。
正确做法：
-- 1) 加索引前先 EXPLAIN 看现状
EXPLAIN SELECT ... ; -- 记录 type/key/rows
-- 2) 加索引后再 EXPLAIN 对比，确认确实走了新索引
ALTER TABLE t ADD INDEX idx_xxx(col);
EXPLAIN SELECT ... ; -- 确认 key 变成新索引、rows 下降
-- 3) 观察一段时间慢查询日志，确认真的变快了再保留
加分点：建索引是 DDL，大表上加索引本身就有风险（锁、IO）。线上加索引要用 ALGORITHM=INPLACE, LOCK=NONE 或 pt-online-schema-change，别在业务高峰直接加。
坑 11：把索引当万能药
场景 / 错误示范：
-- 查询慢，第一反应永远是"加个索引"
SELECT * FROM big_t WHERE ...; -- 慢
ALTER TABLE big_t ADD INDEX ...;
-- 加了还是慢——根因其实是 SELECT * 取了 TEXT 大字段、或深分页
为什么是坑：索引只解决"定位行慢"的问题，解决不了"取出行后传输慢"（SELECT * 大字段）、"排序慢"（filesort 大结果集）、"深分页慢"（LIMIT 1000000,20）。把索引当万能药，加了一堆索引还是慢，反而牺牲了写入性能。慢的根因可能在别处。
正确做法：
-- 先 EXPLAIN + 慢日志定位根因，再决定手段
-- 是定位慢 -> 加/改索引
-- 是取列多 -> 去掉 SELECT *
-- 是深分页 -> 改游标分页（WHERE id > ? LIMIT 20）
-- 是结果集太大 -> 加 LIMIT、分批
EXPLAIN SELECT id, name FROM big_t WHERE status = 1 ORDER BY id LIMIT 20;
加分点：性能优化是个系统工程，索引只是其中一把工具。看到慢查询先问"它到底慢在哪一步"，而不是条件反射式加索引。
坑 12：唯一索引只在数据库建、代码里不判重
场景 / 错误示范：
-- 给用户手机号建了唯一索引
ALTER TABLE user ADD UNIQUE INDEX uk_phone(phone);
# 代码里直接 INSERT，赌不会重复
def register(phone):
cur.execute("INSERT INTO user(phone) VALUES (?)", (phone,))
# 高并发下两个请求同时进来，都"以为"手机号没注册，
# 后插入的那个撞上唯一索引，抛 Duplicate entry 异常，前端 500
为什么是坑：唯一索引是最后一道防线，不是第一道防线。代码里不先查一下，高并发下必然频繁撞唯一键冲突，异常满天飞。反过来，代码里先 SELECT 再 INSERT 也有并发漏洞（两个请求同时查到不存在）。正确姿势是"数据库唯一约束兜底 + 代码优雅处理冲突"，两头都要有。
正确做法：
-- 唯一索引保留（兜底防重）
ALTER TABLE user ADD UNIQUE INDEX uk_phone(phone);
# 代码里用 INSERT ... ON DUPLICATE KEY UPDATE 或捕获冲突优雅处理
sql = "INSERT INTO user(phone, name) VALUES (?, ?) " \
"ON DUPLICATE KEY UPDATE name=VALUES(name)"
cur.execute(sql, (phone, name)) # 重复时不报错，转为更新
加分点：唯一索引保证数据不重复，应用层负责把"硬冲突"转成"友好提示"。别让唯一键冲突直接冒泡成 500 给用户看。
速查表：索引设计决策速记
| 场景 | 错误做法 | 正确做法 |
|---|---|---|
| 低区分度列 | 单独建索引 | 与高区分度列组联合索引 |
| 已有 (a,b) | 再建 (a) | 删除冗余 (a) |
| 写多读少表 | 建 10 个索引 | 控制在 5 个内 |
| 长字符串 | 整列建索引 | 前缀索引 url(20) |
| 带 ORDER BY | 只建过滤列索引 | (过滤列, 排序列) |
| JOIN 关联列 | 不建索引 | 被驱动表关联列建索引 |
| 高频更新列 | 塞进多个索引 | 非必要不进索引 |
| 等值+排序查询 | 顺序颠倒 | 等值列在前、排序列在后 |
| 高频列表查询 | 只建过滤索引 | 覆盖索引 |
| 慢查询 | 盲目加索引 | 先 EXPLAIN 定位根因 |
注意事项：索引上线前 Checklist
每次准备加索引，先过一遍下面这张清单，避免"加了白加"甚至"加了更慢"。
- [ ] 这条 SQL 真的慢吗？慢日志里频率多高？值不值得加？
- [ ] EXPLAIN 现状是什么？type、key、rows、Extra 都看了吗？
- [ ] 新索引能被目标 SQL 用上吗？符合最左前缀吗？
- [ ] 这张表写入频率多高？加索引的写代价值得吗？
- [ ] 是不是已有更宽的联合索引能覆盖这个需求？
- [ ] 长字符串用了前缀索引吗？
- [ ] 加索引用 ONLINE DDL 了吗？避开业务高峰了吗？
- [ ] 加完后 EXPLAIN 复验、观察慢日志了吗？
一句话总结：索引设计 = 在"查询收益"和"写入成本、空间成本"之间取平衡，每加一个索引都要能回答"它解决了哪条具体 SQL、值多少代价"。
巡检建议：每季度跑一次 schema_unused_indexes 和 schema_redundant_indexes，把长期不用、被更宽索引覆盖的索引下线，给写入减负。索引是会"积灰"的资产，定期打扫和定期新增同样重要。
自测：你能避坑吗
题 1：性别列只有男/女两种值，单独建索引为什么没用？
<details>
<summary>参考答案</summary>
区分度太低，无论怎么查都命中约一半行，走索引再回表比全表扫还贵，优化器会放弃这个索引。应把它和高区分度列组合成联合索引，而不是单独建。
</details>
题 2：已有联合索引 (a,b)，再建一个 (a) 有问题吗？
<details>
<summary>参考答案</summary>
有，(a) 是冗余索引——(a,b) 的最左前缀已经能满足按 a 查询的需求。再建 (a) 只会让每次写入多维护一棵没人用的索引树，应删掉。
</details>
题 3：WHERE status=1 ORDER BY create_time DESC 怎么建索引？
<details>
<summary>参考答案</summary>
建 (status, create_time)：status 等值过滤列在前，create_time 排序列在后，索引天然有序，既过滤又避免 filesort。顺序不要反。
</details>
题 4：为什么要给长字符串列建前缀索引？如何确定前缀长度？
<details>
<summary>参考答案</summary>
整列建索引会让索引页容纳的键值变少、B+ 树变高。前缀索引只索引前 N 字符，显著省空间降树高。N 的选法：让 COUNT(DISTINCT LEFT(col,N))/COUNT(*) 接近整列的区分度即可。
</details>
题 5：为什么写多读少的流水表要严控索引数量？
<details>
<summary>参考答案</summary>
每个二级索引在 INSERT/UPDATE 时都要同步维护一棵 B+ 树，索引越多写放大越严重。流水表写入极其频繁，加一堆索引会让写入 RT 飙升、主从延迟加大。应只为真正高频的查询建必要的少量索引。
</details>
题 6：给一张千万行大表加索引，直接在业务高峰 ALTER TABLE ADD INDEX 有什么风险？
<details>
<summary>参考答案</summary>
传统加索引会锁表、占用大量 IO，高峰执行会阻塞写入甚至拖垮实例。应使用 ALGORITHM=INPLACE, LOCK=NONE 或 pt-online-schema-change，并在业务低峰执行，先在从库验证。
</details>
题 7：EXPLAIN 的 Extra 里出现 "Using filesort"，一定是索引设计错了吗？
<details>
<summary>参考答案</summary>
多半是。filesort 表示 MySQL 拿到结果后还要额外排序。若这个排序是高频查询，可以用"过滤列+排序列"联合索引消除它；若命中行很少，filesort 代价可接受，不必为了它硬加索引。结合业务频率判断，不是见 filesort 就必须改。
</details>
题 8：联合索引 (a,b,c)，查询 WHERE a=1 AND c=2 能用到哪几列？
<details>
<summary>参考答案</summary>
只能用到 a。中间跳过了 b，违反最左前缀，c 用不上索引。若业务常按 a、c 查询，应另建 (a,c) 联合索引，而不是依赖 (a,b,c)。
</details>
下一篇预告：第 46 章 查询写法踩坑（一）
文章回复
0 条公开回复