图解数据库连接查询（JOIN）的三种实现算法： MySQL、Oracle、SQL Server 等

文章目录

        嵌套循环连接
        哈希连接
        排序合并连接
        总结

An SQL query walks into a bar and sees two tables. He walks up to them and asks ‘Can I join you?’
一个 SQL 查询走进酒吧看到两张桌子（table），走到它们面前问“我可以和你们坐在一起（join）吗？”

SQL 连接查询（JOIN）可以同时获取多个表中的关联数据。例如，查看某个完整的订单数据时，可能需要从产品表、用户表、用户订单表、以及订单明细表中获取相关的信息。

不过，今天我们要讨论的是数据库内部如何利用算法实现连接查询。通常实现连接查询的算法有三种：Nested Loop Join、Hash Join 以及 Sort Merge Join。本文涉及到的数据库包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite，首先给出结论：

📝关于各种内、外连接的查询方式和语法可以参考这篇文章。

接下来针对三种算法进行具体的分析。
嵌套循环连接

嵌套循环连接（Nested Loop Join）是一种最基本的连接实现算法。它先从外部表（驱动表）中获取满足条件的数据，然后为每一行数据遍历一次内部表（被驱动表），获取所有匹配的数据。下图演示了嵌套循环连接的执行过程（图片来源于bertwagner）：

Nested Loop Join 类似于编程语言中的嵌套 for 循环；当然，数据库在实现时会进行各种优化，例如通过索引提高扫描速度。

我们可以通过执行计划查看 JOIN 的实现方式，先看 MySQL 中的以下示例（示例表来自这里）：

– MySQL
explain analyze
select e.first_name,e.last_name,e.salary,d.department_name
from employees e
join departments d on (e.department_id = d.department_id)
where d.department_name = ‘IT’;

-> Nested loop inner join (cost=7.38 rows=24) (actual time=0.080…0.102 rows=5 loops=1)
-> Filter: (d.department_name = ‘IT’) (cost=2.95 rows=3) (actual time=0.043…0.061 rows=1 loops=1)
-> Table scan on d (cost=2.95 rows=27) (actual time=0.036…0.050 rows=27 loops=1)
-> Index lookup on e using emp_department_ix (department_id=d.department_id) (cost=1.08 rows=9) (actual time=0.035…0.038 rows=5 loops=1)

对于以上查询，MySQL 选择了使用 Nested loop inner join 算法；departments 是驱动表，循环 1 次返回 1 行数据；employees 是被驱动表，使用索引进行遍历，然后回表查找表中的数据，循环了 1 次（因为 departments 返回了 1 条记录）。实际上 MySQL 对这个嵌套循环连接进行了优化，采用的是 Index Nested Loop Join 算法，在内层循环中扫描索引 emp_department_ix 而不是数据表，从而提高效率。

📝关于各种数据库中执行计划的查看方法，可以参考这篇文章。

下面是该语句在 Oracle 中的执行计划：

– Oracle
EXPLAIN PLAN FOR
select e.first_name,e.last_name,e.salary,d.department_name
from employees e
join departments d on (e.department_id = d.department_id)
where d.department_name = ‘IT’;

SELECT * FROM TABLE(DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT
Plan hash value: 1021246405

--------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
--------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 10 | 380 | 4 (0)| 00:00:01 ||
| 1 | NESTED LOOPS | | 10 | 380 | 4 (0)| 00:00:01 ||
| 2 | NESTED LOOPS | | 10 | 380 | 4 (0)| 00:00:01 ||
|* 3 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 16 | 3 (0)| 00:00:01 ||
|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 ||
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 220 | 1 (0)| 00:00:01 ||
--------------------------------------------------------------------------------------------------|
|

3 - filter(“D”.“DEPARTMENT_NAME”=‘IT’) |
4 - access(“E”.“DEPARTMENT_ID”=“D”.“DEPARTMENT_ID”) |
|

Note
- this is an adaptive plan

Oracle 也是选择了 departments 表作为，然后通过索引（EMP_DEPARTMENT_IX）范围扫描进行遍历找出满足连接条件的索引值和 ROWID，最后通过遍历这些索引 ROWID 获取 employees 中的数据。

SQL Server 的执行计划和 Oracle 几乎完全一致：

– SQL Server
SET STATISTICS PROFILE ON
select e.first_name,e.last_name,e.salary,d.department_name
from employees e
join departments d on (e.department_id = d.department_id)

更多请见：http://www.mark-to-win.com/tutorial/51664.html