CMU-15445数据库系统导论笔记<九>—执行优化因为SQL是声明性的，所以查询只告诉DBMS要计算什么，而不告诉DB

持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第22天，点击查看活动详情

执行优化

因为SQL是声明性的，所以查询只告诉DBMS要计算什么，而不告诉DBMS如何计算。在这个前提之下，各家数据库用起来的实际流程都差不多，区别在于DBMS怎样执行SQL更加高效，这就是查询优化。

查询优化有两种高级策略。第一种方法是使用静态规则或启发式。启发式算法将查询的部分与已知的规则相匹配。这些规则改变了SQL执行计划，进而提升效率。尽管这些规则可能需要查阅目录以了解数据的结构，但它们永远不需要检查数据本身，也就是说不会利用到数据库内部数据。

另一种方法是使用基于成本的搜索来读取数据，并估计执行等效计划的成本。成本模型选择成本最低的计划。查询优化是构建数据库管理系统最困难的部分。大部分数据库这两种方法都会涉及到。

一些系统试图应用机器学习来提高优化器的准确性和效率，比如DB2中的LEO算法，但由于其不可解释性难以应用，直到现在也没有大型数据库管理系统部署基于这种技术的优化器。

一个SQL语句从字符串到可执行的算子的过程如下图所示：

可执行的算子就是优化的起点，两种优化思路都在这里优化执行计划。

启发式方法

逻辑查询优化

等价关系表达式

如果两个关系表达式是等价的，则它们得到相同的结果。换言之，它们可以进行转换，这种转换逻辑计划的底层关系代数表示的技术称为查询重写。数据库可以直接在逻辑上判断是否等价，不需要任何数据辅助。这是逻辑优化最基础的原理。

因为没有代价模型，因此我们无法通过比较两个关系表达式哪个更好来决定使用哪个可搜索的执行计划，我们只能使用匹配规则将逻辑计划转换为等效的逻辑计划，进而达到“理论”最优。

具体的，我们可以通过以下原则优化一个执行计划：

尽早过滤（谓词下推）
重新排序谓词，以便DBMS首先应用最有选择性的谓词。
分解复杂谓词并向下推（拆分连接谓词）

用一个例子来看基础的优化思路

嵌套子查询

另一个优化方法是优化嵌套子查询，有两种方法，一是重写成一个查询，另一个是把子查询提前计算出结果，作为常数放置在总的执行计划里。

表达式重写

提前定义一些规则，找到符合规则的表达式，并将它们重写，第一种是删除不必要的谓词，例如：

第二种是合并谓词，例如

基于代价的方法

估算代价

在基于代价的方法中，最基本的是估算代价，现代DBMS估算的执行代价来自三个方面

物理代价
- 估算CPU周期，IO时间，缓存未命中，内存访问，提前获取等。。
- 极其依赖硬件，因此经常出现在数据库一体机上（Oracle，SQL server）
逻辑代价
- 估计算子整体开销，不考虑算子所用的算法特征
- 需要估计算子所处理的数据（统计信息）
算法代价
- 分析算子的详细步骤，根据时间复杂度估计

磁盘开销

基于磁盘的数据库始终需要注意，有多少磁盘IO需要执行总是占执行计划的开销大头。因此磁盘IO是重点要估计的指标。

例一：PostgreSQL Cost Model（开源数据库）

PG在代价的估计上完全基于经验，例如

在内存中处理一个元组的速度大概是磁盘的400倍
顺序IO比随机IO快4倍

这些参数没有逻辑，都是大量调参得来的结果，这看起来不可思议，但其实还算比较有效，在缺少信息时进行动态调整很可能会造成更差的性能。

例二：DB2 Cost Model（商业数据库）

DB2作为商业数据库，其代价估计灵活且强大，具体的说，与以下方面有关：

元数据中的数据库特征
硬件环境
存储设备特征
通信带宽
内存资源
并发环境

统计信息

如果想系统解决这个问题，首先要对问题进行抽象，要想近似估计查询计划的代价，DBMS就要维护表、属性和索引的内部统计信息，当内部数据发生变化的时候，DBMS更新对应的统计信息。

具体的，对于一个关系R，DBMS一般要维护以下信息：

$N_{R}$ ：关系R的元组数
$V(A, R)$ ：对于属性A，可能的不同取值数，例如对于属性sex，V一般为2

基于这两种信息，我们在一定假设下能够算出每个属性的selection cardinality，也叫选择性，数学表示为

SC(A,R)=\frac{N_{R}}{V(A,R)}

此时SC表示A属性不同取值的可能占总数的数量，比如有元组100条，那么对于sex属性，男女应当各有50条，即 $SC(sex_{1}, R)=50$ 和 $SC(sex_{2}, R)=50$ 。

有了SC，我们能够快速估计出关系R中某个属性某种取值的数据量大小，相当于我们能够在简单谓词的约束下求出算子执行的数据量吞吐量大小，也就能近似估计出执行的开销。

具体的，我们把最简单的谓词，也就是等值谓词，比较准确的估计出来，比如

SELECT * FROM people WHERE id = 123;

但是更复杂的谓词，比如范围谓词和连表谓词则更难估计出来，比如

SELECT * FROM people, house WHERE people.pno = house.pno AND people.val > 1000;

这种情况下我们首先观察简单谓词的特征，可以看出谓词的选择性和谓词成立的概率几乎是一样的，因此我们可以使用分析概率的方法去分析复杂谓词的选择性，具体的说，假设数据是均匀的，各个谓词的选择性是独立的，那么复杂谓词的选择性能够使用简单谓词的选择性组合计算出来。

前面的假设

之前我们对统计信息的应用都是基于以下几个假设：

Uniform Data：数据分布是均匀的，比如男女比例就是1：1
Independent Predicates：应用谓词的属性之间没有关联，各自独立，例如性别和年龄一般没关系
Inclusion Principal：对于AB表，A表的每一个数据都能在B表上找到匹配

假设不成立的办法

各个数据面对这些假设不成立的方法往往各不相同，并且一般都很复杂，这里只介绍一些基本的思路，具体的还得看paper。

直方图 histograms

真实数据往往是歪七扭八的，我们需要一种将获取的数据变均匀的方法，直方图是一种将数据分桶的思路，具体的直接看图就能懂

通过分桶，原始凌乱的离散（连续）数据分成了相对均匀的统计信息。其中类似第三种等深直方图的方法现在也被大部分数据库采用

**采样：**这是面对不均匀数据的常见方法了，没什么好说的，优点是高效，就是比较费性能

计划枚举

估计代价的前提是要有足够多的方案可供选择，选择计划执行路径的过程就叫计划枚举

单个关系的计划

对于OLTP型数据库，用户操作往往不会涉及到太多表，因此可变性最高的就是不同access method的选取，比如sequential scan、binary search（B+树）、index scan（如果有的话）。变化并不多，因此这部分可以只用简单的启发式规则去定义如何选择即可，这也正是OLTP所需的简单高效。

多个关系的计划

对于涉及到多个关系的计划，情况则大不相同，对于整个计划，DBMS需要枚举以下三个方面：

连表顺序：左深树，右深树，还是随机，哪个表先连？哪个表后连？
连表算法：Hash Join，Merge Join还是Nested Loop Join
数据访问方法：sequential scan，binary search（B+树）还是index scan（如果有的话）

我们没有办法定义简单的启发式规则去定义哪种计划最好，但也不能盲目的在如此庞大的空间中搜索代价最小的那个，解决办法依然是我们熟悉的近似方法。

动态规划

动态规划是目前大部分数据库都会采用的方法，我们不讨论具体的算法，而是直观展示一下动态规划的作用

例如一个三连表的计划枚举，原始的方案选择路径如下

动态规划一步步的选择当前较优的方案，直到选出估计最优

实例：Postgre优化方法

检查所有种类的连表顺序（左深树，右深树，随机树）
两种路径选择方法
- 动态规划
  - 12个表以内用动态规划的做法找连表路径
- 基因遗传算法
  - 超过12个表用遗传算法
遗传算法的实例如图所示，就是随机定下来几个完整方案的开销，然后优胜劣汰，过一定轮次就定下来比较优的方案。