谈谈MYSQL索引失效场景

​

目录

前言

隐式类型转换

索引列上使用函数

非最左匹配

错误模糊查询 

IS NULL不走索引，IS NOT NULL走索引

OR引起的索引失效 

不等于(!= 或者<>)索引失效 

​编辑

前言

MYSQL中索引是经常用来对数据库查询性能优化的方式，再MySQL中采用了B+树作为索引结构来减少磁盘IO次数去提高数据的检索性能。但是在某些场景下，由于查询语句设计不合理，或者对MySQL的理解不够深入。索引有可能会失效，变为全表扫描，这对于大数据量的查询是非常低效的。今天我们就来聊聊这些常见的失效场景。

MySQL中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了访问高效数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。

• 使用索引可以快速地定位表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。
• 如果查询时没有使用索引，查询语句就会扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。

大多数情况下都（默认）采用B+树来构建索引。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引。

其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器？基于cost开销(CostBaseOptimizer)，它不是基于规则(Rule-BasedOptimizer)，也不是基于语义。怎么样开销小就怎么来。另外，SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

当Mysql使用索引的要扫描行记录数超过全表的10%-30%时，优化器可能会放弃走索引。

隐式类型转换

隐式类型转换会导致索引失效，比如当查询条件类型为数值时，将字符串类型转换为浮点型可能会将索引数据无效。解决方式是统一设置字段类型。

select * from table_name t1 left join table_name2 t2 on t1.id=t2.tid;

如果 t1 表的id 类型和 t2 表的tid 类型不一致的时候，就无法按索引执行

索引列上使用函数

索引列上使用函数是因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，所以无法使用索引。

SELECT * FROM `user` WHERE DATE(create_time) = '2023-11-29';

非最左匹配

非最左匹配指的是查询不满足最左前缀原则中的最左边的匹配要求，即查询字段不能包含联合索引中的所有索引字段。最左前缀原则是MySQL中的最佳左前缀原则，通过使用联合索引可以避免最左边的匹配问题。因此，如果查询字段包含联合索引，则应优先选择使用最左前缀原则。

例如有这样一个组合索引 index(a,b,c)

select * from table_name where b='1'and c='2'
select * from table_name where c='2'

最左原则，就是要最左边的优先存在，如果不存在的话，你们自己就玩不动了，除非你自己单独创立一个索引，下面这几条 SQL 就可以走索引执行 ：

select * from table_name where a = 'asaa' and b='1'and c='2'
select * from table_name where a = 'asda' and b='1231' 

查询过滤条件的字段，必须顺序的包含索引中的字段，一旦跳过某个字段，则索引后面的字段就会失效。如果过滤条件中没有使用联合索引中的第一个字段，则这个索引不会被使用到。 

错误模糊查询 

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为’%‘，索引就不会起作用。只有’%'不在第一个位置，索引才会起作用。

select id,name,age,salary from table_name where name like '%lucs%';

IS NULL不走索引，IS NOT NULL走索引

索引不存储null值，可以避免对多列索引进行null值判断，确保表中没有null值，并保证查询的准确性 。索引无法存储null值，所以where的判断条件如果对字段进行了null值判断，将导致数据库放弃索引而进行全表查询，如 

SELECT * FROM `user` WHERE address IS NULL

不走索引 

SELECT * FROM `user` WHERE address IS NOT NULL;

走索引 

如果没有必要的要求必须为NULL，那么最好给个默认值空字符串

为什么索引列无法存储Null值？

索引是有序的。NULL值进入索引时，无法确定其应该放在哪里。（将索引列值进行建树，其中必然涉及到诸多的比较操作，null 值是不确定值无法比较，无法确定null出现在索引树的叶子节点位置。）

OR引起的索引失效 

使用or操作符会导致MySQL无法使用索引，因为索引是根据某个字段进行排序建立的，当使用or操作符时，只有满足其中一个条件才能成立，否则该条件都不成立，记录的索引也会失效。所以，尽量避免使用or操作符。

SELECT * FROM `user` WHERE `name` = '张三' OR height = '175';

在查询过滤条件中，OR的前面或者后面的列不是所有，那么导致整个where过滤条件的所有索引失效。OR的前面和后面的列必须是索引列，才能生效。因为OR就是必须前面和后面的条件都满足，才能是全满足。

不等于(!= 或者<>)索引失效 

name字段建立了索引，但是如果!= 或者 <> 这种都会导致索引失效，进行全表扫描，所以如果数据量大的话，谨慎使用

SELECT * FROM `user` WHERE `name` != 'zs';

​