MySQL中Nested-Loop Join算法小结
不知不觉的玩了两年多的MySQL,发现很多人都说MySQL对比Oracle来说,优化器做的比较差,其实某种程度上来说确实是这样,毕竟MySQL才到5.7版本,Oracle都已经发展到12c了,今天我就看了看MySQL的连接算法,嗯,现在来说还是不支持Hash Join,只有Nested-Loop Join,那今天就一下我学习的心得吧。
Nested-Loop Join基本算法实现,伪代码是这样
for each row in t1 matching range { for each row in t2 matching reference key { for each row in t3 { if row satisfies join conditions, send to client } } }
这段代码很简单,虽然我也不怎么会写代码,我还是看得懂的。这里假设有三张表,t1, t2, t3,这段代码,分别会展现出explain计划里的range, ref和ALL,表现在SQL执行计划层里,t3就会进行一次全表扫描,我今天在这个地方看到了一个很妖的优化SQL方法,Straight-join,其中提到了驱动表的概念,那么对应过来,驱动表就是伪代码里的t3表,博文里说MySQL会自动选择结果集最小的表作为驱动表,作为算法分析,这样选择驱动表确实是消耗最小的办法。那么这里还提到了,通过缩小驱动表结果集进行连接优化,那么根据这个算法来看,结果集较小的驱动表确实可以使循环次数减少。
了,MySQL自己在这个算法基础上,演进出了Block Nested-Loop join算法,其实基本上和上面的算法没有区别,伪代码如下
for each row in t1 matching range { for each row in t2 matching reference key { store used columns from t1, t2 in join buffer if buffer is full { for each row in t3 { for each t1, t2 bination in join buffer { if row satisfies join conditions, send to client } } empty buffer } } } if buffer is not empty { for each row in t3 { for each t1, t2 bination in join buffer { if row satisfies join conditions, send to client } } }
这个算法,将外层循环的数据缓存在join buffer中,内层循环中的表回合buffer中的数据进行对比,从而减少循环次数,这样便可以提高效率。官网上有个example,我有点没有看明白如果有10行被缓存到了buffer里,这10行被传给了内层循环,内层循环的所有行都会和buffer中的这10行进行对比。原文是这样的
For example, if 10 rows are read into a buffer and the buffer is passed to the next inner loop, each row read in the inner loop can be pared against all 10 rows in the buffer
如果S指的是t1, t2组合在缓存中的大小,C是这些组合在buffer中的数量,那么t3表被扫描的次数应该是
(S C)/join_buffer_size + 1
根据这个算式,join_buffer_size越大,扫描的次数越小,如果join_buffer_size到了能缓存所有之前的行组合,那么这时就是性能最好的时候,之后再增大也就没有什么效果了。
在有索引的情况下,MySQL会尝试去使用Index Nested-Loop Join算法,在有些情况下,可能Join的列就是没有索引,那么这时MySQL的选择绝对不会是最先介绍的Simple Nested-Loop Join算法,因为那个算法太粗暴,不忍直视。数据量大些的复杂SQL估计几年都可能跑不出结果,如果你不信,那就是too young too simple。或者Inside君可以给你些SQL跑跑看。
Simple Nested-Loop Join算法的缺点在于其对于内表的扫描次数太多,从而导致扫描的记录太过庞大。Block Nested-Loop Join算法较Simple Nested-Loop Join的改进就在于可以减少内表的扫描次数,甚至可以和Hash Join算法一样,仅需扫描内表一次。
编程语言
- 甘肃哪有关键词排名优化购买方式有哪些
- 甘肃SEO如何做网站优化
- 河南seo关键词优化怎么做电话营销
- 北京SEO优化如何做QQ群营销
- 来宾百度关键词排名:提升您网站曝光率的关键
- 卢龙关键词优化:提升您网站排名的策略与技巧
- 山东网站优化的注意事项有哪些
- 四川整站优化怎样提升在搜索引擎中的排名
- 疏附整站优化:提升网站性能与用户体验的全新
- 海南seo主要做什么工作售后服务要做到哪些
- 荣昌百度网站优化:提升您网站的搜索引擎排名
- 河北seo网站排名关键词优化如何做SEO
- 江西优化关键词排名推广售后保障一般有哪些
- 古浪SEO优化:提升你的网站可见性
- 西藏网站排名优化怎么把网站排名在百度首页
- 如何提升阳东百度快照排名:详尽指南