不论对于 nest loop 还是hash join的方式,决定驱动表和被驱动表,需要综合表的数据量大小和表数据的适合访问方式(随机读取或顺序读取)来确定。
对于循环嵌套 nest loop的表联结方式
驱动表,即需要从驱动表中拿出来每条记录,去与被驱动表的所有记录进行匹配探测。
理解驱动表和被驱动表的差异,最本质的问题,需要理解顺序读取和随机读取的差异,内存是适合随机读取的,但是硬盘就不是,对于硬盘来说顺序读取的效率比较好。
驱动表,作为外层循环,若能只进行一次IO把所有数据拿出来最好,这就比较适合顺序读取,一次性批量的把数据读取出来,这里没考虑缓存等细节。
被驱动表,即里层循环,由于需要不断的拿外层循环传进来的每条记录去匹配,所以如果是适合随机读取的,那么效率就会比较高。如果表上有索引,实际上就意味着这个表是适合随机读取的。
如果表的数据量较大,且没有索引,那么就不适合多次的随机读取,比较适合一次性的批量读取,就应该作为驱动表。
对于哈希关联 hash join的表联结方式
通常应该选用小表作为被探测表优先放入内存中,然后读取被探测表并计算关联列的hash值与前者匹配,因此,被探测表适合顺序读取或关联列有索引实际已排序,是比较好的。
(优化器是如何决定何时使用循环嵌套和何时使用哈希联结的,网上有其他很多的文章,待我学习吸收后再完善此博文~(@^_^@)~)
hints调整
常见的调整驱动表和被驱动表关系的,有ordered和leading,其中leading的控制粒度比较细。
附本人总结的优化思路:
大牛给出的优化思路:
参考阅读:
1 、《MySQL索引背后的数据结构及算法原理》 http://blog.codinglabs.org/articles/theory-of-mysql-index.html