对于数据库操作的敬畏小案例
自鞥列进行这是学习笔记的第 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_dataCreate 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=utf81 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_dataCreate 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=utf81 row in set (0.00 sec)所以整个操作整体是一个异步的处理过程，每一步都是相对独立的，而且能够把整个操作的范围控制在一个尽可能小的范围呢。
腾讯云开发者
