萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 表分區完整實戰:RANGE/LIST/HASH/KEY 分區策略與管理運維

為什麼需要分區?

當單表超過億級資料,查詢、維護、備份均面臨效能瓶頸。分區將邏輯上的一張大表,物理上拆分為多個獨立段(分區),實現:

  • 資料管理優化:秒級刪除歷史分區 (DROP PARTITION),無需 DELETE 慢查詢。
  • 查詢裁剪:WHERE 條件命中分區鍵時,僅掃描相關分區,大幅降低 I/O。
  • 並行聚合:SUM/COUNT 等可跨分區並行,提升吞吐。
  • 跨磁盤分佈:不同分區掛載不同磁碟,突破單磁碟瓶頸。

四大核心分區類型詳解

1. RANGE 分區:按連續區間劃分 (最常用於時間序列)

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 分區,否則超出範圍資料寫入報錯。
  • 適合:日誌、訂單、監控資料等按時間自然增長場景。

2. LIST 分區:按離散值集合劃分 (適合枚舉型業務)

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 等效語法,所有可能值必須顯式列出,否則寫入失敗。
  • 適合:地區、狀態碼、租戶 ID 等固定枚舉場景。

3. HASH 分區:按雜湊值均勻分散 (適合無明顯範圍/列表特徵的大表)

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;
  • MySQL 自動按 MOD(expr, partitions) 分配,資料分布均勻。
  • 支援 LINEAR HASH (線性雜湊),增刪分區更快,但分布略不均。
  • 分區鍵最好為整數或可轉整數表達式,避免複雜函數影響寫入效能。

4. KEY 分區:由 MySQL 內建雜湊函數計算 (支援非整數列)

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;
  • 類似 HASH,但雜湊函數由 Server 提供 (MyISAM 用內部函數,NDB 用 MD5)。
  • 支援字串、二進位等非整數列直接分區,無需自行轉換。
  • 同樣支援 LINEAR KEY。

子分區:二維劃分 (RANGE/LIST + HASH/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;

關鍵限制與避坑指南

  1. 主鍵/唯一鍵必須包含分區鍵:PRIMARY KEY (id, sale_date)。
  2. 所有分區必須用同一存儲引擎 (MySQL 5.1+ 強制)。
  3. 外鍵不支援分區表:避免在分區表上建 FK。
  4. 分區數上限:8192 (含子分區),實際建議 < 1000。
  5. 查詢未命中分區鍵 = 全分區掃描:EXPLAIN 觀察 partitions 欄是否為 p2022,p2023 而非 all。
  6. 避免在分區鍵上使用函數:WHERE YEAR(date) = 2023 可裁剪,WHERE DATE_FORMAT(date, '%Y') = '2023' 可能失效。

合理設計分區鍵、善用裁剪特性,億級大表也能輕馳駿馬。

copyright © 萬盛學電腦網 all rights reserved