YugabyteDB中的YSQL表连接策略深度解析-优快云博客

本文链接：https://blog.youkuaiyun.com/gitblog_01076/article/details/148440591

YugabyteDB中的YSQL表连接策略深度解析

yugabyte-db yugabyte/yugabyte-db: 是 YugaByte DB 的官方仓库，一个高性能、高可扩展、分布式的 SQL 数据库，支持 PostgreSQL 兼容性。适合对分布式数据库、SQL 数据库和云原生应用的开发者。项目地址: https://gitcode.com/gh_mirrors/yu/yugabyte-db

引言

在分布式数据库系统中，表连接(Join)操作是最常见但也最具挑战性的查询类型之一。YugabyteDB作为一款高性能的分布式SQL数据库，提供了多种高效的连接策略来优化查询性能。本文将深入探讨YugabyteDB YSQL接口支持的各种表连接策略，帮助开发者理解其工作原理和适用场景。

表连接基础概念

表连接是关系型数据库的核心操作，它允许我们将多个表中的数据基于关联字段组合起来。在YugabyteDB中，YSQL接口支持标准的SQL连接类型：

内连接(INNER JOIN)：只返回两表中匹配的行
左外连接(LEFT OUTER JOIN)：返回左表所有行和右表匹配的行
右外连接(RIGHT OUTER JOIN)：返回右表所有行和左表匹配的行
全外连接(FULL OUTER JOIN)：返回两表所有行
交叉连接(CROSS JOIN)：返回两表的笛卡尔积

虽然用户使用标准SQL语法编写连接查询，但YugabyteDB查询优化器会根据表大小、索引、数据分布等因素选择最合适的执行策略。

实验环境准备

为了演示各种连接策略，我们先创建一个简单的示例数据库：

-- 创建学生表
CREATE TABLE students (
    id int,
    name varchar(255),
    PRIMARY KEY(id)
);

-- 创建成绩表
CREATE TABLE scores (
    id int,
    subject varchar(100),
    score int,
    PRIMARY KEY(id, subject)
);

-- 创建索引
CREATE INDEX idx_name on students(name);
CREATE INDEX idx_id on scores(id);

然后插入一些测试数据：

-- 插入学生数据
INSERT INTO students (id,name)
   SELECT n, (ARRAY['Natasha', 'Lisa', 'Mike', 'Michael', 'Anthony'])[floor(random() * 4 + 1)]
      FROM generate_series(1, 20) AS n;

-- 插入成绩数据
WITH subjects AS (
    SELECT unnest(ARRAY['English', 'History', 'Math', 'Spanish', 'Science']) AS subject
)
INSERT INTO scores (id, subject, score)
    SELECT id, subject, (40+random()*60)::int AS score
        FROM subjects CROSS JOIN students ORDER BY id;

最后更新统计信息：

ANALYZE students, scores;

嵌套循环连接(Nested Loop Join)

工作原理

嵌套循环连接是最基础的连接算法，它通过双重循环实现：

外层循环遍历左表的每一行
对于左表的每一行，内层循环遍历右表查找匹配行

性能特点

时间复杂度：O(m*n)，其中m和n分别是左右表的大小
内存消耗最低，不需要额外内存
当右表有索引且左表较小时效率较高

示例分析

EXPLAIN (ANALYZE, DIST, COSTS OFF)
/*+ 强制使用嵌套循环连接 */
SELECT name, subject, score
FROM students JOIN scores USING(id)
WHERE name = 'Natasha' and score > 70;

执行计划显示：

先扫描students表找到name='Natasha'的记录
对每个找到的学生ID，通过索引查找scores表中score>70的记录
总共进行了9次存储读取请求

批量嵌套循环连接(Batched Nested Loop Join)

优化原理

标准嵌套循环连接对右表进行多次单独查询，在分布式环境中会产生大量网络请求。批量嵌套循环连接通过以下方式优化：

收集左表的一批键值(默认最多1024个)
将这些键值打包成一个数组
通过一次RPC请求批量查询右表

配置参数

yb_bnl_batch_size：控制批量大小，默认1(关闭)，建议1024

示例分析

EXPLAIN (ANALYZE, DIST, COSTS OFF)
/*+ 强制使用批量嵌套循环连接 */
SELECT name, subject, score
FROM students JOIN scores USING(id)
WHERE name = 'Natasha' and score > 70;

执行计划关键区别：

内层循环只执行1次
使用id = ANY(ARRAY[...])条件批量查询
存储读取请求减少到4次

合并连接(Merge Join)

工作原理

合并连接要求两个输入表都已按连接键排序，然后像合并两个有序数组一样合并它们：

比较两个表的当前行
如果匹配则输出，并推进两个指针
否则推进较小值的指针

适用场景

表已按连接键排序(如有索引)
大数据集上的等值连接
不需要构建哈希表，内存消耗较低

示例分析

EXPLAIN (ANALYZE, DIST, COSTS OFF)
/*+ 强制使用合并连接 */
SELECT name, subject, score
FROM students JOIN scores USING(id)
WHERE name = 'Natasha' and score > 70;

执行计划显示：

先对两个表按id排序
然后执行合并操作
总共2次存储读取请求

哈希连接(Hash Join)

工作原理

构建阶段：扫描左表，在内存构建哈希表
探测阶段：扫描右表，在哈希表中查找匹配

适用场景

连接键没有索引
中等大小表(哈希表能放入内存)
等值连接条件

示例分析

EXPLAIN (ANALYZE, DIST, COSTS OFF)
/*+ 强制使用哈希连接 */
SELECT name, subject, score
FROM students JOIN scores USING(id)
WHERE name = 'Natasha' and score > 70;

执行计划显示：