联合索引 && 覆盖索引 && 最左前缀原则 && 索引下推 && 回表

以下是数据库索引核心概念的清晰解析，涵盖你提到的五个关键技术点：

---

1. 联合索引（Composite Index）

• 定义：在多个列上建立的索引（例如 INDEX (col1, col2, col3)）。
• 存储结构：
  B+树中按列顺序存储键值，先按 col1 排序，col1 相同再按 col2 排序，以此类推。
• 作用：
  优化涉及多列的查询（如 WHERE col1=1 AND col2=2）。

---

2. 覆盖索引（Covering Index）

• 定义：索引本身包含查询所需的所有列（无需回表查主键）。
• 示例：
  索引 (name, age)，查询 SELECT name, age FROM users → 直接读取索引数据。
• 优势：
  ⚡️ 避免回表，减少 I/O，显著提升性能。

---

3. 最左前缀原则（Leftmost Prefix Principle）

• 规则：联合索引 (A,B,C) 仅对以下查询有效：
  • WHERE A=1
  • WHERE A=1 AND B=2
  • WHERE A=1 AND B=2 AND C=3
  • WHERE A=1 AND C=3（仅用 A，C 失效）
• 无效场景：
  • WHERE B=2（未用 A）
  • WHERE B=2 AND C=3（未用 A）

📌 本质：索引键值按左到右顺序匹配，跳过左侧列则索引失效。

---

4. 索引下推（Index Condition Pushdown, ICP）

• 定义：在存储引擎层提前过滤数据（MySQL 5.6+ 支持）。
• 未启用 ICP：
  WHERE name LIKE '张%' AND age=20（联合索引 (name,age)）
  → 先按 name 查主键 → 回表 → 再过滤 age=20。
• 启用 ICP 后：
  存储引擎直接按 name LIKE '张%' AND age=20 过滤 → 仅有效数据回表。
• 优势：
  ⬇️ 减少回表次数，降低 I/O 开销。

---

5. 回表（Bookmark Lookup）

• 定义：通过二级索引找到主键后，再查聚簇索引取完整数据。
• 流程（非聚簇索引查询）：
  1. 二级索引树 → 查得主键值 PK
  2. 用 PK 查聚簇索引 → 获取完整行数据
• 性能问题：
  🚨 两次索引搜索 → I/O 开销翻倍（避免回表是优化关键）。

---

概念间关联总结

技术	核心目标	依赖关系
联合索引	加速多列组合查询	是覆盖索引/索引下推的基础
覆盖索引	避免回表	依赖联合索引的列覆盖查询字段
最左前缀原则	确保联合索引有效使用	约束联合索引的查询方式
索引下推	减少回表次数	依赖联合索引的列完成提前过滤
回表	二级索引取完整数据	覆盖索引/索引下推可减少其发生

---

实战优化建议

1. 设计联合索引时：
   • 高频查询列放左侧（满足最左前缀）
   • 尽量包含 SELECT 所需列（实现覆盖索引）
2. 避免回表：
   • 用 EXPLAIN 查看 Extra 字段：
     • Using index → 覆盖索引生效
     • Using index condition → 索引下推生效
3. 索引下推开关：
   MySQL 默认启用，可通过 SET optimizer_switch='index_condition_pushdown=on|off'; 控制。

---

通过合理利用这些机制，可显著减少磁盘 I/O 和 CPU 计算量，尤其对大数据量表性能提升明显。