MySQL删了索引,性能反而提升了十几倍?

科技   2024-08-08 12:00   上海  

开个公众号,把之前的视频内容转过来开场。本来想偷懒直接用视频内的字幕贴过来,发现不忍直视,还是重写。

背景是我在看8.0Change log的时候,想到的一个case。

复现步骤是用 create  table t1(id int, c int, index(c)) 创建一个无自定义主键的表,字段c上有有一个索引,先插入四行(1,1),(2,2),(3,3),(4,4), 然后重复执行 insert into t1 select * from t1直到表t1的数据行数是16384.

然后用create table t2 like t1; insert into t2 select  * from t1复制一个表结构和内容相同的表t2。

此时执行 select count(*) from t1, t2 where t1.c=t2.c,需要33秒。
为了确认是否使用了索引,用 explain语句查看。

可以看到是能够使用索引的,而且是覆盖索引。

之后再把两个表的索引c删掉,在执行上面的select语句,这次只需要2.5秒就返回了。

但是从explain结果里看到了hash join字样。

可以得出结论是因为没有索引a,MySQL改用了Hash join导致执行速度提升。因为如果用上索引a,就是执行了16384次B+树查找,而使用hash索引,则是执行了16384次hash 匹配。hash匹配的效率是比hash join快的。

但是即使有索引a, 如果优化器能够更理想地判断成本,还是应该能够自动选择hash join的。 比如PG和Oracle在相同的测试数据和步骤下,都是能够用hash join算法来执行这个语句的。而MySQL还是选择了Nested Loop Join,这个性能差距这么大,可以认为是MySQL优化器的bug。

视频末尾提的两个语句的扫描行数的计算问题,微博上有个同学回答得非常好,直接上图



MySQL数据库联盟
关注后,回复“高可用”,可获取8篇MySQL高可用文章
 最新文章