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 ;

参考文献：