14.查询处理（一）

主要内容

• 查询处理
  • 查询成本度量
  • 选择操作
  • 外部排序
• 连接算法
• 其他操作
  • 去重和投影
  • 集合操作
• 物化和流水线处理

查询处理

查询的基本步骤

• 步骤一：解析和转化
  • 将 SQL 查询语句转化为关系代数
  • 解析器检查语法和表名
  • 
• 步骤二：求解
  • 查询执行引擎采用查询评估计划，执行该计划，然后返回查询的答案
  • 关于查询时长的统计数据有助于指定更优的查询计划
  • 

查询成本度量

• 关系代数表达式可以有许多等效表达式

  • 

• 一个简单的关系运算也可以有很多种算法实现

• 综上，求解关系代数需要依据特定的 evaluation-plan

• 对查询
  

  • 方案 1
    • 
  • 方案 2
    • 

• 查询优化：在所有同等执行计划中，选择（预期）成本最低的一项

• 我们把时间成本简单计算为 - 在磁盘和主存之间读写 page 的总数量

  • 忽略顺序 I/O 和随机 I/O 之间的成本差异（忽略寻道）
  • 忽略 CPU 花费
  • 忽略将最终结果写入磁盘的成本

• 花费：时间成本也依赖于主存中缓冲区的大小，拥有更多内存可以减少对磁盘访问的请求

选择操作

检索满足 \(\sigma_A = v(r)\) 选择条件的一些算法

• 算法 A1：访问每个 page 并测试所有记录是否满足选择条件
  • 成本估计（扫描的磁盘页数）= \(b_r\)
    • \(b_r\) 表示包含关系表 \(r\) 中的记录和页数
    • 如果选择条件是关于键（ key ）的，则一旦访问到目标记录查询即可停止。平均成本 = \((br/2)\)，最坏成本 = \(b_r\)
• 算法 A2：如果选择操作是作用在文件排序所依据的属性上会比较适用
  • 假设该表的文件 page 是连续存储在磁盘上的（所以才可使用二分查找）
  • 成本估算（要扫描的磁盘块数）：
    • 通过搜索定位第一个元组的成本 \(\left\lceil \log_2(b_r) \right\rceil\)
    • 如果有多条记录满足选择条件，则还需算上这些记录跨越的 page 页数

如果现在我们假设选择条件是作用在搜索键上的

• 算法 A3（在候选键中建立主索引，使用等值查询）：检索满足相应相等条件的单个记录
  • 成本 = \(HT_1 + 1\)，\(HI_1\) 为树的高度
• 算法 A4（在非键属性上建立主树索引，进行等值查询）：检索满足条件的多个记录，记录将在连续的页面上
  • 成本 = \(HT_1 + 包含检索记录的页数\)，\(HI_1\) 为树的高度
• 算法 A5（在辅助索引的搜索键上进行等值匹配）：
  • 如果搜索键是候选键，则检索单个记录
    • 成本 = \(HT_1 + 1\)
  • 否则需要检索多个记录
    • 成本 = \(HT_1 + 检索到的记录数\)
      • 但记录可能位于不同的页面上 - 可能非常昂贵！
      • 最坏的情况：每条检索到的记录都需要一页访问
  • 

如果涉及到比较式
可以通过以下方式实现表单的选择 \(\sigma_A \leq \nu(r) \quad \text{or} \quad \sigma_A \geq \nu(r)\)

• 线性文件扫描或者二分搜索

• 索引

• 算法 A6（主索引，比较）（关系在属性 A 上已排序）

  • 对于 \(\quad \sigma_A \geq \nu(r)\)，使用索引查找满足的第一个元组，并访问之后的所有 page
  • 对于 \(\sigma_A \leq \nu(r) \quad\)，从第一个page开始往后访问，直到检索到大于 v 的元组则可以停止

• 算法 A7（辅助索引，比较）（关系没有在属性 A 上排序）

  • 对于 \(\quad \sigma_A \geq \nu(r)\)，使用索引查找第一个索引条目 \(≥ v\)，从那里开始连续访问向后的索引条目，以查找所有满足条件记录的指针，然后根据指针寻找记录
  • 对于 \(\sigma_A \leq \nu(r) \quad\)，只需从头开始扫描索引的 \(leaf page\)，查找指向记录的指针，知道第一个条目 \(＞v\)

• 在任何一种情况下，检索指向记录的指针，最坏情况下每条记录都需要一个\(page I/O\)

• 如果检索到许多记录，线性文件扫描文件可能效率更高

复杂选择的实现

• 算法 A8（使用单一索引的合取选择）：选择其中的一个条件 \(θ_i\)，为其从算法 A1-A7 中挑一个算法，使得 \(θ_i\) 与 \(A_j\) 组合的 \(\quad_{θi}(r)\) 的查询成本最小，将元组提取到主存后测试记录对其他条件的满足性，不满足则丢弃
• 算法 A9（使用多值索引的合取选择）：如果可行，也可以选择使用复合（多键）索引
• 算法 A10（通过标识符交集进行合取选择）
  • 需要用有记录指针的多个搜索键的索引
  • 对每个条件使用相应的索引，并对所有获得的记录指针集进行交集，然后从文件中获取记录，如果某些条件没有合适的索引，则在记录读到内存后再对它们进行该条件的测试
    
• 算法 A11（通过标识符并集进行析取选择）
  • 如果所有的条件都有可用索引，则使用，否则使用线性扫描
  • 对每个条件使用相应的索引，并对所有获得的记录指针集进行并集，然后从文件中获取记录
    
• 对文件使用线性扫描
• 如果满足 \(\negθ\) 的记录很少，并且有关于 \(θ\) 的索引，且索引适用于 \(θ\) 使用索引查找满足 \(\negθ\) 的条件的记录

外部排序

• 当我们在执行选择操作的时候，有时候需要对记录进行排序
• 譬如SQL语句中用到 \(asc\) 或者 \(desc\) 指令
• 如果能够把全部记录读取到内存中，那么我们可用传统的排序算法，譬如快排等，因为这些算法的时间成本是以数值之间的比较，以及置换操作衡量的
• 但是，当我们在操作数据库时，我们知道记录体量一般很大，需要分成很多个 \(page\) 存储，在更多时候是不能把全部数据装进主存的
• 这个时候再用快排就不科学了，因为此时对数据进行排序的主要消耗的时间成本在磁盘读写上，而快排等算法是针对主存内的操作进行优化的
• 因此，我们需要外部排序，考虑到的优化目标是最小化磁盘读写（ I/O ）成本

使用三页主存缓冲区合并已排序文件

Q：当我需要合并超过 2 个排序好的文件时，可以这样做吗？

• A：是的，可以合并最多 M-1 个文件，而不是 2 个文件（因为需要 1 页写入输出）。M 是主存可以容纳的最大页数

Q：上面假设了每个文件都已经排好序，如果文件未排序，应该怎么办？（仅使用三个主存页）

• A：前述示例中的每个文件都有 2 页，因此，我可以将整个文件放入内存中，并使用任何主内存算法对其进行排序

Q：如果每个文件大于 2 页呢？此时主存排序算法不能用于单个文件，怎么办？

• A：
  • 1. 将数据逐批带入主存，每一批都是 M 页，使用主存排序算法进行排序，然后写回磁盘，此时这里的每个批次 M 个 page 就构成我们之前所说的一个 sorted file
  • 2. 迭代的对上述排序文件进行 M-1 路的合并

例子

• 1. M = 4 page，我们有一个大文件，每个文件有 100 个 page，完全未排序。分 25 次把它妒读进主存，每次读取 4 个 page，这 4 个 page 内部和 page 之间都是未排序，所以直接调用主存算法对它们进行排序，分别得到内部排序好的 1 个小文件（文件 \(a_i\) ），大小分别为 4 个 page。一共得到 \(a_1\) 至 \(a_25\) 的文件，每个文件内部是排序的，文件之间是无序的
     
• 2. 迭代的对上述 25 个文件进行 3 路的合并，每次合并 3 个，一开始是 25 个文件，第一轮合并后，变成 \(\left\lfloor \frac{25}{3} \right\rfloor = 9\)个文件，继续迭代 3 路合并，依次得到 9，3，1 个文件，最后结束
     

具体流程

M：主存大小，R：关系表，\(b_r\)：存储整个 R 需要 page 个数

• 步骤一：
  • 
  • 记 N 为上述执行循环的次数，则我们有 Sorted Runs = \(\{R_0,...,R_{N-1}\}\)
• 步骤二：
  • 如果 N < M
    • 直接使用 N-路合并，使用 N 页内存来存储 N 个文件的当前页，使用 1 页来写出排序结果
    • \(总成本 = 2b_r + b_r = 3b_r\)
      • \(2b_r\)：在步骤一，需要完整的读入以及写出全部的 pages
      • \(b_r\)：在步骤二，需要完整读入全部 pages
      • 注：我们之前说过，最后输出结果写出到磁盘，不算入成本
  • 如果 N ≥ M
    • 对每次 pass，连续合并 M-1 个文件。先合并 0 至文件 M-2，然后合并 M-1 至 2M-2，一直合并到 N-1，一个 pass 将文件个数减少 M-1 倍，同时每个文件长度变大 M-1 倍
      • 例如，If M = 11, N = 90。 在第一次 pass 中，因为最多可以执行 10-way merge，所以一批次可以排序合并 10 个 runs，那么这个 pass 一共执行了 9 个批次 merge。最后变成 9 个内部排好序的文件。文件个数减小了 10 倍，文件大小增加了 10 倍。
    • 迭代重复上述 pass 直到所有文件合并成一个，每次 pass 将 N 减少 M-1 倍
    • \(总成本=b_r \left( 2{\lceil \log_{M-1} \left( \frac{b_r}{M} \right) \rceil + 1} \right)\)
      • \(b_r/M\)：经过第一轮内部排序后得到多少个 \(run\)
      • 2*：读写各一次
      • 1：第一轮内部排序的成本