MySQL 分區表管理與維護完整實戰指南

MySQL 分區表管理與維護完整實戰指南

分區表通過將大表拆分為多個物理分區，提升查詢性能與維護靈活性。本文系統講解分區表的創建、分區策略變更、分區增刪改、分區交換、維護操作等核心技能，所有示例均在 MySQL 5.7 環境驗證。

一、基礎環境準備

-- 創建範圍分區表示例表
CREATE TABLE trb3 (
  id INT,
  name VARCHAR(50),
  purchased DATE
) PARTITION BY RANGE (YEAR(purchased)) (
  PARTITION p0 VALUES LESS THAN (1990),
  PARTITION p1 VALUES LESS THAN (1995),
  PARTITION p2 VALUES LESS THAN (2000),
  PARTITION p3 VALUES LESS THAN (2005)
);

-- 批量插入測試數據存儲過程
DELIMITER $$
CREATE PROCEDURE pr_trb3(IN begin_date DATE, IN end_date DATE, IN tab_name VARCHAR(40))
BEGIN
  WHILE begin_date < end_date DO
    INSERT INTO trb3 VALUES (NULL, CONCAT('name_', begin_date), begin_date);
    SET begin_date = DATE_ADD(begin_date, INTERVAL 1 DAY);
  END WHILE;
END$$
DELIMITER ;

-- 執行插入 1985-2004 年數據
CALL pr_trb3('1985-01-01', '2004-12-31', 'trb3');

二、查看分區數據分布

SELECT
  PARTITION_NAME AS part,
  PARTITION_EXPRESSION AS expr,
  PARTITION_DESCRIPTION AS descr,
  TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME = 'trb3';

輸出示例：

+------+------------------+-------+------------+
| part | expr             | descr | table_rows |
+------+------------------+-------+------------+
| p0   | YEAR(purchased)  | 1990  | 1826       |
| p1   | YEAR(purchased)  | 1995  | 1826       |
| p2   | YEAR(purchased)  | 2000  | 1826       |
| p3   | YEAR(purchased)  | 2005  | 1826       |
+------+------------------+-------+------------+

注意：TABLE_ROWS 為優化器估算值，InnoDB 分區表不精確，以 COUNT(*) 為準。

三、改變分區策略

-- 將 RANGE 分區轉為 KEY 分區（4個分區）
ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 4;

操作等效於 DROP TABLE + CREATE TABLE ... PARTITION BY KEY(id) PARTITIONS 4，數據自動重分布。

四、存儲引擎變更

ALTER TABLE trb3 ENGINE = MyISAM;

分區表變更引擎與普通表無異，各分區獨立轉換，SHOW CREATE TABLE 可見每個分區獨立指定引擎。

五、移除分區（轉為普通表）

ALTER TABLE trb3 REMOVE PARTITIONING;

數據保留，分區元數據清除，INFORMATION_SCHEMA.PARTITIONS 僅顯示一行 NULL 分區。

六、RANGE/LIST 分區精細管理

1. 增加分區

ALTER TABLE trb3 ADD PARTITION (PARTITION p5 VALUES LESS THAN (2010));

2. 合併分區

ALTER TABLE trb3 REORGANIZE PARTITION p3, p5 INTO (
  PARTITION p5 VALUES LESS THAN (2010)
);

3. 拆分分區

ALTER TABLE trb3 REORGANIZE PARTITION p5 INTO (
  PARTITION p3 VALUES LESS THAN (2005),
  PARTITION p4 VALUES LESS THAN (2010)
);

七、HASH/KEY 分區數量調整

-- 創建 12 個 HASH 分區
CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)
PARTITION BY HASH(YEAR(purchased)) PARTITIONS 12;

-- 縮減至 8 個分區（COALESCE 合併尾部分區）
ALTER TABLE trb3 COALESCE PARTITION 4;

數據自動按新 Hash 算法重分布，COUNT(*) 總量不變，單分區行數變化符合預期。

八、分區與非分區表數據交換

核心語法：ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt;

-- 創建分區表 e
CREATE TABLE e (id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30))
PARTITION BY RANGE (id) (
  PARTITION p0 VALUES LESS THAN (50),
  PARTITION p1 VALUES LESS THAN (100),
  PARTITION p2 VALUES LESS THAN (150),
  PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
INSERT INTO e VALUES (16,'Frank','White'), (337,'Mary','Jones'), (1669,'Jim','Smith'), (2005,'Linda','Black');

-- 創建結構相同的非分區表 e2
CREATE TABLE e2 LIKE e;
ALTER TABLE e2 REMOVE PARTITIONING;

-- 交換 p0 分區與 e2 表
ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;

-- 創建帶子分區表
CREATE TABLE es (...)
PARTITION BY RANGE (id) SUBPARTITION BY KEY (lname) SUBPARTITIONS 2 (...);

-- 僅能按子分區粒度交換
ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;