跳转到主内容
极星编程网：以代码为星，赴技术山海！
📁 网站地图

SQL 单列索引与复合索引设计方法

苏承栈
苏承栈
• 👁 0人浏览过
单列索引足够，无需强行建复合索引；复合索引必须按最左前缀顺序使用；ORDER BY字段需紧接等值条件字段并方向一致；UPDATE/DELETE性能取决于WHERE是否命中索引。 WHERE 条件里只用一个字段，该建单列索引还是复合索引？ 单列索引足够，别硬凑复合索引。MySQL 和 PostgreSQL 都能高效走
WHERE user_id = ?
这类单条件查询，前提是
user_id
上有单列索引。强行加成复合索引（比如
(user_id, created_at)
）反而浪费空间，还可能干扰优化器对其他查询的判断。 常见错误现象：看到慢查询日志里反复出现
WHERE status = 'active'
，顺手建了个
(status, id)
复合索引——但实际业务中
status
几乎从不和其他字段联合过滤，这个
id
就纯属冗余。 单列索引适合：高频、独立使用的过滤字段，如
user_id
、
order_no
、
email
复合索引的“前缀匹配”特性在此无用武之地，因为没用到第二列 注意 B+ 树索引页大小和写放大：多一列就多存一份数据，尤其当第二列是
TEXT
或大
VARCHAR
时更明显 WHERE 同时用两个字段，必须按顺序建复合索引吗？ 必须，而且顺序不能反。复合索引
(a, b)
能加速
WHERE a = ? AND b = ?
，也能加速
WHERE a = ?
，但对
WHERE b = ?
完全无效——B+ 树只支持最左前缀匹配。 使用场景典型如订单表：常查
WHERE shop_id = ? AND status = ?
，那就建
(shop_id, status)
；如果反过来，先查
status
再查
shop_id
，就得另建索引或调整查询逻辑。 参数差异关键在“选择性”：高区分度字段（如
user_id
）放前面，低区分度字段（如
status
只有 3–5 个值）放后面 执行计划里看
key_len
：如果
WHERE a = ? AND b = ?
走了
(a, b)
索引但
key_len
只显示 a 的长度，说明 b 没用上——可能是 b 用了函数、类型隐式转换，或被
OR
/
LIKE '%xxx'
破坏了索引下推 PostgreSQL 中
WHERE b = ? AND a = ?
仍能走
(a, b)
索引，但 MySQL 严格依赖书写顺序是否匹配最左前缀 ORDER BY + LIMIT 场景下，索引要覆盖排序字段吗？ 要，否则容易触发
Using filesort
。比如
SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC LIMIT 10
，只建
(user_id)
单列索引，MySQL 得先把所有匹配行捞出来再内存排序；而建
(user_id, created_at)
后，B+ 树天然按
created_at
排好序，直接取前 10 条就行。 容易踩的坑是忽略方向一致性：
ORDER BY created_at DESC
和
INDEX (user_id, created_at ASC)
在 MySQL 8.0 以前无法利用排序能力，必须显式声明
DESC
（
CREATE INDEX idx ON orders (user_id, created_at DESC)
）。 复合索引中排序字段必须紧接在等值条件字段之后，中间不能插非等值条件（如
WHERE a = ? AND b > ? ORDER BY c
，
(a, b, c)
依然有效；但
WHERE a > ? ORDER BY b
就不行）
LIMIT
越小，覆盖索引收益越明显；但若
LIMIT 1000
，且
created_at
分布极散，仍可能回表大量数据，这时得权衡是否加
INCLUDE
列（PostgreSQL）或覆盖索引（MySQL） 注意时间字段精度：
DATETIME(6)
和
DATETIME
类型不一致会导致索引失效，即使看起来值一样 UPDATE 或 DELETE 语句走不走索引，取决于什么？ 取决于
WHERE
子句能否命中索引，和
UPDATE
自身改哪些列完全无关。很多人误以为“更新太多列就会慢”，其实瓶颈永远在定位行——只要
WHERE id = ?
走了主键索引，哪怕更新 20 个字段也很快；反之，
WHERE name LIKE '%abc%'
没索引，整表扫描才是真慢。 真实线上事故常见于：某天突然发现
DELETE FROM logs WHERE created_at 卡住，查发现 created_at
没索引，又没加
LIMIT
，直接扫了几千万行。 写操作的索引成本是双刃剑：每建一个索引，
INSERT/UPDATE/DELETE
都要多维护一棵 B+ 树，尤其是高并发写入场景，索引越多，写放大越严重 复合索引对写的影响比单列更大：比如
(a, b, c)
索引，只要 a/b/c 中任一列被更新，整个索引项就得重写 MySQL 的
EXPLAIN UPDATE
不直观，建议用
EXPLAIN FORMAT=JSON
看
used_key_parts
字段确认实际用了索引哪几列 事情说清了就结束。索引不是越多越好，也不是照着
WHERE
字段堆砌就行；真正卡住性能的，往往是那个漏掉的等值条件字段，或者排序字段没放进复合索引的第二位。

相关文章