Mysql数据库大表归档操作

最新推荐文章于 2024-05-26 19:23:37 发布

蓝星福

最新推荐文章于 2024-05-26 19:23:37 发布

阅读量8.8k

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/bluestarf/article/details/49641047

版权

MySQL 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

由于公司的某个系统数据表过亿，考虑到数据表的压力。所以根据某个时间点对数据表做了一个归档。以下的操作是在当前的数据库新建表用于存储历史数据，然后再对生产表做一个清理操作。如果有条件的话可以把归档的数据放在一台新的数据库服务器上。（具体的表名和时间结点根据自己需要修改）

归档sql：

方法一：复制表并且按照条件插入数据（此种方法除了主键索引不包括其他索引）

CREATE TABLE lime_survey_549656_20151001 as select * from lime_survey_549656  where submitdate < "2015-10-01 00:00:00";
 
ALTER TABLE lime_survey_549656_20151001 change id id int primary key auto_increment;
 
CREATE TABLE lime_survey_186194_20151001 as select * from lime_survey_186194 where submitdate < "2015-10-01 00:00:00";
 
ALTER TABLE lime_survey_186194_20151001 change id id int primary key auto_increment;
 
CREATE TABLE lime_survey_279575_20151001 as select * from lime_survey_279575 where submitdate < "2015-10-01 00:00:00";
 
ALTER TABLE lime_survey_279575_20151001 change id id int primary key auto_increment;

方法二：创建一张空表，结构和索引和原表一样

create table lime_survey_549656_20151001 like lime_survey_549656; 
INSERT INTO lime_survey_549656_20151001 select * from lime_survey_549656  where submitdate < "2015-10-01 00:00:00";

create table lime_survey_186194_20151001 like lime_survey_186194; 
INSERT INTO lime_survey_186194_20151001 select * from lime_survey_186194  where submitdate < "2015-10-01 00:00:00";

create table lime_survey_279575_20151001 like lime_survey_279575; 
INSERT INTO lime_survey_279575_20151001 select * from lime_survey_279575  where submitdate < "2015-10-01 00:00:00";

数据归档成功后清理数据sql:

deletefrom lime_survey_549656  where submitdate < "2015-10-0100:00:00";
 
deletefrom lime_survey_186194  where submitdate < "2015-10-0100:00:00";
 
deletefrom lime_survey_279575  where submitdate < "2015-10-0100:00:00";