导致mysql索引失效的原因有哪些

article/2025/9/30 0:46:02

MySQL五种索引类型

sql执行顺序

（根据sql执行顺序来优化，可以把关联表判断条件放到on后面）
from  
on  
join  
where  
group by  
having  
select  
distinct  
union  
order by   
limit

一、哪些字段适合建索引

在这里插入图片描述

二、范围查询索引失效问题

范围查询索引失效，只针对二级索引（除了主键，其他索引都是二级索引）
(1)、如果走二级索引查询，步骤就是：二级索引–>主键索引–>数据，这就是回表查询（mysql索引中关联的主键，而不是数据，所以二级索引查询会有一个回表的操作）。
(2)、硬盘随机I/O的性能远低于顺序I/O。

1、范围查询失效问题

注意，放弃索引走全表查询需要满足以下几点：
1、二级索引
2、范围查询
3、查询数量超过了总数据的某个百分比
4、并且查询字段不止id和索引还包含了其他字段（select后面字段不仅仅为id和where后端索引字段）

主键一定走索引
其他范围查询 >, >=, <, <=, like, !=, <>, is null, is not null虽然可以走索引，但是当查询数量超过了总数据的某个百分比，并且为二级索引，并且查询字段不止id和索引还包含了其他字段也会走全表查询，因为mysql的sql优化器会认为回表查询的随机I/O代价大于顺序I/O全表扫的情况下，将放弃走索引。
如下这个sql是不走索引的，并且查询时间为7.337s，数据量为3557003

如果我们强制他走索引，这时候去执行sql时，查询时间用了39.680s，数据量依然为3557003

由上可见这里不用索引查询更快，因为通过索引查询会有一个回表操作

建议索引 select * from 表名 use index(索引名) where …
强制索引 select * from 表名 force index(索引名) where …
忽略索引 select * from 表名 ignore index(索引名) where …

2、左前缀原则

使用联合索引(id,name,age)查询数据,判断条件需遵循最左原则

3、函数导致索引失效

SELECT * FROM user WHERE DATE(create_time) = ‘2020-09-03’;
create_time字段设置索引，那就无法使用函数，否则索引失效，因为函数会破坏索引有序性，sql优化器会放弃走索引。

4、字段类型不一致索引失效

SELECT * FROM user WHERE height= 175;
height为varchar类型导致索引失效，尤其多张表时注意。

5、运算符导致索引失效

如果你对列进行了（+，-，*，/，!）, 那么都将不会走索引。

SELECT * FROM user WHERE age - 1 = 20;

6、or引起索引失效

or两边必须都是索引，否则用or连接的左右语句会索引失效

例：（counter1 和 counter2是索引，counter5不是索引字段）

在这里插入图片描述

7、模糊查询导致索引失效

SELECT * FROM user WHERE name LIKE ‘%冰’;
模糊搜索如果你前缀也进行模糊搜索，那么不会走索引。

8、IN、NOT IN、 EXISTS、NOT EXISTS导致索引失效

和模糊查询类似，当数据量超过某个百分比后会放弃索引，通常情况下在这些条件查询的数据量不大还是会走索引的
NOT IN 和 NOT EXISTS 区别，not in会过滤掉null的数据，而exists不会，所以在用not in时要注意

在这里插入图片描述

注意：
exists 只检查存在性，出现多个一样的他只匹配一次
in 后面出现多个一样的他是会每个匹配一次
所以，可以的话尽量用exists

网上说的大表套小表用in，小表套大表用exists，因为exists后面会关联查询多次，经验证不存在这种问题情况，从下面执行计划中可以看出是先执行 SELECT 1 from om_obd_unbinding c WHERE required_order_code = 'OBD202210311122456538422' 因为这个sql查询的数据量刚好是1，然后在结果集的临时表中去获取最终结果。这种情况用in或是exists都一样效果，区别无非是我上面说的相同数据匹配一次或多次区别！
在这里插入图片描述