MYSQL删除大表

背景

1、生产环境有表一百多个G了。
2、需要删除历史数据，大概保留两三天左右的数据。

基础知识

MySQL中，drop和truncate是DDL(data define language)操作，操作立即生效，原数据不放到 rollback segment中，无法回滚；而delete语句为DML(data maintain Language)，这个操作会被放到 rollback segment中，事务可以回滚。执行效率方面，drop>truncate>delete。

方案

1、创建一张表结构和原表一样的新表，在原表上建立CUD触发器
2、等待三天左右
3、新旧表swap
4、对原表drop或truncate
SQL

--创建镜像表
CREATE TABLE `region_mirror` (
	`region_no` VARCHAR(16) NOT NULL COMMENT '行政区域编码',
	`name` VARCHAR(32) NOT NULL COMMENT '名称',
	`alias` VARCHAR(32) NULL DEFAULT NULL COMMENT '别名',
	`level` INT(1) NOT NULL COMMENT '级别（1省 2市 3区县 4村镇）',
	`parent_no` VARCHAR(16) NOT NULL COMMENT '父ID',
	`short_name` VARCHAR(16) NULL DEFAULT NULL COMMENT '简拼',
	`full_name` VARCHAR(128) NULL DEFAULT NULL COMMENT '全拼',
	`en_name` VARCHAR(64) NULL DEFAULT NULL COMMENT '英文名称',
	`longitude` VARCHAR(20) NOT NULL COMMENT '经度',
	`latitude` VARCHAR(20) NOT NULL COMMENT '纬度',
	`created_time` DATETIME NOT NULL COMMENT '创建时间',
	`updated_time` DATETIME NULL DEFAULT NULL COMMENT '最近更新时间',
	PRIMARY KEY (`region_no`) USING BTREE,
	UNIQUE INDEX `code1111` (`region_no`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;


--在原表创建触发器
CREATE DEFINER=`root`@`localhost` TRIGGER `add` AFTER INSERT ON `region` FOR EACH ROW BEGIN
   REPLACE  INTO `region_mirror` VALUES (NEW.`region_no`, NEW.`name`, NEW.`alias`, NEW.`level`, NEW.`parent_no`, NEW.`short_name`, NEW.`full_name`, NEW.`en_name`, NEW.`longitude`, NEW.`latitude`, NEW.`created_time`, NEW.`updated_time`);
END;

CREATE DEFINER=`root`@`localhost` TRIGGER `update` AFTER UPDATE ON `region` FOR EACH ROW BEGIN
   delete IGNORE from region_mirror where region_no = OLD.region_no;
   REPLACE  INTO `region_mirror` VALUES (NEW.`region_no`, NEW.`name`, NEW.`alias`, NEW.`level`, NEW.`parent_no`, NEW.`short_name`, NEW.`full_name`, NEW.`en_name`, NEW.`longitude`, NEW.`latitude`, NEW.`created_time`, NEW.`updated_time`);
END;

CREATE DEFINER=`root`@`localhost` TRIGGER `delete` AFTER DELETE ON `region` FOR EACH ROW BEGIN
   delete IGNORE from region_mirror where region_no= OLD.region_no;
END;

--交换原表和镜像表
rename table region to region_old,region_mirror to region

--清理数据
truncate region_old
或者
drop table region_old

注意点

● 如有外键约束，请另想方案
● 在磁盘空间不足的情况下，需清理出足够的磁盘空间
● 对保留数据有其它要求，可以替换步骤2为自定义的拷贝逻辑