数据库内核：PostgreSQL 查询优化

查询评估

查询评估介绍

之前介绍的下层结构全都是为了支持这一层查询评估（Query Evaluation），它处于最顶层。

SQL中的查询声明了它需要什么样的答案（声明性），但是没有说明这些答案是如何计算得到的（程序性）。

查询评估器/处理器：

• 获取查询的声明性描述（在 SQL 中）
• 解析查询到内部表示（关系代数）
• 确定回答查询的计划（表示为 DBMS 操作）
• 通过 DBMS 引擎执行方法（生成结果元组）

一些数据库管理系统会保存查询计划以供之后使用。

以下是查询评估器/处理器的内部结构：

数据库管理系统在将一个 SQL 查询解析为一个关系代数时，往往会有多种选择。例如，有几个选择的“版本”可用，对特定类型的选择，某些“版本”会比较有效：

select * from R where id = 100;	# – hashing 
select * from S where age > 18 and age < 35;  # – Btree index
select * from T where a = 1 and b = ’a’ and c = 1.4;	# – MALH file

我们将这些特别的关系代数称为 RelOps。查询处理器（Query Processor）的一项重要任务就是：

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

查询优化器（Query Translator/Optimiser）会考虑如下的信息：

• 关系的相关信息，大小、主键等等
• 操作的相关信息（操作效率以及期望的结果）

RelOps 在执行时实现为通过管道或临时关系进行通信的互通节点的集合。

一些术语的变化：

• SQL 查询的关系代数表达式 = 中间查询表示 = 逻辑查询计划

• 作为 RelOps 集合的执行计划 = 查询评估计划 = 查询执行计划 = 物理查询计划

查询翻译（Query Translation）

查询翻译的任务就是将 SQL 转化为 RA 表达式。举个例子就是将 select name from Students where id=7654321; 转化为 $Pro{j}_{[name]}(Se{l}_{[id=7654321]}(Students))$。

在进行翻译的过程中，需要进行很多处理，比如词法分析器/解析器，映射规则，重写规则等。同时，在翻译的过程中，也会进行一定程度的优化。例子如下：select * from Students where id = 54321 and age > 50; 会被转化为 $Se{l}_{[age>50]}(Se{l}_{[id=54321]}(Students))$ 而不是 $Se{l}_{[id=54321\mathrm{\&}age>50]}(Students)$。

解析 SQL（Parsing SQL）

解析任务与编程语言的任务相似。其语言元素如下：

• 关键字（keywords）：create, select, from, where,...
• 标识符（identifiers）：Students, name, id, CourseCode,...
• 操作符（operators）：+,-,=,<,>,AND,OR,NOT,IN,...
• 常量（constants）：’abc’, 123, 3.1, ’01-jan-1970’,...

PostgreSQL 中的解析器是通过 lex/yacc 实现的（在 src/backend/parser 目录下），将标识符映射为小写，需要处理用户可扩展的操作符集合，广泛使用目录（在 src/backend/catalog 目录下）。

表达式重写规则

因为 RA 是一个定义明确的正式系统。RA 表达式上存在许多代数定律，这可以作为表达式重写的基础。重写的目的是为了得到等价且效率更高表达式。除此以外，之所以要基于这些规则重写表达式，是因为可以简化/提升 SQL 转化 RA 的结果，而且可以生成新的计划以对比查询效率。

关系代数定律

• 交换法和关联法

  • $R\bowtie S\leftrightarrow S\bowtie R,(R\bowtie S)\bowtie T\leftrightarrow R\bowtie (S\bowtie T)$ (自然连接)
  • $R\cup S\leftrightarrow S\cup R,(R\cup S)\cup T\leftrightarrow R\cup (S\cup T)$
  • $R{\bowtie }_{Cond}S\leftrightarrow S{\bowtie }_{Cond}R$
  • ${\sigma }_c({\sigma }_d(R))\leftrightarrow {\sigma }_d({\sigma }_c(R))$

• 选择分解（其中 c 和 d 是选择条件）

  • ${\sigma }_{c\wedge d}(R)\leftrightarrow {\sigma }_c({\sigma }_d(R))$
  • ${\sigma }_{c\vee d}(R)\leftrightarrow {\sigma }_c(R)\cup {\sigma }_d(R)$

• 选择推导

  • ${\sigma }_c(R\cup S)\leftrightarrow {\sigma }_{c}R\cup {\sigma }_{c}S,{\sigma }_c(R\cap S)\leftrightarrow {\sigma }_{c}R\cap {\sigma }_{c}S$
  • ${\sigma }_c(R\bowtie S)\leftrightarrow {\sigma }_c(R)\bowtie S$，如果 c 是只来自关系 R 的属性
  • ${\sigma }_c(R\bowtie S)\leftrightarrow R\bowtie {\sigma }_c(S)$，如果 c 是只来自关系 S 的属性
  • ${\sigma }_{c^{\prime }\wedge c^{″}}(R\bowtie S)\leftrightarrow {\sigma }_{c^{\prime }}(R)\bowtie {\sigma }_{c^{″}}(S)$，c' 是只来自关系 R 的属性，c'' 是只来自关系 S 的属性。

• 投影规则的重写

  • ${\pi }_{L_1}({\pi }_{L_2}(...{\pi }_{L_n}(R)))\to {\pi }_{L1}(R)$，除了最后一个投影，其余的都可以忽略
  • ${\pi }_L(R{\bowtie }_{c}S)\leftrightarrow {\pi }_L({\pi }_M(R){\bowtie }_c{\pi }_N(S))$，这里的 M 和 N 必须包含 c 所需的所有属性

查询重写

子查询（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=ma{x}_{[id]}(Students)$ 和 $Res={\pi }_{(sid,cid)}({\sigma }_{sid=Val}(Enrolments))$

在 PostgreSQL 中，视图（views）是通过重写规则实现的。

• 创建一个新的视图：create view COMP9315studes as select stu,mark from Enrolments where course=’COMP9315’;
• 对于当前的视图进行一个查询：select stu from COMP9315studes where mark >= 50;
• 此时的 COMP9315students 视图即为：$COMP9315students=Pro{j}_{[stu,mark]}(Se{l}_{[course{=}^{\prime }COMP{9315}^{\prime }]}(Enrolments))$
• 而对其进行的查询即为：$Pro{j}_{[stu]}(Se{l}_{[mark>=50]}(COMP9315studes))$
• 可以将上面两个表达式进行结合：$Pro{j}_{[stu]}(Se{l}_{[mark>=50]}(Pro{j}_{[stu,mark]}(Se{l}_{[course{=}^{\prime }COMP{9315}^{\prime }]}(Enrolments))))$
• 而上面这个结合后的表达式可以重写为：$Pro{j}_{[stu]}(Se{l}_{[mark>=50\mathrm{\&}course=COMP9315]}(Enrolments))$

练习：将下面 SQL 转化为 RA 表达式（尽可能高效）

假设 R.id 是主键，关系 R 对 id 进行了哈希，而 R.b 属性有 B 树索引。

select * from R where a > 5;
select * from R where id = 1234 and a > 5;
select R.a from R, S where R.i = S.j;
select R.a from R join S on R.i = S.j;
select * from R, S where R.i = S.j and R.a = 6;
select R.a from R, S, T where R.i = S.j and S.k = T.y;

• ${\sigma }_{a>5}(R)$
• ${\sigma }_{a>5}({\sigma }_{id=1234}(R))$
• ${\pi }_{R.a}(R{\bowtie }_{R.i=S.j}S)$
• ${\pi }_{R.a}(R{\bowtie }_{R.i=S.j}S)$
• ${\sigma }_{R.a=6}(R){\bowtie }_{R.i=S.j}(S)$
• $R{\bowtie }_{R.i=S.j}(S{\bowtie }_{S.k=T.y}T)$

查询优化

查询优化简介

在将 SQL 查询转换为 RA 表达式时，就已经通过重写进行了一定程度的优化。而查询优化器的目的就是将 RA 表达式转化为高效的评估计划。

查询优化是查询评估的关键步骤。查询优化器从 SQL 解析器中获取关系代数表达式，然后生成生成 RelOps 序列来评估表达式，查询执行计划应该提供高效的评估。对于查询优化器来说，”优化“ 这个词或许说的不够准确，因为它实际上是选择了一个好的计划，但这个计划未必是最佳的。可见的查询时间等于计划时间加上评估时间。

那为什么不直接生成 “最佳” 计划呢？这是因为寻找这个最佳计划意味着需要穷举搜索所有可能的计划，对每个可能的计划都需要评估代价，这就使得成本变得很高。因此数据库管理系统选取折中的方式，借助启发式的方法在一定范围内选择一个相对合理的高效执行计划。

优化的方法

优化的方法可以分为三类：

• 代数的：根据等价关系、重写、启发式
• 物理的：执行成本、基于搜索
• 语义的：应用属性、启发式

这些方法都是以最小化（或至少降低）“成本”为目标而驱动。真正的查询优化器是使用前两者方法的组合，第三类实现起来比较困难且成本高。

以下是一个优化转换的例子：

对于 Join，还可以考虑 sort/merge join 和 hash join。下面将整个优化算法用伪代码的形式表示出来：

translate SQL query to RAexp
for enough transformations RA of RAexp {	// 给定一个关系代数表达式RAexp，通过一系列的转换和优化，生成 RA'
	while (more choices for RelOps) {	// 遍历不同的关系操作选择（RelOps），以构建最优的执行计划
		Plan = {}; i = 0; cost = 0
		for each node e of RA (recursively) {	// 对 RA' 的每个节点 e 进行递归处理
			ROp = select RelOp method for e	// 对于每个节点 e，通过选择适合的关系操作（RelOp）方法
			Plan = Plan ∪ ROp	// 将该操作添加到计划中
			cost += Cost(ROp) // 信息计算成本
		}
		if (cost < MinCost)
			{ MinCost = cost; BestPlan = Plan }
	}
}

评估查询的成本由以下因素决定：

• 关系的大小（数据库关系和临时关系）
• 访问机制（索引、散列、排序、Join 算法）
• 主内存缓冲区的大小和数量以及页面替换策略

成本分析涉及估算：

• 中间结果的大小
• 读写磁盘的次数

例子

输入：

• 一个 RA 操作 （$\sigma ,\pi ,\bowtie$）
• 关于文件以及数据的相关信息
• 数据库引擎支持的操作组成的列表

输出：实现该 RA 操作的特定数据库管理系统操作

例如一个 RA 操作为 $Se{l}_{[name{=}^{\prime }Joh{n}^{\prime }\wedge age>21]}(Student)$。

已知关系 Student 是基于 name 属性的 B 树索引结构。而数据库管理系统基本都是支持 B 树操作的。那么，此时就可以使用 B 树搜索进行 $Se{l}_{[name]}$，使用线性搜索进行 $Se{l}_{[age]}$，在此过程中，会生成 2 个 临时文件：

• $tmp[i]=BtreeSearc{h}_{[name=John]}(Student)$
• $tmp[i+1]=LinearSearc{h}_{[age>21]}(tmp[i])$

在可能的情况下，使用流水线以避免将 $tmp[i]$ 存储在磁盘上。

一些准则

• 选择 Selection 操作 $\sigma$ 的准则：

  • ${\sigma }_{A=c}(R)$，R 在属性 A 上有索引，因此使用规则 $indexSearc{h}_{[A=c]}(R)$
  • ${\sigma }_{A=c}(R)$，R 在属性 A 上有哈希，因此使用规则 $hashSearc{h}_{[A=c]}(R)$
  • ${\sigma }_{A=c}(R)$，R 在属性 A 上有顺序，因此使用规则 $binarySearc{h}_{[A=c]}(R)$
  • ${\sigma }_{A\ge c}(R)$，R 在属性 A 上有簇索引，因此使用规则 $indexSearc{h}_{[A=c]}(R)$
  • ${\sigma }_{A\ge c}(R)$，R 在属性 A 上有哈希，因此使用规则 $SlinearSearc{h}_{[A\ge c]}(R)$

• 选择 Join 操作 $\bowtie$ 的准则：

  • $R\bowtie S$，关系 R 可以放入内存缓冲区，因此使用规则 $bnlJoin(R,S)$
  • $R\bowtie S$，关系 S 可以放入内存缓冲区，因此使用规则 $bnlJoin(S,R)$
  • $R\bowtie S$，关系 R 和 S 的 Join 属性都是排序了的，因此使用规则 $smJoin(R,S)$
  • $R\bowtie S$，关系 R 的 Join 属性有索引，因此使用规则 $inlJoin(R,S)$
  • $R\bowtie S$，关系 R 和 S 的 Join 属性没有索引，也没有顺序，因此使用规则 $hashJoin(R,S)$

注意：$bnl$ = block nested loop，$inl$ = index nested loop，$sm$ = sort merge

成本评估

在不执行计划的情况下，是不知道其精确的代价的。因此，查询优化器通过以下方式估算成本：

• 执行操作的成本
• 结果的大小（它会影响下一个操作的成本）

通过关系统计信息来估计的结果大小，例如：

• $r_S$：关系 S 的基数
• $R_S$：关系 S 中元组的平均大小
• $V(A,S)$：关系 S 中属性 A 的不同取值
• $min(A,S)$：关系 S 中属性 A 的最小值
• $max(A,S)$：关系 S 中属性 A 的最大值

估计投影结果的大小：比较直接，因为我们知道输出元组的数量 $r_{out}=|{\pi }_{a,b,...}(T)|=|T|=r_T$

估计选择结果的大小：选择性就是期望满足条件的元组的比例。

• 一般都假设属性值是均匀分布的。
  • 考虑以下 SQL select * from Parts where colour=’Red’;，如果 $V(colour,Parts)=4,r=1000$ 那么就可以推出 $|{\sigma }_{colour=red}(Parts)|=250$。一般来说，$|{\sigma }_{A=c}(R)|\cong \frac{r_R}{V(A,R)}$。而在 PostgreSQL中，使用的是启发式：$|{\sigma }_{A=c}(R)|\cong \frac{r_R}{10}$。
  • 而对于一个不等条件选择操作：select * from Enrolment where year > 2015;，可以通过均匀分布的假设，$r$ 以及最大/最小年份来进行估计。假设 $min(year)=2010,max(year)=2019,|Enrolment|={10}^5$。那么 ${10}^5$ 个元组从 2010 年到 2019 年平均分，每一年 10000 个元组。所以估计 2016 年开始有 40000 个元组。在一些启发式的系统中，对于一个不等条件选择，它的估计是 $|{\sigma }_{A>c}(R)|\cong \frac{r_R}{3}$。
  • 而对于多个不等条件选择操作：select * from Enrolment where course <> ’COMP9315’;，可以通过均匀分布的假设，$r$ 以及域大小来进行估计。假设 $|V(course,Enrolment)|=2000$，那么 $|{\sigma }_{A<>c}(E)|=r\ast \frac{1999}{2000}$。在一些启发式的系统中，对于多个不等条件选择，它的估计是 $|{\sigma }_{A<>C}(R)|\cong r$。
• 当属性值不是服从均匀分布时：
  • 此时需要收集存储于属性/关系的值的统计信息，把这些以直方图的形式存储在关系的元数据中。例如，分布可能是 $White=35\mathrm{\%},Red=30\mathrm{\%},Blue=25\mathrm{\%},Silver=10\mathrm{\%}$，此时就需要直方图存储的数据作为基础来决定选择的元组的数量。但是缺点在于存储直方图 需要额外的开销。

估计 Join 结果的大小：其分析依赖于有关数据/关系的语义知识。考虑一个普通的等式 Join $R{\bowtie }_{a}S$。

• 第一种情况：当 $V(a,R)\cap V(a,S)=\left\{\right\}$ 时，$size(R{\bowtie }_{a}S)=0$
• 第二种情况：当 $uniq(R.a),uniq(S.a)$ 时，$size(R{\bowtie }_{a}S)\le min(|R|,|S|)$
• 第三种情况：当 $pkey(R.a),fkey(S.a)$ 时，$size(R{\bowtie }_{a}S)\le |S|$

不准确的成本估算可能导致评估计划不佳。上述方法可能会（有时）给出不准确的估计。为了获得更准确的成本估算：

• 更多的时间：选择性的复杂计算
• 更多的空间：数据值直方图的存储

无论哪种方式，优化过程的成本都更高，这需要在优化器性能和查询性能之间进行权衡。

练习

假设所有的属性是均匀分布的，属性之间是相互独立的，$V(A,R)=10,V(B,R)=100,r=1000$。对以下查询给出预期结果数量的公式。

• select * from R where not A=k;：$|{\sigma }_{A=k}(R)|\cong \frac{r}{V(A,R)}=\frac{1000}{10}=100$
• select * from R where A=k and B=j;：$temp=|{\sigma }_{A=k}(R)|\cong \frac{r}{V(A,R)}=\frac{1000}{10}=100,|{\sigma }_{B=j}(temp)|\cong \frac{temp}{V(B,R)}=\frac{100}{100}=1$
• select * from R where A in (k,l,m,n);：$|{\sigma }_{A=k}(R)|\cong \frac{r}{V(A,R)}=\frac{1000}{10}=100$，每个常量都相同，因此为 400.

对于以下 Join 查询，输出元组的数量为多少？

• select * from R, S where R.s = S.id;，这里的 S.id 是主键，R.s 是相应的外键：$size(R{\bowtie }_{a}S)\le |S|$
• select * from R, S where R.s <> S.id;，这里的 S.id 是主键，R.s 是相应的外键：$size(R{\bowtie }_{a}S)\le min(|R|,|S|)$
• select * from R, S where R.x = S.y;，$dom(R.x)\ne dom(S,y)$：$size(R{\bowtie }_{a}S)=0$

PostgreSQL 中的查询优化

输入：解析器返回的查询节点树

输出：查询执行者使用的计划节点树

状态信息包含在 PlannedStmt 节点中，中间数据结构是路径节点树，一个路径表示查询的一个评估顺序。所有类型的节点定义在 include/nodes/*.h 中。

查询优化分两个阶段进行（解析后）：

• 重写：使用 PostgreSQL 的规则系统，查询树会被展开，例如视图定义。
• 计划和优化：通过对生成的路径进行基于成本的分析，通过两种不同的路径生成器之一，从考虑的所有路径中选择成本最低的路径，生成相应的计划。