MySQL中的JOIN

MySQL的官方文档上面是这么写的“MySQL executes joins between tables using a nested-loop algorithm or variations on it.”但是似乎又有其他算法，这篇文章的题目就是《MySQL Isn’t Limited to Nested-Loop Joins》，作者还在文中抱怨说“I think the MySQL documentation could help a little by calling things names that normal users understand.”。
不管咋样吧，我看了一下SQL Server的三种join类型（算法），记得Oracle的join也是nested-loop join，merge join，hash join。要把表连接起来，总是要找个表作为基础表，然后遍历符合筛选条件的行（筛选条件要么在join的and里面，要么在where里面，在逻辑上来说where的执行顺序在join之后，但是聪明的优化器会先用where条件过滤掉一部分不符合筛选条件的行），再去用连接条件找其他表里面符合连接条件的行。

严格来说上面的这个表述只是nested-loop算法，找其他表里面符合连接条件的行时要么遍历表，要么利用索引。merge join算法是这样，两个表有聚簇索引（聚簇索引的顺序就是数据的物理存储顺序，而对非聚簇索引的索引顺序与数据物理排列顺序无关），merge join的过程就相当于把两个排好序的列表放在一起比较，看它们两个有多少个项能对齐，这么一想，还不是遍历基础表，只不过把对整表的遍历换成了对聚簇索引的遍历。我试着写一下算法代码，下面代码中list1和list2都是排好序的列表，result是最后两个列表中可以“对齐”的项。这段python代码的语法应该是正确的

m = len(list1)
n = len(list2)
result = []
i, j = 0, 0
while i < m:
    while j < n:
        if list1[i] == list2[j]:
            i += 1
            j += 1
            result.append(list1[i])
        elif list1[i] > list2[j]:
            i = i
            j += 1
        elif list1[i] < list2[j]:
            i += 1
            j = j

至于hash join，其实就是给一个表建了个临时的索引，然后利用这个索引获得连接时的性能提升，跟nested-loop join时非基础表有索引是一样的。

看到这里，肯定有人觉得写SQL语句时FROM之后表出现的顺序很重要，其实不是，优化器会决定实际的连接顺序。查询执行计划（query execution plan (QEP)）指的就是执行查询时实际的表连接顺序和表中数据的检索方法（access method），就不展开讲了。

既然说MySQL执行表连接的算法是nested-loop和它的变种，那么我们来看一下变种是什么。

Block Nested-Loop Join Algorithm，应该是叫块内嵌循环连接算法。这个算法的想法是一次只取基础表的一个值去比较太麻烦太慢了，一次性多拿出几个来，然后去和其他表比较，这样就可以减少比较次数。

Batched Key Access（BKA） Join Algorithm，批量关键码访问连接算法？这个算法跟Block Nested-Loop Join Algorithm有点类似，用在第二个表有索引的情况下，针对第一个表中符合筛选条件的行建立缓存（buffer），BKA算法会算出这些行的关键码，把这些行的关键码批量导入数据库引擎来对第二个表的索引执行查询。可能没表述清楚，原文档在这里，感兴趣的读者可以自行查阅。