一文熟悉PolarDB-PG 分区表核心特性

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介: 在 PolarDB-PG 数据库中,分区表 (Partitioned Table) 使您能够将非常大的表分解为更小且更易于管理的部分,这个部分称为分区 (Partition) 。 每个分区都是一个独立的对象,具有自己的名称和可选的存储特性。本文首先简单的介绍了分区表策略以及它的优势特点,然后介绍了PolarDB-PG 分区表支持的查询优化特性,最后介绍了分区表上的本地索引和全局索引,从而帮助用户对PolarDB-PG 分区表有一个全面的了解。

作者:慎追

概述

在 PolarDB-PG 数据库中,分区表 (Partitioned Table) 使您能够将非常大的表分解为更小且更易于管理的部分,这个部分称为分区 (Partition) 。 每个分区都是一个独立的对象,具有自己的名称和可选的存储特性。从数据库管理员的角度来看,分区表具有多个部分,可以集中或单独管理。 这为管理员在管理分区表方面提供了相当大的灵活性。 然而,从应用程序的角度来看,分区表与非分区表是相同的; 使用 SQL 查询和 DML 语句访问分区表时无需进行任何修改。

功能优势

  • 更高的查询性能

在某些情况下,查询性能可以显著提高,特别是当表中大多数访问频繁的行位于单个分区或少量分区中时。 分区有效地替代了索引的上层树,使得索引的频繁使用的部分更有可能适合内存。当查询或更新访问单个分区或者少量分区时,可以通过使用该分区的顺序扫描而不是使用索引来提高性能,避免了分散在整个表中的随机访问读取。

  • 更方便的管理

分区对象具有可以集体或单独管理的部分。 DDL语句可以操作分区而不是整个表或索引。 因此,您可以分解资源密集型任务,例如重建索引或表。 您可以一次移动一个表分区。 如果出现问题,则只需重做分区移动,而不是表移动。 此外,如果分区设计中考虑了使用模式,则可以通过添加或删除分区来完成批量加载和删除。 使用DROP TABLE删除单个分区或执行ALTER TABLE DETACH PARTITION比批量操作要快得多。 这些命令还完全避免了批量 DELETE 造成的 VACUUM 开销。

  • 减少资源的争用

在某些 OLTP 系统中,分区可以减少对共享资源的争用。 例如,DML 分布在多个分区而不是一个分区上。

  • 提高可用性

分区不可用并不意味着整个表不可用。 查询优化器会自动从查询计划中删除未引用的分区,因此当分区不可用时查询不会受到影响。

  • 降低存储成本

不经常使用的数据可以迁移到更便宜和更慢的存储介质,可以节省成本。

以上分区表的优势通常只有在表非常大时才有价值。当单表的大小超过数据库服务器的物理内存大小时,且有一定的类别特征,建议使用分区表。



分区策略

PolarDB PostgreSQL版分区表提供了多种分区策略(Partitioning Strategies)来控制数据库如何将数据放入分区:

  • 范围分区 (Range Partitioning)

表被分区为由分区键定义的“范围”,分配给不同分区的值范围之间没有重叠。

  • 列表分区(List Partitioning)

列表分区是指通过显式列出每个分区中出现的键值来对表进行分区。

  • 哈希分区(Hash Partitioning)

哈希分区是指通过为每个分区指定模数和余数来对表进行分区。 每个分区将保存分区键的哈希值除以指定模数将产生指定余数的行。

  • 多级分区(Multi-Level Partitioning)

分区表被分成多个分区后,这些分区还可以继续被分区,这样的分区表被称之为多级分区。

PolarDB PostgreSQL版目前没有限制分区的级数,但是不建议建立太多级别。一般3级以下都属于正常范围,级别太多会不利于分区表的管理,同时查询性能可能也会退化。


分区表查询优化

分区剪枝

PolarDB PostgreSQL版提供了分区剪枝(Partition Pruning)功能,如果启用了分区剪枝,规划器将会检查每个分区的定义并且检验该分区是否因为不包含符合查询子句的行而无需扫描。若不包含,则会把分区从查询计划中排除(剪枝)。分区剪枝极大地减少了从磁盘检索的数据量并缩短了处理时间,从而提高了查询性能并优化了资源利用率。

根据实际的SQL语句,PolarDB PostgreSQL版数据库支持使用静态或动态剪枝。

  • 静态剪枝发生在编译时,并预先访问有关分区的信息。静态剪枝的一个示例场景是包含 WHERE 条件的 SQL 语句,该条件在分区键列上带有常量文字。
  • 动态剪枝发生在运行时,事先不知道语句要访问的确切分区。动态剪枝的一个示例是在 WHERE 条件中使用运算符或函数。

分区剪枝会影响发生剪枝的对象的统计信息,也会影响语句的执行计划。

分区剪枝技术将数据搜索限制为仅搜索您要搜索的值可能所在的分区。 这两种剪枝技术都会从查询的执行计划中删除分区,从而提高性能。

PolarDB PostgreSQL版中将条件表达式分为三个级别,即不变的(Immutable)、稳定的(Stable)、易变的(Volatile)。这三个级别依次对应了三种剪枝:

  • 如果条件表达式值是不变的(比如常量静态值),则它会被用于最早的剪枝优化器剪枝;
  • 如果条件表达式值是稳定的(比如),则会发生执行器初始阶段的剪枝;
  • 如果条件表达式是易变的(比如),则会发生执行器运行时剪枝。

分区连接

分区连接用于两个分区表之间Join优化。当分区表之间使用分区键进行Join时,可以通过分区连接减少分区之间无效的连接,提升连接查询的性能。



并行查询

PolarDB PostgreSQL版支持分区表的并行查询(Parallel Append)功能,可以更好地处理大规模数据的查询。当代计算机往往有更多的核心可以使用,并行查询是现代数据库必不可少的能力。PolarDB PostgreSQL版对分区表的并行查询,和普通表相比有更加优异的性能。



根据并行的方式,分区并行可分为分区间并行、分区内并行和混合并行。

分区间并行是指每个worker查询一个分区,从而实现多个worker并行查询整个分区表。

分区内并行是指每个分区内部并行查询,但是整个分区表是串行的。

混合并行是指分区间和分区内都可以并行执行,以达到分区表整体的并行执行,这是并行度最高的一种并行查询。

分区计划加速

PolarDB PostgreSQL版对分区表的分区数量没有限制。当分区超过2级时,分区数量便会成倍增加。

例如,一个分区表有两级分区,一级分区按照哈希分区,有100个分区;二级分区按照哈希分区,每个二级分区再次分成100个子分区。此时整个分区表共有10000个分区。此时如果对这个分区表进行查询,查询计划如下:


explain analyze select * from part_hash;
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Append  (cost=0.00..344500.00 rows=16300000 width=22)
   ->  Seq Scan on part_hash_sys0102  (cost=0.00..26.30 rows=1630 width=22)
   ->  Seq Scan on part_hash_sys0103  (cost=0.00..26.30 rows=1630 width=22)
   ->  Seq Scan on part_hash_sys0104  (cost=0.00..26.30 rows=1630 width=22)
  ...
  ...
  ...
   ->  Seq Scan on part_hash_sys10198  (cost=0.00..26.30 rows=1630 width=22)
   ->  Seq Scan on part_hash_sys10199  (cost=0.00..26.30 rows=1630 width=22)
   ->  Seq Scan on part_hash_sys10200  (cost=0.00..26.30 rows=1630 width=22)
 Planning Time: 3183.644 ms
 Execution Time: 633.779 ms
(10003 rows)
Total Memory: 216852KB


从上述结果可以看到,查询过程比较缓慢。这是因为分区表在优化器中的原理可以简单理解为:首先对每个分区生成最优的Plan,然后使用算子把这些Plan并联起来作为分区表的最优Plan。如果分区表的分区数量较少,这个过程会很快,对于用户是无感知的;但是一旦达到一定规模的分区数,这个过程变得逐渐明显,用户在查询过程中感到分区表的查询相比于普通表尤为缓慢。

如上面的SQL中,表有10000个分区,它的可以达到3秒左右,但普通表的查询仅需0.1毫秒,达到了几百倍的差距。并且除了上的差距,查询进程内存的占用也非常巨大,可能会引发OOM,分区表的这个缺陷在使用连接查询时更加明显。

为了解决这个问题,PolarDB PostgreSQL版提供了算子。它是一个分区表的查询算子,比Append更加高效,可以明显降低,且使用更少的内存,有效避免OOM。该算子用于解决分区表分区数量过多时,查询性能慢的问题。

下方展示了当使用算子时,分别查询SQL所用的和内存。


explain analyze select * from part_hash;
                                 QUERY PLAN                                  
----------------------------------------------------------------------------------------------------------------------------------------------------
PartitionedTableScan on part_hash  (cost=0.00..1.00 rows=1 width=22) (actual time=134.348..134.352 rows=0 loops=1)(Iteration partition number 10000)
   Scan Partitions: part_hash_sys0102, part_hash_sys0103, ...part_hash_sys10198, part_hash_sys10199, part_hash_sys10200
   ->  Seq Scan on part_hash  (cost=0.00..1.00 rows=1 width=22)
 Planning Time: 293.778 ms
 Execution Time: 384.202 ms
(5 rows)
Total Memory: 40276KB


相比于更加高效。如下测试数据展示了和的性能对比。


  1. 单条SQL的Planning Time


分区数量

Append Planning Time

PartitionedTableScan Planning Time

16

0.266ms

0.067ms

32

1.820ms

0.258ms

64

3.654ms

0.402ms

128

7.010ms

0.664ms

256

14.095ms

1.247ms

512

27.697ms

2.328ms

1024

73.176ms

4.165ms


  1. Memory(单条SQL的内存使用量)


分区数量

Append Memory

PartitionedTableScan Memory

16

1,170 KB

1,044 KB

32

1,240 KB

1,044 KB

64

2,120 KB

1,624 KB

128

2,244 KB

1,524 KB

256

2,888 KB

2,072 KB

512

4,720 KB

3,012 KB

1024

8,236 KB

5,280 KB


分区表的索引

本地索引

在分区表本地索引(Local Index)中,本地索引与分区表的分区一一对应,具有与其表相同的分区数和相同的分区范围。每个索引分区都与基础表的一个分区相关联,因此索引分区中的所有键仅引用单个表分区中存储的行。 因此数据库会自动将索引分区与其关联的表分区同步,从而使每个表索引相互独立。



本地索引通过指定属性创建。 在与基础表相同的列上对索引进行分区,创建相同数量的分区或子分区,并为它们提供与基础表的相应分区相同的分区范围。

当基础表中的分区被添加、删除、合并或拆分时,或者当散列分区或子分区被添加或合并时, PolarDB PostgreSQL版会自动维护索引分区。

如果分区列构成索引列的子集,则可以创建UNIQUE本地索引,从而保证具有相同索引键的行始终映射到同一分区。

全局索引

全局索引(Global Index)是一种B树索引,它也可以被分区,其分区独立于创建它的基础表。



不同于本地索引中索引分区和表分区一一对应的关系,全局索引分区可以指向所有表分区。全局索引也可以被分区,它的分区键必须是索引键的前缀。,PolarDB PostgreSQL版提供了全局索引功能。全局索引(Global Index)是一种在分区表上创建的索引。不同于默认在每个子分区上创建的局部索引(Local Index,一个索引对应一个子分区),全局索引通过一个索引来索引整个分区表的数据(一个索引对应多个子分区),从而可以提供非分区键上的全局唯一约束或者主键,也可以大幅提升非分区键的查询性能。


  • 非分区键上的点查性能


类别

TPS

Prepared Statement

不使用

使用

并发数

1

32

64

1

32

64

普通表

27,732

494,433

430,848

53,935

985,880

886,882

分区表+局部索引

367

4,155

3,688

856

8,742

6,790

分区表+全局索引

19,006

308,128

262,941

45,090

820,924

731,557


  • 非分区键上的TPC-B性能包含了点查和DML。


类别

TPS

Prepared Statement

不使用

使用

并发数

1

32

64

1

32

64

普通表

1,115

51,025

60,409

4,822

90,312

100,802

分区表+局部索引

271

2,903

2,524

550

5,276

4,237

分区表+全局索引

3453

36320

39941

4,334

69,040

75,232


总结

本文首先简单的介绍了分区表策略以及它的优势特点,然后介绍了PolarDB-PG 分区表支持的查询优化特性,最后介绍了分区表上的本地索引和全局索引,从而帮助用户对PolarDB-PG 分区表有一个全面的了解。如果希望进一步了解每个特性的使用,请查看PolarDB-PG 产品文档分区表特性


相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
10天前
|
关系型数据库 MySQL 测试技术
数据库专家带你体验PolarDB MySQL版 Serverless的极致弹性特性!
本次基于阿里云瑶池数据库解决方案体验馆,带你体验PolarDB MySQL Serverless形态下的性能压测环境,基于可选择的标准压测工具进行压测,构造弹性场景进行压测,实时动态展示弹性能力、价格和性价比结果,压测环境可开放定制修改、可重复验证。参与活动即有机会获得鼠标、小米打印机、卫衣等精美礼品。
数据库专家带你体验PolarDB MySQL版 Serverless的极致弹性特性!
|
10天前
|
关系型数据库 MySQL 测试技术
数据库专家带你体验PolarDB MySQL版 Serverless的极致弹性特性
本次基于阿里云瑶池数据库解决方案体验馆,带你体验PolarDB MySQL Serverless形态下的性能压测环境,基于可选择的标准压测工具进行压测,构造弹性场景进行压测,实时动态展示弹性能力、价格和性价比结果,压测环境可开放定制修改、可重复验证。参与活动即有机会获得鼠标、小米打印机、卫衣等精美礼品。
|
7月前
|
SQL 监控 关系型数据库
PostgreSQL普通表转换成分区表
如何使用pg_rewrite扩展将普遍表转换成分区表
401 0
|
10天前
|
存储 关系型数据库 分布式数据库
PolarDB-X HTAP新特性 ~ 列存索引
随着数据爆炸式的增长,传统的OLTP和OLAP解决方案基于简单的读写分离或ETL模型,将在线库的数据以T+1的方式抽取到数据仓库中进行计算,这种方案存在存储成本高、实时性差、链路和维护成本高等缺陷。 为应对数据爆炸式增长的挑战,PolarDB分布式版本基于对象存储设计了一套列存索引(Clustered Columnar Index,CCI)功能,支持将行存数据实时同步到列存存储上
76019 148
|
8月前
|
关系型数据库 调度 数据库
直播预告 | PolarDB-PG 企业级特性 —— Shared Server特性详解
PolarDB-PG 提供了 Shared Server 内置连接池功能,实现了用户连接与后端进程的解绑。后端进程在运行时可以根据实时负载和进程污染情况进行动态转换。负载调度算法使用 Stall 机制弹性控制 Worker 数量,同时避免用户连接饿死。从根本上解决了高并发或者大量短连接带来的性能、稳定性问题。
|
8月前
|
关系型数据库 分布式数据库 数据库
直播预告 | PolarDB-PG 企业级特性 —— 闪回特性详解
闪回表 (Flashback Table) 功能是PolarDB-PG数据库高可用的一个重要特性,支持在数据人为误操作时,快速闪回到某个时间点,恢复以及查看丢失的数据。本期分享将会介绍闪回表、闪回日志的基本原理以及特性,并演示闪回功能的使用方法。
直播预告 | PolarDB-PG 企业级特性 —— 闪回特性详解
|
10月前
|
存储 SQL 运维
PolarDB MySQL大表实践-分区表篇
背景:分区表到底是什么?分区作为传统企业级数据库的特性,早已经在很多大数据和数仓场景中得到广泛应用。基于维基百科的解释,分区是将逻辑数据库或其组成元素如表、表空间等划分为不同的独立部分。数据库分区通常是出于可管理性、性能或可用性的原因,或者是为了负载平衡。它在分布式数据库管理系统中很流行,其中每个分区可能分布在多个节点上,节点上的用户在分区上执行本地事务。这提高了具有涉及某些数据视图的常规事务的站
680 0
PolarDB MySQL大表实践-分区表篇
|
存储 SQL 缓存
PolarDB-MySQL 新特性 - Partial Result Cache
背景查询缓存(Query Cache)是数据库执行层的一个加速查询的特性,用来缓存一条查询语句的结果集,如果后续再有相同的查询,直接从结果集缓存中读取结果,而不用再重新执行而极大提升查询性能。但Query Cache在实际业务使用中存在较多的局限性,首先能够命中Query Cache的规则非常严格,必须是完全相同的SQL语句,并且被查询的表的数据不能有任何的修改,有任意规则不符合要求都会造成cac
167 0
PolarDB-MySQL 新特性 - Partial Result Cache
|
关系型数据库 分布式数据库 PolarDB
|
关系型数据库 分布式数据库 PolarDB

相关产品

  • 云原生数据库 PolarDB