GaussDB(DWS)数据库-性能调优和开发实践篇

GaussDB(DWS) 数据库数据仓库数据

数据

发布日期: 2025-08-04

更新日期: 2025-08-04

文章字数: 5.7k

阅读时长: 20 分

阅读次数:

性能调优

定义：通过优化数据库系统的配置及SQL查询，以提高数据库性能和效率的过程。目的是消除性能瓶颈、减少响应时间、提高系统吞吐量和资源利用率，降低业务成本，从而提高系统稳定性，给用户带来更大的价值。
SQL执行计划解析, 执行计划命令：
- EXPLAIN VERBOSE + SQL语句(select/update/insert/delete/merge into/create table as):获取详细执行信息，SQL语句不会真正执行 verbose
- EXPLAIN PERFORMANCE+ SQL语句(select/update/insert/delete/merge into/create table as):获取详细执行信息。performance
verbose选项下打印详细计划信息中，plan information信息包括：
- E-rows:算子估算输出行数
- E-distinct:单DN上算子distinct估计值
- E-memory:DN上每个算子估算的内存使用量，只有DN上执行的算子才会显示。某些场景会在估算的内存使用量后面使用括号，表示该算子在内存资源充足下的自动扩展内存上限。
- E-width:每个算子输出元祖的估算宽度
- E-costs:每个算子估算的执行代价
performance选项下打印执行信息中，和verbose相比，新增的相关信息：
- A-time：算子实际执行时间，在DN上输出由[]括起来的，由逗号分割的两个值，分别表示算子在不同DN上执行的最短时间和最长时间。
- A-row：算子实际输出元祖数，是各个DN上算子输出元祖数总和。
- Peak memory：算子消耗内存峰值，在DN上输出由[]括起来的，由逗号分割的两个值，分别表示算子在不同DN上执行的最小内存消耗和最大内存消耗。
- A-width：算子每行元祖实际宽度，仅涉及重内存使用算子。
performance选项打印执行中的相关信息：
- plan information:以表格形式显示整个执行过程中每个算子的执行概要信息。
- SQL Diagnostic information:SQL自诊断信息
  - verbose可诊断：统计信息未收集、分区不剪枝、SQL不下推
  - performance可诊断：统计信息未收集、分区不剪枝、SQL不下推、HashJoin中大表做内表、大表等值连接使用Nestloop、大表Broadcast、数据倾斜、索引不合理。
- predicate information:算子计算信息，如scan的filter条件，join的join条件
- Datanode information:算子在每个DN上执行的详细信息，包括执行时间、CPU、Buffer的使用情况。
  - 执行时间（actual time）:如果这个值在各DN上存在较大差异，可初步判断存在计算倾斜（各DN上承担计算量差异过大）
  - 输出元祖数（rows）:结合执行时间进一步佐证是否存在计算倾斜。
  - CPU执行cycle:在算子执行期间，执行所消耗的CPU cycle
  - Buffer命中率（hit）:针对Scan算子做数据扫描时。从性能角度看，buffer命中率越高越好，这需要增大集群的shared_buffers(行存)、cstore_buffers(列存)的配置参数。
- User Define information:性能profile信息，算子执行过程中的，关键动作性能打桩信息。
- Memory information:算子执行过程中的内存消耗信息。包括内存信息和下盘信息。
- Query Summary:query执行的概要信息。这部分打印总的执行时间和网络流量，包括各DN上初始化和结束阶段的最大最小执行时间、CN上的初始化、执行、结束阶段的时间、以及当前语句执行时系统可用内存、语句估算内存等信息。
性能分析主要关注Plan information/SQL diagnostic ioformation/predicate information/Datanode information这4部分信息。在大集群下，对复杂SQL，建议使用explain analyze 打印概要的实际执行信息，避免打印各个节点信息太多导致执行计划过长
执行计划类型：3种
- FQS计划（fast query shipping）
  - CN直接将原语句下发的DN,各个DN单独执行，并将执行结果在CN上汇总。
- Stream计划：
  - 算子形态：GATHER/REDISTRIBUTE/BROADCAST
  - CN根据原语句先生成执行计划，再将计划下发到DN执行，各DN执行过程中使用Stream算子进行数据交互。
- Remote Query计划
  - CN生成执行计划后，将部分原语句下发到DN,各DN单独执行，执行后将结果发送给CN，CN执行剩余计划。
- Explain执行计划:
  - ANALYZE | ANALYSE :显示实际运行时间和其他统计数据
  - VERBOSE:显示有关计划的额外信息，例如输出列信息。
  - COST: 包括每个规划节点的估计总成本，以及估计的行数和每行的宽度。
  - CPU: 打印CPU的使用情况信息。
  - DETAIL:打印DN上的信息。
  - NODES:打印query执行的节点信息
  - NUM_NODES:打印执行中的节点个数信息
  - BUFFERS:包括缓冲区的使用情况信息
  - TIMING:包括实际的启动时间和花费在输出节点上的时间信息。
  - PLAN:是否将执行计划存储在plan_table中。
  - FORMAT:指定输出格式
  - GENERIC:显示将语句中的常数替换为参数后生成的generic计划
TopSQL：
- 定义：将SQL的排队信息、运行信息（耗时、CPU、内存、IO、网络、空间）记录到一张系统表中，即作业级监控。
- 功能：
  - 确定影响数据库性能资源最密集的SQL查询
  - 监控和跟踪SQL查询，随时间推演的性能变化
  - 分析查询执行计划，以确定潜在的优化
- 分类:实时/历史, 当前CN/全部CN,级别都是query
  - 实时当前CN:GS_WLM_SESSION_STATISTCS
  - 实时全部CN:PGXC_WLM_SESSIOIN_STATISTICS
  - 历史当前CN:GS_WLM_SESSION_INFO
  - 历史全部CN:PGXC_WLM_SESSION_INFO
历史TopSQL：
- Topsql主要是通过视图进行承载，按照级别分为query/perf/operator
- query:SQL语句的计划信息，类似于explain输出信息，记录到Topsql中。
- perf:包含实际执行时间和执行行数的计划信息，类似于explain analyze输出信息，记录到Topsql中。
- operator:不仅会把包含实际执行时间和执行行数的信息记录到TopSql中，还会把算子级别执行信息，记录到Topsql中.
历史TopSQL视图记录了作业运行结束时的资源使用情况、运行状态信息和性能告警信息。
- 分类：级别（query/perf+operator）+当前CN/全部CN
- query/perf级别当前CN: GS_WLM_SESSION_INFO
- query/perf级别全部CN: PGXC_WLM_SESSION_INFO
- operator级别当前CN:GS_WLM_OPERATOR_INFO
- operator级别全部CN:PGXC_WLM_OPERATOR_INFO
TopSQL配置GUC参数：
- 实时TopSQL参数：运行中的语句记录
  - enable_resource_track(ON):资源实时监控开启，实时TopSQL总开关，关闭后实时TopSQL不再记录，不会出现在历史TopSQL中。
  - resource_track_cost(0):执行代价阈值，对当前会话语句进行资源监控的，最小执行代码。
  - resource_track_level(query):资源监控等级，当前会话的资源监控等级，默认为query级别。
- 历史TopSQL参数：运行完成的语句记录
  - enable_resource_record(on):资源监控记录归档，开启后，执行结束的记录会分布归档到相应INFO视图，CN和DN都需要设置上。
  - resource_track_duration(60s):作业运行时间阈值，实时TopSQL中记录的语句执行结束后，进行历史转存的最小执行时间，其判断包含排队时间和运行时间，当排队时间+运行时间>resource_track_duration时，Topsql历史视图会记录作业信息。当执行完成的作业，其执行时间不小于此参数值时，作业信息会从实时视图（statistics为后缀的视图）转存到相应的历史视图。
  - topsql_retention_time(30天):历史数据老化周期，历史TopSQL当前CN视图（GS_WLM_SESSION_INFO、GS_WLM_OPERATOR_INFO）中数据保持时间，单位为天。
- 数据流转过程：作业运行->运行信息记录实时Topsql->作业运行结束->执行信息记录历史Topsql->结束。

SQL调优

调优原则：也是唯一原则，资源利用最大化原则.其中资源包括CPU、内存、磁盘IO、网络IO,SQL语句应当尽量高效、节省资源开销，即以最优的执行方式实现功能。SQL语句应当充分利用资源，实现性能极致。
调优分类和流程：先静态调优，再动态调优
- 静态调优：根据硬件资源和客户的业务特征，确定集群部署方案和表定义。集群部署方案和表定义一旦确定，后续改动的代价会比较大。
- 动态调优：根据SQL语句执行的实际情况，采取针对性干预SQL执行计划的方式，提升性能。采取的手段包括：SQL改写，GUC参数干预，Plan Hint
静态调优手段(5种):表定义、存储类型、分布列、局部聚簇、分区表
- 表定义的目的：
  - 表数据均匀分布在各个DN，选择合适分布列避免数据分布倾斜，防止单个DN数据过多导致集群有效容量下降。
  - 表Scan压力均匀分布在各个DN,避免单DN的scan压力过大，形成scan的单节点瓶颈。避免把基表上的等值filter中的列作为分布列。
- 存储类型：
  - 用途：客户业务属性决定表的存储类型；存储类型决定存储格式，进而影响I/O操作行为。
- 分类：
  - 行存：适合点查询（返回记录少，基于索引的简单查询），增删改比较多的场景
  - 列存：统计分析类查询（group, join 多的场景），即席查询（查询条件列不确定，行存无法确定索引）
- 分布列：
  - 分布列选择原则：列值应比较分撒，以便数据能够均匀分布都各个DN上。尽量不要选择存在常量等值过滤条件的列，避免DN剪枝后Scan集中到一个DN上。选择查询中的连接条件为分布列，以便join任务能够下推到DN中执行，而且可以减少DN间的通信数据量，建议选择join-condition或者group by 列为分布列。根据以上原则尽量根据业务特征选择hash分布方式，无法确定时可以选择roundrobin分布：
- 分布方式：复制（Replication）、哈希（Hash）、轮询（RoundRobin）
  - 复制Replication:在集群中的每个DN实例上都有一份全量表数据。存在数据冗余。适用于小表、维表。join操作可减少重分布造成的网络开销。
  - 哈希（Hash）：数据通过hash方式散列到集群的所有DN实例上。适用于数据量大的表。读写数据可充分利用各个节点IO资源，提升读写速度。
  - 轮询（RoundRobin）：数据通过轮询方式发放到集群内所有DN实例上。适用于数据量大的表，而且各列都有严重倾斜的表。读写数据可充分利用各个节点IO资源，提升读写速度。
  - 分布方式分析和调整
    - 判断数据是否存在存储倾斜：table_distribution()，不同DN的数据量，相差5%以上即可视为倾斜，相差10%以上，建议调整分布列。
    - 在线判断数据列是否存在倾斜：table_skewness()，
    - 调整分布列语法： bash alter table table_name distribution by hash()/replication/roundrobin;
- 局部聚簇（Partial Cluster Key，简称PCK）：
  - 定义：列存储下的，一种通过min/max稀疏索引，实现基表快速扫描的，一种索引技术。
  - 优化原理：入库时进行局部排序，来换取查询性能
  - 使用约束:列存表，一个列存表只能创建一个PCK。适用数据类型包括整型、时间类型和字符串类型。对于字符串数据类型，如果当前库的collate不为C，则只对表达式col = Const起大加速查询的效果
  - 使用场景：
    -业务特征：大表大批量数据导入，每次导入数据量远大于DN数*6W。
    -基表存在大量形如col op Const约束，其中col为列名，const为常量值，op为操作符=，<, > ,<=, >=
    -选择选择度比较高的简单表达式的列，建立PCK
  - 使用方法：在创建表的时候，指定PCK约束。在alter table语法中添加PCK约束（只对后续导入数据生效）
- 分区表
  - 定义：把逻辑上的大表按照某种策略划分为几块物理块进行存储，逻辑上的大表成为分区表，每个物理块为一个分区。
  - 原理：在查询时，通过分区剪枝技术来尽可能减少底层数据扫描。
  - 适用场景：数据规模上的大表，业务特征为通过剪枝缩小查询范围。
  - 优势：改善查询性能、增强可用性、方便维护。
  - 分区键选择：将数据可以均匀映射到各个分区的列，常见分区键为时间列。
  - 分类：range分区和list分区
执行计划：
- 执行计划三要素：统计信息、优化器、配置参数
  - 统计信息（表的数据特征）：包括表的元祖数，字段宽度、null记录比率，distinct值，MCV值（most common value）、hb值（直方图，数据分布概览区间）
  - 优化器（Cost-Based Optimization,CBO，基于代价的优化）：数据库根据大量的表数据特征，结合代价计算模型，通过代价估算，输出估算后的最优执行计划。其中统计信息是查询优化的核心输入，准确的统计信息可以帮助优化器选择最合适的查询计划。
  - 配置参数：GUC参数和HINT信息。配置参数直接干预优化器的路径选择。
  - 统计信息收集：使用analyze语法来收集整个表或者表的若干列统计信息。
  - 操作：大批量数据导入、更新、删除之后，及时analyze.
- 执行计划流程：
  - 词法&语法解析：按照约定SQL语句规则，输入SQL语句从字符串转化为格式化结构（Stmt）
  - 语义解析: 格式化结构转化为数据库可识别对象
  - 查询重写：根据规则，将语义解析的输出，等价转化为执行上更为优化的结构。
  - 查询优化：根据“查询重写”的输出和数据库内部的统计信息，规划SQL语句的具体执行方式。
  - 查询执行：根据“查询优化”规划的执行路径，执行SQL查询语句。
动态调优：即执行态调优
- 定义：先跑query，判断性能是否满足客户需求，如果不满足，则进一步分析性能瓶颈点，进行针对性优化，重新试跑，一直到满足性能目标为止。
- 步骤：
  - 判断查询相关表是否已收集统计信息
  - 判断查询语句是否下推
  - 收集perfromance信息进行性能分析，并做针对性优化
  - SQL改写优化
- 统计信息收集：对统计信息前后的执行计划，做对比分析
  - E-rows：在没有收集统计信息的执行计划中，估计值E-row会比实际值小
  - 执行计划：在没有收集统计信息的执行计划中，出现两个低效的Nest loop算子。
- 查询语句下推：
  - 执行计划类型：并行计算能力是DWS数据库的性能优势
  - 优化器在分布式框架下有三种执行规划策略：
    - 下推语句计划：CN发送查询语句到DN直接执行，执行结果返回给CN.计划特征：REMOTE_FQS_QUERY
    - 分布式计划：CN先生成计划树，再发送计划树给DN执行，DN执行完成后，执行结果返回给CN.计划特征：Streaming(type: GATHER)
    - 不下推计划：CN承担大量计算任务，导致性能劣化。优化器先将部分查询（多为基表扫描语句）下推的DN中执行，将中间结果返回给CN,CN再执行执行计划剩下的部分。执行计划特征：REMOTE_XXX + Coordinator quals
- 不下推分析：
  - 常见不下推原因：含有shippable属性且为false的函数语句不下推。
  - 问题定位手段：
    - Explain performance/Explain verbose：对正在执行的SQL，使用explain performance/explain verbose,在输出的自诊断信息（SQL Diagostic information）中会提示具体的不下推原因。
    - TopSQL：历史执行信息会记录到系统表中，使用postgres库中的查询视图（历史全部DN的perf级）pgxc_wlm_session_info，获取历史SQL的执行信息，此表中的warning字段会记录对应的SQL语句不下推原因。
- performance分析
  - explain performance优化：收集query执行信息，分析可能的性能问题，针对性优化
  - 重点关注信息：
    - 算子：耗时占整体执行时间高的算子
    - 执行信息：DataNode information、Memory information、Targetlist information
  - 算子瓶颈和优化策略：
    - Scan性能瓶颈：基表扫描元组数过多场景：增加索引，使用PCK，使用分区
  - 性能提升策略：
    - 减少实际IO：针对点查询场景：增加索引，使用PCK(列存表)；针对范围查询场景：使用分区（优化IO）
    - 数据在各个DN分布不均衡场景：调整分布列方式。把Scan压力分散到各个DN上：是指数据倾斜，IO压力分布不均衡，performance信息中各DN扫描时间存在明显差异，优化策略是修改分布列
Join性能瓶颈：
- join方式选择不当场景：使用plan hint，增加索引
- join内外表选择不当场景：使用plan hint，改写SQL
- join类型：
  - 定义：表链接join，根据特定规则从两个其他表（真实表或者生成表）中派生出的结果集。
  - 类型：在语法层，内连接（inner join）、外连接（outer join）、交叉连接（笛卡尔积， cross join）。inner是缺省的，left、right、full都是外连接，连接条件在on或using子句中指定。在内置实现支持：半连接（Semi join，in约束转化生成，匹配上即命中）、反半连接（Anti Join, NOT IN约束转化生成，匹配上即排除）
- join性能提升策略：
  - 选择高效的join方式：通常情况下，hashjoin较为高效。改写SQL实现hashjoin,可以尝试将不等值join条件转化等值join条件。在部分特定场景下，nestloop+indexScan性能更好
  - 选择合适的内外表：hashjoin：内表小，外表大，执行更高效。或者使用plan hint 调整内外表顺序。

plan hint

定义：可直接影响执行计划生成的手段，目的是通过对执行计划的调优，提升查询性能。
常见hint调优手段：
- 指定scan方法
- 指定join方法、join顺序和join时的stream策略
- 指定估算行数
- 指定重分布过程中的倾斜信息
- 配置参数的hint
使用要求：
- plan hint仅支持在select关键字后面通过如下形式指定。
- 可以同时指定多个hint，不同hint信息使用空格分隔。
- 配置参数之外的hint 只能hint当前层的计划，对于子查询计划中的hint，需要在子查询的select关键字后面指定hint信息。
- 在视图定义时指定hint，该视图每次被调用时都会使用该hint信息。
- 在视图定义时指定hint，该视图每次被调用时都会使用该hint信息。表只能用单个字符串表示，不能带schema；表如果存在别名，需要优先使用别名来表示该表。
语法格式：
- 在视图定义时指定hint，该视图每次被调用时都会使用该hint信息。指定scan方法：[no] tablescan|indexscan|indexonlyscan(table [index]): no表示hint的scan方式不使用。table表示hint指定的表，只能指定一个表，如果表存在别名，应该优先使用别名进行hint。index表示使用indexscan或者indexonlyscan的hint时，指定的索引名称，当前只能指定一个。
- 在视图定义时指定hint，该视图每次被调用时都会使用该hint信息。指定join方法：[no] nestloop|hashjoin|mergejoin(table_list):no表示hint的join方式不使用。table_list是hint表集合的字符串，中间不允许出现括号指定join的优先级。
  - 仅指定join顺序，不指定内外表顺序：leading(join_table_list)
  - 同时指定join顺序和内外表顺序，内外表顺序仅在最外层生效：leading((join_table_list))
- 在视图定义时指定hint，该视图每次被调用时都会使用该hint信息。指定估算行数：rows(table_list #|+|-|* const):
  - 支持#，+，-，*四种操作符，#表示直接使用后面的行数，替换优化器中的估算行数，+、-、*表示对原来估算的行数进行加减乘操作。
  - 运算后的行数最小值为1行。table_list为hint对应的单表或者多表join结果集，与join的hint中的table_list相同。
  - const可以是任意非负数，支持科学计数法
  - 支持绝对值和相对值的hint，常用于多表的join时，中间结果集估算表不准的场景。
- 在视图定义时指定hint，该视图每次被调用时都会使用该hint信息。join顺序和join时的stream策略：[no] broadcast|redistribute(table_list):no表示hint的stream方式不使用。table为进行stream操作的单表或者多表join结果集。
  - stream hint 和join hint配合使用，先hint明确join顺序，然后hint明确中间结果集的数据流动方式。

SQL改写

相关子链接改写：
- 场景：子查询和子链接性能较差。大部分场景，可提升为join进行优化；小部分场景，需要用户改写SQL进行优化。
- 改写策略：在语义等价前提下，将子链接和子查询的查询语句，提升到外层查询进行关联查询。
join条件改写：等值join条件的join列增加非空过滤条件
- 场景：等值join，而且join列存在大量的null
- 优化原理：null值和任何值比较的结果都是null，而且通过给关联列添加is not null，降低基表扫描输出的数据量，从而减少参与join运算的数据量
not in改写：not in 转为not exists
- 场景：子链接输出列上不存在null值，或者逻辑判断语义上不需要比较null值。
- 优化原理：只输出where条件为true的结果。null 和任何值的比较操作都是null。null和bool类型的逻辑运算。