记一次线上数据库删除百万级数据

背景

不知道有没有读者经历过删除线上数据库记录，反正这事对我来说是挺有压力的。激动的心，颤抖的手，万一删错了数据就麻烦了，影响数量大，领导会批评，还有复盘自己批评，想想就头大。那么就要在删除数据之前做好计划，避免删数据时发生意料之外的事情。记录一次删除线上数据。

业务以半年为一个周期，每一个周期体验参与用户在40w左右。新一期开始后过期体验用户的数据较大，会让数据库增删改查速度变慢，但这一部分数据对业务已经没有帮助了，所以要清理过期体验用户的数据。

一共要清理5张表：
A：数量 36w
B：数量 36w
C：数量 140w
D：数量 36w
E：数量 72w

要求

对于大量数据的查询和删除不能使用一条语句完成，会造成数据库阻塞，特别是删除数据时会锁表或锁行。要减少对线上正常业务的影响，在删除数据的同时不能影响数据库正常增删改查，而且不能让数据库查询等待过长而导致API返回超时。所以查询和删除都需要分批完成。

删除数据底层流程

简单的说明一下在删除数据时MySQL数据底层流程。
MySQL数据库分为两个部分：server层和存储引擎，如下图：

在删除一条记录时，流程分别是：

1. 客户端连接到server层
2. 分析器对sql语句进行词法分析和语法分析，知道是一条删除语句
3. 优化器选择合适的索引去查找待删除的记录
4. 执行器调用存储引擎删除所有记录
5. 存储引擎将维护的数据内存里的记录标记为已删除
6. 存储引擎根据刷盘规则将内存的记录写到磁盘中

以上就是删除一条语句的过程，因为每一个sql语句是一个事务，每一个事务都涉及到加锁，所以在以上的删除流程中，锁的使用是这样的：

1. 如果查询待删除记录走的是索引，那么会将所有删除记录行加锁
2. 如果查询待删除记录没有走索引，那么将会将整张表都锁起来，直到删除完成

删除实施

针对最多140w的数据，肯定不能一次性删除，否则锁表的时间会让其他业务无法进行，同时要在删除时使用行锁而不是表锁。所以删除分为两步走：

1. 使用分块查询，将所有记录的主键查询出来
2. 根据主键分批删除，同时每一次删除间隔一定时间，异常捕获不影响删除流程

经过5张表的删除，得出以下脚本删除时对数据库影响较小

# 分批查询，只查询id
def chunk_model(model, conditions, start_id=0, chunk_size=500):
    while True:
        models = list(
            model.select(model.id)
            .where(model.id > start_id, *conditions)
            .order_by(model.id.asc())
            .limit(chunk_size)
        )
        if not models:
            break

        yield models
        start_id = models[-1].id


import time
from app.camp.models import UserPlan

plan_id_list = [33,34]

delete_id_list = []
for chunk_data in chunk_model(UserPlan, [UserPlan.plan_id.in_(plan_id_list)]):
    print(chunk_data[0].id)
    for data in chunk_data:
        delete_id_list.append(data.id)

# 分批删除
for id_index in range(0, len(delete_id_list), 1000):
    print(id_index)
    try:
        delete_ids = delete_id_list[id_index:id_index+1000]
        UserPlan.delete().where(UserPlan.id.in_(delete_ids)).execute()
        time.sleep(0.5)
    except Exception as e:
        print(id_index, e)

最多的140w数据耗时在10分钟左右，全程数据库无波动，业务没有影响。