MySQL联合索引最左前缀原则违反导致索引失效的典型SQL

当MySQL查询未能利用已创建的联合索引时，性能往往会急剧下降。理解并避免违反最左前缀原则，是优化查询的关键一步。

一、什么是联合索引与最左前缀原则？

假设我们为一张表创建了一个联合索引：(a, b, c)。

这意味着数据库会创建一个类似电话簿的结构，先按字段 a 排序，在 a 相同的情况下再按字段 b 排序，b 也相同时最后按字段 c 排序。

最左前缀原则指：查询条件必须从索引的最左列开始，并且不跳过中间的列，索引才能被有效使用。你可以把它想象成查字典，必须先确定第一个字（最左列），才能在后面的字（后续列）中快速定位。

以下面的索引和表为例：

-- 创建联合索引
CREATE INDEX idx_a_b_c ON my_table (a, b, c);

二、导致索引失效的典型SQL场景

以下所有示例均基于上述 (a, b, c) 的联合索引。

场景1：查询条件中缺少索引最左列

直接跳过索引的最左列，索引完全失效。

-- ❌ 失效：缺少最左列 `a` 的条件
SELECT * FROM my_table WHERE b = 2 AND c = 3;

解释：索引首先按 a 排序。如果查询不包含 a，数据库无法利用已排序的 a 列来快速定位，只能进行全表扫描。

场景2：查询条件未遵循索引列顺序

中间列缺失，导致后续列的索引无法使用。

-- ❌ 失效：跳过了中间列 `b`
SELECT * FROM my_table WHERE a = 1 AND c = 3;

解释：查询使用了 a 和 c，但跳过了 b。因为索引是 (a, b, c) 的顺序，在 a=1 确定后，数据在 b 上是有序的，但在 c 上并不是直接有序的（需要在 b 有序的基础上再去排序 c）。因此 c 列无法利用索引，只有 a 列能用上索引。

场景3：对索引列使用函数或运算

对索引列进行任何计算、函数调用或类型转换，都会使其“失效”。

-- ❌ 失效：对索引列 `a` 使用了函数
SELECT * FROM my_table WHERE YEAR(a) = 2023;

-- ❌ 失效：对索引列 `a` 进行了计算
SELECT * FROM my_table WHERE a + 1 = 10;

-- ❌ 失效：对索引列 `b` 进行了隐式类型转换（假设b是varchar，但用数字查询）
SELECT * FROM my_table WHERE a = 1 AND b = 2; -- 如果b是varchar，这里的2会导致转换

解释：索引存储的是列 a 的原始值。函数或运算改变了查询目标，数据库无法直接在B+树中查找“经过函数处理后的值”，必须取出原始值逐行计算判断。

正确做法：改写查询，将函数或运算应用到常量上。

-- ✅ 优化后：将计算移到常量侧
SELECT * FROM my_table WHERE a >= '2023-01-01' AND a < '2024-01-01'; -- 假设a是日期
SELECT * FROM my_table WHERE a = 9; -- a + 1 = 10 改写为 a = 10 - 1

场景4：范围查询后的列索引失效

索引列一旦使用范围查询（>, <, BETWEEN, LIKE 'abc%'），其右侧的列索引将失效。

-- ⚠️ 部分失效：`b` 列之后的 `c` 列索引失效
SELECT * FROM my_table WHERE a = 1 AND b > 10 AND c = 3;

解释：索引在 a=1 基础上，对于 b > 10 的数据，b 是有序的，但是 c 的顺序是在 b 确定的前提下才有序。b > 10 是一个范围，对于范围内每一个不同的 b 值，对应的 c 值顺序各不相同，因此无法利用 c 的索引进行快速定位。只有 a 和 b 两列能利用索引。

唯一例外：如果是 >= 和 <= 这种等价范围，后续列可能还能用（取决于优化器），但为了保险起见，应遵循此原则。

场景5：在索引列上使用 != 或 NOT IN

这些否定条件通常会让MySQL放弃使用索引。

-- ❌ 可能失效：使用不等于
SELECT * FROM my_table WHERE a != 1 AND b = 2;

解释：索引的价值在于快速定位。不等于条件（!=, <>, NOT IN）意味着需要排除大量数据，而通过索引排除远不如直接扫描可能更高效，优化器可能因此选择全表扫描。

场景6：OR 连接非索引列

OR 连接的条件中，只要有一个条件没有索引，整个查询的索引可能失效。

-- ❌ 失效：`d` 列没有索引，导致整个查询索引失效
SELECT * FROM my_table WHERE a = 1 OR d = 4;

解释：MySQL优化器可能认为分别使用索引扫描 a 列和全表扫描 d 列再合并结果的成本，还不如直接全表扫描。除非 d 列也有独立索引。

三、如何检查和优化

1. 使用 EXPLAIN 分析查询计划

   这是最重要的诊断工具。在你的SQL前加上 EXPLAIN。

   EXPLAIN SELECT * FROM my_table WHERE a = 1 AND c = 3;

   重点关注：

   • type 列：ref, range, index 通常表示用上了索引，ALL 表示全表扫描。
   • key 列：实际使用的索引名称。如果为 NULL，则没有使用索引。
   • key_len 列：使用的索引长度，可以推断出使用了联合索引中的前几列。
   • Extra 列：出现 Using index 表示覆盖索引，很好；出现 Using filesort 或 Using temporary 则可能需要优化。

2. 遵循的优化原则

   • 设计索引时：根据高频查询的 WHERE、ORDER BY、GROUP BY 子句中的字段顺序来创建联合索引。将选择性高的列放在前面。
   • 编写SQL时：确保查询条件能从联合索引的最左列开始连续匹配。
   • 覆盖索引：如果查询只需返回索引中的列，可以避免回表查询，极大提升性能。例如索引是 (a, b, c)，查询 SELECT a, b FROM ... WHERE a=1。

     -- ✅ 高效的覆盖索引查询
     SELECT a, b FROM my_table WHERE a = 1;

   • 避免索引列上的计算：将计算逻辑放在等号右边。
   • 注意隐式转换：确保查询条件的数据类型与索引列定义一致。

通过识别这些典型的“反模式”SQL，并运用 EXPLAIN 工具进行验证，你可以有效避免因违反最左前缀原则而导致的索引失效问题，从而编写出更高性能的数据库查询。