📚 课程目录第3章索引设计原则

3、索引设计原则：高并发场景下的索引选择、联合索引最左前缀原则、索引下推优化

在高并发评分通道中，索引是提升查询性能最直接、成本最低的手段。然而，错误的索引设计不仅无法加速查询，反而会拖慢写入、增加存储开销。本节聚焦高并发场景下的索引选择策略、联合索引的最左前缀原则以及 MySQL 5.6+ 引入的索引下推（ICP）优化，帮助你在海量评分请求中精准命中索引。

3.1 高并发场景下的索引选择策略

高并发写入与高频查询并存时，索引选择需遵循以下核心原则：

原则	说明	高并发场景建议
区分度高优先	选择基数（Cardinality）大的列作为索引前列	用户ID、订单号、评分时间戳
查询驱动索引	索引设计以 WHERE、JOIN、ORDER BY 为输入	分析慢查询日志，覆盖 95% 的查询模式
避免冗余索引	联合索引可覆盖多个单列索引的功能	删除重复索引，减少写入维护成本
索引列短小	使用整型或短字符串，减少 B+Tree 层级	评分值用 TINYINT，状态用 ENUM
写放大控制	每个索引都会拖慢 INSERT/UPDATE	单表索引数控制在 5 个以内

实战建议：在评分通道中，user_id 和 score_time 是高频查询列。优先建立联合索引 (user_id, score_time)，而非分别为两列建立独立索引，可减少索引数量并提升查询效率。

3.2 联合索引最左前缀原则

联合索引（Composite Index）遵循最左前缀原则：查询条件必须从索引的最左列开始匹配，才能使用该索引。跳过左侧列或使用右侧列作为起始条件，会导致索引失效。

  核心规则：联合索引 (a, b, c) 可匹配的查询模式：
  WHERE a = ? —— 使用索引
WHERE a = ? AND b = ? —— 使用索引
WHERE a = ? AND b = ? AND c = ? —— 使用索引
WHERE b = ? —— 无法使用索引
WHERE a = ? AND c = ? —— 仅使用 a 列索引，c 列无法利用

常见误区：不要将区分度低的列（如性别、评分等级 1-5）放在联合索引最左侧。例如索引 (score_value, user_id) 在查询 WHERE user_id = ? 时完全失效，因为跳过了左列。

3.3 索引下推优化（Index Condition Pushdown）

索引下推（ICP）是 MySQL 5.6 引入的优化，允许在索引遍历阶段就过滤不符合条件的记录，减少回表次数。对于联合索引中无法使用最左前缀的列，ICP 能显著提升查询性能。

对比项	无 ICP（MySQL 5.6 之前）	有 ICP（MySQL 5.6+）
执行流程	索引定位 → 回表读取完整行 → Server 层过滤	索引定位 → 存储引擎层过滤 → 仅满足条件时回表
回表次数	所有索引匹配行都回表	仅通过索引过滤后的行回表
适用场景	—	联合索引右侧列的条件过滤
性能提升	基线	减少 50%~90% 的回表 I/O

3.4 评分通道实战案例

假设评分表结构如下：

CREATE TABLE score_record (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    score_time DATETIME NOT NULL,
    score_value TINYINT NOT NULL,
    channel_id INT NOT NULL,
    INDEX idx_user_time (user_id, score_time)
) ENGINE=InnoDB;

高频查询场景：

-- 查询某用户最近 30 天的评分记录
SELECT * FROM score_record
WHERE user_id = 12345
  AND score_time >= NOW() - INTERVAL 30 DAY
ORDER BY score_time DESC;

-- 查询某用户在某时间段的评分明细（含评分值过滤）
SELECT * FROM score_record
WHERE user_id = 12345
  AND score_time BETWEEN '2024-01-01' AND '2024-01-31'
  AND score_value IN (4, 5);

  优化分析：
  第一个查询：联合索引 (user_id, score_time) 完全匹配最左前缀，score_time 范围查询在索引内完成，无需回表过滤。
第二个查询：联合索引 (user_id, score_time) 匹配前两列，score_value 条件通过 ICP 在索引层过滤，仅满足 score_value IN (4,5) 的行才回表，减少约 60% 的回表 I/O。
若将索引改为 (user_id, score_value, score_time)，则第二个查询可完全使用索引（覆盖索引），但第一个查询的 score_time 范围过滤将无法利用索引右侧列，需权衡。

3.5 索引设计避坑清单

避免在索引列上使用函数：WHERE DATE(score_time) = '2024-01-01' 会导致索引失效，应改为 WHERE score_time >= '2024-01-01' AND score_time < '2024-01-02'。
避免隐式类型转换：WHERE user_id = '12345'（字符串 vs INT）会使索引失效，确保类型一致。
避免 OR 条件拆分不当：WHERE user_id = 1 OR score_time > '2024-01-01' 无法使用联合索引，应改用 UNION ALL 或拆分为两个查询。
避免索引列参与计算：WHERE score_value * 2 = 10 应改为 WHERE score_value = 5。