一场由数据归档引发的生产事故

背景

    业务数据越写越多，当到达一定量级时，性能变慢，交易变慢，这时就会涉及到表中数据的归档与清理。这也是数据库运维经常遇到的问题，正好最近由于数据库归档发了异常生产事故，让我有机会来谈一谈如何对存储了大量数据的的表进行数据归档。

开始

    早上到达公司后像往常一样烧壶水，沏杯茶，准备开始一天有条不稳定的工作。本以为又是波澜不惊的一天，突然接到数据库告警，系统会话数超过日常基线数倍，告警大屏显示了多条业务异常，某业务线数据库异常。随后迅速告知应用运维排查异常，应用运维反馈交易大面积受阻，紧接着按照应急方案迅速组织多方力量开始排查。

    DBA通过数据库运维平台，很快定位了慢SQL，随后开发反馈昨晚该一张上亿行记录的表进行了数据的归档和清理操作，DBA发现其新建的表缺失了索引，然后迅速创建索引，业务逐渐恢复。所幸故障处理的较快，未造成重大损失，也归功于我们精准的告警，高效的团队配合。不过是什么原因导致少建了索引，需要我们进行复盘，避免故障再次发生。

分析一下为什么

最初的数据清理方案

大致步骤：

1.新建中间表的时候插入近一个月数据

2.删除原表

3.中间表改名字为原表

代码示意：

create t_tmp as select * from t_old where create_time > to_date('2023-02-11','yyyy-mm-dd');#保留一个月数据drop table t_old;alter table t_tmp rename to t_old;

这个步骤里面有几个问题：

1.create ... as select 通常我们叫做 CTAS 语法，这个语法仅复制表结构，不复制索引和约束，未复制primary key也是这起事故的直接原因。

2.直接drop table 删除了原表，不考虑变更回滚了，缺乏规避风险的思维。

3.在create table 过程中，新插入的数据丢失了。

❝

小知识点：primary key 主键约束其实是unique 约束 + not null 非空约束，而且 unique 唯一约束会自动创建一个 unique 唯一索引。

❞

建议使用的数据归档方案

1.使用DDL创建中间表，包含约束和索引

2.原表改名为bak表，中间表改为原表

3.新的表从bak表中插入近一个月数据

代码示意：

获取t_old 的建表语句，也就是ddlcreate t_tmp (x int primary key , y char(10) ,z varchar(50),create_time date); #ddl里创建约束create index idx_time_new on t_tmp(create_time); #不要忘记索引alter table t_old raneme to t_20230311_bak;alter table t_tmp rename to t_old;insert into t_old select * from t_20230311_bak where create_time > to_date('2023-02-11','yyyy-mm-dd'); drop table t_20230311_bak; #稳定运行一个月以后执行

新的方案里面充分考虑了数据归档可能受到的影响。

1.使用ddl创建的表，不会缺失约束和索引。

2.先rename 后插入数据，不会缺失交易数据影响最小。

3.稳定运行一个月后删除数据，充分考虑了业务回滚或者历史数据查询的情况。

❝

注意：rename时会有短暂的业务中断，还是建议业务低峰执行。

❞

❝

小知识点：Mysql数据库的话可以使用 create table ... like 方式复制表结构，Oracle数据库就只能乖乖使用DDL方式创建了。

❞

复盘总结

1.由于最初的方案里使用CTAS 方式未复制约束和索引，导致SQL变为全表扫面，虽然仅保留了一个月的数据，不过对其full scan带来的性能问题还是非常严重的，应该使用DDL方式创建，考虑所有的约束和索引情况。

2.在定制方案时一定要考虑回退的方案，不能轻易执行drop\delete\rm这类操作。

3.如果是在7*24小时业务，rename尽量放在业务低峰执行。