您现在的位置是：首页 > 文章详情

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

日期：2023-12-04点击：64收藏

本文分享自华为云社区《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类型的逻辑运算。