【MySQL】索引失效问题详解

目录

1. 最左前缀原则

2. 条件左边有函数或运算

3. 隐式类型转换

4. LIKE 模糊查询以 % 开头

5、MySQL 优化器选择全表扫描

⭐对 in 关键字特别说明⭐

（1）列表太大时，走全表扫描了

（2）隐式类型转换

（3）最左前缀原则

（4）NOT IN 导致索引失效的场景

---

        大家好，今天给大家总结一下索引失效的场景。有忽略的欢迎补充！

1. 最左前缀原则

如果索引有多个列，即联合索引，注意他们是有顺序的。指的是查询从索引的最做前列开始匹配，并且不调过索引中的列，一旦跳过了某个索引中的列，那么对这个索引后面的列进行查询就会失效。

（1）加入给name、status、address加了联合索引： 

（2）范围查询（如 >, <, BETWEEN）之后的列不会使用索引： 

2. 条件左边有函数或运算

原因：MySQL 无法利用索引中的值进行范围匹配。

-- 索引失效，因为对字段进行了函数处理
SELECT * FROM user WHERE YEAR(birthday) = 2020;-- 例2：name 字段的第三到第四个字符（即从 name 字段的第3个字符开始，取2个字符）等于 '科技'
SELECT * FROM user WHERE substring(name,3,2) = '科技';

 运算场景：

-- 也会失效：字段参与了运算
SELECT * FROM user WHERE age + 1 = 30;

3. 隐式类型转换

原因：由于在查询的时候，mysql的查询优化器，会自动的进行类型转换，造成索引失效

-- 如果 age 是 int 类型，'18' 是字符串，会触发类型转换
SELECT * FROM user WHERE age = '18';-- 字符串没有加引号，也会进行隐式类型转换;下面这条sql如果status是varchar类型则索引会失效
select * from tb_user where name = '小明' and status = 0

4. LIKE 模糊查询以 % 开头

-- 索引失效（以 % 开头）
SELECT * FROM user WHERE name LIKE '%abc';SELECT * FROM user WHERE name LIKE '%a%';-- 可以使用索引（不以 % 开头）
SELECT * FROM user WHERE name LIKE 'abc%';

5、MySQL 优化器选择全表扫描

即使索引存在，MySQL 优化器也可能选择全表扫描，常出现在以下情况：

• 表数据太少，扫描成本比走索引还低；
• 索引选择性太差（重复值多）；

示例：超大数据量分页

-- 查询十万页后的数据：
select * from t5 order by text limit 100000, 10;

采用这种SQL查询分页的话，从几百万数据中取出这10行数据的代价是非常大的，需要先排序查出前1000010条记录，然后抛弃前面1000000条。

使用explain分析执行情况来看，在大分页的情况下，MySQL没有走索引扫描，即使text字段已经加上了索引。

主要原因：MySQL在查询代价估算中，认为全表扫描方式比走索引扫描的方式效率更高的话，就会放弃索引，直接全表扫描。

解决：使用覆盖索引或者子查询

-- 使用覆盖索引，不需要回表查询
select id,text from t5 order by text limit 100000, 10;--使用子查询，不需要回表查询
select * from t5 where id>=(select id from t5 order by text limit 100000,1) limit 10

⭐对 in 关键字特别说明⭐

首先，IN 关键字在 MySQL 中 并不会必然导致索引失效，关键在于使用方式是否合理。

因为in关键字也会遵循上面所说的最左匹配原则、隐式类型转换、全表扫描的规定。

（1）列表太大时，走全表扫描了

-- 优化器判断走索引不划算，放弃索引
SELECT * FROM user WHERE id IN (1, 2, ..., 10000);-- 子查询返回结果量过大时，索引可能失效
SELECT * FROM user WHERE id IN (SELECT user_id FROM orders);

（2）隐式类型转换

-- id 是 int 类型，但 IN 列表里是字符串
SELECT * FROM user WHERE id IN ('1', '2', '3');

（3）最左前缀原则

-- 有索引 (a, b)，以下查询只对 a 有索引效果；因为 b 不是最左前缀，索引不会起效。
SELECT * FROM table WHERE b IN (1, 2, 3);

（4）NOT IN 导致索引失效的场景

SELECT * FROM user WHERE id NOT IN (SELECT user_id FROM orders);

• 如果 orders.user_id 里有 NULL，那么整个 NOT IN 条件都会为 FALSE 或 UNKNOWN，结果是 整张表都不符合条件，索引失效 + 结果错误；
• MySQL 在处理 NOT IN 时，会对每一行都判断是否在列表中，一旦有 NULL，逻辑就崩了。

 解决方法：确保子查询列无 NULL，或使用 NOT EXISTS 替代。