细说 GaussDB(DWS) 的 2 种查询优化技术

2023-12-04
广东
本文字数：3665 字
阅读完需：约 12 分钟

本文分享自华为云社区《GaussDB(DWS)查询优化技术大揭秘》，作者：胡辣汤。

大数据时代，数据量呈爆发式增长，经常面临百亿、千亿数据查询场景，当数据仓库数据量较大、SQL 语句执行效率低时，数据仓库性能会受到影响。本期《GaussDB(DWS)查询优化技术大揭秘》的主题直播中，我们邀请到华为云 GaussDB(DWS)技术布道师王跃老师，深入讲解在 GaussDB(DWS)中如何进行表结构设计，如何进行 SQL 优化，如何查找慢 SQL 和高频 SQL。

一、认识优化器

数据库的优化器基本上有 2 种模式，基于规则的优化器(rbo)和基于成本的优化器(cbo)。当前比较通用的是 CBO 模型的优化器。

基于成本的优化器(cbo，cost based optimizer)：该优化器通过根据优化规则对关系表达式进行转换，生成多个执行计划，然后 CBO 会根据统计信息(Statistics)和代价模型(Cost Model)计算各种可能“执行计划”的“代价”，即 COST，从中选用 COST 最低的执行方案，作为实际运行方案。

优点：可以自动适应表数据量变化，计算量发生变化，自动调节，选择较优的执行计划。

缺点：依赖于 COST 计算模型重要的影响因子：统计信息，需要给优化器提供准确的统计信息，才能做出好的执行计划。

SQL 执行流程

执行计划是查询语句在数据库中执行过程的描述，执行计划描述了 SQL 引擎为执行 SQL 语句进行的操作，分析 SQL 语句相关的性能问题或仅仅质疑查询优化器的决定时，必须知道执行计划，所以执行计划常用于 SQL 调优。要读懂执行计划，首先要知道数据库执行算子的概念：

二、调优流程

当前数据库调优主要分为静态调优和动态调优两种，静态调优是根据硬件资源和客户的业务特征确定集群部署方案、表定义。执行态调优（动态调优）是根据 SQL 语句执行的实际情况采取针对性干预 SQL 执行计划的方式来提升性能。

调优流程

三、静态调优

本次直播主要从表定义角度介绍静态调优的 5 种常用方法，帮助用户根据业务场景选择合适的调优方式，提高 SQL 语句的查询性能。

3.1 表定义：集群部署有相关工作人员协助，用户只需要关注表定义创建策略。GaussDB 数据库中，分布式框架下，数据分布在各个 DN 上，一个或者几个 DN 的数据存在一块物理存储设备上。好的表定义可以达到以下几个目的：

表数据均匀分布在各个 DN 上，防止单个 DN 数据过多导致集群有效容量下降。
表 Scan 压力均匀分散在各个 DN 上，避免单 DN 的 Scan 压力过大，形成 Scan 的单节点瓶颈。
减少扫描数据数据量，通过分区机制实现。
尽量减少随机 IO，通过聚簇/局部聚簇可以实现。
尽量避免数据 shuffle，减小网络压力。建议选择 join-condition 或者 group by 列为分布列。

3.2 存储类型：进行数据库设计时，表设计上的一些关键项将严重影响后续整库的查询性能，表设计对数据存储也有影响，好的表设计能够减少 I/O 操作及最小化内存使用，进而提升查询性能。

行、列存选择依据

3.3 分布列：

分布列决定了数据按哪一列拆分到各个 DN 上，好的分布列会使用数据在各个节点上分布均匀，减少数据重分发，充分发挥各个节点的性能。当前支持如下 3 种分布方式：

复制 (Replication)

集群中每个 DN 实例上都有一份全量表数据；
Join 操作可减小重分布造成的网络开销；
存在数据冗余；
适用于小表、维表。

哈希 (Hash) -- 8.1.3 之前默认分布方式

数据通过 Hash 方式散列到集群的所有 DN 实例；
读写数据可充分利用各个节点 IO 资源，提升读写速度；
适用于数据量大的表。

轮询 (RoundRobin) -- 8.1.3 开始之后默认分布方式

数据通过轮询方式发放到集群内所有 DN 实例；
读写数据可充分利用各个节点 IO 资源，提升读写速度；
适用于数据量大的表，且各列都有严重倾斜的表。

如何选择最佳分布列：

列值应比较离散，以便数据能够均匀分布到各个 DN，通常选择表的主键为分布列；
尽量不要选取存在常量等值过滤条件，避免 DN 剪枝后 Scan 集中到一个 DN 上；
选择查询中的连接条件为分布列，以便 Join 任务能够下推到 DN 中执行，且减少 DN 间的通信数据量；
根据上述原则尽量根据业务特征选择 hash 分布方式，无法确定时可以选择 roundbobin 分布。

3.4 局部聚簇：列存储下一种通过 min/max 稀疏索引实现基表快速扫描的一种索引技术。

适用场景：

业务特征：大表大批量数据导入，每次导入数据量远大于 DN 数 * 6W；
基表存在大量形如 col op Const 约束，其中 col 为列名，const 为常量值，op 为操作符 =、>、>=、<=、<；
选用选择度比较高的简单表达式的列上建 pck。

选取原则：

受基表的简单表达式约束。一般形如 col op const,其中，col 为列名，op 为操作符=、>、>=、<=、<, const 为常量值；
尽量选用选择度比较高（可以过滤掉更多数据）的简单表达式的列；
尽量把选择度低的约束 col 放在局部聚簇中的前面；
尽量把枚举类型的列放在 PCK 中的前面。

3.5 分区表：把逻辑上的一个大表按照某种策略分成几块物理块进行存储时，逻辑上的大表称为分区表，每个物理块则称为一个分区。在查询时，通过分区剪枝技术尽可能减少底层数据扫描。

适用场景：
数据规模：大表；
业务特征：通过剪枝缩小查询范围。
分区键的选择：可以将数据均匀映射到各个分区的列，常见的分区键一般是时间列。

四、动态调优

动态调优，即执行态调优，分析其性能劣化点，加以优化的手段。包括如下 3 个步骤：

步骤 1：收集 SQL 中涉及到的所有表的统计信息。

在数据库中，统计信息是规划器生成计划的源数据。没有收集统计信息或统计信息陈旧往往会造成执行计划严重劣化，从而导致性能问题。从经验数据来看，10%左右性能问题是因为没有收集统计信息。

步骤 2：通过查看执行计划查找原因。

如果 SQL 长时间运行未结束，通过 EXPLAIN 命令查看执行计划，进行初步定位。

如果 SQL 可执行结束，则执行 explain performance 命令收集详细计划，或者借助日志，进一步分析性能劣化点，比如，语句不下推、数据下盘，或数据分布造成 IO 瓶颈点等等。

步骤 3：针对分析得出的劣化原因，采取相应措施进行优化改进，从而提高性能。

4.1 统计信息：GaussDB(DWS)的优化器是典型的基于代价的优化 (Cost-Based Optimization，简称 CBO)。在这种优化器模型下，数据库根据表的元组数、字段宽度、NULL 记录比率、distinct 值、MCV 值（Most Comman Value）、HB 值（直方图，数据分布概率区间）等表数据特征，结合代价计算模型，通过代价估算输出估算的最优执行计划，这些特征值就是称之为统计信息。统计信息是查询优化的核心输入，准确的统计信息将帮助优化器选择最合适的查询计划。

没有收集统计信息或在统计信息陈旧往往会造成执行计划严重劣化，从而导致性能问题。ANALYZE 语句可以收集与数据库中表内容相关的统计信息，统计结果存储在系统表 PG_STATISTIC 中。查询优化器会使用这些统计信息，生成最有效的执行计划。

4.2 不下推分析：分布式集群相对于单机最显著的优势在于并行分布式计算能力，通过多节点、多实例并向计算，充分利用系统资源，提升查询性能。优化器在分布式框架下有三种执行计划规划策略：下推语句计划、分布式计划、不下推计划，一般来说不下推计划会因为不能充分利用并行计划能力而导致比较严重的性能问题。

下推语句计划：指直接将查询语句从 CN 发送到 DN 进行执行，然后将执行结果返回给 CN。一般只有简单的查询语句才会走这种计划。
分布式计划：CN 生成计划树，再将计划树发送给 DN 进行执行，DN 执行完毕后把结果返回到 CN。
不下推计划：上述两种方式都不可行时，优化器将部分查询（多为基表扫描语句）下推到 DN 进行执行，获取中间结果到 CN，然后 CN 执行剩下的部分。

执行语句不下推通常是因为语句中含有 shippable 属性为 false 的函数的语句。不下推问题的定位手段通常有两种，通过日志可以看到类似“”SQL can’t be shipped.“的 LOG 以及对不下推原因的初步信息。

4.3 Performance 分析：explain performance 可以收集详细执行信息，并从中分析可能的性能问题，从而做出针对性优化。

4.4 Scan 性能优化：Scan 性能提升策略主要有 2 个，减少实际 IO 和分散 Scan 压力到各个 DN 上。

4.5 Join 性能优化：GaussDB(DWS)表连接（Join）是根据特定规则从两个其他表（真实表活生成表）中派生出结果集。语法上，两表做连接操作时需要引入 Join 算子。Join 性能提升策略有 2 个，选择高效的 Join 方式和选择合适的内外表。

4.6 SQL 改写：SQL 改写主要涉及相关子链接改写、Join 条件改写、NOT IN 改写。

相关子链接改写：当子查询和子链接性能较差时，大部分场景，可提升为 Join 进行优化；小部分场景，需要用户改写 SQL 进行优化。改写策略：在语义等价前提下，将子链接、子查询的查询语句提升到外层查询进行关联查询
Join 条件改写：等值 Join 条件的 Join 列增加非空过滤条件，可以减小参与连接运算的数据量。
NOT IN 改写：当子链接输出列上不存在 NULL 值，或者逻辑判断语义上不需要比较 NULL 值时需要进行 NOT IN 改写。优化原理：只输出 WHERE 条件为 true 的结果、NULL 和任何值的比较操作均为 NULL、NULL 和 bool 类型的逻辑运算。