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

MySQL 分區表完整指南：類型、建立、管理與效能優化實戰

編輯：MySQL 綜合教學

MySQL 分區表完整指南：類型、建立、管理與效能優化實戰

當單表資料量達到千萬級，查詢效能明顯下降。除分表、分庫、業務拆分外，表分區是維護成本最低、代碼改動最小的方案。本文系統整理 MySQL 5.1+ 分區功能：支援檢查、五大分區類型、建立語法、子分區、索引限制與實戰建議。

如何確認資料庫支援分區

-- MySQL 5.6 之前
SHOW VARIABLES LIKE '%partition%';
-- have_partition_engine = YES 表示支援

-- MySQL 5.6 及之後
SHOW PLUGINS;
-- partition | ACTIVE | STORAGE ENGINE 表示已啟用

MySQL 僅支援水平分區（橫向拆分）

將表的不同行依規則存放於不同物理檔案，甚至不同磁碟。垂直分區（欄位拆分）不支援。

1. 建立分區表：必須在 CREATE TABLE 時指定

CREATE TABLE `T_part` (
  `f_id` INT DEFAULT NULL,
  `f_name` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`f_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY RANGE (f_id) (
  PARTITION p0 VALUES LESS THAN (10),
  PARTITION p1 VALUES LESS THAN (20)
);

f_id < 10 → p0；10 ≤ f_id < 20 → p1
插入 f_id ≥ 20 會報錯，需加 PARTITION p2 VALUES LESS THAN MAXVALUE 兜底

動態管理分區

-- 新增分區
ALTER TABLE T_part ADD PARTITION (PARTITION p2 VALUES LESS THAN (MAXVALUE));
-- 刪除分區（資料同步刪除！）
ALTER TABLE T_part DROP PARTITION p2;

2. 五大分區類型詳細解析

類型	適用場景	語法關鍵字
RANGE	連續區間：時間、ID 範圍	PARTITION BY RANGE(expr) + VALUES LESS THAN
LIST	離散值集合：性別、狀態碼、地區代碼	PARTITION BY LIST(expr) + VALUES IN (...)
HASH	資料均勻分佈，無明確範圍	PARTITION BY HASH(expr) + PARTITIONS N
LINEAR HASH	大量資料時更優的修剪效能	PARTITION BY LINEAR HASH(expr)
KEY	由 MySQL 內部哈希函數計算，支援非整數欄位	PARTITION BY KEY(col) + PARTITIONS N

RANGE 範例：按年份分區

CREATE TABLE range_p (
  perid INT, pername CHAR(12), monsalary DECIMAL(10,2), credate DATETIME
) PARTITION BY RANGE (YEAR(credate)) (
  PARTITION p2011 VALUES LESS THAN (2011),
  PARTITION p2012 VALUES LESS THAN (2012),
  PARTITION p2013 VALUES LESS THAN (2013),
  PARTITION p2014 VALUES LESS THAN (2014),
  PARTITION p2015 VALUES LESS THAN MAXVALUE
);

LIST 範例：按性別分區

CREATE TABLE list_p (
  perid INT, pername CHAR(12), sex INT, monsalary DECIMAL(10,2), credate DATETIME
) PARTITION BY LIST (sex) (
  PARTITION psex1 VALUES IN (1),
  PARTITION psex2 VALUES IN (2)
);

注意： LIST 僅支援整數值，字串會報錯 ERROR 1697。

HASH / KEY 範例

-- HASH
CREATE TABLE hash_p (...) PARTITION BY HASH(perid) PARTITIONS 8;
-- 或按年份哈希
CREATE TABLE hash_p (...) PARTITION BY HASH(YEAR(credate)) PARTITIONS 8;

-- KEY（MySQL 內建哈希，支援多欄位）
CREATE TABLE key_p (...) PARTITION BY KEY(perid) PARTITIONS 8;
CREATE TABLE key_p (...) PARTITION BY LINEAR KEY(perid) PARTITIONS 3;

3. 子分區：分區內再分區

CREATE TABLE T_part (
  f_id INT, f_name VARCHAR(20), PRIMARY KEY (f_id)
) PARTITION BY RANGE (f_id)
SUBPARTITION BY HASH(f_id) SUBPARTITIONS 2 (
  PARTITION p0 VALUES LESS THAN (10),
  PARTITION p1 VALUES LESS THAN (20)
);

實際產生 2×2=4 個物理分區。可進一步指定每個子分區的資料/索引目錄分離至不同磁碟：

SUBPARTITION s0 DATA DIRECTORY='/disk0/data' INDEX DIRECTORY='/disk0/idx', ...

4. 關鍵限制與避坑指南

分區欄位必須包含在主鍵/唯一索引中：A PRIMARY KEY MUST INCLUDE ALL COLUMNS IN THE TABLE'S PARTITIONING FUNCTION
NULL 值視為 0：RANGE/LIST 分區會將 NULL 歸入最小分區，建議欄位設 NOT NULL
無全域索引，僅分區索引：查詢若無分區鍵會掃描所有分區，效能劇降
RANGE 新增分區只能加在高端：不可插入中間區間
5.5+ 支援 COLUMNS 分區：直接使用 DATE、DATETIME、CHAR、VARCHAR 等非整數型別分區

COLUMNS 分區範例（按日期直接分區）

CREATE TABLE range_p (...) 
PARTITION BY RANGE COLUMNS (credate) (
  PARTITION p20151 VALUES LESS THAN ('2015-04-01'),
  PARTITION p20152 VALUES LESS THAN ('2015-07-01'),
  PARTITION p20153 VALUES LESS THAN ('2015-10-01'),
  PARTITION p20154 VALUES LESS THAN ('2016-01-01'),
  PARTITION partlog VALUES LESS THAN MAXVALUE
);