为什么这些sql语句逻辑相同，性能却差异巨大读后总结

雨点打透心脏的1/2处 2024-04-17 05:24 36阅读 0赞

1.条件字段函数操作  
例子有一张日志表，查询 指定年限7月的记录总数  
mysql> CREATE TABLE `tradelog` (  
`id` int(11) NOT NULL,  
`tradeid` varchar(32) DEFAULT NULL,  
`operator` int(11) DEFAULT NULL,  
`t_modified` datetime DEFAULT NULL,  
PRIMARY KEY (`id`),  
KEY `tradeid` (`tradeid`),  
KEY `t_modified` (`t_modified`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;  
mysql> select count(*) from tradelog where month(t\_modified)=7;  
我们在sql中条件字段调用了month函数  
对索引字段做函数操作，可能会破坏索引值的有序性。因此优化器决定放弃走树搜索功能  
如果不用month函数直接将范围写出来就可以利用索引  
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’);  
优化器在这个函数调用操作有偷懒的行为，即使是不改变顺序性的函数，也不会考虑使用索引。  
比如select \* from tradelog where id+1=10000,还是会走全表扫描

2.隐式类型转换  
mysql> select \* from tradelog where tradeid=110717;  
看这条sql，tradeid是varchar类型的，但是比较的条件是int类型的  
从而走了进行了隐式类型转换。使得优化器放弃了索引进行了全索引扫描。  
这里给一个较好判断的sql来判断隐式转换的规则  
select “10”>9；的输出来判断  
对于优化器来说改sql相当于  
mysql> select \* from tradelog where CAST(tradid AS signed int) = 110717;

3.隐式字符编码转换  
连表查询时，两张表的charset不同也会发生转换  
例如utf8mb4是utf8超集，这两个类型的字符串在做比较的时候，mysql内部会先将utf8转为utf8mb4，本质也是对条件字段进行函数操作。