MySQL14-为什么SQL语句逻辑相同，性能却差异巨大？

案例一：条件字段函数操作

不要对字段进行函数计算操作。比如下面的就不行

select * from tradelog where id + 1 = 10000;
select count(*) from tradelog where month(t_modified)=7;

为什么不能对条件字段做计算？–>无法用到B+树的快速定位能力
因为如果对字段做了函数计算， 就用不上索引了， 这是MySQL的规定。而我们上面的id和t_modified都有索引，但是显然我们都对它们进行了操作，这样就无法用到B+树的快速定位能力，也就是说对对索引字段做函数操作， 可能会破坏索引值的有序性， 因此优化器就决定放弃走树搜索功能。所以等SQL语句执行了很久才会返回结果。

select count(*) from tradelog where month(t_modified)=7;

在这个语句中，放弃了树搜索功能， 优化器可以选择遍历主键索引， 也可以选择遍历索引t_modified， 优化器对比索引大小后发现， 索引t_modified更小， 遍历这个索引比遍历主键索引来得更快。 因此最终还是会选择索引t_modified。

我们使用explain命令， 查看一下这条SQL语句的执行结果。

key="t_modified"表示的是， 使用了t_modified这个索引； 我在测试表数据中插入了10万行数据， rows=100335， 说明这条语句扫描了整个索引的所有值； Extra字段的Using index， 表示的是使用了覆盖索引。
也就是说， 由于在t_modified字段加了month()函数操作， 导致了全索引扫描导致执行时间过长。

如何运用B+树的快速定位能力?
为了能够用上索引的快速定位能力， 我们就要把SQL语句改成基于字段本身的范围查询。

mysql> select count(*) from tradelog where
-> (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
-> (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or
-> (t_modified >= '2018-7-1' and t_modified<'2018-8-1');

案例二：隐式类型转换

数据类型与字段类型不同的，将导致全表扫描。
数据类型转换的规则是什么？
数据库中数据类型很多，涉及到的类型转换也很多，比如字符串和数据之间的比较，需要将字符串转换成数字才能比较，比如：

mysql> select * from tradelog where tradeid=110717;

tradeid的字段类型是varchar(32)， 而输入的参数却是整型， 所以需要做类型转换。
交易编号tradeid这个字段上， 本来就有索引， 但是explain的结果却显示， 这条语句需要走全表扫描。

为什么有数据类型转换， 就需要走全索引扫描？
我们知道了数据类型转换，在MySQL中， 字符串和数字做比较的话， 是将字符串转换成数字。

mysql> select * from tradelog where tradeid=110717;

那么上面这条语句对优化器而言相当于

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

这条语句触发了我们上面说到的规则： 对索引字段做函数操作， 优化器会放弃走树搜索功能，所以只能走全表查询。

案例三：隐式字符编码转换

两张表编码格式不一致也会导致全表查询。
什么是字符编码？
所谓的字符编码就是两个表选择的编码字符集不同，比如一个表选择utf-8，另一个表选择utf8mb4，当表连接查询的时候用不上关联字段的索引，先把utf8字符串转成utf8mb4字符集， 再做比较。 所以会导致执行的速度很慢。

比如这条语句

mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

我们一起来看下这个结果：

1. 第一行显示优化器会先在交易记录表tradelog上查到id=2的行， 这个步骤用上了主键索引， rows=1表示只扫描一行；
2. 第二行key=NULL， 表示没有用上交易详情表trade_detail上的tradeid索引， 进行了全表扫描。

了解它的执行过程

1. 是根据id在tradelog表里找到L2这一行；
2. 是从L2中取出tradeid字段的值；
3. 是根据tradeid值到trade_detail表中查找条件匹配的行。 explain的结果里面第二行的key=NULL表示的就是， 这个过程是通过遍历主键索引（全表扫描） 的方式， 一个一个地判断tradeid的值是否匹配

你会发现第3步不符合我们的预期。 因为表trade_detail里tradeid字段上是有索引的， 我们本来是希望通过使用tradeid索引能够快速定位到等值的行。 但， 这里并没有。

有什么解决方法呢？
连表查询的时候两个连接字段使用join操作。便可使用tradeid索引。