一文熟悉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

1. Memory（单条SQL的内存使用量）

分区表的索引

本地索引

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

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

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

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

全局索引

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

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

• 非分区键上的点查性能

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

总结

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