MySQL 索引失效的场景与原因

在 MySQL 中，索引是优化查询性能的重要手段，但在某些情况下，即使创建了索引，也可能无法生效，导致全表扫描，从而影响查询性能。下面详细介绍常见的 索引失效场景 及原因。

1. 使用左模糊或全模糊匹配

场景：
当使用 LIKE 关键字进行模糊匹配，并且以 % 开头 或者左右都有 % 时，索引会失效。例如：

-- 索引列 name
SELECT * FROM user WHERE name LIKE '%abc';    -- 左模糊匹配
SELECT * FROM user WHERE name LIKE '%abc%';   -- 全模糊匹配

原因：
索引底层通常采用 B+ 树 存储，数据按照索引值的前缀有序排列。如果 LIKE 模式以 % 开头，MySQL 无法确定从哪个位置开始匹配，只能进行全表扫描。

正确写法（右模糊匹配可以使用索引）：

SELECT * FROM user WHERE name LIKE 'abc%';

2. 对索引列使用函数

场景：
当查询条件中对索引列使用了函数（如 DATE(), UPPER(), SUBSTRING() 等），索引会失效。例如：

-- 对索引列 create_time 使用 DATE() 函数
SELECT * FROM orders WHERE DATE(create_time) = '2024-08-01';

原因：
索引存储的是字段的原始值，而不是经过函数计算后的值。如果查询条件中包含函数，MySQL 无法直接利用索引，需要先计算再比较，因此会全表扫描。

** 正确写法：**

-- 避免对列使用函数，可以调整为范围查询
SELECT * FROM orders WHERE create_time >= '2024-08-01' AND create_time < '2024-08-02';

3. 对索引列进行表达式计算

场景：
当在查询条件中对索引列进行数学计算或其他表达式操作时，索引同样会失效。例如：

-- 对 price 进行了表达式计算
SELECT * FROM products WHERE price * 2 > 200;

原因：
同样是因为索引是基于列的原始值建立的，对列进行计算会导致 MySQL 无法直接通过索引定位数据，只能回退到全表扫描。

** 正确写法：**

SELECT * FROM products WHERE price > 100;

4. 隐式类型转换导致索引失效

场景：
当查询条件与索引列类型不一致时，MySQL 会进行隐式类型转换，可能导致索引失效。例如：

-- id 字段类型是 VARCHAR
SELECT * FROM user WHERE id = 1000;  -- 隐式转换

原因：
当字段是字符串类型（VARCHAR），而条件值是数字类型，MySQL 会将索引列通过 CAST() 转换成数字再比较。由于转换发生在列上，等价于对索引列使用函数，导致索引失效。

注意：

• 如果字段是 字符串，条件值是数字 → 会失效。
• 如果字段是 整型，条件值是字符串（如 '1'）→ 不会失效，MySQL 会将字符串转换为数字，不影响索引。

** 正确写法：**
保持类型一致：

SELECT * FROM user WHERE id = '1000';  -- 如果字段是 VARCHAR

5. 联合索引未遵循最左匹配原则

场景：
联合索引（例如 (a, b, c)）的使用必须遵循 最左匹配原则，即查询条件必须从最左边的列开始，且不能跳过中间列，否则后续列无法使用索引。例如：

-- 联合索引 (a, b, c)
SELECT * FROM table WHERE b = 1 AND c = 2;  --  跳过 a

原因：
联合索引本质是按最左列排序，再按次列排序。如果跳过最左列，MySQL 无法利用 B+ 树进行有序查找，只能全表扫描。

** 正确写法：**

SELECT * FROM table WHERE a = 1 AND b = 1;          -- ✔ 可使用索引
SELECT * FROM table WHERE a = 1 AND c = 2;          -- ✔ a 可以用索引，c 可能回表

注意：
范围查询（>, <, BETWEEN）会影响联合索引的使用：

SELECT * FROM table WHERE a = 1 AND b > 10 AND c = 2;
-- a 和 b 可以使用索引，但 c 不能，因为 b 是范围条件。

6. WHERE 子句中 OR 条件导致索引失效

场景：
当 WHERE 子句中使用 OR，且 OR 左右条件不完全都是索引列 时，索引会失效。例如：

SELECT * FROM user WHERE id = 1 OR name = 'Tom';

假设：

• id 是索引列。
• name 没有索引。

原因：
OR 条件表示满足任一条件即可。如果只有一边是索引列，另一边不是，MySQL 无法仅通过索引完成匹配，只能做全表扫描。

** 解决方法：**

• 给 OR 条件中所有字段建立索引。
• 或者改用 UNION 拆分查询：

SELECT * FROM user WHERE id = 1
UNION
SELECT * FROM user WHERE name = 'Tom';

总结表

高频面试问题：

• 索引什么时候会失效？为什么？
• 如何优化 OR 查询以使用索引？
• 为什么左模糊匹配会失效？
• 联合索引的最左匹配原则是什么？