15.查询处理（二）

连接算法

• 块嵌套循环连接算法
• 索引嵌套循环连接
• 排序合并连接
• 哈希连接

连接过程

• Join 实现算法也不是唯一的
• 时耗计算 - 只考虑简单 I/O 次数，即 page 读写次数
• Notation
  • \(r，s\)：两个待连接关系
  • \(n_r,n_s\)：两个关系的记录数量
  • \(b_r,b_s\)：两个关系的页数
  • \(M\)：内存中的可用页数
• 在以下两个关系表上使用等值连接：
  • 
  • 连接的属性是 customer-name，是 Customer 的 key

块嵌套循环连接

我们想要计算

r 称为连接的外关系（ outer ralation ），s 称为连接的内关系（ inner relation ），本算法不需要索引，且可以跟任意连接操作符使用（不仅限于 equijoin ）

• 最坏情况成本估计 = \(b_r * b_s + b_r\) ，对 r 中的每个 page，我们都需要对 s 整个表格遍历一次
  

• 最好情况成本估计 = \(b_r + b_s\) ，可以把整个 r 表格都读进主存

• 一般情况成本估计：$\text{Cost} = \left\lceil \frac{b_r}{M-2} \right\rceil \times b_s + b_r $

例子

计算存款人（ depositor ）和客户（ customer ）的连接，以存款人作为外部关系（ outer relation ）

• 存款人的页数 \(b_depositor = 100\) ，客户页数 \(b_customer = 100\)
• 最坏情况下的块嵌套循环连接成本
  • \(100×400+100=40,000\) 次页面访问
  • 需要 3 主存页
• 最佳情况下的快嵌套循环连接成本
  • \(100+400=500\) 次页面访问
  • 需要 102 主存页
• 使用 52 页主存页的块嵌套循环连接成本
  • \(2 × 400 + 100 = 900\) 次页面访问

索引嵌套循环连接

如果连接是等值连接或者自然连接，并且内关系的自然属性上有索引，则可用索引代替查找代替文件扫描内关系
对于外关系 r 中每个元组 \(t_r\)，使用索引查找 s 中满足与元组 \(t_r\) 的连接条件的元组

• 成本 = \(b_r + n_r * c\)
  • 其中 c 是遍历索引并获取一个元组（总共\(n_r\)个元组）的所有匹配 s 元组的成本
    如果 r 和 s 的连接属性上都有索引，则使用元组较少的关系作为外关系

例子

计算存款人（ depositor ）和客户（ customer ）的连接，以存款人作为外部关系（ outer relation ）。
假设客户表有一个基于连接属性客户名称（ customer-name，即客户的主键）的四级 B+ 树索引。

• 存款人的页数 \(b_depositor = 100\)
• 存款人的记录数 \(n_depositor=5000\)

索引嵌套循环链接成本

• \(100 + 5000 × 5 = 25,100\) 次磁盘访问（与需要 40,100 次磁盘访问的块嵌套循环连接情况相比，很快了）
• \(5 = 4（索引页面） + 1（数据页面）\)

这里的计算表明，使用索引的嵌套循环连接可以显著减少磁盘 I/O 操作的次数，从而提高查询性能。在这种情况下，每个存款人记录需要 5 次磁盘访问，4 次用于访问 B+ 树索引的各级页面，1次用于访问实际的数据页面

排序合并连接

根据连接属性对两个关系进行排序，参考排序合并算法

• 如果两边出现重复值的话，需要回退
• 仅对等值连接和自然连接适用

因此，合并连接的页面访问次数是\(b_r + b_s + 排序的成本（如果关系未排序，回想外部排序合并）\)

哈希连接

• 应用在等值连接和自然连接
• 哈希函数 h 用于将两种关系的元组划分为 n 个桶
• h 把连接属性的值哈希到 n 个 buckets 中

第 i 个 bukects 里的 r 元组只需要跟第 i 个 bukects 里的 s 元组比较

哈希连接算法
使用函数 h 对 r 和 s 分别进行哈希，分到若干个桶中，每次哈希的过程中，主存中至少一页用于存读取数据，每个桶在主存中占用一页
对每个 i ：

• 把 r 中哈希到第 i 个 bucket 中的记录读取到内存中，并且对这些记录在主存中再建立一个哈希索引（ h' 主存的索引）
• 把 s 中哈希到第 i 个 bukect 中的记录读到内存中，对每个 s 记录，在主存中根据哈希函数 h' 寻找与其连接的 r 记录
• 成本：$分区:2（b_r+b_s） + 构建和探测:（b_r + b_s） = 3(b_r+b_s) $
  • 哈希过程，读写各一遍：这指的是在哈希连接中，首先对一个表（通常是较小的表）进行哈希处理，将数据分区并写入内存，然后再读出来进行连接操作
  • 连接过程，需要对 r 和 s 的数据一桶一桶地读进内存：这指的是在哈希连接的探测阶段，需要将另一个表（通常是较大的表）的数据分批次（一桶一桶）读入内存，与已经哈希好的表连接

例子
假设内存大小为 \(M = 25\) 页，存款人 \(depositor\) 的页数 \(b_depositor = 100\)，客户 \(customer\) 的页数 \(b_costomer = 400\)，

• 存款人是构建索引的输入表
• 将存款人分为 5 个桶，每个桶大小为 20 页。这种分区可以在一次遍历中完成
• 客户是查询索引的输入表
• 将客户分为 5 个桶，每个桶 80 页。这同样可以在一次遍历中完成

依次读入存款人的每个桶，每次读入一个桶后，分批次读入客户相应桶的数据，然后根据主存中的哈希函数进行查询存款人中对应的匹配
因此，\(总成本 = 3(100+400) = 1500\)
这里的成本计算考虑了三个阶段：首先是分区阶段，需要读取两个表的数据各一遍；然后是构建哈希表阶段，需要再次读入存款人的数据；最后是探测阶段，需要读取客户数据。每个阶段都涉及到页面的传输，所以总成本是两个表页数之和的三倍

成本计算

• 条件一：构建输入表 r 的每个桶都在内存中

\[M > \left\lceil \frac{b_r}{n} \right\rceil \]

• 其中 M 是内存页数，\(b_r\)是表 r 的页数，n 是桶的数量
• 条件二：每个桶我们有一个缓冲页用于分区，

\[M ＞ n+1 \]

为了满足这些条件，取

\[M＞\sqrt{b_r} \]

• 探测关系的分区不需要常驻内存
• 如果分区数量 n 大于 M，则需要递归分区

哈希连接的进一步优化：混合哈希连接

当内存大小相对较大并且 build input 大于内存时很有用
主要特征：将 build input 的第一个桶保留在主存中

例子

当 M = 25 时，

• depositor 被分为五个桶，每个桶的 size 为 20 pages，第一个桶在哈希过程中就一直保持在主存中，不写回磁盘，一直占用主存中的 20 个 pages
• 剩余 5 个 page：1 个 page 读入关系表的记录，剩余 4 个 pages 缓存存放其他 4 个桶的输出（当 pages 满了以后，就写出磁盘，然后就可以对该 page 清空）
• Customer 也被分为 5 个桶，每个桶有 80 个 pages 大
• 哈希到第一个桶的 customer 记录在主存中就当即与保存在主存的 depositor 第一个桶进行匹配，不用写回到磁盘
• 成本 \(cost = 3*(80+320)+20+80=1300\)

其他操作

消重和投影

• 通过排序发现并删除重复记录
• 通过哈希发现重复记录（对在同一个桶中的记录进行检查）
• 删重耗时大，慎用 DISTINCT
• 投影：对每个元组执行投影，然后消除重复项，如果投影涉及候选键（唯一的），则不需要

集合运算

集合运算可用类似于 merge-join 或 hash-join 的方法来解决

合并连接方法

• 先对两个集合排序（根据相同的属性）
• 合并的过程看具体集合运算
  • 交集：发现记录在两边都出现才报告
  • 并集：对于重复出现的只需报告一次
  • 差集：只报告出现在第一个集合但不出现在第二个集合的记录

哈希连接方法

• 用哈希函数 h 对 R 和 S 两个表的记录分到不同的桶里
• 对于桶 i，先读取 S 的记录，并用第二个哈希函数 h' 为其建立一个主存里的哈希表，然后读取该桶的 R 记录。对于读取的每个 R 的记录 t，针对不同的运算：
  • 交集：如果其能索引到相应的 s 记录，则报告
  • 并集：如果 t 不属于 S，则报告 t，最后报告 S 的所有元组
  • 差集：仅当 t 不属于 S 时才报告 t

物化和流水线处理

计算一个完整关系代数表达式的时耗的选择：

• 物化：一次执行一个 operation，并把该步骤结果写回磁盘，再执行下一个 operation
• 流水线：还没执行完当前 operation，就把部分结果输出给下个 operation

物化

从最低级别开始，一次评估一项操作。将执行该操作时临时得到的结果具体化为一张临时的关系表，已构成下一级别的操作的输入

• 例子：找到账户余额在 2,000 以下的客户的名字
  1. 计算并存储 \(\sigma_{\text{balance} < 2500}(\text{account})\) 获得客户 ID
  2. 然后计算并存储其与 customer 的连接结果
  3. 最后计算对 customer-name 的投影
     

评估

基于物化的评估总是可行的，但是将中间结果写入磁盘并读回的成本可能相当高
双缓冲：每个操作使用两个 buffer

• 当其中一个 buffer 满了，将其写回磁盘。写到磁盘同时，可以在另外一个 buffer 写进计算该 operation 得到新结果
• 同时进行写磁盘根持续执行该运算，如此并行加快速度

流水线

还没对当前运算计算完毕就将现有结果传给下个运算，如此并行计算多个运算
在之前的树中，不存储临时结果 \(\sigma_{\text{balance} < 2500}(\text{account})\)，相反将元组直接传递到连接。同样，不存储连接结果，将元组直接传递给投影操作。它比物化简单便宜的多，无需存储与磁盘的临时关系

• 需求驱动或惰性评估（拉模型）：从上至下呼叫元组
  • 系统从顶层操作重复请求下一个元组
  • 每个操作根据需要从子操作请求下一个元组，以便输出其下一个元组
• 生产者驱动或急切管道（推模型）：从下至上传递元组
  • 每个操作生成元组，并将其传递给父级运算符之间维护的缓冲区
  • 下层操作将元组放入缓冲区，父级从缓冲区中删除元组
  • 如果缓冲区已满，下层操作会等待缓冲区有空间，然后生成更多元组

有些算法即使获得输入元组也无法输出结果。它们被称为阻塞。例如。排序合并连接或散列连接。这会导致中间结果被写入磁盘，然后总是读回。

• 流水线连接技术
  • 修改混合哈希连接以在内存中缓冲两个关系的第一
    个分区的元组，在它们可用时读取它们，并输出第一个分区的元组之间的任何匹配的结果。 当找到新的 \(r_0\) 元组时，将其与现有的 \(s_0\) 元组进行匹配，输出匹配项。