MySQL分表後原分割槽表處理方案

主題：原分割槽表，由於資料量上漲，需要進行分表，分表後原分割槽表的資料刪掉，將表修改為非分割槽表。

大資料量資料刪除問題（原則）

1、批次刪除，這樣往往可以工作得更快，你可能需要在每次批次刪除前sleep一段時間，控制刪除的頻率，

這樣的目的是減少對生產系統的IO衝擊，把符合平均分佈，避免從庫滯後太多；

2、可以考慮分割槽表技術，刪除一個分割槽，比刪除大量資料簡單方便的多，這也是分割槽表清理/歸檔資料的優勢所在；

刪除分割槽會把分割槽內的資料刪掉，本次實踐就是採用這樣的方式。

3、按照主鍵的序列分批分批，或者基於時間分批分批，你總可以找到一種方式批次刪除，

如果實在沒有批次刪除的方式，可能你的表結構設計得不好；

4、基於硬體的效能，每批刪除的記錄數，可以選擇幾百到幾千到幾萬的資料量，

但不要太大，MySQL很難同時處理好大事務和隨機小事務；

5、如果要刪除大部分資料，那麼可以考慮的方式是，

建立一個新表，insert要保留的資料，然後切換表；

6、對於大表(InnoDB)刪除大量資料，如果是一個很大的事務，中止刪除資料的操作，

可能需要幾倍的時間用於回滾，導致嚴重的IO瓶頸，而批次刪除可以讓我們的回滾恢復得快得多。

7、需要留意空間的釋放，選擇獨立表空間會更有利於釋放空間。

實踐步驟

MySQL分割槽表的建立

CREATE TABLE tmp_User
(
   UserNum              BIGINT NOT NULL AUTO_INCREMENT,
   UserName             VARCHAR(30),
   CreateDate              DATE NOT NULL,
   Remark              VARCHAR(64),
   PRIMARY KEY (UserNum)
)
ENGINE = INNODB
 PARTITION BY RANGE
 (TO_DAYS(CreateDate))
    (
        PARTITION p_first
 VALUES LESS THAN (TO_DAYS('2021-01-01')),
        PARTITION p202101
 VALUES LESS THAN (TO_DAYS('2021-02-01')),
		PARTITION p202102
 VALUES LESS THAN (TO_DAYS('2021-03-01')),
 		PARTITION p202103
 VALUES LESS THAN (TO_DAYS('2021-04-01')),
        PARTITION p202104
 VALUES LESS THAN (TO_DAYS('2021-05-01')),
		PARTITION p202105
 VALUES LESS THAN (TO_DAYS('2021-06-01')),
		PARTITION p202106
 VALUES LESS THAN (TO_DAYS('2021-07-01')),
        PARTITION p202107
 VALUES LESS THAN (TO_DAYS('2021-08-01')),
		PARTITION p202108
 VALUES LESS THAN (TO_DAYS('2021-09-01')),
		PARTITION p202109
 VALUES LESS THAN (TO_DAYS('2021-10-01')),
        PARTITION p202110
 VALUES LESS THAN (TO_DAYS('2021-11-01')),
		PARTITION p202111
 VALUES LESS THAN (TO_DAYS('2021-12-01')),
		PARTITION p202112
 VALUES LESS THAN (TO_DAYS('2022-01-01')),
PARTITION p_future
 VALUES LESS THAN (MAXVALUE)
    );

報錯資訊

[Err] 1503 - A PRIMARY KEY must include all columns in the table's partitioning function 

分割槽的欄位必須是要包含在主鍵當中。這時候分割槽的欄位要麼是主鍵，要麼把分割槽欄位加入到主鍵中，從而形成複合主鍵。

或者不指定主鍵！

修改sql如下

CREATE TABLE tmp_User
(
   UserNum              BIGINT NOT NULL AUTO_INCREMENT,
   UserName             VARCHAR(30),
   CreateDate              DATE NOT NULL,
   Remark              VARCHAR(64),
   PRIMARY KEY (UserNum,CreateDate)
)
ENGINE = INNODB
 PARTITION BY RANGE
 (TO_DAYS(CreateDate))
    (
        PARTITION p_first
 VALUES LESS THAN (TO_DAYS('2021-01-01')),
        PARTITION p202101
 VALUES LESS THAN (TO_DAYS('2021-02-01')),
		PARTITION p202102
 VALUES LESS THAN (TO_DAYS('2021-03-01')),
 		PARTITION p202103
 VALUES LESS THAN (TO_DAYS('2021-04-01')),
        PARTITION p202104
 VALUES LESS THAN (TO_DAYS('2021-05-01')),
		PARTITION p202105
 VALUES LESS THAN (TO_DAYS('2021-06-01')),
		PARTITION p202106
 VALUES LESS THAN (TO_DAYS('2021-07-01')),
        PARTITION p202107
 VALUES LESS THAN (TO_DAYS('2021-08-01')),
		PARTITION p202108
 VALUES LESS THAN (TO_DAYS('2021-09-01')),
		PARTITION p202109
 VALUES LESS THAN (TO_DAYS('2021-10-01')),
        PARTITION p202110
 VALUES LESS THAN (TO_DAYS('2021-11-01')),
		PARTITION p202111
 VALUES LESS THAN (TO_DAYS('2021-12-01')),
		PARTITION p202112
 VALUES LESS THAN (TO_DAYS('2022-01-01')),
PARTITION p_future
 VALUES LESS THAN (MAXVALUE)
    );

檢查表結構

SHOW CREATE TABLE tmp_User;

造資料

DROP PROCEDURE idata;
DELIMITER ;;
CREATE PROCEDURE idata()
BEGIN
DECLARE i INT;
SET i=1;
WHILE(i<=1000)DO
INSERT INTO tmp_User (UserName,CreateDate,Remark) 
VALUES(CONCAT('張',i), DATE_ADD(NOW(),INTERVAL 1 DAY),'儲存insert');
SET i=i+1;
END WHILE;
END;;
DELIMITER ;
CALL idata();

檢查分割槽資訊

-- 檢查分割槽
SELECT TABLE_NAME,PARTITION_NAME,PARTITION_METHOD,
PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,
DATA_FREE,CREATE_TIME,UPDATE_TIME
FROM information_schema.`PARTITIONS` WHERE TABLE_NAME = 'tmp_User';

如果以上資料是來自生產庫資料，測試過程中不能隨便改動。

需要先建立一張相同的表，建立方法如下

create table xx as 只複製表結構和資料，不包含主鍵，索引，分割槽等資訊

CREATE TABLE tmp_user1 AS
SELECT * FROM tmp_user;

create table xx like 只複製表結構無資料，包含主鍵，索引，分割槽等資訊

CREATE TABLE tmp_user2 LIKE tmp_user;

資料insert

REPLACE INTO tmp_User2
SELECT * FROM tmp_User;

檢查分割槽資訊

SELECT TABLE_NAME,PARTITION_NAME,PARTITION_METHOD,
PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,
DATA_FREE,CREATE_TIME,UPDATE_TIME
FROM information_schema.`PARTITIONS` WHERE TABLE_NAME = 'tmp_User';

刪除全部分割槽語句透過select語句實現

SELECT CONCAT('alter table ',table_name,' drop partition ',partition_name,';')
FROM information_schema.`PARTITIONS` 
WHERE TABLE_NAME LIKE 'tmp_User2' ;

執行刪除分割槽命令刪除到最後一個分割槽時會報錯

[Err] 1508 - Cannot REMOVE ALL PARTITIONS, USE DROP TABLE instead

因為是分割槽表，所以不能把所有的分割槽都刪掉，只能刪除表。

如何讓分割槽表變為普通表

MySQL5.6開始支援 ALTER TABLE xx EXCHANGE PARTITION的語法。

允許分割槽或自分割槽的資料與另一個分割槽的資料進行交換。

如果非分割槽表的資料為空，相當於將分割槽中的資料移動到非分割槽表。

如果分割槽表資料為空，相當於將外部表資料匯入分割槽中。

注意事項

要交換的表和分割槽表的表結構相同且不能含有分割槽。

非分割槽表的資料必須在交換的分割槽內定義。

被交換的表不能含有外來鍵，且其他表不能含有對該表的外來鍵引用。

使用者需要有ALTER、INSERT、CREATE、DROP的許可權。

使用該語句時觸發器不會被觸發。

AUTO_INCREMENT列將會被重置。

如果分割槽表和非分割槽表的資料都為空這種方法就不適用了！

分割槽表和非分割槽表的資料都為空採取一下措施

表的重新命名

RENAME TABLE tmp_User2 TO tmp_User2old ;

表的建立

CREATE TABLE tmp_User2old
(
   UserNum              BIGINT NOT NULL AUTO_INCREMENT,
   UserName             VARCHAR(30),
   CreateDate              DATE NOT NULL,
   Remark              VARCHAR(64),
   PRIMARY KEY (UserNum)
)
ENGINE = INNODB;

資料insert

REPLACE INTO tmp_User2 
SELECT * FROM tmp_User2old ;

tmp表刪除

DROP TABLE IF EXISTS tmp_User2old ;

參考文獻：

• 《MySQL技術內幕：sql程式設計》第十章 分割槽

• MySQL刪除大量資料的一些建議 - Gary Chen的文章 - 知乎