您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 分區表功能與限制完整解析：RANGE、LIST、HASH、KEY 分區策略選型指南

編輯：MySQL 資料庫相關知識

MySQL 分區表核心價值

MySQL 5.1+ 引入的分區功能，將邏輯上單一大表物理拆分為多個較小的分區，每個分區可獨立儲存、維護與查詢。正確使用分區能大幅提升大資料量場景下的查詢效能、維護彈性與儲存成本控制。

分區能帶來的具體效益

邏輯資料分割：依業務維度（時間、地區、租戶等）自然切分，符合資料生命週期管理
提升單一讀寫效能：分區裁剪使查詢僅掃描相關分區，減少 IO 與鎖競爭
加速範圍查詢：時間序列資料按日期分區，歷史區間查詢自動跳過無關分區
多路徑物理儲存：不同分區可掛載不同磁碟/儲存介質（熱資料 SSD、冷資料 HDD）
高效歷史資料保存：直接 DROP PARTITION 刪除舊分區，秒級完成、無碎片、不影響線上服務
單表約束檢查：主鍵、唯一鍵約束在分區層級生效，保證資料完整性
主從異構分區策略：Master 用 HASH 分區均衡寫入，Slave 用 RANGE 分區優化歷史查詢

四大核心分區類型與選型建議

類型	適用場景	範例	優點	缺點
RANGE	時間序列、連續數值區間	按年/月分區訂單表	範圍查詢極快、易管理歷史資料	熱點寫入集中於最新分區
LIST	離散值列舉（地區、狀態、租戶）	按城市分區用戶表	精確控制分區歸屬	值變更需 REORGANIZE PARTITION
HASH	均衡分散、無明顯業務維度	用戶 ID 取模分區	寫入負載均衡、無熱點	範圍查詢需全分區掃描
KEY	非整數欄位、MySQL 自動計算雜湊	UUID、字串欄位分區	支援非整數型別	效能略低於 HASH

硬性限制與避坑指南

分區鍵型別限制：僅支援整數型別（或可透過函數轉換為整數的型別，如 YEAR(date)、TO_DAYS()）。字串型別需用 KEY 分區或建立整數虛擬欄位
最大分區數 1024：含子分區。過多分區會增加優化器規劃開銷，建議實際不超過 100~200
唯一鍵/主鍵包含分區鍵：若表有 PK 或 UK，分區欄位必須包含於其中。常見做法：PRIMARY KEY (id, created_at) 配合 PARTITION BY RANGE (YEAR(created_at))
不支援外鍵：分區表無法定義 FK，應用層需自行維護參考完整性
不支援全文索引：FULLTEXT 索引與分區互斥，全文搜尋需用專用引擎
日期分區最佳實踐：使用 RANGE COLUMNS(created_at)（5.5+）或 RANGE (TO_DAYS(created_at))，避免函數導致分區裁剪失效
字串分區建議：改用 KEY 分區，或建立整數雜湊欄位（如 CRC32(email)）配合 HASH 分區

常見分區 DDL 範例

-- 按月 RANGE 分區（訂單表）
CREATE TABLE orders (
    id BIGINT NOT NULL,
    created_at DATETIME NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE COLUMNS (created_at) (
    PARTITION p202301 VALUES LESS THAN ('2023-02-01'),
    PARTITION p202302 VALUES LESS THAN ('2023-03-01'),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 按用戶 ID HASH 分區（均衡寫入）
CREATE TABLE user_logs (
    user_id INT NOT NULL,
    log_time DATETIME NOT NULL,
    content TEXT,
    PRIMARY KEY (user_id, log_time)
) PARTITION BY HASH (user_id) PARTITIONS 16;

維護操作速查

ALTER TABLE t DROP PARTITION p202201; — 秒級刪除歷史分區
ALTER TABLE t ADD PARTITION (PARTITION p202312 VALUES LESS THAN ('2024-01-01')); — 新增未來分區
ALTER TABLE t REORGANIZE PARTITION pmax INTO (...); — 拆分 MAXVALUE 分區
EXPLAIN PARTITIONS SELECT ...; — 驗證分區裁剪是否生效

MySQL 分區是大資料量單表優化的利器，但非銀彈。決策前務必評估：查詢模式是否符合分區裁剪、維護複雜度是否可控、是否觸發上述限制。結合讀寫分離、分庫分表、冷熱分離等架構手段，才能構建真正可擴展的資料層。