將一張大表按規則水平拆分為多個物理獨立的分區,邏輯上仍為一張表。MySQL 5.1+ 支持。
DROP PARTITION 秒級)CREATE TABLE orders (
id BIGINT, order_date DATE, amount INT
) PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
MAXVALUE 必須作為最後兜底分區。CREATE TABLE stores (
id INT, region_id INT
) PARTITION BY LIST (region_id) (
PARTITION p_north VALUES IN (1,3,5),
PARTITION p_south VALUES IN (2,4,6)
);
MAXVALUE,插入非列表值直接報錯。CREATE TABLE logs (
id BIGINT, created_at DATETIME
) PARTITION BY HASH (MONTH(created_at))
PARTITIONS 12;
PARTITIONS N。TRUNCATE PARTITION)。CREATE TABLE users (
id INT, name VARCHAR(50)
) PARTITION BY KEY (id)
PARTITIONS 10;
LINEAR KEY(2 的冪算法,增刪分區更快)。PRIMARY KEY (id, order_date) 才能按 order_date 分區。YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND()
TO_DAYS(), TO_SECONDS(), DATEDIFF()
WEEKDAY(), DAYOFWEEK(), DAYOFMONTH(), DAYOFYEAR(), QUARTER(), WEEK()
UNIX_TIMESTAMP() (5.7+)
ABS(), CEILING(), FLOOR(), MOD()
EXTRACT(), MICROSECOND(), TIME_TO_SEC()
❌ 不支持:FROM_UNIXTIME()、NOW()、RAND()、自定義函數、非確定性函數。
CREATE TABLE `order` (
order_id BIGINT NOT NULL,
`date` DATE NOT NULL,
amount INT,
status TINYINT,
PRIMARY KEY (order_id, `date`) -- 分區鍵 date 必須在 PK 中
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 按 年月 分區:date DIV 100 → 202401, 202402...
ALTER TABLE `order` PARTITION BY RANGE (`date` DIV 100) (
PARTITION p202401 VALUES LESS THAN (202402),
PARTITION p202402 VALUES LESS THAN (202403),
PARTITION p202403 VALUES LESS THAN (202404),
PARTITION p_catch_all VALUES LESS THAN MAXVALUE
);
| 操作 | SQL 範例 | 說明 |
|---|---|---|
| 新增分區 | ALTER TABLE t REORGANIZE PARTITION p_catch_all INTO (PARTITION p202404 VALUES LESS THAN (202405), PARTITION p_catch_all VALUES LESS THAN MAXVALUE); | 必須拆分 MAXVALUE 分區,數據不丟失 |
| 合併分區 | ALTER TABLE t REORGANIZE PARTITION p202401,p202402,p_catch_all INTO (PARTITION p_merged VALUES LESS THAN MAXVALUE); | 需包含 MAXVALUE 分區,範圍總和不變 |
| 刪除分區(保留數據) | ALTER TABLE t REMOVE PARTITIONING; | 5.5 支持,5.6+ 需驗證 |
| 刪除分區(刪數據) | ALTER TABLE t DROP PARTITION p202301; | 秒級完成,數據不可恢復 |
| 截斷分區 | ALTER TABLE t TRUNCATE PARTITION p202301; | 保留分區結構,清空數據 |
❌ 無效:WHERE date >= '2024-01-01' AND date <= '2024-01-31'(範圍查詢不剪枝)
✅ 有效:WHERE date = '2024-01-15' 或 WHERE date IN ('2024-01-01','2024-01-02',...)
EXPLAIN PARTITIONS 驗證 partitions 列是否為單一分區。
RANGE/LIST + HASH/KEY 組合:
PARTITION BY RANGE (YEAR(order_date))
SUBPARTITION BY HASH (MONTH(order_date))
SUBPARTITIONS 12 (...)
適用於:超大表、需同時按時間範圍查詢和哈希並行聚合的場景。