为什么是这三列、这个顺序
上一篇第 1 篇里那把 idx_user_status_time (user_id, status, create_time) 一把梭见效，但顺序是拍脑袋的吗？当然不是——这期把联合索引的设计规则讲透，核心就一件事：索引里的行是按 (user_id, status, create_time) 这个顺序整体排序的，先按 user_id 排，user_id 相同再按 status 排，还相同才按 create_time 排。像一本字典：先按首字母，再按第二个字母。
最左前缀原则：字典的查法
字典里所有词按拼音字母序排，所以你能快速查「zh」开头的词，却没法快速查「ing」结尾的词——结尾没有排序可言。联合索引同理，查询条件必须从索引最左列开始连续命中：
| 查询条件 | 能否走索引 | 说明 |
|---|---|---|
| user_id = 9527 | 能 | 命中第 1 列 |
| user_id = 9527 AND status = 1 | 能 | 连续命中前 2 列 |
| user_id = 9527 AND status = 1 AND create_time > x | 能 | 三列全命中，范围列放最后 |
| status = 1（没带 user_id） | 不能 | 跳过最左列，status 在各 user_id 段内是无序的 |
| user_id = 9527 AND create_time > x | 部分 | 只用到 user_id；create_time 在 status 不同的行间无序，跳过 status 断了 |
两个重要细节：一是顺序由索引定义决定，与 WHERE 里写的先后无关——优化器会自动把条件按索引顺序对齐；二是范围查询列放在最后，范围列（>、<、BETWEEN）之后的列就用不上索引有序性了——这也是为什么把 create_time 排在第三列而不是第二列：等值列在前，范围列压轴。
回表：二级索引的原罪
第 3 篇讲过，二级索引叶子节点只存主键值。查 SELECT * FROM orders WHERE user_id = 9527 时，索引里找不到 order_no、amount 这些字段，只能拿着主键回聚簇索引再查一遍——每行一次回表。行数少无所谓，扫几千行回几千次表就肉疼了。
覆盖索引：把回表打到零
如果查询要的列全部都在索引里，就不用回表了——这叫覆盖索引，EXPLAIN 的 Extra 会显示 Using index（第 2 篇的暗语）。老王的订单列表就是现成的例子：
# 列表页只需要这几列：把 amount 塞进联合索引，整条查询不回表
ALTER TABLE orders ADD INDEX idx_cover (user_id, status, create_time, amount);
SELECT id, order_no, amount, create_time
FROM orders WHERE user_id = 9527 AND status = 1
ORDER BY create_time DESC LIMIT 20;注意 id 是主键，二级索引天然携带，不用重复建进索引。Extra 出现 Using index 就说明覆盖成功。代价是索引变宽、写入变慢——只为高频 SQL 做覆盖，别给每条查询都配一把。
索引下推：5.6 送的礼物
还有个容易混淆的优化：索引下推（ICP，Index Condition Pushdown）。查询 WHERE name LIKE '陈%' AND age = 25，联合索引 (name, age)：name 的前缀匹配能定位范围，但 age 因为 name 是范围条件而用不上索引排序——没有 ICP 时，InnoDB 把范围内所有行取出来回表，再在服务层过滤 age；有 ICP 时，age 的判断被下推到引擎层、直接在索引里过滤，不满足的行连回表都省了。EXPLAIN 里对应 Using index condition。它和覆盖索引的区别一句话：覆盖索引不回表，下推少回表。
设计口诀
- 等值列在前，范围列压轴，排序列跟在等值后——这样 WHERE 和 ORDER BY 一把全收。
- 一表多查询场景，建多把小索引别建一把万能索引——万能索引必冗余，但重复前缀可以合并（(user_id, status) 已存在，(user_id, create_time) 的前缀查询单独建）。
- 高频查询优先考虑覆盖，把 SELECT 里确有必要的列补进索引，宁窄勿滥。
- 区分度低的列不单独建索引（比如 status 只有 3 个值），放联合索引里当过滤条件即可。
规矩都立了，但总有人写出「让索引悄悄失效」的 SQL——函数包一包、类型错一错，EXPLAIN 立刻翻脸。下一篇专门盘点这些失效写法，都是老王项目里真实踩过的。
咖啡凉了，记得趁热喝。
评论 (0)