前言:因为想到在公司里偶然听到的一句:
“不要太小看MySQL了,其实只要索引用的对,千万级数据对MySQL来说也没什么压力的,现在MySQL执行器连最左匹配原则都能给你优化好。”
那么接下来我们就自己验证一下:是不是不符合最左匹配的原则时,执行器也会走索引?
Step1:新建测试表(MySQL 8.4.0)。
这里我们用MySQL社区版 最新的版本8.4.0测试一下。
以下是建表语句:
CREATE TABLE `index_test` ( `id` int NOT NULL, `column_a` int DEFAULT NULL, `column_b` int DEFAULT NULL, `column_c` int DEFAULT NULL, `column_d` int DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
该表由id和用于测试索引的4个字段组成:
Step2:创建索引。
我这里直接用Navicat生成关于a/b/c/d四个字段的索引。
Step3:执行SQL,查看性能。
这里我在WHERE条件中采用c、a、b为顺序的三个字段作为条件,来看一下该条SQL性能如何。
SELECT it.column_a, it.column_b, it.column_c, it.column_d FROM index_test it WHERE it.column_c = 233484 AND it.column_a = 259241 AND it.column_b = 406462
通过下面的图片可以看到:因为实在是太快了,时间直接显示了0.000s。
所以我们增大数据量到1700万条。
在1700万条数据的单表中,我们挑一条Navicat自然排序靠中间的数据来编写我们的SQL语句。
SELECT it.column_a, it.column_b, it.column_c, it.column_d FROM index_test it WHERE it.column_c = 25933986 AND it.column_a = 24478995 AND it.column_b = 4435505
这里我们看到,即使在1700万条数据的情况下,速度还是非常的快的,还是显示0.000s。
问:那么如何查看具体的执行时间呢?
答:可以用SHOW PROFILES语句来查看。(大家可以看我之前的一篇文章)
MySQL调优分析篇1(SHOW PROFILES):SQL语句效率分析实战https://blog.csdn.net/Mccson/article/details/129930907
这里可以看到具体的语句执行时间为:0.000303s。
所以,从这里可以看出来,在索引使用正确的情况下,MySQL8应对千万级的数据,还是没什么压力的。
Step3:查看执行计划。
那么我们可以看到,这里使用的WHERE条件并没有遵循最左匹配原则,那么是不是执行器就不会去使用我的索引了呢?接下来我们通过执行计划来揭晓答案。
答案很明显:MySQL使用了我们的索引。
Step4:对比不用索引的查询性能情况。
接下来我们把索引删除,看看执行器自动选择索引带来的提升到底有多大?
可以看到,把索引删除以后,DBMS执行了全表扫描,查询时间高达4.785s。
通过SHOW PROFILES FOR QUERY;可以看到耗时的确实是执行SQL的时间。
总结:
1.MySQL的执行器在我们的条件不符合最左匹配原则的情况下,确实会自动帮我们选择最匹配的索引(MySQL8)
2.在索引使用正确的情况下,MySQL8应对千万级的数据时,也是毫无压力的。