关于join算法的四篇文章

MySQL Join算法与调优白皮书(一)

MySQL Join算法与调优白皮书(二)

MySQL Join算法与调优白皮书(三)

MySQL Join算法与调优白皮书(四)

 

MariaDB Join 

MySQL数据库虽然提供了BKA Join来优化传统的JOIN算法,的确在一定程度上可以提升JOIN的速度。但不可否认的是,仍然有许多用户对于Hash Join算法有着强烈的需求。Hash Join不需要任何的索引,通过扫描表就能快速地进行JOIN查询,通过利用磁盘的带宽带最大程度的解决大数据量下的JOIN问题。

MariaDB支持Classic Hash Join算法,该算法不同于Oracle的Grace Hash Join,但是也是通过Hash来进行连接,不需要索引,可充分利用磁盘的带宽。

 

Classic Hash Join

其实MariaDB的Classic Hash Join和Block Nested Loop Join算法非常类似(Classic Hash Join也成为Block Nested Loop Hash Join),但并不是直接通过进行JOIN的键值进行比较,而是根据JoinBuffer中的对象创建哈希表,内表通过哈希算法进行查找,从而在Block Nested Loop Join算法的基础上,又进一步减少了内表的比较次数,从而提升JOIN的查询性能。过程如下图所示:

 

MySQL Join算法与调优白皮书(四)

 

同样地,如果Join Buffer能够缓存所有驱动表(外表)的查询列,那么驱动表和内表的扫描次数都将只有1次,并且比较的次数也只是内表记录数(假设哈希算法冲突为0)。

 

Classic Hash Join和BKA Join算法一样,需要强制开启,优化器无法做自动的选择,这点也是目前MariaDB和MySQL数据库都存在的问题。但是,MySQL团队已经在重构优化器模块,相信不久的将来,这些问题将很快得到解决。

 

最后,各JOIN算法成本之间的比较如下表所示:

 

开销统计

SNLJ

INLJ

BNLJ

BNLJH

外表扫描次数:O

1

1

1

1

内表扫描次数:I

R

0

R*used_column_size/ join_buffer_size + 1

R*used_column_size/ join_buffer_size + 1

读取记录数:R

R + S*R

R + Smatch

R + S*I

R + S*I

Join比较次数:M

S*R

R * IndexHeight

S*R

S/I

回表读取记录次数:F

0

Smatch (if possible)

0

0

 

Hash Join算法虽好,但是仅能用于等值连接,非等值连接的JOIN查询,其就显得无能为力了。另外,创建哈希表也是费时的工作,但是一旦建立完成后,其就能大幅提升JOIN的速度。所以通常情况下,大表之间的JOIN,Hash Join算法会比较有优势。小表通过索引查询,利用BKA Join就已经能很好的完成查询。

 

总结

 

本文介绍了MySQL数据库的各类JOIN算法,其中有Index Nested-Loop Join、Block Nested-Loop Join、Batched Key Access Join算法,最后还介绍了MariaDB的Classic HashJoin算法。通过本文,用户可以发现,虽然MySQL在JOIN算法的支持力度上远不如传统的Oracle、Microsoft SQL Server数据库,但是完成一般的JOIN查询任务是完全没有问题的。用户可能需要选对各种JOIN算法,然后根据不同算法进行参数调优,从而提升JOIN的速度。

 

不可否认的是MySQL的优化器现在还是存在缺陷的,如优化器无法直接选择Batched Key Access Join和Classic Hash Join。好在Oracle官方已经在重构MySQL优化器模块,相信一个更好的基于成本计算的优化器终将来临。

 

即使MySQL未来支持Hash Join,但是大数据的查询已经不是传统数据库适合解决的问题,未来这部分工作将越来越多地通过Hadoop这样的集群来解决。

 

参考文献

 

[1].https://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_join_buffer_size

[2].https://dev.mysql.com/doc/refman/5.6/en/nested-loop-joins.html

[3].https://dev.mysql.com/doc/internals/en/join-buffer-size.html

[4].https://mariadb.com/kb/en/mariadb/block-based-join-algorithms/

posted @ 2016-02-18 15:48  yuyue2014  阅读(1768)  评论(0编辑  收藏  举报