MySQL—索引失效

一、 条件字段使用函数

select count(*) from tradelog where ABS(a)=7;

如果对字段做了函数计算，就用不上索引了，这是MySQL的规定。

为什么会失效呢？

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

1.1 隐式类型转换

假设id类型为varchar(10)，且建立了索引

select * from tradelog where id=110717;

上面这个SQL语句不会走索引而是全表扫描，因为id类型为varchar(10)，而输入的参数类型为整型，所以需要做类型转换

数据类型转换的规则是什么？

在MySQL中，字符串和数字做比较的话，是将字符串转换成数字。

为什么有数据类型转换，就需要走全索引扫描？

对于优化起来说，上面这条SQL语句相当于下面这条SQL

select * from tradelog where  CAST(tradid AS signed int) = 110717;

其实可以看出来，隐式类型转换造成的索引失效本质上也是条件字段使用了函数

1.2 隐式字符编码转换

select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value; 

隐式字符编码转换本质上也是条件字段使用了函数

说明：下面这种情况就会走索引，因为函数是加在参数上的，而不是加在字段上的

select operator from tradelog  where traideid =CONVERT($R4.tradeid.value USING utf8mb4); 

二、 like查询是以%开头

select * from tradelog where name='%张';

三、索引列使用了表达式计算

select * from tradelog where score+1 > 80;

四、使用不等于(!= 或者<>)的时候

select * from tradelog where name != '张三'; 

五、使用is not null 或者 is null

select * from tradelog where name is not null; 

六、条件带有or

mysql中，如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

七、如果mysql使用全表扫描要比使用索引快,则不会使用到索引

mysql查询单表时，查询得到的结果集占数据总量很大比例，mysql会认为全表扫描会优于索引，则不走索引。

八、mysql中，字符串不加单引号索引会失效

九、不能使用索引中范围条件右边的列，范围之后索引失效。

比如根据 (name，age，birthday)三个列建立了索引，这时候如果有以下SQL语句

select * from tradelog where age = 20; 

这时就无法使用索引

select * from tradelog where name = '张三'; 

而这个SQL就可以使用索引，因为符合最左匹配原则