分區表 vs 分表:本質區別
| 維度 | 分區表 | 分表 |
| 邏輯視圖 | 單表 | 多表 |
| 應用改造 | 無感知 (透明) | 需修改 SQL/路由邏輯 |
| 管理操作 | DDL 秒級 (ADD/DROP PARTITION) | 應用層遷移數據 |
| 索引 | 局部索引 (每分區獨立) | 全局索引需自建 |
| 跨分區查詢 | 優化器自動裁剪 | 應用層 UNION |
分區類型完整語法 (含 5.5+ COLUMNS 增強)
1. RANGE 分區 —— 歷史數據歸檔首選
CREATE TABLE sales (
id INT NOT NULL,
sale_date DATE NOT NULL,
amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
2. RANGE COLUMNS (5.5+) —— 直接用 DATE 列,無需函數
CREATE TABLE sales_v2 (
id INT NOT NULL,
sale_date DATE NOT NULL,
amount DECIMAL(10,2)
)
PARTITION BY RANGE COLUMNS (sale_date) (
PARTITION p2023_q1 VALUES LESS THAN ('2023-04-01'),
PARTITION p2023_q2 VALUES LESS THAN ('2023-07-01'),
PARTITION p2023_q3 VALUES LESS THAN ('2023-10-01'),
PARTITION p2023_q4 VALUES LESS THAN ('2024-01-01')
);
3. LIST 分區 —— 業務枚舉分組
CREATE TABLE orders (
id INT,
region_id INT
)
PARTITION BY LIST (region_id) (
PARTITION p_north VALUES IN (1,2,3),
PARTITION p_south VALUES IN (4,5,6),
PARTITION p_other VALUES IN (7,8,9,0)
);
4. LIST COLUMNS (5.5+) —— 直接用字符串枚舉
CREATE TABLE products (
id INT,
category VARCHAR(20)
)
PARTITION BY LIST COLUMNS (category) (
PARTITION p_electronics VALUES IN ('phone','laptop','tablet'),
PARTITION p_clothing VALUES IN ('shirt','pants','shoes')
);
5. HASH 分區 —— 均勻分布熱點數據
CREATE TABLE logs (
id INT,
created_at DATETIME
)
PARTITION BY HASH (MONTH(created_at))
PARTITIONS 12;
6. KEY 分區 —— MySQL 內部哈希 (支持非整型)
CREATE TABLE sessions (
session_id CHAR(32),
user_id INT
)
PARTITION BY KEY (user_id)
PARTITIONS 8;
分區前提檢查
-- 確認實例支持分區
SHOW VARIABLES LIKE 'have_partition_engine';
-- 或 5.7+
SHOW PLUGINS WHERE Name='partition';
分區管理操作實戰
新增分區 (預創建未來分區)
ALTER TABLE sales ADD PARTITION (PARTITION p2024 VALUES LESS THAN (2025));
刪除分區 (數據同步刪除,不可恢復)
ALTER TABLE sales DROP PARTITION p2020;
合併/重組分區 (季度合併為半年)
ALTER TABLE sales REORGANIZE PARTITION p2023_q1,p2023_q2,p2023_q3 INTO (
PARTITION p2023_h1 VALUES LESS THAN ('2023-07-01'),
PARTITION p2023_h2 VALUES LESS THAN ('2024-01-01')
);
查看分區信息
SELECT TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS, DATA_LENGTH
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA='db_name' AND TABLE_NAME='sales';
驗證分區裁剪 (關鍵性能指標)
EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2023-04-01';
-- partitions 列應僅顯示 p2023_q1
-- 若顯示 ALL/多個分區 → SQL 未命中分區裁剪,需改寫
避坑指南 (血淚總結)
- ❌ 分區鍵不在 WHERE 中 → 全分區掃描,性能反而比不分區差
- ❌ 分區列允許 NULL → NULL 值擠入最小分區,嚴重傾斜,**必須 NOT NULL**
- ❌ 分區鍵不在主鍵/唯一鍵中 →
ERROR 1503,**調整主鍵包含分區鍵**
- ❌ 過度分區 (如按天分區 10 年 = 3650 分區) → 元數據開銷大、打開表慢,**單分區 1-5千萬行**
- ❌ 外鍵約束 → 分區表不支持 FK,業務層保證
- ✅ 覆蓋索引包含分區鍵 → 索引下推更高效
- ✅ 定時任務預創建未來分區、歸檔歷史分區