《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB PostgreSQL版解析与实践(下)——三、SQL性能调优(下)

本文涉及的产品
阿里云百炼推荐规格 ADB PostgreSQL,4核16GB 100GB 1个月
简介: 《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB PostgreSQL版解析与实践(下)——三、SQL性能调优(下)

更多精彩内容,欢迎观看:《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB PostgreSQL版解析与实践(下)——三、SQL性能调优(下):


6. 可视化执行计划

 

如图是执行计划可视化展示。

 

 示例语句

explain (format json,analyze true) select count(*) from test,testr where test.num1=testr.num2;


image.png

 

Postgres EXPLAIN Visualizer

http://tatiyants.com/pev/#/plans/new

 

7. 如何发现问题

 

自上而下,梳理痛点:自上而下梳理计划,确定时间开销大的算子。

查看代价,对比行数:查看比较代价估算的异常,对比估算行数和实际执行行数差异大的情况。

耗时算子,尽量避免:AP场景很少需要NestLoop、Sort+GroupByAgg。

具体算子,是否合理:是否有不必要的Motion算子,Join内外表顺序是否合适,Scan是否可以使用索引。

内存信息,调整参数:查看下盘情况,分析后适当调整statement_mem参数。

 

8. 通过索引提升查询性能

 

ADB PG支持如下索引类型及语句示例/适用场景:

 

B-tree:create index i1 on t1 using btree(c1),适用大多数场景,尤其对于点查询和更新等操作。

Bitmap:create index i2 on t2 using bitmap(c2),唯一值低于10w且低于总行数1/10,常与其他列有联合过滤条件。

GIN/GiST:全文检索,数组,JSON。

 

1) B-Tree索引优化建议,建议创建索引的场景

 

点查询的场景。

where条件的过滤效果较好的场景。

 

2) 不建议创建索引的场景

 

更新较多的表上不建议建索引,更新较为频繁的表上创建索引。

一个表的索引数最好不超过6个。

避免创建超过3列的组合索引。

避免创建重复的索引或具有相同前导列的索引。

 

3) 索引使用的建议

 

组合索引是从前向后匹配where条件的,不能命中前导列的where条件,不会使用该索引。

批量导入大量数据前可删除索引,导入数据后重建索引。

索引创建完成后,最好做一下统计信息收集。

 

9. 消除Redistribute Motion

 

在进行连接或聚集操作时,会根据数据分布情况添加分布式算子,对数据进行重分布Redistribute Motion或广播Broadcast Motion。分布式算子会占用大量的网络资源。如果能够通过建表和业务逻辑进行分布式算子的规避,则能够提升数据库查询性能。

 

示例假设有两张表,执行查询语句:

 

SELECT* FROM t1,t2 WHERE t1.a=t2.a;

t1表的分布键为t1.a,t2表的分布列是t2.b,会出现t2表的重分布

t1表的分布键为t1.a,t2表的分布列是t2.a,无需重分布直接Join。

image.png

 

10. 避免下盘

 

查询执行过程中,当集群内存不足时,数据库可能会选择将临时结果暂存到磁盘。由于磁盘操作相对内存访问缓慢,避免查询执行过程中的算子下盘,有助于提高查询效率。

算子下盘常见原因优化建议:调整statement mem(默认2GB)。

 

11. 锁的检测及处理

 

1) 死锁

 

死锁的检测和处理为数据库内部机制,无需手工干预,出现死锁会影响数据库吞吐量。

 

死锁检测方式:

 Local Deadlock Detector:用于检测单个计算节点内发生的死锁。

 Global Deadlock Detector:用于检测跨计算节点发生的分布式死锁。

 

示例

 

Session1

BEGIN;UPDATE t SET j=33 WHERE pk=3;UPDATE t SET j=33 WHERE pk=7;END;

  

Session2

BEGIN;UPDATE t SET j = 33 WHERE pk=7;UPDATE t SET j = 33 WHERE pk=3;END;

 

 当pk=3,pk=7落在单个计算节点上时,Local Deadlock Detector能检测到死锁;

当pk=3,pk=7落在不同计算节点上时,Global Deadlock Detector能检测到这种分布式死锁。

 

2) 常规锁

 

查看所有当前被加锁的对象,以及相应加锁的SQL

 

执行语句

select * from gp_toolkit.gp_locks_on_relation where lorrelname ='<table>';

  

12. 空间回收

 

为什么会空间膨胀

 

表中的数据被删除或更新后UPDATE/DELTE,物理存储层面并不会直接删除数据,而是标记这些数据不可见,所以会在数据页中留下很多“空洞”,在读取数据时,这些“空洞”会随数据页一起加载,拖慢数据扫描速度,需要定期回收删除的空间。

 

膨胀率判断方法

 

通过gp_toolkit.gp_bloat_diag视图,bdirelpages表示表实际占用Page数,bdiexppages表示表实际需要Page数,bdirelpages/bdiexppages > 4时,即可考虑进行空间回收。

 

回收操作可以通过vacuum和vacuum full

 

ü vacuum:回收时不锁表,但只标记删除空间可被再利用,不释放物理空间。

ü vacuum full:回收时锁表,表无法读写,回收物理空间,建议在维护窗口进行。

 

维护定期回收空间任务

https://help.aliyun.com/document_detail/59176.html

 

13. 避免数据倾斜

 

image.png 

 

1) 表现形式

 

数据存储倾斜,表现形式为数据在多个Segment节点上分布不均匀,存在如下影响:

 

磁盘存储水位不均匀,个别Segment节点磁盘使用过多,提前用满磁盘存储空间。

节点参与计算数据量不均匀,存在木桶效应。

 

2) 数据倾斜排查

 

用户控制台排查:

 

控制台基础信息项,会展示实例最大存储水位与实例存储总水位,存储数据倾斜时,两个数值将会差异过大。

 控制台监控与报警项,计算节点监控处会展示所有计算节点的空间使用量,存储数据倾斜时,节点磁盘空间使用量会差异过大。

 

通过SQL排查:

 

 通过控制台信息确定存在存储倾斜后,使用SQL排查倾斜的表。

 查询结果根据数据倾斜程度排序,当tb_balance_rate大于1.1时,认为该表存在数据倾斜。

 

排查同一张表在各个Segment节点下的存储数据量,执行如下语句:

select gp_segment_id, pg_size_pretty(pg_total_relation sizeltable name))from gp_dist_random('gp_id');

  

排查同一张表在各个Segment节点下的行数,执行如下语句:

select gp_segment_id,count(1) from table name group by gp segment id;

  

3) 避免数据倾斜

 

建表过程指定分布键或分布规律:

CREATE TABLE table_name (......) [DISTRIBUTED BY(column name,[...]) |DISTRIBUTED RANDOMLY |DISTRIBUTED REPLICATED];

  

修改分布键或分布规律:

ALTER TABLE [IF EXISTS] [ONLY] name SET WITH (REORGANIZE=true/false)| DISTRIBUTED BY (column_name,[...])|DISTRIBUTED RANDOMLY|DISTRIBUTED REPLICATED;

  

注意

修改分布键或分布规律,大多数情况都将会进行数据迁移,对于数据量过大的表,该操作会相对较久并且会锁表,无法查询

REORGANIZE=false仅在修改前后一致、或修改为随机分布时才会不进行数据重分布。

 

分布策略选择规则:

 

 小表(总行数低于1万)优先选择复制表分布策略(DISTRIBUTED REPLACATED)。

 大表优先选择参与Join/GroupBy计算的字段作为分布键Hash分布。

 若没有数据分布均匀的字段作为分布键使用,采用随机分布策略(DISTRIBUTED RANDOMLY)。

 

相关实践学习
AnalyticDB PostgreSQL 企业智能数据中台:一站式管理数据服务资产
企业在数据仓库之上可构建丰富的数据服务用以支持数据应用及业务场景;ADB PG推出全新企业智能数据平台,用以帮助用户一站式的管理企业数据服务资产,包括创建, 管理,探索, 监控等; 助力企业在现有平台之上快速构建起数据服务资产体系
相关文章
|
3月前
|
SQL 关系型数据库 PostgreSQL
CTE vs 子查询:深入拆解PostgreSQL复杂SQL的隐藏性能差异
本文深入探讨了PostgreSQL中CTE(公共表表达式)与子查询的选择对SQL性能的影响。通过分析两者底层机制,揭示CTE的物化特性及子查询的优化融合优势,并结合多场景案例对比执行效率。最终给出决策指南,帮助开发者根据数据量、引用次数和复杂度选择最优方案,同时提供高级优化技巧和版本演进建议,助力SQL性能调优。
226 1
|
6月前
|
SQL 关系型数据库 MySQL
如何优化SQL查询以提高数据库性能?
这篇文章以生动的比喻介绍了优化SQL查询的重要性及方法。它首先将未优化的SQL查询比作在自助餐厅贪多嚼不烂的行为,强调了只获取必要数据的必要性。接着,文章详细讲解了四种优化策略:**精简选择**(避免使用`SELECT *`)、**专业筛选**(利用`WHERE`缩小范围)、**高效联接**(索引和限制数据量)以及**使用索引**(加速搜索)。此外,还探讨了如何避免N+1查询问题、使用分页限制结果、理解执行计划以及定期维护数据库健康。通过这些技巧,可以显著提升数据库性能,让查询更高效流畅。
|
6月前
|
SQL 容灾 关系型数据库
阿里云DTS踩坑经验分享系列|DTS打通SQL Server数据通道能力介绍
SQL Server 以其卓越的易用性和丰富的软件生态系统,在数据库行业中占据了显著的市场份额。作为一款商业数据库,外部厂商在通过解析原生日志实现增量数据捕获上面临很大的挑战,DTS 在 SQL Sever 数据通道上深研多年,提供了多种模式以实现 SQL Server 增量数据捕获。用户可以通过 DTS 数据传输服务,一键打破自建 SQL Server、RDS SQL Server、Azure、AWS等他云 SQL Server 数据孤岛,实现 SQL Server 数据源的流动。
306 0
阿里云DTS踩坑经验分享系列|DTS打通SQL Server数据通道能力介绍
|
7月前
|
SQL 关系型数据库 OLAP
云原生数据仓库AnalyticDB PostgreSQL同一个SQL可以实现向量索引、全文索引GIN、普通索引BTREE混合查询,简化业务实现逻辑、提升查询性能
本文档介绍了如何在AnalyticDB for PostgreSQL中创建表、向量索引及混合检索的实现步骤。主要内容包括:创建`articles`表并设置向量存储格式,创建ANN向量索引,为表增加`username`和`time`列,建立BTREE索引和GIN全文检索索引,并展示了查询结果。参考文档提供了详细的SQL语句和配置说明。
162 2
|
8月前
|
SQL Oracle 关系型数据库
如何在 Oracle 中配置和使用 SQL Profiles 来优化查询性能?
在 Oracle 数据库中,SQL Profiles 是优化查询性能的工具,通过提供额外统计信息帮助生成更有效的执行计划。配置和使用步骤包括:1. 启用自动 SQL 调优;2. 手动创建 SQL Profile,涉及收集、执行调优任务、查看报告及应用建议;3. 验证效果;4. 使用 `DBA_SQL_PROFILES` 视图管理 Profile。
|
8月前
|
存储 人工智能 关系型数据库
AnalyticDB PostgreSQL版:Data+AI 时代的企业级数据仓库
AnalyticDB PostgreSQL版是面向Data+AI时代的企业级数据仓库,涵盖产品架构、核心技术、客户案例及功能发布四大部分。产品架构包括数据分析和AI/ML的存储与计算优化;核心技术涉及高性能实时引擎Beam、向量化执行引擎Laser及优化器Orca;客户案例展示了丝芙兰和领跑汽车的应用;新功能如pgsearch全文检索和In-Database AI/ML进一步提升了性能与易用性。
190 0
|
10月前
|
SQL 数据库 UED
SQL性能提升秘籍:5步优化法与10个实战案例
在数据库管理和应用开发中,SQL查询的性能优化至关重要。高效的SQL查询不仅可以提高应用的响应速度,还能降低服务器负载,提升用户体验。本文将分享SQL优化的五大步骤和十个实战案例,帮助构建高效、稳定的数据库应用。
732 3
|
10月前
|
SQL IDE 数据库连接
IntelliJ IDEA处理大文件SQL:性能优势解析
在数据库开发和管理工作中,执行大型SQL文件是一个常见的任务。传统的数据库管理工具如Navicat在处理大型SQL文件时可能会遇到性能瓶颈。而IntelliJ IDEA,作为一个强大的集成开发环境,提供了一些高级功能,使其在执行大文件SQL时表现出色。本文将探讨IntelliJ IDEA在处理大文件SQL时的性能优势,并与Navicat进行比较。
150 4
|
10月前
|
SQL 存储 缓存
如何优化SQL查询性能?
【10月更文挑战第28天】如何优化SQL查询性能?
525 10
|
10月前
|
SQL 关系型数据库 MySQL
惊呆:where 1=1 可能严重影响性能,差了10多倍,快去排查你的 sql
老架构师尼恩在读者交流群中分享了关于MySQL中“where 1=1”条件的性能影响及其解决方案。该条件在动态SQL中常用,但可能在无真实条件时导致全表扫描,严重影响性能。尼恩建议通过其他条件或SQL子句命中索引,或使用MyBatis的`&lt;where&gt;`标签来避免性能问题。他还提供了详细的执行计划分析和优化建议,帮助大家在面试中展示深厚的技术功底,赢得面试官的青睐。更多内容可参考《尼恩Java面试宝典PDF》。

热门文章

最新文章

推荐镜像

更多