本文是 MySQL overview 的 implementation-layer deep article。Overview 已說明 MySQL 在 OLTP 譜系的定位、本文聚焦 native partitioning — 5 段 lifecycle + 4 種 type + 跟 Vitess sharding / PG 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）。

4 種 partition type

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 簡單。

缺點：value list 不能變更（不 supported ALTER PARTITION ADD VALUE）、新國家代碼必須 REORGANIZE。

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 SELECT ... 顯示 query 命中哪些 partition：

1EXPLAIN PARTITIONS
2SELECT * FROM orders WHERE created_at BETWEEN '2026-02-15' AND '2026-02-20';
3
4+----+-------------+--------+------------+-------+
5| id | select_type | table  | partitions | type  |
6+----+-------------+--------+------------+-------+
7|  1 | SIMPLE      | orders | p202602    | range |
8+----+-------------+--------+------------+-------+

只命中 p202602、其他 partition 不 scan。

Pruning 失效場景：

1. Function on partition key：

   1WHERE YEAR(created_at) = 2026  -- 沒 pruning、scan 全部

   應該寫成：

   1WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'

2. Implicit conversion：

   1WHERE created_at = '2026-02-15'  -- 字串 vs DATETIME、可能失效

   應該：

   1WHERE created_at = TIMESTAMP '2026-02-15 00:00:00'

3. OR 跨 partition：

   1WHERE created_at = '2026-02-15' OR user_id = 100  -- partition + non-partition column OR、scan 全部

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

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 工作流的核心工具。

5 個 Production 踩雷

1. 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 >= ... 才高效。

2. Global index 沒原生支援

MySQL partitioning 沒 global secondary index（PG 有）。每個 partition 各自有自己的 local index、跨 partition 的 unique constraint 必須 包含 partition key。

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

1-- MySQL 不允許這樣 — UNIQUE 必須含 created_at
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, created_at)  -- 必須含 created_at、不是純 user_id
8);

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

3. 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 同時改、不能漏一個

4. 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）

5. 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 對比

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

跟 PostgreSQL declarative-partitioning 對比

PG partitioning 對 跨 partition unique 跟 partition-wise join 處理較好、是 reporting workload 的優勢。MySQL partitioning 對 archive workflow（DROP / EXCHANGE）較成熟。詳見 PostgreSQL Declarative Partitioning。

何時用 native partitioning

跟其他模組整合

跟 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。

容量規劃要點

相關連結

• MySQL vendor overview
• MySQL Vitess sharding（跨 instance 切割對比）
• MySQL Online Schema Change（partition table 的 schema change）
• MySQL Query Optimization（EXPLAIN PARTITIONS）
• MySQL InnoDB Tuning（partition + buffer pool 互動）
• PostgreSQL Declarative Partitioning（PG sibling 對比）
• Partition 卡片
• 官方：MySQL Partitioning