Access Path Selection in a Relational Database Management System 论文阅读和翻译

阅读后的感受：这是一篇开创性的文章，提出了以代价计算的方式，对sql查询计划进行选择（本文关注点是join order的选择），包含统计信息的计算，join算子代价的计算，动态规划算法进行join order的选择，提出了一些关键的概念，例如interesting order，一些启发式算法，例如优先考虑具有连接谓词的join。对于像我这样的初学者帮助很大，能够让我了解一些基本概念，同时文中包含例子，读起来并不晦涩难懂。下文是我的阅读笔记，我对论文中的一些细节仍然没有完全读懂，暂且记录下来，肯定有一些理解上的错误，希望大家多多指正。

论文的写作背景（网上找的）

1973:
　　IBM 研究中心启动了 System R 项目，旨在探讨和研究多用户与大量数据下关系型数据库的实际可行性。
1974:
　　IBM 的研究员 Don Chamberlin 和 Ray Boyce 通过 System R 项目的实践，发表了论文〈SEQUEL：A Structured English Query Language〉。论文中提出的 SEQUEL 语言是一套更适合最终用户使用的非程式化查询语言，我们现在所熟知的 SQL 语言就是基于它发展起来的。
1975:
　　IBM 的研究员 Don Chamberlin 和 Morton Astrahan 的论文〈Implentation of a Structured English Query Language〉在 SEQUEL 的基础上描述了 SQL语言的第一个实现方案。这也是 System R 项目得出的重大成果之一。
1976:
　　IBM System R 项目组发表了论文〈A System R: Relational Approach to Database anagement〉，描述了一个关系型数据库的原型。
　　IBM 的研究员 Jim Gray 发表了名为〈Granularity of Locks and Degrees of Consistency in a Shared DataBase〉的论文，正式定义了数据库事务的概念和数据一致性的机制。
1977:
　　System R 原型在 3 个客户处进行了安装，这 3 个客户分别是：波音公司、Pratt & Whitney 公司和 Upjohn 药业。这标志著 System R 从技术上已经是一个比较成熟的数据库系统，能够支撑重要的商业应用了。
1979:
　　IBM 的研究员 Pat Selinger 在她的论文〈Access Path Selection in a Relational Database Management System〉中描述了业界第一个关系查询优化器。

这些数据库的历史能够说明论文写作的环境，和此论文是一篇开创性的文章。

Access Path Selection in a Relational Database Management System （正文开始）

一些概念

• RSS:存储系统
• RSI:存储系统接口，返回元组。
• SARGS(search arguments)：这些谓词能够在RSI返回之前就过滤元组。
• interesting order：意思是上层对下层的输出结果的顺序感兴趣。如果元组顺序是由查询块的GROUPBY或ORDERBY子句指定的，那么我们说这个顺序是一个令人感兴趣的顺序。在保存代价最小的方案时，对于每种interesting order中的列顺序，都需要保存一个代价最小的方案。
  当上层存在group by ,order by,和join时，会产生interesting order。

单表访问路径的代价计算（Costs for single relation access paths）

总的代价公式

COST = PAGE FETCHES + W*（RSI CALLS)
RSI CALLS是：预测的返回元组数
PAGE FETCHES是IO代价， W*（RSI CALLS)是cpu代价（因为每返回一个元组，cpu都需要对这个元组进行相应的处理）。

统计信息

NCARD(T)，关系T的基数cardinality（我理解为行数）
TCARD(T)，段中关系T的页数。
P(T)，段中关系T的页数比例。
ICARD(I)，索引I不同key的数量。
NINDX(I)，存储中索引I的页数。

选择率的计算

选择率（selectivity）：给定一个谓词（bool)，满足条件的tuple的比例。

谓词	选择率
column=value	如果该列有索引，F=1/ICARD(I)，假设数据分布满足均匀分布。 如果没有索引，F=1/10。
column1 = column2	如果两列都有索引，F=1/MAX(ICARD(I1),ICARD(I2))，这里假定基数小的全部出现在基数大的中。 如果只有一列有索引，F=1/ICARD(I)。 如果都没有索引，F=1/3。
column > value	如果该列是数值类型，F=（列最大值-value)/(列最大值-列最小值） 。 否则，F=1/3，这个值没什么意义，估计值。
column between value1 and value2	如果该列是数值类型，F=(value2-value1)/(列最大值-列最小值）。 否则，F=1/4，这个值没什么意义，估计值。
column in (list of values)	F = (list中元素数量）* （column=value 情况下的F值）
column in subquery	F=(预测子查询会返回的行数）/ 子查询from后所有表的行数乘积
(pred expression1) or (pred expression2)	F=F(pred1)+F(pred2)-F(pred1)*F(pred2)
(pred expression1) and (pred expression2)	F=F(pred1)*F(pred2) 这里使用了独立性假设
not (pred expression)	F=1-F(pred)

Query cardinality：子查询的基数（行数），是子查询中所有from后table的乘积*各自谓词的选择率
RSI CALLS(RSICARD): 是子查询中所有from后table的乘积*各自SARGS的选择率

如何计算单个关系的COST?

• 等值谓词类上有唯一索引
  $$COST=1+1\ast W$$
  （原文写的1+1+w，我怀疑是笔误，含义其实就是常数次的IO+1行的结果*权重W)

• 一个或者多个谓词列上有聚集索引

$$COST=F(preds)\ast (NINDX(I)+TCARD)+W\ast RSICARD$$
　　第一项代表IO成本，当列上有聚集索引时，只需要访存读取索引page，并按页读取tuple即可

• 一个或者多个谓词列上有非聚集索引

$$COST=F(preds)\ast (NINDX(I)+NCARD)+W\ast RSICARD$$
　　当列上有非聚集索引时，对每个tuple，都需要访存一次，读取一个page，因此访存次数增多，相比聚集索引，IO更多。
　　由于当磁盘中的page读到内存中会放在buffer中，如果表比较小，表的page能够全部放入buffer中，那么,$$COST=F(preds)\ast (NINDX(I)+TCARD)+W\ast RSICARD$$
　　因为只需要访存一次将page装入buffer，后续访问buffer即可。
　　以上两种利用索引直接对元组进行过滤，减少IO。

• 使用列上聚集索引读取表，但谓词中列没有聚集索引
  　　(使用索引扫描的原因是按照索引列有序)
  　　$$COST=F(preds)\ast (NINDX(I)+TCARD)+W\ast RSICARD$$

• 使用列上非聚集索引读取表，但谓词中列没有非聚集索引

$$COST=F(preds)\ast (NINDX(I)+NCARD)+W\ast RSICARD$$
　　如果表比较小，表的page能够全部放入buffer中

$$COST=F(preds)\ast (NINDX(I)+TCARD)+W\ast RSICARD$$

• 顺序扫描

$$COST=TCARD/P+W\ast RSICARD$$
　　表页数除以比例为实际IO的页数

join路径的选择（access path selection for joins）

首先明确几个概念：
join的两表，先读取的表叫外表，后读取的表叫内表，根据外表中tuple的值决定是否需要读取内表的值tuple 。
join谓词是将两表的列关联起来的谓词。
join列：join谓词引用的列。

简单介绍一下下文将用到的nestloop join和merge join。
nestloop join（略）。

merge join：

• 要求两表以join列有序；
• 与order by，group by 一样，有interesting order；
• 如果有多个join谓词，取出一个作为join 谓词，其它的作为普通谓词；
• 仅用于等值连接；
• 如果join列没有索引，则必须排序。

对于n-way join，可以视为2-way join的序列，外表通常是复合的join结果，内表是向这个符合join结果上新增的表。因此，2-way join的方法可以很容易的拓展到n-way join上。但是，请注意，并不必须完成前一个2-way join再去做下一个2-way join，可以使用流水线的方式进行。仅当下一个join需要排序时，才会存储中间结果。

我们现在考虑如何选择join顺序，需要注意的是，尽管n个关系的join结果集是相同 的，但是join代价却不同。对于n个关系来说，有n!个join顺序的选择（全排列）。

一个启发式的方式是，优先执行带有谓词的join，使得没有谓词的join（使用笛卡尔积）执行得越晚越好。
在找最优join顺序的时候，会估计每个join子集的行数，并保存。而且，对于结果无序的join，和结果具有interesting order的join，实现每种order的代价最小的join方式和代价会保存起来。即对于每个interesting order的实现，会保存（join方式，join代价，和结果集行数）。会通过等价类减少interesting order的数量。

接下来的地方概述了一下如何构建join树并选择代价最低的join order：
其实是一个动态规划算法，先找到访问单个关系的最小代价，然后找到两表join的最小代价的路径，然后第三个表和两表join的的最小代价的结果进行join，找出最小代价的路径（这个过程中是需要考虑interesting order的）。（使用启发式算法进行路径搜索，在此基础上选择最小的代价的路径。）

对每个join关系，都会保存(Cost + Cardinality + Interesting Order）即join代价，结果行数，interesting order的组合。

举例说明join order选择算法

我们以图1中的join为例，进行说明。

单表路径选择（第一层）

访问EMP关系有三种路径：DNO列上的索引扫描，JOB列上的索引扫描，和顺序扫描。我们假设JOB列上的索引扫描是代价最低的，因此我们剪枝掉EMP表顺序扫描路径。
访问DEPT表有两种路径：DNO列上的索引扫描，顺序扫描。我们假设DNO列上的索引扫描是代价最低的，因此我们剪枝掉顺序扫描。
访问JOB表有三种路径：JOB列上的索引扫描，顺序扫描。我们假设顺序扫描是代价最低的，因此我们两种路径都保留。
搜索结果见图2。

单关系搜索树见图3

两表路径选择（第二层）

然后，我们搜索两表join路径，即将第二个表与图2中表进行join。

使用nestedloop join

先考虑nestedloop join，路径选择树见图4。
树剪枝的考虑：
1.优先考虑有谓词的两表进行join。
2.用nestedloop这种方式当内表有索引时，性能会比较好。

使用merge join

再考虑merge join，路径选择树见图5。
树剪枝的考虑：
优先考虑有序，如果以join列有序的表扫描路径的代价，低于无序的表扫描路径的代价，那么无序的表扫描路径将被剪枝；如果以join列有序的表扫描路径的代价，高于无序的表扫描路径的代价，那么无序的表的扫描路径将会被保留。

将nestedloop join和merge join结合在一起考虑

相同join表和相同的join结果order的路径，将会进行比较，取代价最小的路径，其余的路径都会被剪枝。

三表join路径选择（第三层）

第三个表的选择树见图6：
这块不是很理解，为什么第三层都是merge join。

结论

1. 通过对比不同路径的代价，选出最优路径的研究正在开展。初步的结论是，尽管计算出的代价并不精确，但在大多数情况下，能够选出一个最优路径。
2. 相对于执行SQL的代价，优化SQL的代价较小。
3. 本论文的关键贡献是：
   扩展了对统计信息的使用
   代价公式中包括了CPU的利用
   确定join order的方法。
4. 尽可能在访问存储和选择路径时利用选择率。
5. interesting order会增加额外的记录工作，但这额外的工作会避免存储和排序的中间结果。
6. 树的剪枝和搜索技术能使得interesting order更有效的工作。

引用
1.https://zhuanlan.zhihu.com/p/73545345