以下是导致索引失效的常见情况，分类并举例说明：
1. 对索引列进行运算或函数操作
当在索引列上使用函数、表达式、计算或类型转换时，MySQL无法直接使用索引来定位数据。
失效示例：
-- 使用函数
SELECT * FROM users WHERE YEAR(create_time) = 2023;
-- 使用表达式
SELECT * FROM products WHERE price * 2 > 100;
-- 进行运算
SELECT * FROM employees WHERE salary + 1000 > 5000;
优化方案：
将运算或函数操作转移到常量一侧。
-- 优化后：在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：需要检查所有不在列表中的值，效率低下。
失效示例：
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（无索引）两个字段。
SELECT * FROM table WHERE a = 1 OR b = 2;
因为 b 列没有索引，MySQL必须读取所有行来检查 b=2 的条件，所以它也会放弃使用 a 列的索引。
优化方案：
-
为
b列也添加索引。 -
使用
UNION或UNION ALL将查询拆分，确保每个部分都能利用索引。SELECT * FROM table WHERE a = 1 UNION ALL SELECT * FROM table WHERE b = 2;（注意：此方案的前提是
b=2的结果集很小，或者最终也必须为b列创建索引）
4. 隐式类型转换
如果查询条件的数据类型与索引列的定义类型不一致，MySQL会进行隐式类型转换，这相当于在索引列上使用了函数，导致索引失效。
失效示例：
假设 user_id 字段是字符串类型（VARCHAR），但查询时使用了数字。
SELECT * FROM users WHERE user_id = 123456; -- 失效
MySQL需要将表中每行的 user_id 字符串转换成数字才能与 123456 比较。
优化方案：
确保类型匹配。
SELECT * FROM users WHERE user_id = '123456'; -- 有效
5. 违反最左前缀原则
这是针对联合索引（复合索引） 的经典陷阱。联合索引的顺序非常重要，它是从最左列开始有序的。
假设有一个联合索引 idx_first_last (first_name, last_name)。
有效示例（遵循最左前缀）：
SELECT * FROM employees WHERE first_name = 'Zhang'; -- 使用索引
SELECT * FROM employees WHERE first_name = 'Zhang' AND last_name = 'San'; -- 使用索引
失效示例（违反最左前缀）：
SELECT * FROM employees WHERE last_name = 'San'; -- 失效！跳过了 first_name
这就像打电话簿，电话簿是按（姓，名）联合排序的。如果你只知道名而不知道姓，是无法快速查找的。
6. 使用 LIKE 以通配符 % 开头
使用 LIKE 进行模糊查询时，如果通配符 % 出现在开头，索引会失效。
失效示例：
SELECT * FROM products WHERE name LIKE '%apple%'; -- 可能全表扫描
SELECT * FROM products WHERE name LIKE '%apple'; -- 失效
有效示例：
SELECT * FROM products WHERE name LIKE 'apple%'; -- 有效，使用索引范围扫描
因为 ‘apple%’ 是前缀，索引的有序性可以发挥作用。而 ‘%apple’ 不知道开头是什么，无法利用索引排序。
7. 索引列使用 IS NULL 或 IS NOT NULL
在某些情况下（特别是当表中允许为NULL的值非常多或非常少时），优化器可能选择全表扫描而不是索引扫描。
示例：
SELECT * FROM users WHERE phone_number IS NULL; -- 可能失效
优化器会评估使用索引的成本，如果NULL值很多，它可能觉得全表扫描更快。
8. 数据分布不均匀（优化器放弃索引）
当MySQL的优化器通过统计信息发现，使用索引查询需要回表的数据量非常大（例如超过表记录的20%-30%），它可能会认为全表扫描的成本比使用索引更低，从而放弃使用索引。
示例：
假设有一个 gender 字段，上面有索引，但值只有 ‘M‘ 和 ’F‘，且分布大致为50%/50%。
SELECT * FROM students WHERE gender = 'M'; -- 优化器可能选择全表扫描
因为需要回表获取一半的数据，不如直接顺序读整个表。