萬盛學電腦網

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

MySQL 分區表新增分區實戰:從非分區表遷移、分區管理與資料驗證

現有非分區表需改為分區表,不能直接 ALTER TABLE ADD PARTITION(報錯 ERROR 1505: Partition management on a not partitioned table is not possible)。本文提供標準遷移路徑:新建分區表 → 資料遷移 → 原子切換,並演示分區增刪改查、合併拆分全流程操作。

遷移標準流程

  1. 建立同結構分區表
  2. 資料遷移:INSERT INTO partitioned_tbl SELECT * FROM original_tbl
  3. 驗證資料一致性:行數、檢查和、業務抽樣
  4. 原子切換:RENAME TABLE original_tbl TO original_tbl_bak, partitioned_tbl TO original_tbl;
  5. 觀察期後清理備份表

實戰演示:RANGE 分區表全生命週期

1. 建立測試分區表

CREATE TABLE tr (
  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)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

2. 插入測試資料

INSERT INTO tr VALUES
(1, 'desk organiser', '2003-10-15'),
(2, 'CD player', '1993-11-05'),
(3, 'TV set', '1996-03-10'),
(4, 'bookcase', '1982-01-10'),
(5, 'exercise bike', '2004-05-09'),
(6, 'sofa', '1987-06-05'),
(7, 'popcorn maker', '2001-11-22'),
(8, 'aquarium', '1992-08-04'),
(9, 'study desk', '1984-09-16'),
(10, 'lava lamp', '1998-12-25');

3. 分區剪枝驗證

-- 僅掃描 p1、p2、p3 三個分區
EXPLAIN PARTITIONS SELECT * FROM tr 
WHERE purchased BETWEEN '1995-01-01' AND '2004-12-31';

4. 刪除分區(資料同步刪除)

ALTER TABLE tr DROP PARTITION p2;
SHOW CREATE TABLE tr\G
-- p2 消失,p0、p1、p3 保留

5. 刪除後新插入資料自動路由

INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');
-- 原屬 p2 的資料現路由至 p3(VALUES LESS THAN 2005)

進階:REORGANIZE PARTITION 合併與拆分

場景:members 表按出生年份分區,需拆分最早分區

CREATE TABLE members (
  id INT,
  fname VARCHAR(25),
  lname VARCHAR(25),
  dob DATE
) PARTITION BY RANGE (YEAR(dob)) (
  PARTITION p0 VALUES LESS THAN (1970),
  PARTITION p1 VALUES LESS THAN (1980),
  PARTITION p2 VALUES LESS THAN (1990)
);

尾部新增分區

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));

頭部拆分分區:p0 → m0(1960) + m1(1970)

ALTER TABLE members REORGANIZE PARTITION p0 INTO (
  PARTITION m0 VALUES LESS THAN (1960),
  PARTITION m1 VALUES LESS THAN (1970)
);

驗證結果

SHOW CREATE TABLE members\G
-- 結果包含 m0, m1, p1, p2, p3 五個分區,資料完整無損

關鍵操作對比表

操作語法資料影響鎖等級適用場景
新增尾部分區ADD PARTITION無資料移動MDL 共享鎖時間序列定期擴展
刪除分區DROP PARTITION資料永久刪除MDL 獨佔鎖歷史資料歸檔清理
合併/拆分現有分區REORGANIZE PARTITION資料重組,不丟失MDL 獨佔鎖調整分區粒度、合併冷分區
修改分區表達式PARTITION BY ...全表重建MDL 獨佔鎖極少用,需停機

從非分區表遷移的完整腳本範例

-- 1. 建立分區表(結構同步,加上分區定義)
CREATE TABLE orders_partitioned LIKE orders;
ALTER TABLE orders_partitioned 
  PARTITION BY RANGE COLUMNS (order_date) (
    PARTITION p202301 VALUES LESS THAN ('2023-02-01'),
    PARTITION p202302 VALUES LESS THAN ('2023-03-01'),
    PARTITION pmax VALUES LESS THAN MAXVALUE
  );

-- 2. 分批遷移資料(避免長事務、鎖衝突)
-- 假設 order_date 範圍 2023-01-01 至 2023-12-31
INSERT INTO orders_partitioned SELECT * FROM orders 
WHERE order_date < '2023-02-01';
INSERT INTO orders_partitioned SELECT * FROM orders 
WHERE order_date >= '2023-02-01' AND order_date < '2023-03-01';
INSERT INTO orders_partitioned SELECT * FROM orders 
WHERE order_date >= '2023-03-01';

-- 3. 一致性校驗
SELECT COUNT(*), SUM(order_amount) FROM orders;
SELECT COUNT(*), SUM(order_amount) FROM orders_partitioned;

-- 4. 原子切換(需短暫封寫,秒級)
RENAME TABLE orders TO orders_old, orders_partitioned TO orders;

-- 5. 觀察 1-7 天無異常後
DROP TABLE orders_old;

總結

分區表新增/管理操作核心在於:非分區表遷移必須重建表、REORGANIZE PARTITION 安全合併拆分不丟數據、DROP PARTITION 等同於資料刪除需謹慎。生產環境變更請遵循:預演環境驗證 → 分批遷移 → 原子切換 → 觀察期 → 清理 標準流程。

copyright © 萬盛學電腦網 all rights reserved