GBase 8c Join连接查询性能调优实例解析

esc0rp

原文链接：https://www.gbase.cn/community/post/3959

更加多精彩内容都在南大通用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)适用场景：当其中一个表很小，而另一个表很大，况且无适合哈希连接或合并连接的索引时，一般会选取嵌套循环连接。当连接要求不可充分利用哈希或排序算法时，嵌套循环连接是一种简单有效的选取。其优良在于：针对较小的输入表或当连接要求选取欠妥时，嵌套循环连接能够供给一种靠谱的连接方式。重视：嵌套循环连接的性能高度依赖于其中一个表的体积和索引的运用状况。倘若其中一个表很大或无适当的索引，性能可能会受到影响。

原文链接：https://www.gbase.cn/community/post/3959

更加多精彩内容都在南大通用GBase技术社区，南大通用致力于作为用户最信赖的数据库制品供应商。

返回外链论坛：www.fok120.com，查看更加多

责任编辑：网友投稿

听听海

“BS”（鄙视的缩写）‌