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;

实现不停业务，对碎片进行清理。