什麼是分區,有哪些使用情境和功能優勢 - PolarDB

本文詳細介紹了PolarDB PostgreSQL版分區表功能的優勢和特性。

概述

在PolarDB PostgreSQL版資料庫中，分區表 (Partitioned Table) 是將一個表或索引物理地分解為多個更小、更便於管理的部分，這個部分稱為分區 (Partition) 。每個分區都是一個獨立的對象，具有自己的名稱和可選的儲存特性。從資料庫管理員的角度來看，分區表具有多個部分，可以集中或單獨管理。這為管理員在管理分區表方面提供了相當大的靈活性。然而，從應用程式的角度來看，分區表與非分區表是相同的；使用SQL查詢和 DML語句訪問分區表時無需進行任何修改。

表的每個分區必須具有相同的邏輯屬性，例如列名、資料類型和約束，但每個分區可以具有單獨的物理屬性，例如啟用或禁用壓縮、實體儲存體設定和資料表空間。

分區表對於許多不同類型的應用程式很有用，尤其是管理大量資料的應用程式，如資料庫。 OLTP資料庫通常受益於可管理性和可用性的改進，而OLAP資料倉儲則受益於效能和可管理性。

使用情境

當表非常大的時候（例如，資料表空間大於資料庫伺服器的實體記憶體），可以對錶進行分區，從而提升資料庫效能。例如，當表的資料庫大於2 GB時，應該考慮使用分區。
當大表用於儲存歷史資料，新資料均加入至某個最新的分區，該情境下建議使用分區表。例如，大表用於儲存一年12個月的歷史資料。當前月份的資料存在一個單獨的分區中，該分區的資料是可更新的。而歷史月份的資料則存在另一個唯讀分區中。

功能優勢

更高的查詢效能
在某些情況下，查詢效能可以顯著提高，特別是當表中大多數訪問頻繁的行位於單個分區或少量分區中時。分區有效地替代了索引的上層樹，使得索引的頻繁使用的部分更有可能適合記憶體。當查詢或更新訪問單個分區或者少量分區時，可以通過使用該分區的順序掃描而不是使用索引來提高效能，避免了分散在整個表中的隨機訪問讀取。
更方便的管理
分區對象具有可以集體或單獨管理的部分。 DDL語句可以操作分區而不是整個表或索引。因此，您可以分解耗用大量資源的工作，例如重建索引或表。您可以一次移動一個表分區。如果出現問題，則只需重做分區移動，而不是表移動。此外，如果分區設計中考慮了使用模式，則可以通過添加或刪除分區來完成批量載入和刪除。使用DROP TABLE刪除單個分區或執行ALTER TABLE DETACH PARTITION比大量操作要快得多。這些命令還完全避免了批量 DELETE 造成的 VACUUM 開銷。
減少資源的爭用
在某些 OLTP 系統中，分區可以減少對共用資源的爭用。例如，DML 分布在多個分區而不是一個分區上。
提高可用性
分區不可用並不意味著整個表不可用。查詢最佳化工具會自動從查詢計劃中刪除未引用的分區，因此當分區不可用時查詢不會受到影響。
降低儲存成本
不經常使用的資料可以遷移到更便宜和更慢的儲存介質，可以節省成本。

以上分區表的優勢通常只有在表非常大時才有價值。當單表的大小超過資料庫伺服器的實體記憶體大小時，建議使用分區表。

分區表特性

分區表相比於普通表的內部實現更加複雜，但是這一切對於使用者而言是不需要感知的，分區表的管理與使用時與普通表相比也有些區別，更加清楚地瞭解分區表的特性，有利於使用者可以正確高效地使用分區表。

案例1：

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

...
CREATE TABLE measurement_y2007m11 PARTITION OF measurement
    FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');

CREATE TABLE measurement_y2007m12 PARTITION OF measurement
    FOR VALUES FROM ('2007-12-01') TO ('2008-01-01')
    TABLESPACE fasttablespace;

CREATE TABLE measurement_y2008m01 PARTITION OF measurement
    FOR VALUES FROM ('2008-01-01') TO ('2008-02-01')
    WITH (parallel_workers = 4)
    TABLESPACE fasttablespace;

分區鍵

分區鍵（Partition Key）是一個列或多個列的組合，用於確定分區表中的每一行應位於哪個分區。分區表必須要確保每行都明確分配給某一個分區。PolarDB PostgreSQL版會使用分區鍵自動將插入、更新和刪除操作定向到正確的分區。

如上方分區表案例1，其中logdate就是分區表measurement表的分區鍵。measurement表的每一個分區都是由logdate的取值範圍來確定邊界的。

分區策略

PolarDB PostgreSQL版分區表提供了多種分區策略（Partitioning Strategies）來控制資料庫如何將資料放入分區：

定界分割 (Range Partitioning)
表被分區為由分區鍵定義的“範圍”，分配給不同分區的值範圍之間沒有重疊。例如，可以按日期範圍或特定業務對象的標識符範圍進行分區。每個範圍的界限被理解為包括下端和不包括上端。例如，如果一個分區的範圍是從 1 到 10，下一個分區的範圍是從10到20，則值10屬於第二個分區而不是第一個分區。如上方案例1的measurement表就是一個定界分割表。
INTERVAL RANGE分區是定界分割的擴充，詳細資料，請參見INTERVAL RANGE分區。
列表分區（List Partitioning）
案例2：
```
CREATE TABLE department(deptno INT4 Primary Key,dname VARCHAR(50), location VARCHAR(100)) PARTITION BY LIST (deptno);
CREATE TABLE department_p1 partition of department for values in (10, 20);
CREATE TABLE department_p1 partition of department for values in (30, 40);
```
列表分區是指通過顯式列出每個分區中出現的索引值來對錶進行分區。如上方案例2中的department表則是使用了列表分區。它的兩個分區都顯示指定了分區鍵的值，比如department_p1中只會儲存 deptno為10和20的行，department_p2中只會儲存 deptno為30和40的行。
雜湊分割（Hash Partitioning）
雜湊分割是指通過為每個分區指定模數和餘數來對錶進行分區。每個分區將儲存分區鍵的雜湊值除以指定模數將產生指定餘數的行。
案例3：
```
create table idxpart (i int) partition by hash (i);
create table idxpart0 partition of idxpart for values with (modulus 2, remainder 0);
create table idxpart1 partition of idxpart for values with (modulus 2, remainder 1);
```
如案例3中的idxpart表則是使用了雜湊分割。比如idxpart0中只會儲存i的雜湊值除以2餘0的行，idxpart表則是使用了雜湊分割。比如idxpart1中只會儲存i的雜湊值除以2餘1的行。

多級分區（Multi-Level Partitioning）

分區表被分成多個分區後，這些分區還可以繼續被分區，這樣的分區表被稱之為多級分區。

PolarDB PostgreSQL版目前沒有限制分區的級數，但是不建議建立太多層級。一般3級以下都屬於正常範圍，層級太多會不利於分區表的管理，同時查詢效能可能也會退化。

不同層級的分區策略可以不同，如第一級分區使用定界分割，第二級使用雜湊分割，第三級使用列表分區。

案例4：

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01') PARTITION BY Hash (city_id);

CREATE TABLE measurement_y2006m03_hash1 PARTITION OF measurement_y2006m03
    for values with (modulus 2, remainder 0) PARTITION BY List (peaktemp);

CREATE TABLE measurement_y2006m03_hash1_l1 PARTITION OF measurement_y2006m03_hash1 for values in (10, 20);

文法

關於各分區類型的分區表的相關命令及說明，例如建立分區表、添加分區至分區表、合并分區、拆分分區、刪除分區等，請參見分區表命令列表。