sql语句索引相关性能调优

准则：要使用索引就不能破坏索引的原有顺序

情况一：显示函数操作

对索引字段进行函数操作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函数，导致无法走索引树。

解决办法：

1. 如果要走索引的表为子集就将其转化成超集（utf8 — utfbmb4）;
2. 主动将超集的编码类型降低（utf8mb4 — utf8），可能会导致精度降低.

参考文章：

https://time.geekbang.org/column/article/74059