[CMU 15-445] 15 查询计划生成与优化 -Ⅱ

1. Overview

本小节介绍基于代价估计的查询优化，包括代价估计、查询计划枚举、子查询优化。

2. Plan Cost Estimation

为了对每个查询计划的开销进行预估，DBMS统计关于每张表的部分信息并定期更新（一般放在catalog文件中）。

catalog中包括例如表中元组规模、字段、索引等。不同的DBMS系统可能采用不同的策略触发对统计信息的更新操作。

catalog通常会对表中元组数目以及字段的选择基数（Selection Cardinality）进行统计。

选择基数（Selection Cardinality）指某个字段中不同取值的占比。一般情况下，DBMS假设一个字段的取值服从均匀分布，查询优化器通过选择基数可以预估Select语句的输出规模（where子句中为=号条件），为后续计划做参考。但现实数据集中的数据分布往往是倾斜的，因此可能出现偏差。

然而，对于where子句中更复杂的判断条件，如>、<，仅仅只有整个字段的选择率是不够的，还需要字段在不同区间或者多个字段联合后的选择基数。

例如下面的例子，在假设字段取值服从均匀分布的情况下，age>2的选择率约等于1/2（实际是3/5）。

age != 2 的选择率约等于4/5。

若SQL语句中的where子句涉及多个字段，一般情况下，DBMS假设字段之间服从独立同分布，这样不同字段间与或后的选择基数也可以计算得到。如下面两个例子，分别是求P1∩P2、P1∪P2的选择基数。然而，现实情况中不同的字段间可能存在较强的关联，仅根据独立同分布的假设通常会对Select的输出规模做出错误的预估。

综上，普通DBMS假设字段取值服从均匀分布，字段间相互独立，以及还有第三点，Inner table中的待连接字段中出现的值一定也在Outer table中出现。

字段中真实的取值分布可能是如下直方图所情况（并非均匀分布）。

为了在直方图存储和性能之间进行trade-off，通常将直方图的几个取合并为一个bucket。

上述直方图表示方式中，同一个bucket中不同取值的选择基数可能存在较大不同，影响预估结果大小。为了减小这个问题带来的影响，重新设置bucket划分规则，让每个bucket的值近似相等。

除了让DBMS定期更新catalog中的统计信息，商用DBMS也经常结合采样来了解数据表的最新信息。

3. Plan Enumeration

基于上述的代价预估方法，DBMS枚举的不同执行方式，最终选择预估开销最低的方式执行去执行。

对于单表查询的优化较为简单，一般通过启发式策略选择出最高效的索引，复杂的是对于多表连接的优化，搜索空间大给查询优化带来了挑战。

为了简化例子，多表连接以System R中的多表连接为例（受限于早期计算机内存大小的限制，System R只采用left-deep-join的方式进行多表连接）。

总体过程可分为三步，枚举所有连接顺序、枚举所有Join算法、枚举所有的索引。中间使用动态编程技术选择最优的执行方式。

如下所示的多表连接查询优化（连接R、S、T三张表），首先枚举出所有的连接顺序，同时预估使用不同连接算法的代价。

每种连接顺序仅保留代价最低的连接算法。

同样的方式执行下一步，分别得到第二阶段不同连接顺序下最快的执行方式。

最终留下两个阶段代价和最低的执行方式。

上述启发式优化方案中，当连接的表很多时搜索空间巨大，为了加速这一过程，部分商用DBMS系统支持遗传算法来优化连接顺序。

遗传算法首先随机生成若干种执行方式，每次剔除代价最高的执行方式，随后将剩下的查询计划随机合并，直到新产生的计划执行代价不再降低或者算法运行达到时间阈值。

4. Nested Sub-Queries

对于子查询的优化主要可分为两种方法：1 重写SQL语句，转为多表Join；2 拆分查询任务，将结果单独存储到临时表，留作外层查询复用。

例如下面的例子，将子查询改写为多表连接语句，继而优化多表连接性能。

若子查询与外层不是相关子查询，可以将其与外层分离，避免多次执行。

5. Conclusion