人大金仓数据库KingbaseES-查询优化器概述

关键字：

SQL处理过程、优化器、RBO、CBO、表扫描、表连接、人大金仓

SQL处理过程

在介绍查询优化器前，先让我们来了解一条SQL语句的从客户端发起到执行结束的整个生命周期的过程。

在 KingbaseES 中，SQL 语句的处理通常都要经历以下阶段：词法语法分析阶段（parser）-> 语义检查阶段（analyzer） -> 查询重写阶段（rewritter）-> 查询优化阶段（planner） -> 查询执行阶段（executor）5个阶段，如图所示。

(1)词法语法分析阶段：检查 SQL 语句是否符合词法语法规则，然后转化为解析树。

词法分析：就是数据库将接收到的SQL语句（字符串）做切割，切割成一个个token进行归类，例如：关键字、标识符、常量、运算符、逻辑符等。

语法分析：就是将token重新组合成各类语法短句，组成的短句与既定的语法规则进行匹配，判断语法的正确性。

(2) 语义检查阶段：检查解析树是否符合语义规则，然后转化为查询树。

(3) 查询重写阶段：如果查询树中涉及视图或者规则，重写为查询树。

(4) 查询优化阶段：经过逻辑优化和物理优化，生成最优的执行计划。

(5) 查询执行阶段：按照执行计划进行执行。

查询优化器概念与作用

• 概念：查询优化器是数据库管理系统中的一个重要组成部分，它负责生成SQL查询的最优执行计划。
• 作用：对于一条给定的SQL语句，通常会有一个或者多个执行计划可供选择，每个执行计划都可以返回正确的结果，但性能可能不同，查询优化器的任务就是选择性能最优的执行计划。

查询优化器的技术

为了给出最优的执行计划，KingbaseES支持了以下两种优化技术：

• 基于规则的逻辑优化（RBO）
• 基于代价估算的物理优化（CBO）

以上两种技术是自动的，可以极大地解放数据库应用开发人员的生产力。反过来，理解这两种优化手段也有助于 使用者开发出更高效的 SQL 语句，在优化 SQL 语句性能的时候选择更加适合的优化手段。

3.1基于规则的逻辑优化（RBO）

查询优化器在逻辑优化阶段的主要任务是：根据关系代数的等价变换规则找出更高效的变换形式。常见逻辑优化包括：视图重写、表达式预处理、子查询优化、等价谓词重写、条件化简、外连接消除、谓词下推、连接消除及合并子查询。

具体说明如下：

• 视图重写

视图重写是将对视图的引用重写为对基表的引用，KingbaseES 把带有视图的查询转换为基于表的子查 询，因此重写后视图作为子查询进行进一步优化。目前 KingbaseES 仅支持对简单视图的重写，即对 SPJ 视图重写。KingbaseES 在查询重写阶段进行视图重写优化。

• 表达式预处理

这一阶段主要进行常量化简、连接溯源、表达式规范化等操作。

• 子查询优化

应用子查询优化技术，上拉子链接 ((NOT) EXISTS / IN / ANY…)、上拉子查询，使得子查询和父查询 处于同一层次，作为连接关系与外层父查询并列，也就是将子查询重写为等价的多表连接，从而尽可能减少查询语句的层次，便于物理优化阶段考虑多种不同的连接方式。

• 等价谓词重写

数据库执行引擎对一些谓词处理的效率要高于其他谓词，基于这点，把逻辑表达式重写成等价的且效率更 高的形式，能有效提高查询执行效率。

• 条件化简

WHERE、HAVING 和 ON 条件由许多表达式组成，而这些表达式在某些时候彼此之间存在一定的联系。 利用等式和不等式的性质，可以将 WHERE、HAVING 和 ON 条件化简

• 外连接消除

查询优化将满足特定条件的外连接转换为内连接，完成外连接的消除，转换的条件是看 WHERE 子句中 与内表相关的条件是否满足“空值拒绝条件”（reject-NULL 条件）。

• 谓词下推

“选择”、“投影”操作下推（WHERE/JOIN-ON 中的条件尽可能下推），使得关系在连接前尽量减少 元组数量，从而减少连接后得到的中间关系的大小。

• 连接消除

主外键参照的表进行外连接，可以消除主键表，从而消除连接。

• 合并子查询的公共表达式

将 SQL 语句中的子查询的公共表达式进行合并，只需执行一次获得所需结果集，后续直接引用该结果集 即可，减少对公共表达式的重复计算。

3.2基于代价估算的物理优化（CBO）

物理优化会选择那些消耗系统I/O和CPU资源最少的执行路径作为最优选择，在解析目标时，首先会对SQL进行查询转换，然后计算完成查询转换之后得到的等价改写SQL的各种可能的执行路径的成本，再从这些执行路径里选择成本值最小的一条来作为原目标SQL的执行计划，最后KingbaseES就会根据此执行计划去实际执行该SQL，并将执行结果返回给用户。

物理优化原理：枚举所有的执行路径，估算其代价，选出代价最小的路径作为执行计划。

路径多样的原因：各种关系代数操作符在KingbaseES中存在着一种或者多种算法来应对不同情况，包括：

• 选择：seqscan、indexscan、index only scan等

选择也就是通常说的表扫描方式：seqscan顺序扫描（也称为全表扫描）、indexscan索引扫描、index only scan仅索引扫描

• 排序：sort、indexscan
• 连接：hashjoin、merge join、nestloopjoin（连接顺序也可以变化）
• 聚集：hashaggregate、groupaggregate

构造查询的基本动作通常由关系代数的操作符来表达，常见关系代数的操作符包括：

• 选择：与 SQL 查询的 WHERE 子句对应
• 投影：与 SQL 查询的 SELECT 子句对应
• 卡氏积：与 SQL 查询的 FROM 子句的关系列表对应
• 连接：与 SQL 查询中的 JOIN、NATURAL JOIN 和 OUTER JOIN 对应
• 并、交和差：与 SQL 的操作符 UNION、INTERSECT、EXCEPT 对应
• 消除重复：与 SQL SELECT 子句中的关键字 DISTINCT 对应，SQL 的操作符 UNION、INTERSECT、EXCEPT 默认消除重复
• 分组：与 SQL 查询中的 GROUP BY 对应
• 排序：与 SQL 查询中的 ORDER BY 具有相同的效果

物理优化的一个重要手段是代价估算，即估算某个路径（可以是单表的路径，也可以是两个关系连接得到的路径，还可以是多个表根据不同的连接方式得到的连接路径）的代价。这些路径中每个节点主要的操作可归纳为数据的读写及运算，这些操作所产生的开销主要为 CPU 开销及 I/O 开销。所以查询优化代价估算基于 CPU 开销和 IO 开销，某些情况下会有通信代价（如并行）。即：总代价 =I/O 代价 + CPU 代价 + 通信代价（并行）。

3.2.1 单表扫描代价估算

KingbaseES 中常见的单表扫描方式包括：

• 顺序扫描（SeqScan）

• 索引扫描（IndexScan）

1. 顺序扫描

顺序扫描也叫全表扫描，基本算法为：根据实际的数据的存取顺序，连续扫描表中的所有数据。

适用于：选择率高的情况 在选择率高到一定程度的时候，相比于顺序扫描，索引扫描多出扫描索引的代价，所以付出的代价会超过顺序扫描。

顺序扫描代价估计： 顺序扫描操作的代价估算：tuple IO 代价 +tuple CPU 扫描代价。

2. 索引扫描

索引扫描的基本算法为，根据条件值，通过索引结构快速的找到条件值的索引位置，进而找出所有符合条件的值。

适用于：选择率低的情况 在选择率低的情况下，索引扫描需要扫描的索引页面和数据页面要小于顺序扫描，由此付出的 IO 和 CPU 代价 要小于顺序扫描，所以优化器会选择索引扫描。

索引扫描代价估计：

索引扫描操作的代价估算：索引扫描代价 +tuple IO 代价 +tuple CPU 扫描代价。

3.2.2 两表连接代价估算

在数据库中表与表的连接为一项重要的操作，两表连接作为连接的基础尤其

重要。

KingbaseES 提供了 3 种类型的连接方式。每种类型的连接方式对应不同的算法 实现，本文将一一介绍三种连接类型的算法原理及适用场景

一、嵌套循环连接（NestLoop）

嵌套循环连接为两表连接最基本的算法。

1. 基本嵌套循环连接

循环嵌套连接算法本质为一个两层循环，循环外层的表每次拿出一条元组与循环内层的表作比较逐一匹配 是否满足连接条件选出符合连接条件的元组。

适用于：1）内外表数据量不大的情况 2）内表数据量很小，外表数据量大的情况 嵌套循环连接代价估计： 通常情况下由于连接操作主要花费 CPU 资源。所以从理论的角度分析，

嵌套循环连接操作的代价估算： 扫描外表的代价和扫描内表的代价之和

2. 物化嵌套循环连接

每当读取外部表的每个元组时，上述嵌套循环连接都必须扫描内部表的所有元组。由于为每个外部表元组 对内部表做全表扫描是一个昂贵的过程，因此 KingbaseES 支持物化嵌套循环连接以减少内部表的全表扫 描成本。

在运行嵌套循环连接之前，执行程序通过使用临时元组存储模块对内部表进行一次扫描，将内部表元组写 入 work_mem 或临时文件中。与使用缓冲区管理器相比，它有可能更有效地处理内部表元组，尤其是当 所有元组都能写入 work_mem 中时。

适用于：内表数据被重复访问

3. 索引嵌套循环连接

如果存在内部表的索引，并且该索引可以用于满足联接条件的元组以匹配外部表的每个元组，那么优化器 会考虑使用此索引直接搜索内部表元组，而不是顺序扫描。这种变化称为索引嵌套循环联接；尽管该算法 引用了索引“嵌套循环连接”，但该算法基本上只需要在外部表上循环一次，因此，它可以有效地执行连 接操作。 适用于：外部表数据量不大，内部表可以使用索引扫描一次很快的情况 索引嵌套循环连接代价：扫描外表代价 + 使用索引扫描内表代价（通常小于全表扫描内表代价）

二、归并连接（merge join）

与嵌套循环连接不同，归并联接只能用于自然联接和等值联接。

1. 基本归并连接

归并连接的基本原理为对两个表的数据进行排序，然后做连接。

归并连接算法的步骤是：

1）将每个表排好序

2）读入每个表的元组

3）找出两个表中最小的元组进行匹配

4）找出次小的元组匹配依此类推，完成其他元组的两表连接。

归并连接算法要求内外表都是有序的，所以对于内外表都要排序。但是如果内表或者外表有索引，就不需 要进行排序，只对无索引的表进行排序。如果两表都有索引，则两表都不需要排序。

适用情况：1）两个表的数据都是基本有序 2）两表可以使用索引或者外表有索引

归并连接代价估计： 通常情况下由于连接操作主要花费 CPU 资源。所以从理论的角度分析，归并连接操作的代价估算：扫描 外表代价 + 扫描内表代价 + 外表排序代价 + 内表排序代价。

2. 物化归并连接

与嵌套循环连接类似，归并连接支持物化归并连接（Materialized Merge Join），物化内表，使内表扫描 更为高效。

三、hash连接（hash join）

与归并连接相同同，hash 联接只能用于自然联接和等值联接。

内存中的 Hash 连接是在 work_mem 中处理的。Hash 连接有两个阶段：构建阶段和探测阶段。在构建阶段，内 表中的所有元组都会被插入到桶中；在探测阶段，每条外表的元组都会与处理桶中的内表元组比较，如果满足条件则 将两条元组连接起来。

Hash 连接的基本原理为：

1) 对内表建立 hash 表，扫描所有内表数据到各个 hash 桶里面。

2) 一行行扫描外表数据，对外表数据进行 hash，hash 到某个桶里面。

3) 跟这个桶里面的数据进行连接。

适用情况：数据分布比较随机无序，重复值不是特别多的情况。

Hash 连接代价估计：

通常情况下由于连接操作主要花费 CPU 资源。所以从理论的角度分析，Hash 连接操作的代价估算：内表 创建 hash 代价 +(n_outer_tuple × m_inner_tuple × 选择率)× a_tuple_cpu_time a_tuple_cpu_time，获取一个元组消耗的 CPU 时间。

n_outer_tuple，扫描获取的外表元组数。

m_inner_tuple，扫描获取的内表元组数。

优化器的局限性

KingbaseES 的查询优化器会存在一些无法覆盖的情况，最常见的两种情况及解决方式如下：

(1) 对于尚未支持的逻辑优化规则，建议用户通过改写的方式来写出更加高效的 SQL 语句；

(2) 对于代价计算不准（多条件、多表连接、相关子查询条件等）的情况，建议用户通过 HINT 的方式人工指定计划。