深入淺析 MySQL COLUMNS 分區：RANGE COLUMNS 與 LIST COLUMNS 實戰指南

MySQL 5.5 引入 **COLUMNS 分區**，支援整數、日期、字串型別直接分區，**無需函數轉換**，並支援多欄位組合分區鍵。本文詳細解析 RANGE COLUMNS 與 LIST COLUMNS 語法、多欄位比較規則、執行計劃驗證與遷移建議。

COLUMNS 分區 vs 傳統 RANGE/LIST

支援的資料型別

• 整數：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT（不支援 DECIMAL/FLOAT）
• 日期時間：DATE, DATETIME（不支援 TIMESTAMP）
• 字串：CHAR, VARCHAR, BINARY, VARBINARY（不支援 TEXT/BLOB）

一、RANGE COLUMNS 分區

1. 日期欄位分區範例

CREATE TABLE members (
  id INT, joined DATE NOT NULL
) PARTITION BY RANGE COLUMNS(joined) (
  PARTITION a VALUES LESS THAN ('1960-01-01'),
  PARTITION b VALUES LESS THAN ('1970-01-01'),
  PARTITION c VALUES LESS THAN ('1980-01-01'),
  PARTITION d VALUES LESS THAN ('1990-01-01'),
  PARTITION e VALUES LESS THAN MAXVALUE
);

2. 插入測試與分區分布查詢

INSERT INTO members(id,joined) VALUES
(1,'1950-01-01'),(1,'1960-01-01'),(1,'1980-01-01'),(1,'1990-01-01');

SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION,
       PARTITION_DESCRIPTION, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='members';

3. 執行計劃驗證分區裁剪

EXPLAIN SELECT id,joined FROM members WHERE joined=YEAR(NOW());
-- partitions: ALL (使用函數導致無法裁剪)

EXPLAIN SELECT id,joined FROM members WHERE joined='1963-01-01';
-- partitions: b (精準裁剪到 b 分區)

結論：分區欄位**嚴禁使用函數/運算**，否則失效全表掃描。

4. 多欄位組合分區

CREATE TABLE rcx (
  a INT, b INT
) PARTITION BY RANGE COLUMNS(a,b) (
  PARTITION p0 VALUES LESS THAN (5,10),
  PARTITION p1 VALUES LESS THAN (10,20),
  PARTITION p2 VALUES LESS THAN (15,30),
  PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

多欄位比較規則（元組比較）：

1. 先比第一欄位：插入值 < 分區界值 → 歸屬該分區，不再比第二欄位。
2. 第一欄位 = 分區界值 → 比第二欄位：插入值 < 界值 → 歸屬該分區。
3. 第一欄位 > 分區界值 → 繼續比對下一分區。

範例驗證：

INSERT INTO rcx(a,b) VALUES (1,20), (10,15), (10,30);
-- (1,20): 1<5 → p0
-- (10,15): 10>5, 10=10 且 15<20 → p1
-- (10,30): 10=10 但 30>20 → 不屬 p1；10<15 → p2

限制：第一欄位分區界值**必須單調遞增**，不可交叉（如 p2 第一欄位 8 < p1 的 10 會報錯）；第二欄位無順序要求。

二、LIST COLUMNS 分區

1. 非整數欄位分區（日期時間）

CREATE TABLE listvar (
  id INT NOT NULL, hired DATETIME NOT NULL
) PARTITION BY LIST COLUMNS(hired) (
  PARTITION a VALUES IN ('1990-01-01 10:00:00','1991-01-01 10:00:00'),
  PARTITION b VALUES IN ('1992-01-01 10:00:00'),
  PARTITION c VALUES IN ('1993-01-01 10:00:00'),
  PARTITION d VALUES IN ('1994-01-01 10:00:00')
);
ALTER TABLE listvar ADD INDEX ix_hired(hired);
INSERT INTO listvar VALUES
(1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00'),
(1,'1992-01-01 10:00:00'),(1,'1993-01-01 10:00:00');

EXPLAIN SELECT * FROM listvar WHERE hired='1990-01-01 10:00:00';
-- partitions: a (精準裁剪)

2. 多欄位組合分區

CREATE TABLE listvardou (
  id INT NOT NULL, hired DATETIME NOT NULL
) PARTITION BY LIST COLUMNS(id,hired) (
  PARTITION a VALUES IN ((1,'1990-01-01 10:00:00'),(1,'1991-01-01 10:00:00')),
  PARTITION b VALUES IN ((2,'1992-01-01 10:00:00')),
  PARTITION c VALUES IN ((3,'1993-01-01 10:00:00')),
  PARTITION d VALUES IN ((4,'1994-01-01 10:00:00'))
);
ALTER TABLE listvardou ADD INDEX ix_hired1(id,hired);  -- 組合索引最佳

EXPLAIN SELECT * FROM listvardou WHERE id=1 AND hired='1990-01-01 10:00:00';
-- filtered 100%，組合索引生效

三、移除分區定義（保留數據）

ALTER TABLE tablename REMOVE PARTITIONING;

僅移除分區元數據，**不刪除數據**；DROP PARTITION 則會連同數據一併刪除。

總結與建議

• 優先使用 **COLUMNS 分區** 替代傳統 RANGE/LIST，語義更清晰、型別支援更廣。
• 分區鍵**嚴禁函數運算**，查詢條件需與分區定義型別一致（日期傳日期字串，不要傳 YEAR() 結果）。
• 多欄位分區建立**組合索引**完全匹配分區鍵順序，獲得最佳裁剪效率。
• 注意：**不支援 TIMESTAMP 型別** 分區，請用 DATETIME。