看了网上的一些文章,有的人说大表驱动小表查询更快,有的人说小表关联大表更快。
比如:
有人支持小表关联大表
有人支持大表关联小表
这两种观点把我也整蒙了,到底应该大表关联小表,还是应该小表关联大表呢?
我先给出我的结论:大表驱动小表
为什么这么说呢?实践是检验真理的唯一标准。动手自己测一测就知道了。
新建了两张表:
big_table,顾名思义就是数据量较大的table,数据量大概在50W左右,表结构和数据在下面
sid关联到small_table的主键id。
small_table,即相对较小的table,数据量在2W左右,表结构和数据在下面
没有特别说明的话后文b表代指big_table,s表代指small_table。
实验开始
第一步:大表关联小表
SELECT * FROM big_table b LEFT JOIN small_table s ON s.id = b.sid LIMIT 0,500000;
结果:
多测试几次,时间也都稳定在零点几秒。
贴出执行计划:
从执行计划可以看出Nested loop join的基准表是b(外循环),s是内循环。
第二步:小表关联大表
SELECT * FROM small_table s LEFT JOIN big_table b ON s.id = b.sid LIMIT 0,10000;
这里为什么不limit50W呢?因为太慢了,我也很难受。。。取1W看结论。
结果:
可以看到时间明显比第一步的时间更长,而且还仅仅是1W的数据量。
执行计划:
可以看到Nested loop join的基准表是s(外循环),b是内循环。这里和第一步执行计划不同的是:第一步执行计划查询s表使用了索引,即主键索引,而第二步查询b表使用的全表扫描(ALL)。如果给b表的sid列添加索引结果会怎么样呢?
结果一目了然,加了索引之后速度明显变快。
那么继续执行50w的数据查询,结果是什么呢?
执行计划和查询1w数据量是一样的。
结果分析
根据控制变量的观点,第一步内循环采用主键查询,而第二步采用索引查询。这可能是第二步查询时间更长的原因,如果控制变量使第二步也采用主键查询的方式,那么s表数据量也必须扩展到50W,那么熟前熟后就没有意义了!即使是因为主键和索引带来的时间差异,也可以认为大表在前的查询速度更快,当然也可能是因为内部循环数据量更小,查询速度更快。
综上所诉,大表驱动小表(从表驱动主表,外键表驱动主键表)更快。
主表驱动从表应该在从表的外键上建立索引。
说那么多,实际情况下有可能不是主键关联,没有建立索引等等,因此看执行计划才是王道!!
PS)做这个实验的时候我心里也是没底的,不知道大表驱动小表更快还是小表驱动大表更快,所以我最前面的结果也是改了几次。这个结论只是半个下午实验出来的,可能存在一定的局限性!测试的条件可能存在缺陷,希望有人能提出不同的观点。