准则:要使用索引就不能破坏索引的原有顺序
情况一:显示函数操作
对索引字段进行函数操作function(index)可能会破坏原有索引字段的有序性,为了方便起见,MySQL不会判断使用的函数是否有改变索引顺序(偷懒了),而是默认只要使用了函数就改变了原索引的顺序,所以以下sql语句不会走索引,
mysql> select * from table where id + 1 = 10000;
此处id上有索引,但是它是函数中的变量,所以不走索引树(即使没有破坏原索引字段的顺序),可以改成如下语句:
mysql> select * from table where id = 10000 - 1;
也就是说不能对索引字段进行函数操作,但是等号右侧可以为函数。
情况二:隐式类型转换(隐式使用函数)
就是将一个字段的类型转换成另一种类型,也就是对一个字段进行数据类型转换的函数操作,如果索引被转换了,就相当于对索引字段进行了函数操作,所以会破坏索引的原有顺序。
MySQL中字符串与数字比较时会将字符串转换成数字,如下语句,表中id字段类型为varchar
mysql> select * from T where id=100;
执行该sql时MySQL会将id转换成数值型变量,相当于执行sql语句:
mysql> select * from T where CAST(id AS signed int) = 100;
其中CAST就对id索引字段进行了函数操作,因此无法再使用索引树搜索。因此索引字段类型如果是数值型,等号右侧不能是字符串,索引字段类型是字符型等号右侧就可以是字符串或者数值型。
情况三:隐式字符编码转换(隐式使用函数)
补充知识:
多表联查时先查询出某张表a的字段值,根据这些字段值去剩下的表b中查数据,那么表a称为“驱动表”,表b称为“被驱动表”。
有时候要多表联查,如果每张表用的编码类型不同,比较其中的字段值时就要进行编码类型的转换(子集向超集转换,也就是为了防止数据长度大的值被截断,都是将长度短的数据类型转换成长度长的类型)如以下sql语句:
mysql> select * from t1, t2 where t2.num = t1.num and t1.id = 2;
如果t1编码类型是utf8,t2是utf8mb4(utf8的超集),那么以上语句先在t1表中查到id=2的所有t1.num字段值,然后再查找t2表中num字段等于这些t1.num值的记录(此时思考,如果要用t1的num字段上的索引的话,t1.num就不能进行函数操作,但是t1由于是子集utf8,跟编码类型为utf8mb4的t2比较时就会被强转),
分离以上sql语句的第二阶段也就是执行以下sql语句:
mysql> select * from t2 where num = t1.num;
底层等价于调用函数执行以下sql语句:
mysql> select * from t2 where CONVERT(num USING utf8mb4)=t1.num;
可见MySQL底层在索引字段上使用了CONVERT函数,导致无法走索引树。
解决办法:
- 如果要走索引的表为子集就将其转化成超集(utf8 — utfbmb4);
- 主动将超集的编码类型降低(utf8mb4 — utf8),可能会导致精度降低.