MySQL多列索引查询优化

1. 单列索引的使用逻辑

如果 a、b、c 是三个独立的单列索引：

• 优化器通常只会选择其中一个索引（通常是选择性最高的索引）进行扫描，然后通过回表（回主键索引）获取完整数据行，再应用剩余条件（b 和 c）进行过滤。
• 示例：假设 a 的选择性最高（即不同值更多），优化器可能选择 a 的索引，找到所有满足 a 条件的行，再逐行检查 b 和 c 是否符合条件。
• 执行计划：通过 EXPLAIN 查看时，key 列会显示实际使用的索引（如 a），Extra 列可能显示 Using where，表示回表后需过滤其他条件。

2. 索引合并（Index Merge）

在特定情况下，MySQL 可能使用 Index Merge Optimization，即同时使用多个索引的交集（AND 条件）或并集（OR 条件）：

• 触发条件：需要满足 WHERE 中的多个条件可以分别通过不同索引高效过滤。
• 示例：若优化器认为同时使用 a 和 b 的索引更高效，可能通过 index_merge_intersection 合并结果，再过滤 c。
• 执行计划：type 列显示 index_merge，key 列显示多个索引（如 a,b），Extra 列显示 Using intersect(a,b); Using where。

3. 联合索引（最推荐的方式）

如果经常需要同时查询 a、b、c，最佳实践是创建联合索引：

CREATE INDEX idx_a_b_c ON table_name(a, b, c);

• 执行逻辑：通过联合索引直接按最左前缀（a → b → c）快速定位数据，无需回表（如果查询仅需索引覆盖的列）。
• 优势：效率远高于单列索引或索引合并，因为索引本身已包含所有条件字段，且顺序符合最左前缀原则。

验证方法：使用 EXPLAIN

通过 EXPLAIN 查看实际执行计划：

EXPLAIN SELECT * FROM table_name WHERE a = 1 AND b = 2 AND c = 3;

• 关键字段解读：
  • type：访问类型（如 ref、index_merge）。
  • key：实际使用的索引。
  • Extra：额外信息（如 Using where 表示回表后过滤）。

总结

• 单列索引场景：通常只用一个索引，其他条件回表过滤。
• 索引合并场景：可能用多个索引，但需满足优化器策略。
• 最佳实践：优先使用联合索引 (a, b, c)，效率最高。

建议根据实际查询模式设计联合索引，并通过 EXPLAIN 验证优化器的选择。