萬盛學電腦網

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

MySQL 表分區完整指南:四大分區類型、約束限制、分區函數與運維實戰

什麼是表分區

將一張大表按規則水平拆分為多個物理獨立的分區,邏輯上仍為一張表。MySQL 5.1+ 支持。

核心收益

  • 單表突破文件系統大小限制
  • 歷史數據快速歸檔(DROP PARTITION 秒級)
  • 查詢分區剪枝,掃描更少數據
  • 聚合查詢可並行執行
  • 跨磁盤分布 IO,提升吞吐

四大分區類型詳解

1. RANGE 分區:連續區間

CREATE TABLE orders (
  id BIGINT, order_date DATE, amount INT
) PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);
  • 區間連續不重疊,按順序定義。
  • 適合時間序列、自增 ID 範圍。
  • MAXVALUE 必須作為最後兜底分區。

2. LIST 分區:離散值枚舉

CREATE TABLE stores (
  id INT, region_id INT
) PARTITION BY LIST (region_id) (
  PARTITION p_north VALUES IN (1,3,5),
  PARTITION p_south VALUES IN (2,4,6)
);
  • 值列表必須為整數(或返回整數的表達式)。
  • 無 MAXVALUE,插入非列表值直接報錯。
  • 適合枚舉型業務(地區、狀態、類別)。

3. HASH 分區:哈希均勻分布

CREATE TABLE logs (
  id BIGINT, created_at DATETIME
) PARTITION BY HASH (MONTH(created_at))
PARTITIONS 12;
  • 指定分區數 PARTITIONS N。
  • MySQL 內部哈希函數,數據均勻分布。
  • 無法精確刪除特定業務數據(需 TRUNCATE PARTITION)。

4. KEY 分區:MySQL 內置哈希

CREATE TABLE users (
  id INT, name VARCHAR(50)
) PARTITION BY KEY (id)
PARTITIONS 10;
  • 僅支持列名,不支持表達式。
  • MySQL 使用內部哈希函數(類似 PASSWORD())。
  • 支援 LINEAR KEY(2 的冪算法,增刪分區更快)。

硬性約束(必須遵守)

  1. 分區鍵 ⊆ 主鍵/唯一鍵:若表有 PK/UK,分區列必須包含在其中。
    例:PRIMARY KEY (id, order_date) 才能按 order_date 分區。
  2. 分區函數白名單:只能使用官方允許的函數(見下表)。
  3. 分區數上限:8192(含子分區)。
  4. 外鍵不支持:分區表不能有外鍵約束。

允許的分區函數

YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND()
TO_DAYS(), TO_SECONDS(), DATEDIFF()
WEEKDAY(), DAYOFWEEK(), DAYOFMONTH(), DAYOFYEAR(), QUARTER(), WEEK()
UNIX_TIMESTAMP() (5.7+)
ABS(), CEILING(), FLOOR(), MOD()
EXTRACT(), MICROSECOND(), TIME_TO_SEC()

❌ 不支持:FROM_UNIXTIME()、NOW()、RAND()、自定義函數、非確定性函數。


實戰案例:訂單表按年月分區

CREATE TABLE `order` (
  order_id BIGINT NOT NULL,
  `date` DATE NOT NULL,
  amount INT,
  status TINYINT,
  PRIMARY KEY (order_id, `date`)  -- 分區鍵 date 必須在 PK 中
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 按 年月 分區:date DIV 100 → 202401, 202402...
ALTER TABLE `order` PARTITION BY RANGE (`date` DIV 100) (
  PARTITION p202401 VALUES LESS THAN (202402),
  PARTITION p202402 VALUES LESS THAN (202403),
  PARTITION p202403 VALUES LESS THAN (202404),
  PARTITION p_catch_all VALUES LESS THAN MAXVALUE
);

分區運維核心操作

操作SQL 範例說明
新增分區ALTER TABLE t REORGANIZE PARTITION p_catch_all INTO (PARTITION p202404 VALUES LESS THAN (202405), PARTITION p_catch_all VALUES LESS THAN MAXVALUE);必須拆分 MAXVALUE 分區,數據不丟失
合併分區ALTER TABLE t REORGANIZE PARTITION p202401,p202402,p_catch_all INTO (PARTITION p_merged VALUES LESS THAN MAXVALUE);需包含 MAXVALUE 分區,範圍總和不變
刪除分區(保留數據)ALTER TABLE t REMOVE PARTITIONING;5.5 支持,5.6+ 需驗證
刪除分區(刪數據)ALTER TABLE t DROP PARTITION p202301;秒級完成,數據不可恢復
截斷分區ALTER TABLE t TRUNCATE PARTITION p202301;保留分區結構,清空數據

查詢分區剪枝:寫法決定性能

❌ 無效:WHERE date >= '2024-01-01' AND date <= '2024-01-31'(範圍查詢不剪枝)

✅ 有效:WHERE date = '2024-01-15' 或 WHERE date IN ('2024-01-01','2024-01-02',...)

EXPLAIN PARTITIONS 驗證 partitions 列是否為單一分區。


子分區(Composite Partitioning)

RANGE/LIST + HASH/KEY 組合:

PARTITION BY RANGE (YEAR(order_date))
SUBPARTITION BY HASH (MONTH(order_date))
SUBPARTITIONS 12 (...)

適用於:超大表、需同時按時間範圍查詢和哈希並行聚合的場景。

copyright © 萬盛學電腦網 all rights reserved