您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 分區表完整指南：類型、管理、裁剪與最佳實踐

編輯：MySQL 綜合教學

前言

MySQL 5.1 引入分區功能，允許將大表按規則拆分為多個物理文件，實現數據的水平切分。本文基於 MySQL 官方文檔第 20 章，系統梳理分區類型、管理操作、分區裁剪原理、分區選擇及限制條件，助力開發者高效利用分區優化大數據場景性能。

一、分區基礎概念與支持檢查

什麼是分區？

分區將一張邏輯表按指定規則（Range、List、Hash、Key）拆分為多個獨立的物理分區，每個分區可存放於不同磁盤/目錄。對應用透明，SQL 語法無需修改。MySQL 分區屬於水平分區，不支援垂直分區。

如何確認實例支援分區？

SHOW PLUGINS;
# 查看 partition 插件 Status 是否為 ACTIVE
# 或查詢 INFORMATION_SCHEMA.PLUGINS

編譯時若未啟用，可用 --skip-partition 禁用。注意：MERGE、CSV、FEDERATED 引擎不支援分區，InnoDB 要求所有分區使用相同引擎。

二、核心分區類型詳解

1. RANGE 分區 — 連續區間劃分

按列值落在指定連續區間劃分，常用於日期、ID 等有序數據。使用 VALUES LESS THAN 定義邊界。

CREATE TABLE employees (
    id INT, hired DATE, store_id INT
) PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN MAXVALUE  -- 接收超出範圍數據
);

適用場景： 定期刪除歷史數據、按時間/ID 範圍頻繁查詢。

2. LIST 分區 — 離散值集合

按列值屬於指定離散值集合劃分，適合區域、狀態碼等枚舉類型。

CREATE TABLE employees (
    id INT, store_id INT
) PARTITION BY LIST (store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast  VALUES IN (1,2,10,11,19,20)
);

注意： 插入值不在任何分區定義中會報錯，可用 INSERT IGNORE 忽略錯誤行。

3. COLUMNS 分區 — 多列/非整型支援

RANGE/LIST 的增強版，支援多列組合分區、非整型列。
RANGE COLUMNS：不可用表達式，直接比較列值元組。
LIST COLUMNS：支援字符串、日期等類型多列匹配。

4. HASH / LINEAR HASH 分區 — 數據均勻分布

通過用戶定義表達式計算 Hash 值決定分區。
LINEAR HASH：採用 2 的冪次線性演算法，優勢：增刪合併分區極快，適合 TB 級大表；劣勢：數據分布均勻度略遜於傳統 Hash。

5. KEY / LINEAR KEY 分區 — 內部 Hash 函數

類似 Hash，但分區函數由 MySQL 內部提供。若表有主鍵，默認使用主鍵列；否則使用唯一鍵。支援除 TEXT/BLOB 外所有類型。

6. 子分區 — 二維劃分

在 RANGE/LIST 一級分區基礎上，再按 HASH/KEY 進行二級劃分。每個一級分區的子分區數量必須一致。

7. NULL 值處理規則

RANGE：NULL 視為最小值，放入第一個分區。
LIST：必須顯式在 VALUES IN 中包含 NULL，否則報錯。
HASH/KEY：NULL 視為 0 處理。

三、分區管理操作

1. RANGE/LIST 分區增刪改

刪除分區：ALTER TABLE t DROP PARTITION p0; (數據同步丟失，需 DROP 權限)
新增分區：ALTER TABLE t ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000)); (RANGE 只能加高端，LIST 可任意加)
重組分區 (保留數據)：ALTER TABLE t REORGANIZE PARTITION p0 INTO (PARTITION n0 VALUES LESS THAN (1960), PARTITION n1 VALUES LESS THAN (1970));
修改分區類型/表達式：ALTER TABLE t PARTITION BY HASH(id) PARTITIONS 8; (重建表，慎用)

2. HASH/KEY 分區管理

不支援 DROP PARTITION，僅支援 COALESCE PARTITION N 合併減少分區數。
增加分區：ADD PARTITION PARTITIONS N。

3. 分區與表數據交換 — 高效導入/歸檔神器

ALTER TABLE partitioned_t EXCHANGE PARTITION p0 WITH TABLE non_partitioned_t;

條件： 目標表非分區、非臨時表、結構完全一致、無外鍵、無數據 (或加 WITHOUT VALIDATION)。
用途： 秒級歸檔歷史分區、大批量數據快速載入。

4. 維護操作

REBUILD PARTITION：重建分區，整理碎片 (相當於導出再導入)。
OPTIMIZE PARTITION：回收空間，InnoDB 建議用 REBUILD + ANALYZE 替代。
ANALYZE PARTITION：更新索引統計信息。
REPAIR / CHECK PARTITION：修復/檢查損壞 (MyISAM 適用)。

5. 獲取分區信息

SHOW CREATE TABLE、INFORMATION_SCHEMA.PARTITIONS、EXPLAIN PARTITIONS SELECT ... (查看掃描哪些分區)。

四、分區裁剪 — 性能提升核心

優化器根據 WHERE 條件，僅掃描包含匹配數據的分區，跳過無關分區。

可觸發裁剪的條件模式：

partition_col = constant 或 IN (const1, const2...)
範圍條件：<, >, <=, >=, BETWEEN, <> (優化器轉換為等值列表)
日期函數優化：YEAR(date_col), TO_DAYS(date_col) 等單調函數可裁剪 RANGE 分區。

不同分區類型的裁剪能力：

RANGE/LIST/RANGE COLUMNS/LIST COLUMNS：支援上述所有模式。
HASH/KEY/LINEAR HASH/KEY：僅支援等值比較 (= 或 IN)，且分區表達式必須為整型列或返回整型的表達式。日期列無法直接裁剪。

五、分區選擇 — 顯式指定掃描分區

語法：SELECT * FROM t PARTITION (p0, p1) WHERE ...
區別： 裁剪是優化器自動推導；選擇是用戶強制指定。選擇支援所有 DML (INSERT/UPDATE/DELETE)，裁剪不支援 INSERT。

六、分區限制與缺點 (必讀避坑)

1. 硬性限制

分區鍵限制：分區表達式列必須是主鍵或唯一鍵的組成部分 (除非表無主鍵/唯一鍵)。
最大分區數：8192 (含子分區)。
不支援：外鍵、全文索引、空間數據類型、臨時表、Log 表、查詢緩存、REPLACE 修改主鍵可能導致語句重寫。
函數限制：分區表達式僅允許使用特定函數 (YEAR, TO_DAYS, MONTH, UNIX_TIMESTAMP 等)，不允許存儲過程、UDF、變量。

2. 存儲引擎特定限制

InnoDB：不支援外鍵；5.7 要求所有分區同引擎；需開啟 innodb_file_per_table。
MyISAM：每分區佔 2 個文件描述符，大量分區易耗盡 open_files_limit；分區操作鎖全表 (5.6.5 前)，5.7 支援分區級鎖裁剪。
NDB Cluster：僅支援 KEY 分區。