MySQL联合索引（复合索引）原理、最佳实践与避坑指南

联合索引的核心原理

联合索引是在两个或更多列上建立的索引，其结构类似于电话簿：先按姓氏排序，再在相同姓氏内按名字排序。这意味着联合索引必须遵循“最左前缀”原则，即查询条件必须从索引定义的最左侧列开始匹配。

假设创建了联合索引 (a, b, c)， MySQL 可以有效支持以下组合查询：

• a
• a, b
• a, b, c

但不支持仅基于 b、c 或 b, c 的查询优化。只有当最左侧字段作为常量引用时，索引才能充分发挥作用。

联合索引的创建语法

在建表时定义联合索引的语法如下，其中 KEY a(a,b,c) 表示在列 a、b、c 上建立名为 a 的复合索引：

create table test(
a int,
b int,
c int,
KEY a(a,b,c)
);

联合索引的设计原则

第1步：确定高频查询列的顺序

目的：确保常用查询能命中索引，减少回表操作。
动作：如果某个列经常单独被查询，它应作为联合索引的第一列。若业务需要同时对两列进行独立搜索，应考虑建立单独的索引或更复杂的联合索引结构。
注意：包含过多列的联合索引会占用大量内存存储键值，反而降低性能。建议只包含少数几个在 SELECT 查询中频繁使用的列。

第2步：理解主键索引的特殊性

包含多列的主键会自动以复合索引形式存在，列顺序严格遵循表定义中的物理顺序，而非 CREATE TABLE 语句中的指定顺序。在设计时需谨慎安排这些列的位置。

联合索引对 ORDER BY 的优化

联合索引不仅能优化 WHERE 条件，还能优化排序操作。但要求 ORDER BY 的列顺序和方向必须与索引定义完全一致或完全相反。

第3步：构建符合索引顺序的排序索引

入口：创建包含 ASC/DESC 定义的复合索引。
参数示例：CREATE INDEX idx_example ON table1 (col1 ASC, col2 DESC, col3 ASC);

可观察的完成状态：

• 场景1（完全匹配）：查询使用 ORDER BY col1 ASC, col2 DESC, col3 ASC，索引有效。
• 场景2（反向匹配）：查询使用 ORDER BY col1 DESC, col2 ASC, col3 DESC，索引依然有效。
• 场景3（无效匹配）：若查询使用 ORDER BY col1 ASC, col2 ASC...（中间列排序方向改变），索引将失效，无法优化排序。

WHERE 查询中的最左前缀应用

查询优化器在多列索引中优先选择最左面索引前缀。对于索引 (col1, col2, col3)：

有效的查询模式（使用前缀）：

SELECT * FROM tb WHERE col1 = val1;
SELECT * FROM tb WHERE col1 = val1 AND col2 = val2;
SELECT * FROM tb WHERE col1 = val1 AND col2 = val2 AND col3 = val3;

无效的查询模式（跳过前缀）：

-- 直接使用 col3，索引失效
SELECT * FROM tb WHERE col3 = val3;
-- 直接使用 col2，索引失效
SELECT * FROM tb WHERE col2 = val2;
-- 结合使用 col2 和 col3，但仍缺失 col1，索引失效
SELECT * FROM tb WHERE col2 = val2 AND col3 = val3;

LIKE 查询规则：若字符串不以通配符 % 开头（如 '车%'），则可使用索引；若以 % 开头（如 '%车' 或 '%车%'），则无法使用索引。

不同数据库引擎的性能差异

联合索引的效果高度依赖于底层存储引擎的类型（如 InnoDB 与 MyISAM）。以下以一个常见业务场景为例：

场景：获取某类别下的文章列表，按时间倒序排列。

SELECT id FROM articles WHERE category_id = ... ORDER BY created DESC LIMIT ...

这里假设 id 是主键，我们希望通过 Covering Index（覆盖索引）避免回表。

• InnoDB 引擎：二级索引的叶节点已隐含保存了主键值。因此，建立 (category_id, created) 联合索引即可满足覆盖索引需求，无需包含 id。
• MyISAM 引擎：二级索引不保存主键值。若要实现覆盖索引，必须建立 (category_id, created, id) 联合索引，显式包含主键。

此外，需关注 Cardinality（唯一值基数）。若索引列的唯一值比例低于总行数的 20%，索引可能对 SELECT 帮助有限，反而会增加 INSERT/UPDATE 的开销。同时，尽量使用紧凑型字符集（如 latin1 代替 utf8mb4）以减小索引体积并提升速度。

常见的索引失效陷阱与优化

即使建立了合适的索引，错误的查询写法也会导致全表扫描。以下是四大常见陷阱：

第4步：避开等于号以外的比较陷阱

问题：使用 <> 不等于操作符通常会导致全表扫描。
解决：若确需查询非此值的数据，可尝试拆分为范围查询。例如将 staff_num <> 1000 改写为 staff_num < 1000 OR staff_num > 1000，但这仍需验证实际执行计划，部分优化器可能仍选择全扫。

第5步：处理 NULL 值的限制

问题：大多数索引不包含 NULL 值，使用 IS NULL 或 IS NOT NULL 可能限制索引使用。
解决：在建表时将关键索引列定义为 NOT NULL，并提供默认值（如 0 或空字符串），从而充分利用索引。

第6步：避免在列上直接使用函数

问题：在索引列上使用函数会使优化器忽略索引。
案例：WHERE trunc(birthdate) = '01-MAY-82' 不会走索引。
解决：将函数作用于常量一侧，改为范围查询：WHERE birthdate <= '01-MAY-82' AND birthdate >= ...（具体根据语义转换为范围边界，例如原文建议： birthdate < (to_date('01-MAY-82') + 0.9999)）。

第7步：保持数据类型一致

问题：隐式类型转换会导致索引失效。例如 varchar 类型的字段 dept_id 如果传入整数，数据库会自动转换为 to_number(dept_id)，导致全表扫描。
解决：确保查询参数与字段类型一致。将 dept_id = 900198 改为 dept_id = '900198'。

总结

联合索引是提升 MySQL 查询性能的关键利器，但其有效性建立在严谨的设计之上。牢记最左前缀原则，根据存储引擎特性调整覆盖索引策略，并规避函数计算和类型不匹配的陷阱，才能确保每一次查询都能高效命中索引。以上就是 MySQL 联合索引学习教程的详细内容，更多关于 SQL 索引优化的资料请关注本站其它相关文章！