【MySQL】索引失效问题详解-CSDN博客

-- 索引失效，因为对字段进行了函数处理
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 替代。