数据库内核：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; 转化为 \(Proj_{[name]}(Sel_{[id=7654321]}(Students))\)。

Query Translation

在进行翻译的过程中，需要进行很多处理，比如词法分析器/解析器，映射规则，重写规则等。同时，在翻译的过程中，也会进行一定程度的优化。例子如下：select * from Students where id = 54321 and age > 50; 会被转化为 \(Sel_{[age>50]}(Sel_{[id=54321]}(Students))\) 而不是 \(Sel_{[id=54321\&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_cS,\sigma_c(R\cap S)\leftrightarrow \sigma_c R\cap\sigma_cS\)
    • \(\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'\wedge c''}(R\bowtie S)\leftrightarrow \sigma_{c'}(R)\bowtie \sigma_{c''}(S)\)，c' 是只来自关系 R 的属性，c'' 是只来自关系 S 的属性。

  • 投影规则的重写

    • \(\pi_{L_1}(\pi_{L_2}(...\pi_{L_n}(R)))\rightarrow\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=max_{[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=Proj_{[stu,mark]}(Sel_{[course=’COMP9315’]}(Enrolments))\)
  • 而对其进行的查询即为：\(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))\)

练习：将下面 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 操作为 \(Sel_{[name='John'\wedge age>21]}(Student)\)。

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

  • \(tmp[i]= BtreeSearch_{[name=John]}(Student)\)
  • \(tmp[i+1]=LinearSearch_{[age>21]}(tmp[i])\)

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

一些准则

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

    • \(\sigma_{A=c}(R)\)，R 在属性 A 上有索引，因此使用规则 \(indexSearch_{[A=c]}(R)\)
    • \(\sigma_{A=c}(R)\)，R 在属性 A 上有哈希，因此使用规则 \(hashSearch_{[A=c]}(R)\)
    • \(\sigma_{A=c}(R)\)，R 在属性 A 上有顺序，因此使用规则 \(binarySearch_{[A=c]}(R)\)
    • \(\sigma_{A\geq c}(R)\)，R 在属性 A 上有簇索引，因此使用规则 \(indexSearch_{[A=c]}(R)\)
    • \(\sigma_{A\geq c}(R)\)，R 在属性 A 上有哈希，因此使用规则 \(SlinearSearch_{[A\geq 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*\frac{1999}{2000}\)。在一些启发式的系统中，对于多个不等条件选择，它的估计是 \(|\sigma_{A<>C}(R)|\cong r\)。
  • 当属性值不是服从均匀分布时：
    • 此时需要收集存储于属性/关系的值的统计信息，把这些以直方图的形式存储在关系的元数据中。例如，分布可能是 \(White=35\%,Red=30\%,Blue=25\%,Silver=10\%\)，此时就需要直方图存储的数据作为基础来决定选择的元组的数量。但是缺点在于存储直方图 需要额外的开销。

估计 Join 结果的大小：其分析依赖于有关数据/关系的语义知识。考虑一个普通的等式 Join \(R\bowtie_aS\)。

  • 第一种情况：当 \(V(a,R)\cap V(a,S)=\left\{\right\}\) 时，\(size(R\bowtie_aS)=0\)
  • 第二种情况：当 \(uniq(R.a),uniq(S.a)\) 时，\(size(R\bowtie_aS)\leq min(|R|,|S|)\)
  • 第三种情况：当 \(pkey(R.a),fkey(S.a)\) 时，\(size(R\bowtie_aS)\leq |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_aS)\leq |S|\)
  • select * from R, S where R.s <> S.id;，这里的 S.id 是主键，R.s 是相应的外键：\(size(R\bowtie_aS)\leq min(|R|,|S|)\)
  • select * from R, S where R.x = S.y;，\(dom(R.x)\neq dom(S,y)\)：\(size(R\bowtie_aS)=0\)

PostgreSQL 中的查询优化

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

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

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

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

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

PostgreSQL 中的查询优化

posted @ 2023-07-02 00:03  FireOnFire  阅读(67)  评论(0)  编辑  收藏  举报