《Access Path Selection in a Relational Database Management System》论文解读

名词概念

概念	意义
Relation	Relation指的就是表
Tuple	元组，就是一行数据
Page	数据页，tuple保存在数据页中，一般大小是4K，一个page中可能有不同relation的tuple
Segment	多个连续Page就构成了一个Segment，一个Relation包含多个Segment，要找到具体的数据就是从Segment找到tuple的过程
Access Path	数据库存储引擎获取数据的方式，包括Segment Scan全量扫描以及Index Scan索引扫描
Segment Scan	对relation的所有Page进行全量扫描，检索所有的tuples
Index Scan	利用索引对Relation中的tuple进行扫描
query block	有三部分，select的查询列，from指定的表，where中的查询谓词，但是谓词中或者from等都有可能出现子查询，所以query block也有可能是嵌套关系
NDV	Number Of Distinct Values。不同的索引key的数量

整体框架：

System R 分成以下四个组件

Parser

通过yacc解析，生成基本的tree

Optimizer

1. 从Catalog中读取元数据+统计信息以及每个Relation可用的访问路径，语义检查
2. 确定各个query block的求值顺序，对每个query block，计算cost并生成最优plan，plan使用ASL语言描述
3. 将ASL语言转换成machine code，对外暴露为一个函数，其中每个嵌套query block plan，都是一个子函数

Executor

执行machine code，过程中会调用RSI(Relational Storage Interface)接口，访问RSS存储层获取数据，RSI接口是OPEN -> NEXT -> CLOSE的方式，每次获取一个tuple。

RSS （Relational Storage System）

1. Relaition（关系）以tuple的形式被存储，一个关系就是tuple的集合。这些元组被存储在4K的页上。没有元组可以跨页。元组就是一行数据，也就是说同一行数据只能在一个页上，不能一半在页1而另一半在页2上。页被组织成成为Segment（段）的逻辑单元，段上可以有一个甚至多个关系，但是关系不能垮段。
2. 访问关系中的元组就是通过RSS 扫描的方式，通过给定的Access Path来扫描，每次只获取一个tuple。

• RSS扫描的方式分成两种：
  • 第一种是Segment Scan，扫描Segment中包含tuple的所有页，并找到指定Relation的元组。所以该类型是对非空的page都扫描一遍。
  • 第二种是Index Scan，用户通过一个或者多个字段建立索引，这些索引被存储在独立的page上，通过B+树组织，其叶子结点包含了该字段及其对应的元组标识符，因此index scan只要根据索引 的叶子结点顺序读取即可。该类型则是分为聚簇索引和非聚簇索引，非聚簇索引要扫描page两次，一次是扫描索引页一次是扫描数据页。
• 搜索参数SARG
  • Index Scan和Segment Scan在搜索时都可以选择一组谓词，成为SARG （Search Arguments），可以直接应用于tuple，在返回给RSI Caller（调用方）之前，可以先进行判断，如果tuple满足条件则返回，若不满足则直接Next，继续扫描下一个tuple。直到找到满足的tuple，或者该Segment扫描完或者该索引扫描完。
  • 这通过消除对元组进行RSI调用的开销来降低成本。
  • 但是不是所有的谓词都能称为搜索参数SARGS，它的形式需要是列比较运算符。（column oprator value）

单个Relation的Access Path 代价计算：

单个Relation的Cost是多个Relation（比如Join）的基础
Optimizer优化器检查查询上的谓词以及Relations对应的可用的Access Path，并使用如下代价计算公式：

pagesfetched: IO的代价，包含Index Scan和 Segment Scan的读取代价
RSICALL：进入RSS层的次数，可以理解为CPU代价，因为每一次返回到上级Relation的都是一个tuple，所以这个次数就是返回的tuple的数量
W：权重比例，描述IO代价和CPU的权重

Boolean Factor

Where树被认为是一个合取范式，每一个合取被称为Boolean Factor（布尔因子）。这些布尔因子需要被关注。因为返回的每一个tuple都必须满足这些布尔因子的条件。
而如果Boolean Factor是sargable predicate（这种谓词指的是该谓词中相关的列是索引列，例如在一个Salary列上创建了索引，而谓词为’Salary=2000’）。那么走索引会是一种高效的方式，同样如果Boolean Factor是以SARGS的形式来表达的，那么也会更高效，因为可能可以命中索引。

Statistics

Cost的计算需要使用到统计信息。在Optimizer中使用Catalog读取元数据，也会记录下统计信息。
这些统计信息会在初始化加载Relation和创建索引时创建。但是在增删改数据时并不会更新，需要用户手动update，因为这会在增删改时增加额外的操作和开销。
统计信息：
NCARD(T)，关系T的基数cardinality（我理解为行数）
TCARD(T)，段中关系T的页数。
P(T)，段中关系T的页数比例。 TCARD(T)/Segment中非空的所有页数
ICARD(I)，索引I不同key的数量。
NINDX(I)，存储中索引I的页数。

Selectivity Factor

Optimizer 为每一个Boolean Factor 都分配一个选择性因子 ‘F’ ，这个选择性因子大致对应于满足谓词条件的tuple的期望。
QCARD（查询基数）是From中每一个Relation的行数乘以该关系所有布尔因子的选择性因子的乘积，这些
选择率计算（selectivity），给定一个谓词，其满足条件的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)

Interesting Order

为单个Relation选择最优的访问路径除了上述的选择性因子以及统计信息之外。使用索引访问元组或者对排序元组，会按照索引值或者排序键生成tuple。如果一个tuple的顺序是由order by或者group by指定的那么就说这个顺序是一个Interseting Order

总结

对单个Relation来说，通过评估对每一个可用路径（包括index scan加上Segment scan）的代价来计算出最cheapest 的路径。对于每一个路径，会计算它的预计成本以及产生的元组的顺序。
以升序扫描Salary索引为例，会产生一个cost，和一个Salary升序的tuple。
因此要为单个Relation找到最便宜的访问路径，只要找到有Interseting Order的最小cost的路径以及无序的最小cost的路径。如果没有group by或者order by那么就只需要找cost最小的路径即可。因为如果有顺序的要求除了cost外还要加上后续排序的开销。

Joins的路径选择

• 首先以两个Relation做Join为例，一个作为外部Relation，另一个则是作为内部Relation。他们之间的连接条件称为连接谓词。连接谓词中涉及到的列则被称为连接列。但是连接条件可能不止一个，那么就选择其中一个作为连接谓词，其他的条件则称为普通谓词。
• 在这篇文章中，将Join的方式分为两种，一种是NestedLoopJoin，另一种则是Merging Scans。当然现在还有其他的Join方式比如Hash Join，在这里指描述一下这两种链接方式。
  • NestedLoopJoin：就是简单的遍历外部关系中每一个元组，再遍历内部关系中的每一个符合连接谓词的所有元组。
  • merging Scan Join：该连接要求外部Relation以及内部Relation按照连接列顺序扫描。这意味着和Group by和Order by的效果一样，连接谓词中的连接列也属于之前上文中提到的Interesting order。如果连接列上没有添加索引，还需要创建一个临时的排序完成的表。
• 既然明白了two-way Join的原理，那么就可以将其扩展为n-way Join。一旦我们获得了a和b连接的结果，就可以将其与其他关系进行三元连接。并且在一个查询中可以同时使用NestedLoopJoin和Merging Scan Join，比如a和b使用嵌套连接，其连接的结果再与c使用合并连接。

Join顺序选择

即使n个Relation的基数是相同的，但是不同的连接顺序带来的代价却有很大的差别。全排列找的所有Join的可能性又n！种。

启发式裁剪

当然可以使用启发式规则来筛选。例如：只考虑有连接谓词的。例如在From后存在三个Relation T1、T2、T3，T1和T2存在连接谓词，T2和T3存在连接谓词，那么T1T3T2以及T3T1T2这两种连接顺序就不再进行考虑。这意味着所有笛卡尔积的连接都更晚执行，尽可能过滤掉更多的数据。

解树的构成

为了找到join顺序最优解，会构造出一个可能解的tree。对于连接的每一组关系都会估计和保存可能得基数（即该连接可能会有多少行数据）。此外如果该连接是无序连接，则要保存最代价最小的解决方案以及该解决方案的代价。该解决方案包括了之前已经Join的Relation的顺序，每一个Relation的Join 方式（Nested Loop Join或者Merging Scan Join），以及读取每一个Relation的扫描方式(Index Scan或者Segment Scan)。

Order等价类

如果外部复合Relation（此时可能多个Relation已经Join）与内部Relation需要再Join之前进行排序（因为现在的Join方式使用的是Merging Scan Join），那么其实该情况和单个Relation使用Group By和Order By的情况一致，会产生一个Interesting Order。为了最小化Interesting Order的数量，所以只计算每一个Interesting Order等价类中的最优解。举个例子假设两个Relation进行Join 存在两个连接谓词E.DNO = D.DNO and D.DNO = F.DNO，那么这三个列属于一个等价类，只要计算其中最优的解并保存即可。

解树的生成过程

1.首先为单个关系找到有序以及无序的最佳方法（即找到使用Index Scan或者Segment Scan的代价）
2. 根据连接关系的启发式规则，找到连接这些关系的最佳方法。具体方案就是先考虑所有两个关系的集合，再通过启发式的规则来选择第三个要连接的Relation。对于连接一组关系的计划，复合结果的顺序会保存在树中。再所有关系都连接在一起之后，优化器会找到代价最小的方案。如果存在顺序正确的解（即最后的结果和使用了order by和group by是一样的），那么就不会再执行order by和order by。除非有序解比代价最小的无序解加上按照排序字段排序的代价更高。举个例子，需要EMP为有序的，使用Index Scan最后的结果EMP是有序的，但是使用Segment Scan后再按照EMP进行排序的代价更小，那么就会考虑。

成本计算

连接的开销是根据扫描每个关系的代价以及基数（行数）决定的。单个关系的代价可以见上述已经描述过的公式。
下面是具体的代价的算法：
假设C-outer(path1) 是外部Relation的扫描的代价。N是满足谓词的基数。N的公式如下：目前为止所以关系基数的乘积 * 所有谓词的选择率的乘积
N = (product of the cardinalities of all relations T of the join so far) *
(product of the selectivity factors of all applicable predicates).

假设C-inner(path2) 是内部Relation的扫描的代价，并且已经应用了所有的谓词。那么外部Relation与内部Relation做Join的代价即为：外部Relation扫描的代价加上符合谓词的基数*内部扫描的代价
C-nested-loop-join(pathl,path2)=C-outer(path1 + N * C-inner(path2)
而merge scan join的代价就是再加上排序连接列的代价。

但是内部关系不一定是一个Single Relation，如果是一个临时表的情况下就不适用于上述的公式了，而是使用以下公式:
C-inner(sorted list) =TEMPPAGES/N + W*RSICARD
TEMPPAGES指的是保存这个临时的内部关系的页数，假设在合并过程中，每一个页都都会用到一次。
总结：所有可见本质上循环嵌套连接和merge scan连接本质上是一样的。后者将内部Relation通过连接列聚集在了一起。这使page的数量减少了。

现在有三张表，以及Sql语句：

假设有三种Relation，分别是EMP、DEPT、JOB，且还有一个Join的Relation要求DNO和JOB字段需要是排序的。现在的条件是EMP和DEPT的Index Scan的Cost是小于Segment Scan。JOB的index Scan的Cost是大于Segment Scan。
下图中：最左侧是三种Relation，右侧的每一条竖线表示一种可能的Access path，也会进行剪枝，以EMP为例，因为EMP的对字段Index Scan的Cost都是小于Segment Scan的所以后面的Segment Scan都可以直接剪枝，而且本身Segment Scan的结果也是无序的更不可能写了。而Relation JOB因为Segment Scan的Cost小于Index Scan因此保留，因为需要数据是有序的，所以虽然Index Scan的Cost更大，但是在Segment Scan的结果排序后总的Cost是有可能大于Index Scan的Cost的
最下方的N1 C(EMP.DNO)就是产生的Cardinality + Cost + Interesting Order
Note：其中展示出来的是需要做Join连接的字段，所以比如有些字段Title，Name等虽然没有写但是其实也是需要选择的。

图三就是单个Relation的所有Access Path的可能，其实也就是将上图2组合在一起

图四是在图三的基础上的两个Relation做Nest Loop Join，但是是有连接条件的两个Relation的组合，如果没有则是笛卡尔积的形式比如(JOB,DEPT)就不用进行考虑了，如图是Relation EMP和其他两个Relation的所有Join的可能，因为需要遍历所有的可能，并且通过动态规划计算最优解，动态规划的子问题可以看做是每一条到leaf Node的结果。

图五和图四的Join方式不同，使用的Sort Merged Join，这个Join的特点就是需要先对input进行排序，再使用两个指针同时前往下一个进行比较，因此需要比较的字段应该是需要排序完成的，即上面提到的Interesting order。可以看到JOB表作为Ouer表，使用Segment Scan扫描tuple时，需要对结果进行排序，才能再去和EMP表的Index Scan的结果进行merge。每一个从root到leaf的一个分支最后就组成了 Cost+Cardinality + Interesting Order的组合来作为动态规划的子问题。

如下图六，从相同table set和相同Interesting order中找到代价最小的，其他的都可以剪枝掉，从图五中可见最后每一个叶子节点下方都有Cost+Cardinality + Interesting Orde这三个维度。再按照上述逻辑进行第三个Relation的连接。
图五中可见，table set就只有两种(EMP,DEPT)和(EMP,JOB)
以(EMP,DEPT)讲解：首先是同一个table set（EMP、DEPT），由图五可见Interesting order有两种 DNO order和JOB order 因此有两种可能，左分支中因为要和JOB的连接条件是JOB相等，且需要有序，因此还要对连接后的结果按照JOB进行排序，随后就是图二的两个可能 Index Scan JOB或者Segment Scan JOB，对于后一种还需要对Segment Scan后的结果再一次排序否则不符合Merged Join ，下图我认为默认使用Merged Join。

引用：
1.https://blog.csdn.net/weixin_43919570/article/details/122501131
2.Access Path Selection in a Relational Database Management System