這篇整理 MySQL（以 8.4 為準）在同一個 instance 內把一張表切成多個 partition 的做法：partition type 的選法、optimizer 怎麼只掃相關 partition（partition pruning）、ADD / DROP / REORGANIZE / EXCHANGE 的維護操作，以及它跟跨 instance 的 Vitess sharding、PostgreSQL declarative partitioning 的差別。

Partition lifecycle 五段

MySQL native partitioning 是 同 instance 內把一個邏輯 table 拆成多個 physical sub-table、optimizer 可選擇只 scan 相關 partition。整個 partition lifecycle 5 段：

1Design       決定 partition key / type / 數量
2   ↓
3Create       CREATE TABLE ... PARTITION BY ...
4   ↓
5Query        WHERE clause + partition pruning
6   ↓
7Maintenance  ADD / DROP / REORGANIZE / EXCHANGE
8   ↓
9Drop         整個 partition 一次刪（比 DELETE FROM 快 1000x）

每段都有獨立工程決策。設計階段選錯 partition key、後續 maintenance + query 全部 broken。

跟 Vitess sharding 對比：

  • MySQL partitioning：同 instance、optimizer 自動 pruning、無 cross-instance network cost
  • Vitess sharding：跨 instance、application 透過 VTGate routing、可線性 scale

兩者不衝突、可組合：Vitess shard 內部 再 用 MySQL partition（例如：shard 切 16 個、每個 shard 的 table 再按月份 partition）。

Partition type：RANGE、LIST、HASH、KEY 與 sub-partitioning

RANGE partitioning — 連續區間切割

最常見、適合 time-series / 連續數字：

 1CREATE TABLE orders (
 2    id BIGINT AUTO_INCREMENT,
 3    user_id BIGINT NOT NULL,
 4    amount DECIMAL(10,2),
 5    created_at DATETIME NOT NULL,
 6    PRIMARY KEY (id, created_at)              -- PK 必須含 partition key
 7)
 8PARTITION BY RANGE (TO_DAYS(created_at)) (
 9    PARTITION p202601 VALUES LESS THAN (TO_DAYS('2026-02-01')),
10    PARTITION p202602 VALUES LESS THAN (TO_DAYS('2026-03-01')),
11    PARTITION p202603 VALUES LESS THAN (TO_DAYS('2026-04-01')),
12    PARTITION p_future VALUES LESS THAN MAXVALUE  -- 未來資料 fallback
13);

優點：

  • Partition pruning 高效（時間 range query）
  • 整個月 archive 直接 ALTER TABLE orders DROP PARTITION p202601、毫秒級

缺點：

  • 必須 預先建 未來 partition（或用 p_future fallback、但 fallback partition 變大就失去 pruning 意義）
  • Hot partition — 最新 partition 接收所有 INSERT、其他 partition 純歷史

LIST partitioning — 離散值切割

適合 enum-like value：

 1CREATE TABLE users (
 2    id BIGINT,
 3    name VARCHAR(100),
 4    region VARCHAR(10) NOT NULL,
 5    PRIMARY KEY (id, region)
 6)
 7PARTITION BY LIST COLUMNS (region) (
 8    PARTITION p_asia VALUES IN ('TW', 'JP', 'KR', 'CN'),
 9    PARTITION p_americas VALUES IN ('US', 'CA', 'BR'),
10    PARTITION p_emea VALUES IN ('GB', 'DE', 'FR', 'IT')
11);

優點：對 enum-like value 直接命中、pruning 簡單。

缺點：沒有單獨替某個 partition 追加值的語法；新國家代碼放進新的 partition 用 ADD PARTITION，要放進既有 partition 就得用 REORGANIZE PARTITION 重寫那個 partition：

1-- 新代碼自成一個 partition：只新增，不動既有資料
2ALTER TABLE users ADD PARTITION (PARTITION p_sea VALUES IN ('SG', 'MY'));
3
4-- 新代碼併進既有的 p_asia：重新定義整個 value list，p_asia 的資料會被重寫
5ALTER TABLE users REORGANIZE PARTITION p_asia INTO (
6    PARTITION p_asia VALUES IN ('TW', 'JP', 'KR', 'CN', 'HK')
7);

HASH partitioning — 均勻分布

對 numeric / string column 取 hash、均勻分布：

1CREATE TABLE events (
2    id BIGINT,
3    user_id BIGINT NOT NULL,
4    event_type VARCHAR(50),
5    PRIMARY KEY (id, user_id)
6)
7PARTITION BY HASH (user_id) PARTITIONS 8;

優點：均勻分布、沒有 hot partition。

缺點：

  • Range query 沒效 — WHERE user_id BETWEEN 100 AND 200 不能 pruning、scan 全部 partition
  • Partition 數量改變需要 REORGANIZE 整張表

KEY partitioning — MySQL 內部 hash

跟 HASH 類似、但用 MySQL 內部 hash function（不依賴 column 是否 integer）：

1CREATE TABLE sessions (
2    session_id VARCHAR(64),
3    user_id BIGINT NOT NULL,
4    data TEXT,
5    PRIMARY KEY (session_id, user_id)
6)
7PARTITION BY KEY (user_id) PARTITIONS 16;

用於 string column 或 composite column 的均勻分布。一般場景跟 HASH 效果接近。

Sub-partitioning — 兩層切割

RANGE + HASH 組合、深化分隔：

 1CREATE TABLE big_events (
 2    id BIGINT,
 3    user_id BIGINT,
 4    created_at DATETIME,
 5    PRIMARY KEY (id, created_at, user_id)
 6)
 7PARTITION BY RANGE (TO_DAYS(created_at))
 8SUBPARTITION BY HASH (user_id) SUBPARTITIONS 4 (
 9    PARTITION p202601 VALUES LESS THAN (TO_DAYS('2026-02-01')),
10    PARTITION p202602 VALUES LESS THAN (TO_DAYS('2026-03-01'))
11);

每個 RANGE partition 又拆 4 個 HASH sub-partition、共 8 個 physical storage location。適合 時間 range + user_id hash 兩維度。

實務罕用、複雜性高、調 query plan 困難。多數 case 用 single-level partition 即可。

Partition Pruning — Optimizer 怎麼選 partition

EXPLAIN 輸出的 partitions 欄列出 query 命中哪些 partition（MySQL 8.0 起 EXPLAIN 預設就有這一欄，舊版的 EXPLAIN PARTITIONS 寫法在 8.4 是語法錯誤）：

1EXPLAIN
2SELECT * FROM orders WHERE created_at BETWEEN '2026-02-15' AND '2026-02-20';
3
4-- 節錄前五欄（L45–57 的 orders 表、放三列測試資料）
5+----+-------------+--------+------------+------+
6| id | select_type | table  | partitions | type |
7+----+-------------+--------+------------+------+
8|  1 | SIMPLE      | orders | p202602    | ALL  |
9+----+-------------+--------+------------+------+

只命中 p202602、其他 partition 不 scan。

Pruning 失效場景：

  1. Function on partition key：

    1-- partitions: p202601,p202602,p202603,p_future（函式包住 partition key，全掃）
2SELECT id FROM orders WHERE YEAR(created_at) = 2026 AND MONTH(created_at) = 2;

    應該寫成直接比較 partition key 的範圍：

    1-- partitions: p202601,p202602（TO_DAYS 分區遇到範圍條件時多帶了相鄰的 p202601，仍比全掃少）
2SELECT id FROM orders WHERE created_at >= '2026-02-01' AND created_at < '2026-03-01';

  2. OR 跨 partition：

    1-- partitions: p202601,p202602,p202603,p_future（partition key 與非 partition key 用 OR 連接，全掃）
2SELECT * FROM orders WHERE created_at = '2026-02-15' OR user_id = 100;

  3. JOIN 不直接 filter partition key：JOIN 條件不含 partition key、optimizer 估計無法 pruning。

字串常數跟 DATETIME 欄位比對不會讓 pruning 失效：WHERE created_at = '2026-02-15' 的 EXPLAIN 只列 p202602，跟寫成 TIMESTAMP '2026-02-15 00:00:00' 的結果相同。

Partition Maintenance — ADD / DROP / REORGANIZE / EXCHANGE

ADD partition

1ALTER TABLE orders ADD PARTITION (
2    PARTITION p202604 VALUES LESS THAN (TO_DAYS('2026-05-01'))
3);

對 RANGE 簡單、但要 排在 MAXVALUE partition 之前（如果有 p_future、要先 REORGANIZE）。

DROP partition

1ALTER TABLE orders DROP PARTITION p202601;

直接刪 partition file、毫秒級完成。是 time-series archive 的最大優勢 — 對比 DELETE FROM orders WHERE created_at < '...' 跑 hours。

REORGANIZE partition

切分 / 合併 partition：

1-- 切：把 p_future 切成 p202604 + new p_future
2ALTER TABLE orders REORGANIZE PARTITION p_future INTO (
3    PARTITION p202604 VALUES LESS THAN (TO_DAYS('2026-05-01')),
4    PARTITION p_future VALUES LESS THAN MAXVALUE
5);

REORGANIZE rewrites partition data、跟 OSC 一樣慢、大 partition 走 gh-ost / pt-osc 模擬（用 ghost table）。

EXCHANGE partition

把 partition 跟 獨立 table swap（不複製資料）：

1-- 建一個 staging table 跟 partition 同 schema
2CREATE TABLE orders_staging LIKE orders;
3ALTER TABLE orders_staging REMOVE PARTITIONING;  -- staging 必須是 non-partitioned
4
5-- 把 archive partition 的資料 atomic swap 給 staging
6ALTER TABLE orders EXCHANGE PARTITION p202601 WITH TABLE orders_staging;
7
8-- 現在 orders_staging 有 p202601 的資料、orders 的 p202601 變空
9-- 可以 dump staging 到 S3、或 INSERT 進 archive DB

EXCHANGE PARTITION 是 metadata operation、毫秒級完成、不複製資料。Time-series archive 工作流的核心工具。

Production 踩雷

PK 必須含 partition key — Schema 設計受限

MySQL partition 規則：PK 必須包含所有 partition key column。

1-- 錯：PK 沒包含 partition key
2CREATE TABLE orders (
3    id BIGINT AUTO_INCREMENT PRIMARY KEY,  -- 只有 id
4    created_at DATETIME NOT NULL
5) PARTITION BY RANGE (TO_DAYS(created_at)) (...);
6-- ERROR 1503: A PRIMARY KEY must include all columns in the table's partitioning function
1-- 對：PK 包含 partition key
2CREATE TABLE orders (
3    id BIGINT AUTO_INCREMENT,
4    created_at DATETIME NOT NULL,
5    PRIMARY KEY (id, created_at)  -- 兩 column 都進 PK
6) PARTITION BY RANGE (TO_DAYS(created_at)) (...);

修法：

  • 接受 PK 是 composite（id + partition_key column）
  • AUTO_INCREMENT 仍 work、但 INSERT 必須給定 created_at
  • Unique constraint 也受影響 — 所有 UNIQUE index 必須含 partition key

對 application：原本 WHERE id = X 仍 work、但慢（沒 partition pruning）、必須 WHERE id = X AND created_at >= ... 才高效。

Global index 沒原生支援

MySQL partitioning 沒 global secondary index：每個 partition 各自有自己的 local index，所以 unique constraint 只能在單一 partition 內檢查，MySQL 因此要求每個 UNIQUE index 都 包含 partition key。PostgreSQL 的 declarative partitioning 也一樣沒有 global index。

例：希望 user_id 全表 unique、但 partition by created_at：

 1-- 被拒：UNIQUE 只有 user_id
 2CREATE TABLE orders (
 3    id BIGINT AUTO_INCREMENT,
 4    user_id BIGINT,
 5    created_at DATETIME,
 6    PRIMARY KEY (id, created_at),
 7    UNIQUE KEY (user_id)
 8) PARTITION BY RANGE (TO_DAYS(created_at)) (PARTITION p0 VALUES LESS THAN MAXVALUE);
 9-- ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
10
11-- 建得起來：UNIQUE 加上 created_at
12-- 代價是它只保證 (user_id, created_at) 這組值不重複，同一個 user_id 在不同時間仍可以出現多次
13CREATE TABLE orders (
14    id BIGINT AUTO_INCREMENT,
15    user_id BIGINT,
16    created_at DATETIME,
17    PRIMARY KEY (id, created_at),
18    UNIQUE KEY (user_id, created_at)
19) PARTITION BY RANGE (TO_DAYS(created_at)) (PARTITION p0 VALUES LESS THAN MAXVALUE);

對 application：跨 partition 的 unique 需要 application 層處理（INSERT 前 SELECT 檢查）或改用 Vitess lookup_hash Vindex。

EXCHANGE partition — schema 必須完全一致

EXCHANGE 失敗常見：staging table 跟 partition 的 index / column 順序差一個、ERROR 1736: Tables have different definitions。

修法：

  • 建 staging 用 CREATE TABLE staging LIKE orders 而非手寫
  • REMOVE PARTITIONING 後立即 verify schema
  • 跑 OSC 改 schema 時、partition + staging table 同時改、不能漏一個

Orphan partition — Future partition 預先建忘記延展

部署 cron 每月建下個月 partition、cron 失敗 / pause、下個月 INSERT 無對應 partition、寫入 p_future。p_future 一年累積後變超大、partition pruning 沒效、查最近資料 scan 全表。

修法：

  • 監控 p_future partition size、超過 threshold alert
  • Cron 失敗 alert（不是 silent fail）
  • 不依賴 cron、改成 application 層在 INSERT 前 ensure partition exists（lazy create）

Cross-partition query 慢

1SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;

沒 partition key filter、optimizer 不能 pruning、scan 全部 partition。比 single big table without partition 還慢（因為跨 partition aggregation overhead）。

修法：

  • 接受 partition 是 write + archive 效能 工具，而不是 讀效能 工具
  • 跨 partition aggregation 改 materialized aggregation table（trigger / scheduled job 維護）
  • 跨 partition reporting 改丟 OLAP DB（BigQuery / Snowflake / ClickHouse）

跟 Vitess sharding 對比

維度MySQL partitioningVitess sharding
切割範圍同 instance 內跨 instance（無上限）
Cross-shard query不適用VTGate 自動 split + aggregate
ReshardingREORGANIZE（rewrite data）VReplication 自動
Operational cost低（單 instance 內）高（4 component Vitess stack）
可線性 scale write否（單 instance 寫吞吐限）是（加 shard）
Archive 效率DROP PARTITION 毫秒級不是 archive 工具

兩者不衝突、適用不同問題。Partitioning 解決 單 instance archive + write 集中、sharding 解決 跨 instance scale。

跟 PostgreSQL declarative-partitioning 對比

維度MySQL partitioningPostgreSQL declarative-partitioning
Partition typeRANGE / LIST / HASH / KEYRANGE / LIST / HASH
Sub-partitioningRANGE + HASH多層 nested 支援更廣
Global index無無（每個分區各建 local index）
Partition wise join受限PG 11+ 強
Cross-partition unique必須含 partition key同限制（PG 16、17 皆是）
Partition attachEXCHANGE PARTITIONATTACH PARTITION
操作工具gh-ost / pt-osc 對 partitionpg_partman（成熟）
Production maturity中（5.x 開始有、8.0 強化）高（11+ declarative 後成熟）

PG partitioning 對 partition-wise join 處理較好、是 reporting workload 的優勢；跨 partition unique 兩者限制相同，UNIQUE 都必須含 partition key。MySQL partitioning 對 archive workflow（DROP / EXCHANGE）較成熟。詳見 PostgreSQL Declarative Partitioning。

何時用 native partitioning

場景建議
Time-series workload + archive needs（log / event / order history）用 RANGE
大表 > 1 TB 且 query 多有 time filter用 RANGE 加速 prune
跨 region / 跨業務切分用 LIST
需要 線性 scale write throughput不用 partition、用 Vitess sharding
需要 全表 unique constraint不用 partition、影響太大
主要做 ad-hoc analytical query不用 partition、OLAP DB（ClickHouse / BigQuery）
小表 < 100 GB不必 partition、index 夠用

跟其他模組整合

跟 Online Schema Change

對 partitioned table 的 schema change（ALTER COLUMN）必須 每個 partition 都改。gh-ost / pt-osc 對 partitioned table 仍 work、但複雜性增加。詳見 Online Schema Change Tools。

跟 Vitess

Vitess shard 內部可再 partition、單 shard 對應一個 MySQL instance、partition 是 instance 內優化。Vitess vtctldclient PartitionTablet 命令處理 shard-aware partition 操作。詳見 Vitess sharding。

跟 InnoDB Tuning

每個 partition 是獨立 InnoDB tablespace（innodb_file_per_table=ON 預設）、buffer pool 內 cache 行為跟 single big table 不同。Partition 多時 buffer pool warm-up 時間更長。詳見 InnoDB Tuning。

跟 Replication

Partition operation（ADD / DROP / EXCHANGE）是 DDL、走 binlog、replica apply 時可能 locking issue（特別是 EXCHANGE 跟 replica running query 衝突）。詳見 Replication Topology。

跟 Query Optimization

EXPLAIN 輸出的 partitions 欄是 partition-aware query optimization 的關鍵工具、看 query 真的命中哪些 partition。詳見 Query Optimization。

容量規劃要點

維度建議
Partition 數量上限8192（非 NDB 表的硬上限）、實務建議 < 1000（管理成本上升）
單 partition 大小10 GB - 100 GB（太小無 partition value、太大 prune 沒效）
RANGE 時間 partition月 / 週 / 日（依資料量）
HASH partition 數量通常 power of 2（8 / 16 / 32 / 64）
Future partition pre-create至少 6 個月 buffer、cron 每月 add 1 個

相關連結