如何安全高效删除大量数据

【 TiDB 使用环境】生产环境
【 TiDB 版本】v5.0.4
【复现路径】做过哪些操作出现的问题
【遇到的问题:问题现象及影响】
如何删除大量的数据,对线上业务又不造成影响
DELETE
from stat.表1 WHERE
uid NOT IN (
子查询(8000多条)
)
共计需要删除7000多万
因为历史原因呀,现在这部分数据用不到了,删了之后表小了很多,查询速度就快了
【资源配置】
【附件:截图/日志/监控】

  1. 正确的使用索引

  2. 分批对数据进行删除,减少大事务

  3. 尽量使用分区,通过分区快速的定位,来对数据进行处理(truncate 命令)

请参考

问题就是删除是not in 还用不了索引,你说气人不

@大飞飞jeffery 为什么要删除大量数据,背后的原因和使用场景是什么样?想解决什么问题,预期是什么样的,增加说明下。

我感觉你的需求是select into
rename table new to old

不行,影响线上业务

我感觉你的需求是select into new table

rename table old to oldbackup

rename table new to old
这个是毫秒级的
除非你不停有写入

根据你的描述,删除之后表小很多,那说明需要的数据很少,可以按楼上说的,先复制一张表,然后将需要的数据插入到copy表里,此时应该是in操作了吧,然后再将原表重命名成备份表,copy的表重命名成原表。

如果执行不当,删除大量数据会对在线业务产生影响。 您可以按照以下步骤将删除数据的影响降到最低:

import threading
import queue
import time
import pymysql
BATCH_SIZE = 10000
NUM_THREADS = 10
DB_HOST = 'localhost'
DB_PORT = 4000
DB_USER = 'user'
DB_PASSWORD = 'password'
DB_NAME = 'database'
sub_query = """
SELECT uid FROM stat.Table2
"""
delete_query = """
DELETE FROM stat.Table1 WHERE uid NOT IN ({sub_query})
"""
delete_queue = queue.Queue()

def fetch_data_to_delete():
    """
    Fetch the data to delete from the database and add it to the queue
    """
    conn = pymysql.connect(host=DB_HOST, port=DB_PORT, user=DB_USER, password=DB_PASSWORD, db=DB_NAME)
    with conn.cursor() as cursor:
        cursor.execute(sub_query)
        rows = cursor.fetchall()
    batch = []
    for row in rows:
        batch.append(row[0])
        if len(batch) == BATCH_SIZE:
            delete_queue.put(batch)
            batch = []
    if batch:
        delete_queue.put(batch)
    conn.close()

def delete_data():
    """
    Delete the data from the database
    """
    conn = pymysql.connect(host=DB_HOST, port=DB_PORT, user=DB_USER, password=DB_PASSWORD, db=DB_NAME)
    while True:
        data = delete_queue.get()
        if data is None:
            break
        in_values = ','.join(str(uid) for uid in data)
        sub_query_formatted = sub_query.format(in_values=in_values)
        delete_query_formatted = delete_query.format(sub_query=sub_query_formatted)
        with conn.cursor() as cursor:
            cursor.execute(delete_query_formatted)
        conn.commit()
    conn.close()

def main():
    fetch_data_to_delete()
    threads = []
    for i in range(NUM_THREADS):
        t = threading.Thread(target=delete_data)
        threads.append(t)
    for t in threads:
        t.start()
    for t in threads:
        t.join()
    delete_queue.put(None)

if __name__ == '__main__':
    main()

2 个赞

这种方式+1,另外TiDB应该在5.X某个版本支持rename table t1 to t1_backup, t1_new to t1这种语法了,但是具体版本我给忘了,可以试下5.0是不是能执行。

剩余数据量比较小,就在凌晨执行一下备份,将数据库小的记录insert ineo Select 转到新表,然后旧表rename,再把新表rename为线上业务使用的表

如果是删除十分之一数据,我都建议走这个流程:
半夜停业务→rename 原表 to 原表_old;→create 原表;→insert into 原表 select * from 原表_old where 有用的数据;
何况你这都需要删除百分之九十九的数据了。。。

省时省力

应该可以的,我这边 4.0 经常这样搞

他说的应该是一个rename操作多个表的语法吧

对于楼主说的问题。我在请教个问题: 对于其他关系型数据库 进行降低水位操作时新建表,然后备份原始表,将原始表的数据回查到新建表

对于oracle可以进行在线扩容。

对于tidb大量删除数据后,会有很多空 region 。 进行region合并 会降低高水位的问题不 ??

或者有在线缩容的功能 ,后期会开发不?

在线缩容?应该不是指节点缩容吧,建议开新帖咨询,能够得到更多伙伴的响应

percona-toolkit 的 pt-archiver 应该就是解决你这类问题的

您感觉 oracle 能在线缩容 节点的能力不。 oracle 使用 在下表行move的能力 实现 表级别的 高水位缩容。