Mysql索引失效场景

以下是导致索引失效的常见情况，分类并举例阐明：
1. 对索引枚举行运算或函数操纵

当在索引列上利用函数、表达式、盘算或范例转换时，MySQL无法直接利用索引来定位数据。
失效示例：
sql

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

复制代码

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

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

复制代码

2. 利用 NOT LIKE, , NOT IN

这些否定操纵符通常无法有用利用索引的布局举行快速查找。

•  或 !=：必要检查全部未便是该值的记录，本质上靠近全表扫描。
  
• NOT LIKE：雷同。
  
• NOT IN：必要检查全部不在列表中的值，服从低下。
  

失效示例：
sql

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

复制代码

优化方案：
考虑改写查询逻辑，大概偶尔利用 LEFT JOIN ... IS NULL 来替代 NOT IN。
3. 利用 OR 毗连条件（并非全部情况）

如果 OR 毗连的多个条件中，并非全部列都有索引，MySQL通常会放弃利用索引而举行全表扫描。
失效示例：
假设表有 a（有索引）和 b（无索引）两个字段。
sql

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

复制代码

由于 b 列没有索引，MySQL必须读取全部行来检查 b=2 的条件，以是它也会放弃利用 a 列的索引。
优化方案：

• 为 b 列也添加索引。
  
• 利用 UNION 或 UNION ALL 将查询拆分，确保每个部门都能利用索引。
  sql
  1. SELECT * FROM table WHERE a = 1
  2. UNION ALL
  3. SELECT * FROM table WHERE b = 2;

  复制代码
  （留意：此方案的前提是 b=2 的结果集很小，大概最终也必须为 b 列创建索引）
  

4. 隐式范例转换

如果查询条件的数据范例与索引列的界说范例不同等，MySQL会举行隐式范例转换，这相称于在索引列上利用了函数，导致索引失效。
失效示例：
假设 user_id 字段是字符串范例（VARCHAR），但查询时利用了数字。
sql

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

复制代码

MySQL必要将表中每行的 user_id 字符串转换成数字才气与 123456 比较。
优化方案：
确保范例匹配。
sql

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

复制代码

5. 违反最左前缀原则

这是针对团结索引（复合索引） 的经典陷阱。团结索引的次序非常告急，它是从最左列开始有序的。
假设有一个团结索引 idx_first_last (first_name, last_name)。
有用示例（依照最左前缀）：
sql

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

复制代码

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

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

复制代码

这就像打电话簿，电话簿是按（姓，名）团结排序的。如果你只知道名而不知道姓，是无法快速查找的。
6. 利用 LIKE 以通配符 % 开头

利用 LIKE 举行暗昧查询时，如果通配符 % 出现在开头，索引会失效。
失效示例：
sql

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

复制代码

有用示例：
sql

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

复制代码

由于 ‘apple%’ 是前缀，索引的有序性可以发挥作用。而 ‘%apple’ 不知道开头是什么，无法利用索引排序。
7. 索引列利用 IS NULL 或 IS NOT NULL

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

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

复制代码

优化器会评估利用索引的资本，如果NULL值很多，它大概觉得全表扫描更快。
8. 数据分布不匀称（优化器放弃索引）

当MySQL的优化器通过统计信息发现，利用索引查询必要回表的数据量非常大（比方凌驾表记录的20%-30%），它大概会以为全表扫描的资本比利用索引更低，从而放弃利用索引。
示例：
假设有一个 gender 字段，上面有索引，但值只有 ‘M‘ 和 ’F‘，且分布大抵为50%/50%。
sql

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

复制代码

由于必要回表获取一半的数据，不如直接次序读整个表。