Mysql索引失效场景

林中林

以下是导致索引失效的常见情况，分类并举例说明：

1. 对索引列进行运算或函数操作

当在索引列上使用函数、表达式、计算或类型转换时，MySQL无法直接使用索引来定位数据。

失效示例：

sql

-- 使用函数
SELECT * FROM users WHERE YEAR(create_time) = 2023;
-- 使用表达式
SELECT * FROM products WHERE price * 2 > 100;
-- 进行运算
SELECT * FROM employees WHERE salary + 1000 > 5000;

优化方案：
将运算或函数操作转移到常量一侧。

sql

-- 优化后：在create_time上建立索引有效
SELECT * FROM users WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';
-- 优化后
SELECT * FROM products WHERE price > 50;

2. 使用 NOT LIKE, <>, NOT IN

这些否定操作符通常无法有效利用索引的结构进行快速查找。

• <> 或 !=：需要检查所有不等于该值的记录，本质上接近全表扫描。

• NOT LIKE：类似。

• NOT IN：需要检查所有不在列表中的值，效率低下。

失效示例：

sql

SELECT * FROM customers WHERE name NOT LIKE 'A%';
SELECT * FROM orders WHERE status <> 'shipped';
SELECT * FROM products WHERE category_id NOT IN (1, 2, 3);

优化方案：
考虑改写查询逻辑，或者有时使用 LEFT JOIN ... IS NULL 来替代 NOT IN。

3. 使用 OR 连接条件（并非所有情况）

如果 OR 连接的多个条件中，并非所有列都有索引，MySQL通常会放弃使用索引而进行全表扫描。

失效示例：
假设表有 a（有索引）和 b（无索引）两个字段。

sql

SELECT * FROM table WHERE a = 1 OR b = 2;

因为 b 列没有索引，MySQL必须读取所有行来检查 b=2 的条件，所以它也会放弃使用 a 列的索引。

优化方案：

• 为 b 列也添加索引。

• 使用 UNION 或 UNION ALL 将查询拆分，确保每个部分都能利用索引。

  sql

  SELECT * FROM table WHERE a = 1
  UNION ALL
  SELECT * FROM table WHERE b = 2;

  （注意：此方案的前提是 b=2 的结果集很小，或者最终也必须为 b 列创建索引）

4. 隐式类型转换

如果查询条件的数据类型与索引列的定义类型不一致，MySQL会进行隐式类型转换，这相当于在索引列上使用了函数，导致索引失效。

失效示例：
假设 user_id 字段是字符串类型（VARCHAR），但查询时使用了数字。

sql

SELECT * FROM users WHERE user_id = 123456; -- 失效

MySQL需要将表中每行的 user_id 字符串转换成数字才能与 123456 比较。

优化方案：
确保类型匹配。

sql

SELECT * FROM users WHERE user_id = '123456'; -- 有效

5. 违反最左前缀原则

这是针对联合索引（复合索引） 的经典陷阱。联合索引的顺序非常重要，它是从最左列开始有序的。

假设有一个联合索引 idx_first_last (first_name, last_name)。

有效示例（遵循最左前缀）：

sql

SELECT * FROM employees WHERE first_name = 'Zhang'; -- 使用索引
SELECT * FROM employees WHERE first_name = 'Zhang' AND last_name = 'San'; -- 使用索引

失效示例（违反最左前缀）：

sql

SELECT * FROM employees WHERE last_name = 'San'; -- 失效！跳过了 first_name

这就像打电话簿，电话簿是按（姓，名）联合排序的。如果你只知道名而不知道姓，是无法快速查找的。

6. 使用 LIKE 以通配符 % 开头

使用 LIKE 进行模糊查询时，如果通配符 % 出现在开头，索引会失效。

失效示例：

sql

SELECT * FROM products WHERE name LIKE '%apple%'; -- 可能全表扫描
SELECT * FROM products WHERE name LIKE '%apple'; -- 失效

有效示例：

sql

SELECT * FROM products WHERE name LIKE 'apple%'; -- 有效，使用索引范围扫描

因为 ‘apple%’ 是前缀，索引的有序性可以发挥作用。而 ‘%apple’ 不知道开头是什么，无法利用索引排序。

7. 索引列使用 IS NULL 或 IS NOT NULL

在某些情况下（特别是当表中允许为NULL的值非常多或非常少时），优化器可能选择全表扫描而不是索引扫描。

示例：

sql

SELECT * FROM users WHERE phone_number IS NULL; -- 可能失效

优化器会评估使用索引的成本，如果NULL值很多，它可能觉得全表扫描更快。

8. 数据分布不均匀（优化器放弃索引）

当MySQL的优化器通过统计信息发现，使用索引查询需要回表的数据量非常大（例如超过表记录的20%-30%），它可能会认为全表扫描的成本比使用索引更低，从而放弃使用索引。

示例：
假设有一个 gender 字段，上面有索引，但值只有 ‘M‘ 和 ’F‘，且分布大致为50%/50%。

sql

SELECT * FROM students WHERE gender = 'M'; -- 优化器可能选择全表扫描

因为需要回表获取一半的数据，不如直接顺序读整个表。

来源：https://www.cnblogs.com/ljc1212/p/19095027