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

一个搬砖的农民工

已于 2024-06-15 21:51:16 修改

阅读量5.8k

点赞数 4

分类专栏：关系型数据库文章标签： mysql 数据库 sql 数据库索引索引失效

于 2022-08-03 15:58:40 首次发布

本文链接：https://blog.csdn.net/weixin_44183847/article/details/125441331

版权

关系型数据库专栏收录该内容

24 篇文章 2 订阅

订阅专栏

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和索引还包含了其他字段（非索引覆盖）

主键一定走索引
其他范围查询 >, >=, <, <=, 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都一样效果，区别无非是我上面说的相同数据匹配一次或多次区别！

其实大表套小表主要是针对join联合查询，大表作为驱动表会占用很大内存临时空间，可能会导致OOM，可以参考：https://www.cnblogs.com/caoxb/p/15526123.html
在这里插入图片描述