自鞥列进行这是学习笔记的第 2236篇文章
读完需要
9
分钟速读仅需7分钟
之前一直在说对于线上运维操作的敬畏之心,但是话说了,有时候没有案例的说明其实是苍白的。刚好借着最近同事碰到的一个案例来做下说明。
有个数据库环境需要清理一些历史数据,总量在亿级,保留近一个月的数据,大概在千万级。
如果按照常规的理解,可能会是如下的操作:
1)将表test_data修改为test_data_tmp
2)新建一张表test_data,表结构和原来一样
3)将近1个月的数据补录到test_data里面
如果按照SQL语句来细化,大概是如下的操作:
rename table test_data to test_data_tmp;
create table test_data like test_data_tmp;
insert into test_data select * from test_data_tmp where create_date xxxx;
看起来好像没问题,但是实际上有很多的漏洞,随口就能说出一大堆。
1)数据补录会依赖自增ID,会对已有的数据写入产生阻塞
2)数据补录的过程不可控,事务量级太大
3)自增ID的数据冲突,比如自增列id在原来的表中是100,在新的表中是从1开始,数据补录可能会有数据冲突
4)在rename和新建表的过程中,对于业务是完全不可用
我们来做个小的测试,把一个标准化的操作复现一下。
首先做下数据初始化。
create table test_data(id int auto_increment primary key,oid int,name varchar(30),cdate datetime);
alter table test_data add key idx_cdate(cdate);
insert into test_data(oid,name,cdate) values(1,'aa','2020-06-03 18:00:00');
insert into test_data(oid,name,cdate) values(2,'bb','2020-06-02 18:00:00');
insert into test_data(oid,name,cdate) values(3,'cc','2020-06-01 18:00:00');
insert into test_data(oid,name,cdate) values(4,'dd','2020-05-31 18:00:00');
insert into test_data(oid,name,cdate) values(5,'ee','2020-05-30 18:00:00');
insert into test_data(oid,name,cdate) values(6,'ff','2020-05-29 18:00:00');
insert into test_data(oid,name,cdate) values(7,'gg','2020-05-28 18:00:00');
insert into test_data(oid,name,cdate) values(8,'hh','2020-05-27 18:00:00');
insert into test_data(oid,name,cdate) values(9,'ii','2020-05-26 18:00:00');
使用如下的语句实现移形换位,在一个DDL操作粒度内完成表切换。
create table test_arch.test_data like test.test_data;
RENAME TABLE test.test_data TO test_arch.test_data_bak,
test_arch.test_data TO test.test_data,
test_arch.test_data_bak TO test_arch.test_data;
切换后的表结构自增列id是初始值,比如原来是100万,现在就是1
>show create table test.test_data\G
*************************** 1. row ***************************
Table: test_data
Create Table: CREATE TABLE `test_data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`oid` int(11) DEFAULT NULL,
`name` varchar(30) DEFAULT NULL,
`cdate` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_cdate` (`cdate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
我们把自增列进行递增,这样就形成了一个ID列的区间,比如1-10之间是空白的,新增数据会从10开始递增。
>alter table test.test_data auto_increment=10;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
后续的数据补录,我们也做下拆分,可以按照天为单位进行数据补录。
insert into test.test_data select * from test_arch.test_data where cdate between '2020-05-27' and '2020-05-28';
在补录的过程中,自增ID如果没有新增数据,则不会发生变化。
如果写入了数据,则会发生变化。
>insert into test.test_data(oid,name,cdate) values(15,'ll','2020-06-03');
Query OK, 1 row affected (0.00 sec)
>select * from test.test_data;
+----+------+------+---------------------+
| id | oid | name | cdate |
+----+------+------+---------------------+
| 7 | 7 | gg | 2020-05-28 18:00:00 |
| 8 | 8 | hh | 2020-05-27 18:00:00 |
| 10 | 15 | ll | 2020-06-03 00:00:00 |
+----+------+------+---------------------+
3 rows in set (0.00 sec)
写入数据后,查看自增列ID情况,会很自然的+1
>show create table test.test_data\G
*************************** 1. row ***************************
Table: test_data
Create Table: CREATE TABLE `test_data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`oid` int(11) DEFAULT NULL,
`name` varchar(30) DEFAULT NULL,
`cdate` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_cdate` (`cdate`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
所以整个操作整体是一个异步的处理过程,每一步都是相对独立的,而且能够把整个操作的范围控制在一个尽可能小的范围呢。
QQ群号:763628645
QQ群二维码如下, 添加请注明:姓名+地区+职位,否则不予通过
订阅我的微信公众号“杨建荣的学习笔记”,第一时间免费收到文章更新。别忘了加星标,以免错过新推送提示。
7
近期热文
你可能也会对以下话题感兴趣。点击链接就可以查看。
8
转载热文
你可能也会对以下话题感兴趣,文章来源于转载,点击链接就可以查看。