DBMS Implementation 笔记 06: Query Evaluation

从本节开始，我们来看 DMBS Stack 的最后也是最顶层的 Query Evaluation。之前介绍的下层结构全都是为了支持这一层：

Query Processing

SQL Query 一般会显式地声明 (State) 需要什么形式的答案，但是不会讨论这些答案是怎么计算出来的。一个 Query Processor/Evaluator 所要做的就是：

• 接受查询的声明式描述 (Declarative description)
• 将查询 (Query) 解析为一个内部表达形式 (Internal representation) - relational algebra
• 确定回答查询的计划 - DBMS Operations
• 通过 DBMS Engine 执行计划

某些 DBMS 可以将查询计划 (Query Plan) 保存下来以便后续的使用。下面我蛮来看 Query Processor/Evaluator 的内部：

我们在之前已经说过，DBMS 在将一个 SQL Query 解析为一个 Relational Algebra (RA) 时，往往会有多种选择。比如对于 Selection 操作，对于不同类型的 Selection，会有不同的合适选择，比如：

select * from R where id = 100; 

这是一个基于 Primary Key 的等价条件 Selection。此时，如果 R 被作为一个 Hash Table 使用，效率会高得多

select * from S where age > 18 and age < 35; 

此时是一个基于 Non-key Attribute 的 Range Selection。此时如果对于 Relation S 有一个
B-Tree Index，那么效率会得到保障

select * from T where a = 1 and b = 'a' and c = 1.4; 

而对于上面这个联合等价条件的 Selection。使用 Multi-attribute Linear Hashing 会更有效率

我们将这些特别的 Relational Algebra 成为 RelOps。Query Processor 的一项重要任务就是：

• 给出要评估的RA表达式
• 找到RelOps的组合来有效地做到这一点

同时，也会有一个 Query Translator/Optimiser，它会考虑如下的信息：

1. Relation 的相关信息 (Size, Primary Key…)
2. Operations 的相关信息 (操作效率以及期望的结果)

RelOps在执行时实现为通过 Pipeline 或临时 Relations 进行通信的互通节点的集合。

Query Translation

这一小节来看 Query Processing 的第一步 Query Translation。顾名思义，就是将一个 SQL Query 转换为一个 RA 表达式。下面是一个具体的例子：

SQL: select name from Students where id=7654321;
被转换为：
RA: Proj[ name ] (Sel[id=7654321] Students)

在进行 Translation 的过程中，需要进行很多处理，比如词法分析器/解析器，映射规则，重写规则等，同时，在 Translation 的过程中，也会进行一定程度的优化 (Optimisation)，比如：

select * from Students where id = 54321 and age > 50;

1. Sel[age>50] (Sel[id=54321] Students)
2. Sel[id=54321&age>50] (Students)

此时考虑到执行效率，应该选 1，因为 id 是 Primary Key，选项一进行的操作会更少

Parsing SQL

解析任务就和一般的编程语言类似，他有以下几种语言要素 (Language Element)：

• Keywords：create, select, from, where …
• Identifier: Student, id, name, age …
• Operators: + - / * AND OR …
• Constant: ‘abc’, 123, 1.2 …

PSQL 中的 Parser 通过 lex/yacc 实现，将所有的 identifier 映射为小写，需要处理用户可扩展的 Operator 集，同时，需要充分使用 catalog。

Expression Rewriting Rule

Relation Algebra 表达式有着许多规则可以用于重写表达式，这些规则可以使得重写后的等价表达式可能变得更高效。除此以外，之所以要基于这些规则重写表达式，是因为可以简化/提升 SQL → RA 的结果，而且可以生成新的 Plan 以对比查询效率。

常见的 RA 表达式规则如下所示：

除此以外，我们也要知道，子查询 (Subquery) 是可以转换为 Join 的：

select c.code, count(*)  
from Courses c where c.id in (select cid from Enrolments)
group by c.code 

即可以转换为：

select c.code, count(*) 
from Courses c join Enrolments e on c.id = e.cid 
group by c.code 

但是，并非所有的子查询都能转换为 Join

select e.sid as student_id, e.cid as course_id 
from Enrolments e 
where e.sid = (select max(id) from Students) 

此时它的 RA 应表达为：

Val = max[id]Students
Res = π(s_id,c_id) (σ_sid=Val Enrolments)

在 PSQL 中，Views 的实现就是通过重写规则 (Rewrite Rule) 实现的。

– 创建一个新的 View：

create view COMP9315studes as select stu,mark from Enrolments where
course=‘COMP9315’;

– 对于当前的 View 进行一个 Query：

select stu from COMP9315studes where mark >= 50;

– 此时的 View COMP9315students 即为：

COMP9315studes = Proj[stu,mark] (Sel[course=COMP9315] (Enrolments))

– 而对其进行的 Query 即为：

Proj[stu] (Sel[mark>=50] (COMP9315studes))

– 我们可以将上面两个表达式进行结合：

Proj[stu] (Sel[mark>=50] (Proj[stu,mark] (Sel[course=COMP9315] (Enrolments))))

– 该表达式可以重写为

Proj[stu] (Sel[mark>=50 & course=COMP9315]Enrolments)

Query Optimization

事实上，我们之前在将 SQL Query 转换为 RA 时，就已经通过重写进行了一定程度的优化，在这一节，我们要来看看其他方面还进行了哪些优化，这是在 Query evaluation 中非常重要的一步。

Query Optimiser 会进行如下操作：

• 从 SQL 编译器接收 RA 表达式
• 生成一个 RelOps 序列来评估表达式
• Query Excution Plan 应提供有效的评估

对于 Query Optimiser 来说，”优化“ 这个词或许说的不够准确，它实际上是选择了一个好的计划，但这未必是最佳的。我们需要知道，我们直观观测到的查询时间实际上有 2 部分组成：

Observed Query Time = Planning time + Evaluation time

至于为何我们不去找到 ”最佳“ 计划，这是因为寻找这个最佳计划意味着需要穷举搜索所有可能的计划，对每个可能的计划都需要评估代价，这就使得成本变得很高。因此 DBMS 选取折中的方式，借助启发式的方法在一定范围内选择一个相对合理的高效执行计划。

现在我们来看该使用什么方法来进行优化 (Optimization)：

• Algebraic：equivalence，rewriting，heuristic
• Physical：execution cost，search-based
• Semantic：application properties, heuristics

所有的一切都是为了 ”最小化代价“。实际的 Optimizer 会使用 Algebraic + Physical，Sementic 比较难以实现。

上图是一个 Optimization 的例子。优化开始于一个 RA 表达式：Sel[c&d] [R JOIN S]. 这里 c 是 R 中的 Attribute，d 是 S 中的 Attribute。接着，进行 Algebraic 变换，可以将 Selection 向下延申，因为 Sel[c&d] [R JOIN S] = Sel_c [R] JOIN Sel_d [S]. 这就是我们确定的，相对合理的高效方法。最后，我们决定每个 Operation 该使用哪个版本，在这个例子中，我们决定使用 NestedLoop Join，对于 S 中的 Selection 使用 Linear Scan，R 中的 Selection 则使用 B-Tree 下面将整个优化算法用伪代码的形式表示出来：

translate SQL query to RAexp
for enough transformations RA' of RAexp {
	while (more choices for RelOps) {
		Plan = {}; i = 0; cost = 0
		for each node e of RA' (recursively) {
			ROp = select RelOp method for e
			Plan = Plan ∪ ROp
			cost += Cost(ROp) // using child info
		}
		if (cost < MinCost)
			{ MinCost = cost; BestPlan = Plan }
	}
}

评估 Query 的 cost 取决于：

• Size of relation：不仅是 database 中的relation，还有像 view 这样的临时 relation
• Access Mechanisms：indexing， hashing，join algorithm …
• Size/number of main memory buffers： 以及替换策略（比如 LRU, LFU … ）

对于 cost 的分析涉及估计：中间结果的大小以及读写 disk 的次数。

在上面例子中，我们得到了一个 RA 表达式组成的树，对于这棵树中的每个节点都需要决定访问方式 (RelOps)。下面是一个例子：

Inputs：

• 一个 RA Operation (σ, π, ⨝)
• 关于文件以及数据的相关信息
• database engine 支持的 operations 组成的列表

Outputs：

• 实现该 RA Operation 的特定 DBMS 操作

比如：Sel[name=‘John’ ∧ age>21] (Student)


已知 Relation Student 是基于 name 的 B-Tree Index 结构。而 DBMS 大多是支持 B-Tree 操作的。那么，此时就可以使用 B-Tree 搜索进行 Sel_name，使用 Linear Scan 进行 Sel_age，在此过程中，会生成 2 个 temp file：

tmp[i] := BtreeSearch[name=‘John’] (Student)
tmp[i+1] := LinearSearch[age>21] (tmp[i])

下面介绍一些选择 Selection 操作 (σ) 的准则：

基本情况	Selection 操作
σA=c ® 并且 R 基于 A 索引 (Index)	indexSearch[A=c] ®
σA=c ® 并且 R 基于 A 哈希 (Hashing)	hashSearch[A=c] ®
σA=c ® 并且 R 基于 A 排序 (Sort)	binarySearch[A=c] ®
σA≥c ® 并且 R 基于 A 簇索引 (Cluster Index)	indexSearch[A=c] ®
σA≥c ® 并且 R 基于 A 哈希 (Hashing)	linearSearch[A>=c] ®

下面介绍一些选择 Join 操作 (⨝) 的准则：

基本情况	Join 操作
R ⨝ S 并且 R 适合 memory buffers	bnlJoin(R,S)
R ⨝ S 并且 S 适合 memory buffers	bnlJoin(S,R)
R ⨝ S 并且 R, S 基于 Join Attr 排序	smJoin(R,S)
R ⨝ S 并且 R 基于 Join Attr 索引	inlJoin(S,R)
R ⨝ S 并且没有索引也没有排序	hashJoin(R,S)

(bnl = block nested loop; inl = index nested loop; sm = sort merge)

PostgreSQL Query Optimization

在 PostgreSQL 中，进行优化时：

输入：Parser 返回的 Tree of Query Node

输出：Query Executer 将要使用的 Tree of Plan Nodes

Query Cost Estimation

如果不具体执行 Plan，就无法知道确切的 cost。因此，Query Optimizer 会通过以下方式估计 cost：

• 执行 Operations 的 cost
• 结果的规模 (Result Size)

Result size 通过对于 Relations 的统计信息进行评估，比如：

• r_S：Relation S 中有多少 Tuples
• R_S：Relation S 中 Tuple 的平均尺寸
• V(A,S)：属性 A 的独特取值
• min(A,S)：属性 A 的最小值
• max(A, S)：属性 A 的最大值

Estimating Projection Result Size

首先来看对于 Projection 操作的估计。

1. 首先对看 Result 中的 Tuple 数量。Result 中的 Tuple 数量实际上等于 Input 中的 Tuple 数量，r_out = r_T
2. 之后来看 Result 中 Tuple 的 Size。此时 Tuple Size 小于等于 Input 中的 Tuple Size。R_out = sizeof(a) + sizeof(b) + … + tuple-overhead

我们假设 Page Size 为 B。那么通过这些数据就可以计算得出 Page 的数量：b_out = ceil(r_T / c_out ), where c_out = floor(B/R_out )

Estimating Selection Result Size

在进行 Selection 时，一般有一部分 Tuples 会满足我们设立的条件。一般我们认为 Attribute 的值是均匀分布的。比如我们考虑如下所示的一个 Query：

select * from Parts where colour='Red';

假如在 Relation Parts 中，Attribute Color 共有 4 种不同的取值：V(colour,Parts)=4；共有 r = 1000 个 Tuples，那么 |σ_colour=red (Parts)|=250。换言之 | σ_A=c ® | ≅ r_R / V(A,R)。 一般 PostgreSQL 会启发式地认为 | σ_A=c ® | ≅ r/10。

而对于一个不等条件 Selection：

select * from Enrolment where year > 2015;

==可以通过均匀分布假设，r 以及最大/最小年份来进行估计。==假设 min(year) = 2010, max(year) = 2019, |Enrolments| = 10⁵，那么：

• 2010-2019 共有 10⁵ Enrolments，那么平均 10000 Enrolments/year
• 那么从 2016 年开始，大约有 40000 Enrolments

一般 PostgreSQL 会启发式地认为 | σ_A > c ® | ≅ r/3。

接下来看不等条件下的 Selection 操作：

select * from Enrolment where course <> 'COMP9315';

此时可以通过均匀分布假设，r 以及域尺寸 (Domain Size) 来进行估计。 假设 Enrolments 中。courses 的取值有 2000 种，即 | V(course,Enrolment) | = 2000，那么 | σ_A <> c (E) | = r * 1999/2000。一般 PostgreSQL 会启发式地认为 | σ_A<>c ® | ≅ r。

上述的几种情况都是基于均匀分布假设这个关键的思想，那么，如果属性值的分布并不均匀呢？此时需要收集存储于 Attribute/Relation 的值的统计信息，把这些以直方图 (Histogram) 的形式存储在 Relation 的元数据 (Meta Data) 中。比如对于之前的 Relation Parts 中 Attribute Color 的取值，可能会有：

White: 35% Red: 30% Blue: 25% Silver: 10%

此时就需要 Histogram 存储的数据作为基础来决定选择的 Tuples 的数量。但是缺点在于存储 Histogram 需要额外的开销。

Estimating Join Result Size

对于 Join 操作，我们只考虑基于共有属性的等价条件 Join，可能会有以下情况：

1. values(R.a) ∩ values(S.a) = {} ===> size(R ⨝_a S) = 0
2. uniq(R.a) and uniq(S.a) ===> size(R ⨝_a S) ≤ min(|R|, |S|)
3. pkey(R.a) and fkey(S.a) ===> size(R ⨝_a S) ≤ |S|

目前为止我们已经了解了多个 Cost Estimation Strategy。但我们需要时刻谨记，这些都是粗略的非准确估计，之所以这么做是出于对于 Optimization Cost 的考量，我们不希望这个环节占据过长时间。

Query Execution

现在我们来看 Query Processing 的最终阶段 - 执行阶段 (Execution Phase)。在这一阶段中，DB Engine 接受从 Optimizer 而来的执行计划 (Execution Plan)，执行该 Plan 并得到 Result Tuples。

下面给出一个具体的例子：

SQL Query 为：

select s.name, s.id, e.course, e.mark 
from Student s, Enrolment e 
where e.student = s.id and e.semester = '05s2';

Translator 先将其映射为一个 RA Expression：

π_{name,id,course,mark} (Stu ⨝_{e.student=s.id} (σ_{semester=05s2} Enr))

Optimizer 会为每个 Operation 选择特定的 RelOps，将其映射为一个 Execution Plan：

Temp1 = BtreeSelect[semester=05s2] (Enr)
Temp2 = HashJoin[e.student=s.id] (Stu,Temp1)
Result = Project[name,id,course,mark] (Temp2)

通过上面的例子，我们可以很清楚地看到，所谓的 Execution Plan 就是由一系列特定版本 RelOps 组成的，这些 RelOps 会被执行，以得到结果 Tuples。将结果从一个 Operator 传递到下一个 Operator 由 2 种方法：

1. materialization：将中间结果写到 disk，再从 disk 读取它们
2. pipelining：通过 Memory Buffer 生成和传递中间结果

materialization	pipelining
第一个 Operator 读取 Input，将 Output 写入 Disk；下一个 Operator 将 Disk 中的 Tuple 作为输入；实际上 Temp Tables 作为实 Tables 生成	Operators 同时执行 producer/consumer pairs。构造为交互的迭代器（open； while(next)；close）
优点在于中间结果会具备 File Structure	优点在于不需要对 Disk 进行访问
缺点在于需要对 Disk 进行读写操作，也需要 Disk 空间以存储中间结果	缺点在于更高一级的 Operator 需要通过 Linear Scan 访问输入，或者需要充足的 Memory Buffers 来存放所有的 Outputs

这里我们着重看一下 “迭代器 (Iterator)” ，迭代器会以 “流 (Stream)” 的形式生成结果。DBMS 会把之前得到的 Execution Plan 中的每一个 Operation 变为一个 iterator。

下面看一个 Pipelining 的具体例子：

select s.id, e.course, e.mark 
from Student s, Enrolment e 
where e.student = s.id and e.semester = '05s2' and s.name = 'John'; 

通过 RA 树节点之间的通信进行评估：

在这幅图中，需求从上向下传播，需要的 Tuple 从下向上传递

但是我们需要注意的是，Pipelining 不能避免所有的 Disk 访问。有一些操作会需要使用多轮，那么数据就需要在第一轮中写入，后面进行一轮轮读取。因此，在一个 Operation 中，可能需要进行 Disk 读写，但是 Operators 之间，不需要 Disk 读写。

Query Performance Tuning

目前位置我们已经了解了 Query Processing 的全部过程，现在我们来看该如何对 Query 进行调优。即如果DBMS 花了很长的时间来回答 Query，该怎么办。提升性能涉及以下几个方面：

1. 一般来说，SQL Query 大多是某个应用 (Application) 的一部分，所以，我们可以选择提升该应用的整体效率
2. 降低 Query/Transaction 的响应时间
3. 提升 Transaction 整体的吞吐量 (Throughput)

从某种层面来说，Query Optimizer 消除了来自于 DB 开发者的一些干扰和噪音，这是因为它会在 “最佳” 执行计划 (Execution Plan) 上以自己的判断做出决定。 除此以外，调优 (Tuning) 还需要我们考虑以下问题：

• 会用到什么 Query/Transaction
• 每个 Query / Transaction 的使用频率
• 对于 Query/Transaction 是否有时间限制
• 都某些 Attribute 是否有特别的限制（比如取值是否会有重复）
• 多久更新一次