gaussdb大表查询性能慢的解决思路

_Evelyn

已于 2024-08-28 15:17:16 修改

阅读量834

点赞数 8

分类专栏：大数据文章标签： gaussdb

于 2024-08-28 15:09:32 首次发布

本文链接：https://blog.csdn.net/weixin_44861366/article/details/141641062

版权

大数据专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、引言

1.背景介绍

数字化和智能化转型是经济发展的新动力。而面对国外对中国数据库市场的长期垄断，人们对于中国自研数据库的需求越来越普遍，高斯数据库成为许多企业的选择之一。在经营分析5G智慧加油站项目项目中，我接触到高斯数据库，并使用它来进行数据的存储和查询。高斯数据库是以PostgreSQL为内核进行开发的一款数据库产品，其具备了PostgreSQL的高并发特性，又具有很强的通用性，支持行和列两种存储方式，以适配不同的数据场景。然而在我们数据分析场景下，PB级的大表存储和查询依然存在性能问题，那么对大表的查询性能优化在支撑数据分析工作中显得尤其重要。

二、如何进行大表的查询性能优化

在经营分析5G智慧加油站项目项目中，我们经常遇到这些令人头疼的情况：当用户查看报表时，一张PB级大表在直接单表查询的时候耗时达到几十分钟，不仅业务等不及，还消耗了数据库集群的整体资源；当我们开发好的python脚本在平台集群上调度跑数时，一个周期性的大表作业跑数时间长达几个小时，导致了集群整体性能下降，甚至导致大量作业报错。遇到这些情形，我们开发人员该怎么应对呢？

1.大表的单表查询性能慢

遇到单表查询性能慢的问题，我们首先应该查看大表的存储方式是否合理。
1) 评估是否需要分区。 千万级数据量的表，如果业务场景支持，必须进行分区。在经分5G智慧加油站项目项目中，我们一般按时间字段（例如账期billing_cycle_id）进行分区，还有一些表有业务分类，比如我们的产品实例表按产品类型进行分类了，可以用产品类型字段做分区。同样，也可以对多个字段的组合进行分区，比如产品实例日表，我们按产品类型和账期两个字段进行组合分区，这样可以显著提升查询性能。
用查看表结构的语句可以查看表的分区情况：

SELECT * FROM pg_tables WHERE tablename = 'your_table_name';

用这个SQL可以对已经建好的表加分区：

ALTER TABLE your_table_name 
Add CONSTRAINT your_table_name_partition PARTITION BY RANGE (date_id)
(
    PARTITION p1 VALUES LESS THAN ('2024-01-01'),
    PARTITION p2 VALUES LESS THAN ('2024-07-01'),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

不过，对已建好的表加分区会导致表中的数据重新分布，这是一个非常消耗数据库性能的动作，对于这种数据量很大的表进行这种操作风险较大，正确的办法应该是对表的数据做好备份，然后重建该表。备份表并重建分区表的SQL如下：

--数据备份
CREATE TABLE new_your_table_name AS SELECT * FROM your_table_name;
--重建分区表
CREATE TABLE your_table_name (
    col1 INT,
    col2 INT,
    col3 DATE,
    col4 DECIMAL(10, 2)
)
DISTRIBUTED BY (col2)
PARTITION BY RANGE (col3)
(
    PARTITION p1 VALUES LESS THAN ('2023-01-01'),
    PARTITION p2 VALUES LESS THAN ('2023-07-01'),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

2) 评估常用的查询字段是否应该建索引。 比如合同号、订单号。业务聚焦的查询通常只有少数几单合同或几个订单，我们在这些业务字段上建索引，可以有效地提升查询性能。
用查看表结构的语句可以查看表的索引：（gaussdb以pg库为内核，所以很多系统表、系统视图都延续了pg库的使用方法）

SELECT  pg_table_def('your_table_name');

建普通索引的SQL语句如下：

CREATE INDEX your_table_name_idx ON your_table_name (column_name);

3) 评估是否应该修改表的存储方式。 列式存储会进行数据压缩，能够明显提升读性能，在这种单表查询的场景中的表现非常突出。如果大表最常用的场景就是单表查询，可以把耗时较长的写过程交给DWS层，将业务直接查询的大表放置在数仓的DM层，并采用列式存储。
用查看表结构的语句可以查看表的存储方式：

SELECT  pg_table_def('your_table_name');

将表存储方式改为列式存储：

ALTER TABLE your_table_name SET (storage_type = 'COLUMN');

4) 查询表的倾斜率。 在分布式存储的系统中，数据倾斜经常发生，其也是影响单表查询非常重要的原因。我们的高斯库一般采用hash分布方式，数据倾斜一般是因为key的hash分布不均匀导致。高斯数据库建表时需要用distributed by规定数据的分布规则。我们通过查询PGXC_GET_TABLE_SKEWNESS表来查看该表的分布列是否均匀分布。
查看表的倾斜率：

SELECT PGXC_GET_TABLE_SKEWNESS('your_table_name');

修改表的分布列：

ALTER TABLE your_table_name SET DISTRIBUTED BY (new_distribution_column);

注意，修改表的分布列也很消耗集群性能，需要在业务低峰时期进行操作。
5) 表的统计信息是否未收集。 统计信息可以为优化器选择最优执行计划提供依据，因此收集统计信息也是优化查询性能的一项重要操作。在对表进行批量DML或DDL操作后，表的执行计划可能发生变化。虽然有些大批量动作会触发GAUSSDB自动收集表的统计信息，但是这个自动收集的动作人为难以干预，因此当表查询性能变慢时，我们应该将统计信息的收集也纳入考虑因素中。并且，针对容易出现性能问题的表、占用内存大且经常被访问的表，我们要定期收集统计信息，可以将收集统计信息的语句加入调度脚本中。
收集表的统计信息：

Analyse your_table_name;

6) 分析表的碎片率。 日常调度对表的增删改往往会导致表水位线变高，导致表的存储空间“虚大”，给数据查询造成严重的负担。因此需要维护人员对表定时进行碎片清理，一般使用VACUUM 命令来进行碎片清理。VACUUM 命令用于回收数据库中未使用的存储空间，并更新表的统计信息。但是只用的VACUUM语句不够，VACUUM FULL是一种更彻底的操作，它不仅回收空间，还将表的存储整理成连续的空间。
对表清理碎片：

vacuum full your_table_name;

清理大表碎片的动作耗时通常会超过半小时，而分区表内存很大，为了避免耗时太长被数据库杀掉进程，我们往往对分区表按分区进行清理。
对表特定分区清理碎片：

vacuum full your_table_name PARTITION (p2024_01);

注意，这个清理碎片的动作会占用大量数据库资源，最好避开调度高峰期。操作。

2.大表的关联查询性能慢

如果开发人员开发存储过程部署到集群上运行后，发现作业调度非常慢，通常要跑几个小时。通过对日志的监控，我们发现存储过程中的一段关联逻辑导致了性能慢，这种情况应该怎么办？
对于这种关联查询的优化，我们运维、开发人员必须对数据库的执行计划有深刻理解。本文只讲述如何解决性能问题，关于执行计划系统性知识可以参考这篇文章《详解GaussDB(DWS) explain分布式执行计划》：https://blog.51cto.com/u_15214399/2820329
Explain命令是针对SQL进行执行计划的评估，一般趋向于SQL本身性能的评估，评估结果可能和实际的执行结果不一样，有可能有误差。因此我们一般用Explain performance查询SQL的执行计划，这个命令对SQL的每个阶段的执行过程进行时间评估，是实际的执行结果，而且这样可以用表格的形式输出执行计划，可读性较好。但是Explain performance执行速度比单纯的Explain慢，当SQL本身性能问题较大、很久都查不出来的时候，Explain performance可能也耗时很久都查不出来，这时还是先使用explain进行分析。
查询SQL的执行计划：

Explain performance Select a.col1,a.cxol2,b.col1,b.col2 from table1 a inner join table2 b on a.col1=b.col1;

1) 通过explain查询执行计划中基表E-rows的估算行数。 如果查询行数太大，建议用一定的过滤条件进行分段取数。如果只是进行关联查询，可以在该事务中设置guc参数default_statistics_target为-2，即按2%收集样本估算统计信息。

2) 通过explain verbose看query的执行计划中是否有warning信息。 如：

WARNING:Statistics in some tables or columns(public.lineitem(l_receiptdate,l_commitdate,l_orderkey, l_suppkey), public.orders(o_orderstatus,o_orderkey)) are not collected.
HINT:Do analyze for them in order to generate optimized plan.

这则是提示关联的表没有收集统计信息。查看和确认涉及的表或列没有执行ANALYZE后，可以通过对WARNING或日志中上报的表或列执行ANALYZE,直接在事务中执行“ANALYZE TABLENAME”即可，也可以将这个过程写到存储过程或python脚本中，每次对目标表进行了更新对其表分析，可以提升目标表的查询性能。
收集表的统计信息：

Analyse your_table_name;

3) 如果通过观察执行计划发现该sql对大表执行了hash join，且连接的内表行数是外表行数的10倍或以上，同时内表在每个DN上的平均行数大于10万行，且发生了下盘。此时应该将join方式改为nest loop join，扫描节点可以实现index scan，性能预计将会提升。可以设置这个参数：

SET enable_hashjoin = off;

4) 如果通过观察执行计划发现大表上有IndexScan，说明该查询走了索引。上面有提到，索引可以优化单表查询，但是在多表关联的场景下，查询走索引不一定是好事，比如，a表与b表关联，关联字段为b表的索引字段，而不巧a表、b表数据量又都很大，这种情况下如果走了b表的索引，a表的每条数据关联查询b表都会去查b表的索引，消耗大量计算性能，适得其反。那么针对这种情况，我们一般采用hint语句/*+ NoIndexScan(table) */去干预查询计划。
其原理图如下：

优先不在表上使用索引扫描，加上hint后再执行一次执行计划看IndexScan是否消失，执行性能是否变快：

Explain performance 
Select /*+ NoIndexScan(b) */ 
a.col1,a.cxol2,b.col1,b.col2 
from table1 a inner join table2 b 
on a.col1=b.col1;

三、总结

1.经验分享

这一套查询性能优化解决方案解决了我们经营分析5G智慧加油站项目中GAUSSDB的绝大部分SQL性能问题，让大内存的表调度慢不再成为每个开发人员头疼的问题。有时候平台上提供的报错信息显示任务超时，一般是脚本中SQL有性能问题，我们就需要分场景一步步去找原因。数据库报错超时并不像其他编程语言有那么清晰的报错原因，很多情况需要我们开发人员细心、有解决经验。有了这一套解决方案的总结并让开发、运维人员一起交流经验，集思广益，持续积累，能降低性能优化门槛，让新人更快上手。