4. 复合索引设计：多字段联合索引原理、最左前缀原则、索引下推优化

复合索引（Composite Index / Multi-Column Index）是数据库性能调优中最核心、也最容易被误用的工具。它并非简单地将多个单列索引叠加，而是通过特定的数据结构与匹配规则，实现跨字段的快速定位。理解其原理、最左前缀原则以及索引下推优化，是设计高效缓存一致性治理方案的前提。

4.1 多字段联合索引的存储原理

复合索引在 B+Tree 结构中，按照索引定义时的字段顺序，逐层构建排序键。以索引 (a, b, c) 为例，其排序规则为：

• 第一排序键：字段 a 的值。
• 第二排序键：当 a 值相同时，按字段 b 的值排序。
• 第三排序键：当 a 和 b 均相同时，按字段 c 的值排序。

这种排序方式决定了索引的物理组织形态：索引页中的记录并非按 b 或 c 独立有序，而是严格遵循字段定义的层级依赖。

4.2 最左前缀原则（Leftmost Prefix Principle）

最左前缀原则是复合索引使用的核心约束：查询条件必须从索引定义的最左侧字段开始匹配，且不能跳过中间的字段。只有满足这一原则，索引才能被高效利用。

4.2.1 匹配规则详解

4.2.2 范围查询对最左前缀的影响

当查询条件中出现范围查询（>、<、BETWEEN、LIKE 'abc%'）时，最左前缀原则有一个重要变体：范围查询之后的字段无法使用索引进行等值匹配，只能用于过滤。

-- 索引 (status, created_at, user_id)

-- 高效：status 等值，created_at 范围，user_id 无法利用索引排序
SELECT * FROM orders
WHERE status = 'PAID'
  AND created_at > '2024-01-01'
  AND user_id = 123;

-- 低效：status 范围，后续字段均无法利用索引
SELECT * FROM orders
WHERE status > 'PAID'
  AND created_at > '2024-01-01';

4.3 索引下推优化（Index Condition Pushdown, ICP）

索引下推是 MySQL 5.6 引入的优化特性，它允许存储引擎在索引遍历过程中，直接对索引包含的字段进行条件过滤，减少回表次数。在没有 ICP 时，存储引擎只能根据索引定位到行记录，然后交由 Server 层过滤；启用 ICP 后，过滤操作下推到存储引擎层。

4.3.1 ICP 工作原理对比

4.3.2 ICP 适用条件

• 仅适用于二级索引：主键索引（聚簇索引）本身包含完整行数据，无需回表，ICP 无意义。
• 仅适用于 WHERE 条件中可以使用索引列进行过滤的场景：特别是当查询条件包含索引中非最左前缀的字段时，ICP 能显著减少回表。
• 无法用于覆盖索引：如果查询的所有字段都在索引中（覆盖索引），则无需回表，ICP 不会生效。

4.4 复合索引设计策略

4.4.1 字段顺序选择原则

4.4.2 实战案例：订单查询优化

-- 原始查询（慢）
SELECT * FROM orders
WHERE status = 'PAID'
  AND created_at > '2024-06-01'
  AND user_id = 1001
ORDER BY created_at DESC;

-- 索引设计 1（低效）：(status, created_at, user_id)
-- 问题：user_id 无法利用索引，需要回表后过滤

-- 索引设计 2（高效）：(status, user_id, created_at)
-- 理由：
--   1. status 和 user_id 都是等值条件，放在前面
--   2. user_id 选择性高于 status，放在第二位
--   3. created_at 是范围查询 + 排序，放在最后
--   4. 索引天然支持 ORDER BY created_at DESC（反向扫描）