当前位置：首页 > article >正文

《MYSQL实战45讲》为什么使用聚合函数会导致索引失效

article 2024/10/28 11:19:54

案例一：使用聚合函数导致索引失效

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

尽管t_modified列上面有索引，但是使用聚合函数如mouth()就会导致索引失效，原因是通过索引快速找到元素是因为索引B+树上，同一层的节点是有序的，而如果使用了聚合函数，就破坏了索引值的有序性，比如这个例子，在t_modified列上有索引，同一层可能是这样的：2022-07-01,2023-08-01 ,2024-06-01,是有序的，但是加上了mouth聚合函数时，就是7,8,6，就不再是有序的了，所以优化器对于使用了聚合函数的语句会放弃使用索引树来搜索，但是只是放弃使用索引树来搜索，而不是不用索引了。

遍历索引树仍然比遍历全表来得快，索引这个语句会使用索引

假如希望使用索引，就不要同聚合函数，如:


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类型的，但是这里参数用的是数字。

此时MYSQL会将每条记录的tradeid的值转换成数字再和参数110717比较，这样就会导致索引失效，和案例一本质是一样的，因为相当于先用了函数将记录的值进行一次类型转换，会破坏掉索引树的有序性。

但是如果查询语句是这样的，id是整数，而条件参数使用了字符串

select * from tradelog where id="83126";

这个语句会先把条件参数"83126"转换成数字，再去主键索引树上查找，使用了主键索引。

案例三：字符编码类型转换

本质还是和案例一一样：对条件参数使用函数就可能破坏索引树的有序性，优化器不会选择索引树来搜索。

utf8mb4是utf8的超集，将utf8类型字段值和utf8mb4比较时，会把utf8转换成utf8mb4才能继续比较（因为如果反过来转换有可能导致数据的截断，变得不完整导致错误）

看案例：

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

先在tradelog表中找到id为2的记录，然后取出记录的tradeid,再通过trade_detail表的tradeid索引来找到tradeid相同的行放入结果集。所以只需要扫描1+4=5行。

但是使用执行计划分析后发现扫描了1+11=12行：

第一行的执行计划是在tradelog表中找到id为2的记录，因为有主键索引，所以只需要扫描1行

第二行的执行计划就是找出trade_detail表中tradeid与前面查到的这个tradeid相同的记录了，trade_detail表的tradeid有索引，所以只需要先搜索到第一个tradeid符合条件的记录，一直到不符合条件就结束，应该是扫描4行，但是却做了全表扫描，为什么？

这是因为trade_detail表的字符编码是utf8,而trade_log的字符编码是utf8mb4,驱动表是trade_log,trade_detail则是被驱动表，需要将trade_detail表的tradeid字段转码成utfmb4再比较，这样就相当于对于每个tradeid都需要先进行一次转码（使用了函数），这样破坏了索引树的有序性，所以优化器放弃使用索引树搜索，而走了全表。