数据库存储了大数据量冗余数据如何进行删除-CSDN博客

本文链接：https://blog.csdn.net/yesewubian000/article/details/141861188

我们平台目前一天60万的订单。存储了很多数据，部分冗余数据需要进行删除处理。如何处理呢？

数据库层面的处理！

查看数据库是否支持自动删除，mongodb的expireAfterSeconds，hbase的TTL.

设置方式：

数据创建时设置控制ttl字段的值，数据归档时删除控制过期的字段。

示例：计划删除3个月前创建的无效订单，我们设置过期时间为0秒。创建订单时设置字段（delTime）=当前时间+3个月。

订单流转到完结状态，这个订单对我们来说是需要保留的数据，此时清空delTime字段（字段删除，ttl索引不会删除本条数据）。

3个月后，未完结的订单，会被数据库的任务检测自动删除，验证过mongo和hbase的数据删除，对线上无影响。数据库支持过期自动删除,肯定对这方面作过优化，也会比一般程序员自己手写的效率或者稳定性好的多。

定时轮询数据库批量删除怎么样？

示例：今天3月1号检测3个月前的1月1号的所有订单状态，如果未完结进行删除。

说明：一般像这种要清理的数据，数据量大，定时检索数据库，批量删除数据库容易导致数据库服务波动，存在影响线上服务稳定的隐患。（数据量少的随意）

任务调度指定回调时间，通过回调接口删除怎么样？

示例：创建订单，设置任务调度，指定3个月后回调到指定接口。接受回调，状态为未完结则删除。

说明：一般像这种要清理的数据，1.数据量大，2.间隔时间长。压力全怼给了任务调度中心，额外进行数据存储，单独的任务线程创建。压力比较大。（如果时间间隔比较少的随意）

能否变更为我们要删除的状态时，设置删除字段的值？

大部分要删除的数据都是中间状态，并且维持一段时间没有继续流转的数据。比如1个月前创建但是未支付的订单，在创建为待支付的时候，这个数据还是“好”数据，只是一直没流转到支付完成，系统才认为他是“坏”数据。需要清理。如果你真的知道了这个订单需要删除了，直接删除就好了，还设置啥过期时间呢？

已有数据如何处理？

以上只是理论情况，实际呢？没有ttl, 没有expireTime。当时没有考虑过，现在线上数据已经生成了。
直接执行删除

delete from xxx where xxx=xxx;

少数据量可以使用，大数据量数据库直接就崩了。——但凡有点基础的人就知道这是在瞎扯。

首先思考数一般的过期策略是怎么实现的？

1. 一条一个任务事实检测所有，到期立刻删除？（实时删除，cpu可能要崩了）

2. 查询触发的时候查看数据有没有过期？过期了就物理删除？（惰性删除，内存可能要崩了）

3. 检索部分数据，比对是否过期？过期就删除，可能期间会用到对应的时间环之类的优化，让数据检索消耗资源最小。控制检索频率，检索范围，寻找服务器cpu和内存的平衡。

检索字段有索引的情况下：

要检索的数据有索引的情况下比较简单，直接根据查询条件进行检索删除。

delete from xxx where status=xxx limit 10000;

检索字段无索引，主键自增情况下：

有时候发现需要检索的数据有索引是一个很奢侈的事，或者虽然有索引但是索引的区分度不大。如果你的表主键是自增的处理方式如下：

delete from xxx where id BETWEEN ${startId} and ${endId} and status = xxx;
# ps：此时需要通过外部程序去替换id的开始值和结束值。应避免已经被检索过的数据变更为垃圾数据，控制好检索范围。防止需要重复检索。

检索字段无索引，主键不自增情况下：

倒霉的娃啊！需要检索的字段没有索引，主键不自增不能根据主键进行检索。寻找一个带有索引的字段，并且该字段能和删除的逻辑进行关联。（防止重复检索）

delete from xxx where createTime between ${createTimeStart} and ${createTimeEnd} and status = xxx ;
# ps: 创建时间有索引，根据创建时间的时间段进行应删除数据的过滤。（存在回表查询问题）

无索引无主键情况处理

全表无可用索引，无主键或主键不自增。只能用最低效的全表扫描的方式处理了。

举一个例子，如果一个表person有如下字段id(long ,auto-increase,primary key),name(varchar),age(int)，表中有1000w数据；

如下的语句肯定不行：select * from person where id > 10 limit 100000,100;

此处，mysql首先会扫描100010条数据，然后取出最后的10条数据返回，故性能很差；

但是可以变通为 select * from person where id in (select id from person where id >10 limit 100000,10 ),此处子查询中使用了id的索引，可以大大降低查询时间；

delete from xxx where id in (select id from xxx where id >10 limit 100000,10 ) and status = xxx

具体如何书写脚本

select limit + delete PK delete limit

select +delete 书写起来相对比较复杂，性能比较差，但是感觉可靠性更高一些。毕竟查询到了再删除，删除啥数据实实在在看得到，不完全依赖sql语句，代码里面还能再次检测是否需要删除，双重保障。

具体脚本如下：

def find_and_remove():
    pass
    # select id from t_xxx where status = xxx limit 1000
    # delete from t_xxx where id in (xxx,xxx)
# 设置自旋一直进行检测删除，当然也可以自己添加终止条件
while True:
    # 异常捕获，防止异常终止程序执行。
    try:
        find_and_remove()
    except Exception as e:
        print("数据处理异常,睡眠60s后继续执行，防止异常发生在读库之前，频繁读库", e)
        time.sleep(60)

上面方式数据库操作太频繁了影响我线上其他业务的正常执行。

优化：（加睡眠）

import time, datetime
def find_and_remove():
    pass
    # select id from t_xxx where status = xxx limit 1000
    # delete from t_xxx where id in (xxx,xxx)
# 设置自旋一直进行检测删除，当然也可以自己添加终止条件
while True:
    # 异常捕获，防止异常终止程序执行。
    try:
        # 先睡眠，后执行。 睡眠一定要写在前面，防止查询异常，导致一直循环查询。
        time.sleep(1)  
        find_and_remove()
    except Exception as e:
        print("数据处理异常,睡眠60s后继续执行，防止异常发生在读库之前，频繁读库", e)
        time.sleep(60)

上面方式执行时间段和我的业务时间段重合了，我怕影响业务操作。

优化：（加时间过滤）

import time, datetime
def find_and_remove():
    pass
    # select id from t_xxx where status = xxx limit 1000
    # delete from t_xxx where id in (xxx,xxx)
def allowTimes():
    now = datetime.datetime.now()
    # 仅周一至周五执行
    if now.weekday() >= 5:
        return False
    int_time = now.hour * 60 + now.minute
    # 9:00-12:00 13:00-16:00 为允许执行的时间段.
    return 9 * 60 < int_time < 12 * 60 or 13 * 60 < int_time < 16 * 60


# 设置自旋一直进行检测删除，当然也可以自己添加终止条件
while True:
    # 异常捕获，防止异常终止程序执行。
    try:
        # 先睡眠，后执行。 睡眠一定要写在前面，防止查询异常，导致一直循环查询。
        time.sleep(1)
        # 检测是否处于允许执行时间段范围内。
        if allowTimes():
            find_and_remove()
    except Exception as e:
        print("数据处理异常,睡眠60s后继续执行，防止异常发生在读库之前，频繁读库", e)
        time.sleep(60)