當單表超過億級資料,查詢、維護、備份均面臨效能瓶頸。分區將邏輯上的一張大表,物理上拆分為多個獨立段(分區),實現:
CREATE TABLE sales (
id INT NOT NULL AUTO_INCREMENT,
sale_date DATE NOT NULL,
amount DECIMAL(10,2),
PRIMARY KEY (id, sale_date)
) ENGINE=InnoDB
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 pmax VALUES LESS THAN MAXVALUE
);
MAXVALUE 分區,否則超出範圍資料寫入報錯。CREATE TABLE orders (
id INT NOT NULL AUTO_INCREMENT,
region_id TINYINT NOT NULL,
PRIMARY KEY (id, region_id)
) ENGINE=InnoDB
PARTITION BY LIST (region_id) (
PARTITION p_east VALUES IN (1,2,3),
PARTITION p_west VALUES IN (4,5,6),
PARTITION p_other VALUES IN (7,8,9)
);
MAXVALUE 等效語法,所有可能值必須顯式列出,否則寫入失敗。CREATE TABLE sessions (
id BIGINT NOT NULL AUTO_INCREMENT,
user_id BIGINT NOT NULL,
data TEXT,
PRIMARY KEY (id, user_id)
) ENGINE=InnoDB
PARTITION BY HASH (user_id)
PARTITIONS 16;
MOD(expr, partitions) 分配,資料分布均勻。LINEAR HASH (線性雜湊),增刪分區更快,但分布略不均。CREATE TABLE logs (
id INT NOT NULL AUTO_INCREMENT,
trace_id CHAR(32) NOT NULL,
PRIMARY KEY (id, trace_id)
) ENGINE=InnoDB
PARTITION BY KEY (trace_id)
PARTITIONS 8;
LINEAR KEY。CREATE TABLE events (
id BIGINT NOT NULL,
event_date DATE NOT NULL,
event_type TINYINT NOT NULL,
PRIMARY KEY (id, event_date)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(event_date))
SUBPARTITION BY HASH (event_type)
SUBPARTITIONS 4 (
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
每個 RANGE 分區再細分 4 個 HASH 子分區,總計 12 個物理段。適用於「按時間保留、按類型並行」的混合負載。
-- RANGE/LIST
ALTER TABLE sales ADD PARTITION (PARTITION p2023 VALUES LESS THAN (2024));
ALTER TABLE orders ADD PARTITION (PARTITION p_new VALUES IN (10,11));
-- HASH/KEY (只能增加總數,不能指定具體值)
ALTER TABLE sessions ADD PARTITION PARTITIONS 4;
ALTER TABLE sales DROP PARTITION p2020;
ALTER TABLE sales TRUNCATE PARTITION p2021;
-- 合併 p2020,p2021 → p2020_2021
ALTER TABLE sales REORGANIZE PARTITION p2020,p2021 INTO (
PARTITION p2020_2021 VALUES LESS THAN (2022)
);
-- 拆分 pmax → p2023, pmax
ALTER TABLE sales REORGANIZE PARTITION pmax INTO (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
限制:RANGE/LIST 僅能重組相鄰分區;HASH/KEY 不支援 REORGANIZE,只能 COALESCE (合併) 或 ADD PARTITIONS。
-- 重建 (整理碎片,相當於 DELETE+REINSERT)
ALTER TABLE sales REBUILD PARTITION p2022;
-- 優化 (回收空間,整理碎片)
ALTER TABLE sales OPTIMIZE PARTITION p2022;
-- 分析 (更新統計資訊)
ALTER TABLE sales ANALYZE PARTITION p2022;
-- 修復 (損壞修復)
ALTER TABLE sales REPAIR PARTITION p2022;
-- 檢查
ALTER TABLE sales CHECK PARTITION p2022;
-- 查詢執行計劃是否命中分區裁剪
EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2023-01-01';
-- 系統表查看完整元數據
SELECT * FROM information_schema.PARTITIONS WHERE TABLE_NAME = 'sales';
-- 查看建表語句
SHOW CREATE TABLE sales;
PRIMARY KEY (id, sale_date)。partitions 欄是否為 p2022,p2023 而非 all。WHERE YEAR(date) = 2023 可裁剪,WHERE DATE_FORMAT(date, '%Y') = '2023' 可能失效。合理設計分區鍵、善用裁剪特性,億級大表也能輕馳駿馬。