原文链接：www.gbase.cn/community/p…
更多精彩内容尽在南大通用GBase技术社区，南大通用致力于成为用户最信赖的数据库产品供应商。
连接查询是关系型数据库中最主要的查询方法之一，包括哈希连接、合并连接或嵌套循环连接等。GBase 8c数据库中Join查询性能如何优化，下文通过举例子说明。
1、首先创建表，并导入数据。
创建表 departments和employees ：
-- 创建部门表
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(100)
);
-- 插入部门数据
INSERT INTO departments (dept_id, dept_name) VALUES
(1, 'HR'),
(2, 'Engineering'),
(3, 'Marketing');
创建表 employees
-- 创建员工表
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
dept_id INT,
salary DECIMAL(10, 2),
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
-- 插入员工数据
INSERT INTO employees (emp_id, emp_name, dept_id, salary) VALUES
(1, 'Alice', 1, 50000.00),
(2, 'Bob', 2, 60000.00),
(3, 'Carol', 3, 55000.00),
(4, 'David', 1, 48000.00),
(5, 'Eve', 2, 52000.00);
2、连接查询，将员工表中的emp_name与部门表中的dept_name对应起来，并显示执行计划。
- 原始查询
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;
执行计划可能显示类似于以下内容：
在这个执行计划中：表中只有5行数据，数据库执行计划走了hash join明显不对，一般连接数小于1000，nestloop (内循环嵌套连接)的性能明显会优于hash join(哈希连接)，因为hash join 前先需要把小表和大表按连接字段做hash计算，然后分别对每一个hash 桶的结果进行连接。最后把结果汇总，思想有点类似快速排序算法的分而治之。
- 通过hint优化后的查询
-- 让执行计划走nestloop
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT /*+ nestloop (e d) */ e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;
执行计划可能显示类似以下内容：
在优化后的执行计划中：使用了 nestloop提示来强制使用内循环嵌套连接,明显sql的执行时间从0.419ms降到了0.170ms。
3、分析原因
在原始执行计划中，可能由于优化器错误地选择了hash join，导致性能较差。而优化后的执行计划通过 hint /*+ nestloop (e d) */ 提示强制使用了内循环嵌套连接，更适合查询中表数据较少的情况下连接操作。因此如果驱动表的数据量小于1000，一般选择内循环嵌套，可以提高查询的效率。
那么哪些场景可以选择哈希连接呢？对常见连接使用场景总结一下：
-
哈希连接 (Hash Join)
适用场景：当连接的两个表中至少一个表较小，而另一个表较大时，通常选择哈希连接。这是因为哈希连接的性能主要依赖于内存中构建哈希表的速度，而不是依赖于表的大小。
当连接条件能够充分利用哈希算法的快速匹配特性时，例如等值连接 (e.g., JOIN ON table1.key = table2.key)。其优势在于：哈希连接在适当的情况下可以显著提高连接的性能，特别是当内存和哈希函数的选择合适时。
注意：如果内存不足以容纳哈希表，性能可能会下降，因为会有频繁的磁盘 I/O 操作。
-
合并连接 (Merge Join)
适用场景：
当连接的两个输入表已经按照连接条件进行了排序时，合并连接是一个很好的选择。
当连接条件是一个范围查询（例如 <=、>=、BETWEEN）时，合并连接可以有效地利用已排序的输入。
其优势在于：合并连接对于已排序的输入表具有高效的连接性能，尤其在大型数据集上。
注意：如果输入数据没有按连接条件排序，数据库可能需要额外的排序操作，这可能会增加查询的成本。
-
嵌套循环连接 (Nested Loop Join)
适用场景：当其中一个表很小，而另一个表很大，而且没有适合哈希连接或合并连接的索引时，通常会选择嵌套循环连接。当连接条件不能充分利用哈希或排序算法时，嵌套循环连接是一种简单有效的选择。
其优势在于：对于较小的输入表或者当连接条件选择不当时，嵌套循环连接可以提供一种可靠的连接方式。
注意：嵌套循环连接的性能高度依赖于其中一个表的大小和索引的使用情况。如果其中一个表很大或者没有适当的索引，性能可能会受到影响。
原文链接：www.gbase.cn/community/p…
更多精彩内容尽在南大通用GBase技术社区，南大通用致力于成为用户最信赖的数据库产品供应商。
