MySQL表碎片清理
MySQL大表清理
生产环境data库业务表base_data大小：500G，data_free：31G
mysql> SELECT table_schema,table_name,data_free/1024/1024 AS data_free_MB FROM information_schema.tables WHERE engine LIKE 'InnoDB' AND data_free > 100*1024*1024;
mysql> show create table base_data\G;
*************************** 1. row ***************************
Table: base_data
Create Table: CREATE TABLE `base_data` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`method` varchar(50) NOT NULL COMMENT '区分具体操作',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_base_data_ct` (`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1605027 DEFAULT CHARSET=utf8
根据需求，update_time<='2018-01-01 00:00:00'的数据进行备份并清理该部分数据。通过推算得知，
id列创建主键索引，id<=1372421为需要备份清理的数据。
1：与研发协商，分析base_data表，update_time列可以根据时间进行数据分隔，并且该列建有索引，需要保留数据为2018年1月之后的数据，可以清理的数据为2018年1月之前的数据
2：根据id列、update_time列，逐次清理2018年1月之前的数据，可按照每次对一季度的数据进行整理，先备份，再进行清理
如：备份删除id<30,0000的行
mysqldump -uroot -p data base_data --single-transaction --where="id<300000 and update_time <='2018-01-01 00:00:00'" |gzip > /data/backup/base_data.sql.gz
delete from base_data where id<100000 and update_time <='2017-10-01 00:00:00' limit 1000;
delete from base_data where id<200000 and update_time <='2017-10-01 00:00:00' limit 1000;
delete from base_data where id<300000 and update_time <='2017-10-01 00:00:00' limit 1000;
下面进行base_data表碎片整理
3：记录下当前步骤3中update_time列的时间t1，以及此时的表行数；将表base_data的2018年1月之后到时间t1的数据导出base_data1.dmp
mysql> select max(id) from base_data;
+---------+
| max(id) |
+---------+
| 1614699 |
+---------+
1 row in set (0.00 sec)
mysql> select min(id) from hl_base_data;
+---------+
| min(id) |
+---------+
| 300100 |
+---------+
1 row in set (0.00 sec)
mysql> select count(id) from base_data where id<300000 and update_time <='2018-01-01 00:00:00';
+-----------+
| count(id) |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)
mysqldump -uroot -p data base_data --single-transaction --where="id>=300000 and update_time <='t1'" > /backup/base_data.sql
4：编辑dump文件base_data1.dmp，将base_data改名为base_data_tmp，将其导入到与data库同实例下的test库，此时补充增量数据
mysql> insert into test. base_data_tmp select * from data. base_data where update_time >’t1’;
5:最后
mysql> RENAME TABLE data. base_data to data. base_data_old;
mysql> RENAME TABLE test. base_data_tmp to data. base_data;
实现不停业务，对碎片进行清理。
