如何將普通錶轉換為分區表 - PolarDB

當單張普通表的資料量超過10億行或1 TB時，建議採用分區表。本文將介紹如何使用ALTER文法將單張普通錶轉換為分區表。

前提條件

在將普通錶轉換為分區表時，若普通表存在主鍵（Primary Key）和唯一鍵（Unique Key），則主鍵和唯一鍵均需包含分區鍵（Partition Key）欄位。以確保每個分區內資料及全域資料的唯一性。詳細樣本，請參見樣本。

例如：

若按單列order_id進行分區，則主鍵和唯一鍵應包含order_id（如PRIMARY KEY(id, order_id)）。
若按多列region_id和order_date進行分區，則主鍵和唯一鍵應包含region_id和order_date（如PRIMARY KEY(id, region_id, order_date)）。

注意事項

普通錶轉換為分區表的過程中，所有的資料都需要重新進行讀寫，屬於COPY DDL操作。因此，轉換時間較長，且整個轉換過程會阻塞當前表上的所有DML操作。
說明
COPY DDL執行演算法是需要將表中所有的資料複製到新表中。在資料複製期間，會持有原表的SNW（SHARED_NO_WRITE）鎖。因此，在執行DDL操作期間僅支援讀操作，不允許執行並發寫入操作，對業務影響較大。更多資訊，請參見DDL執行演算法。
如果需要按照時間做RANGE分區，並且需要將所有資料放在一個歷史分區中，請參見普通錶快速轉換為RANGE分區表。

支援轉換的分區表類型

所有的分區表類型（如HASH、RANGE以及LIST等）均支援普通錶轉換分區表操作。詳細分區表類型請參見分區表類型及使用說明和INTERVAL RANGE分區。

文法

ALTER TABLE table_name
PARTITION BY RANGE {(expr) | COLUMNS(column_list)}
(partition_definition [, partition_definition] ...);

其中，partition_definition的定義與各個類型的分區表中的partition_definition保持一致，支援二級分區。

說明

PolarDB MySQL版支援的分區表類型及使用，請參見分區表類型及使用說明。

樣本

單列分區

此處以普通表test_users為例，為您示範如何將普通錶轉換為分區表。

建立普通表test_users，並插入資料。

CREATE TABLE test_users (
    id INT AUTO_INCREMENT,
    region_id INT NOT NULL, -- 分區鍵
    email VARCHAR(100) NOT NULL,
    name VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, region_id),  -- 主鍵必須包含分區鍵欄位
    UNIQUE KEY (email, region_id) -- 唯一鍵必須包含分區鍵欄位
);

INSERT INTO test_users (region_id, email, name) VALUES
(1, 'alice@example.com', 'Alice'),
(2, 'bob@example.com', 'Bob'),
(3, 'charlie@example.com', 'Charlie');

將普通表test_users轉換為分區表。

轉換為LIST分區表。更多資訊，請參見LIST。

ALTER TABLE test_users 
    PARTITION BY LIST (region_id) (
        PARTITION p_east VALUES IN (1, 2),  -- region_id為1或2的資料到p_east
        PARTITION p_west VALUES IN (3)      -- region_id為3的資料到p_west
    );

轉換為HASH分區表。更多資訊，請參見HASH。

ALTER TABLE test_users 
    PARTITION BY HASH(region_id)
    PARTITIONS 4;  -- 分為4個雜湊分割

轉換為RANGE分區表。更多資訊，請參見RANGE。

ALTER TABLE test_users 
    PARTITION BY RANGE (region_id) (
        PARTITION p0 VALUES LESS THAN (2),  -- region_id < 2
        PARTITION p1 VALUES LESS THAN (3),  -- 2 ≤ region_id < 3
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );

多列分區

此處以普通表orders為例，為您示範如何將普通錶轉換為分區表。

建立普通表orders，並插入資料。

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    region_id INT NOT NULL,    -- 分區鍵欄位1
    order_date DATE NOT NULL,  -- 分區鍵欄位2
    customer_id INT,
    amount DECIMAL(10,2),
    PRIMARY KEY (order_id, region_id, order_date),             -- 主鍵必須包含所有分區鍵欄位
    UNIQUE KEY (customer_id, order_id, region_id, order_date)  -- 唯一鍵必須包含所有分區鍵欄位
);

INSERT INTO orders (region_id, order_date, customer_id, amount) VALUES
(1, '2022-12-31', 1001, 99.99),
(1, '2023-05-01', 1002, 199.99),
(2, '2023-06-01', 1003, 299.99);

將普通表orders轉換為RANGE分區表。更多資訊，請參見RANGE。

ALTER TABLE orders
    PARTITION BY RANGE COLUMNS(region_id, order_date) (
        PARTITION p0 VALUES LESS THAN (1, '2023-01-01'),  -- region_id < 1 或 region_id=1 且 order_date < '2023-01-01'
        PARTITION p1 VALUES LESS THAN (2, '2024-01-01'),  -- region_id < 2 或 region_id=2 且 order_date < '2024-01-01'
        PARTITION p2 VALUES LESS THAN (MAXVALUE, MAXVALUE)
    );

常見問題

普通錶轉分區表時，報錯：A PRIMARY KEY/UNIQUE INDEX must include all columns in the table's partitioning function？

您可以通過以下兩種方式解決：

根據前提條件，修改普通表的主鍵和唯一鍵，使其均需包含分區鍵欄位。例如：
- 若按單列order_id進行分區，則主鍵和唯一鍵應包含order_id（如PRIMARY KEY(id, order_id)）。
- 若按多列region_id和order_date進行分區，則主鍵和唯一鍵應包含region_id和order_date（如PRIMARY KEY(id, region_id, order_date)）。
使用UNIQUE CHECK IGNORE（UCI）功能，支援分區表的主鍵和唯一鍵不包含分區鍵欄位，您可以選擇任意列作為分區鍵。

專家面對面

如需瞭解更多關於普通錶轉換為分區表的內容，可通過DingTalk搜尋群號入群諮詢。您可以直接@群內專家，並附上您要諮詢的問題。

DingTalk群號：24490017825。

PolarDB：普通錶轉換為分區表