mysql删除大批量数据_删除相同的数据

mysql删除大批量数据_删除相同的数据MySQL DELETE 批量删除 大数据 快速实现方案

MySQL 快速删除大量数据(千万级别)的几种实践方案——附源码

mysql删除大批量数据_删除相同的数据 

 笔者最近工作中遇见一个性能瓶颈问题,MySQL表,每天大概新增776万条记录,存储周期为7天,超过7天的数据需要在新增记录前老化。连续运行9天以后,删除一天的数据大概需要3个半小时(环境:128G, 32核,4T硬盘),而这是不能接受的。当然如果要整个表删除,毋庸置疑用

TRUNCATE TABLE就好。

最初的方案(因为未预料到删除会如此慢),代码如下(最简单和朴素的方法):

delete from table_name where cnt_date <= target_date

代码100分

 

 后经过研究,最终实现了飞一般(1秒左右)的速度删除770多万条数据,单张表总数据量在4600万上下,优化过程的方案层层递进,详细记录如下:

  • 批量删除(每次限定一定数量),然后循环删除直到全部数据删除完毕;同时key_buffer_size 由默认的8M提高到512M

 运行效果:删除时间大概从3个半小时提高到了3小时

(1)通过limit(具体size 请酌情设置)限制一次删除的数据量,然后判断数据是否删除完,附源码如下(Python实现):

代码100分def delete_expired_data(mysqlconn, day):
    mysqlcur = mysqlconn.cursor()
    delete_sql = "DELETE from table_name where cnt_date<="%s" limit 50000" % day
    query_sql = "select srcip from table_name  where cnt_date <= "%s" limit 1" % day
    try: 
        df = pd.read_sql(query_sql, mysqlconn)
        while True:
            if df is None or df.empty:
                break
            mysqlcur.execute(delete_sql)
            mysqlconn.commit()

            df = pd.read_sql(query_sql, mysqlconn)
    except:
       mysqlconn.rollback()

 

(2)增加key_buffer_size

mysqlcur.execute("SET GLOBAL key_buffer_size = 536870912")

key_buffer_size是global变量,详情参见Mysql官方文档: https://dev.mysql.com/doc/refman/5.7/en/server-configuration.html

  • DELETE QUICK + OPTIMIZE TABLE

 适用场景:MyISAM Tables

 Why: MyISAM删除的数据维护在一个链表中,这些空间和行的位置接下来会被Insert的数据复用。 直接的delete后,mysql会合并索引块,涉及大量内存的拷贝移动;而OPTIMIZE TABLE直接重建索引,即直接把数据块情况,再重新搞一份(联想JVM垃圾回收算法)。

运行效果:删除时间大3个半小时提高到了1小时40分

具体代码如下:

代码100分def delete_expired_data(mysqlconn, day):
    mysqlcur = mysqlconn.cursor()
    delete_sql = "DELETE QUICK from table_name where cnt_date<="%s" limit 50000" % day
    query_sql = "select srcip from table_name where cnt_date <= "%s" limit 1" % day
    optimize_sql = "OPTIMIZE TABLE g_visit_relation_asset"
    try: 
        df = pd.read_sql(query_sql, mysqlconn)
        while True:
            if df is None or df.empty:
                break
            mysqlcur.execute(delete_sql)
            mysqlconn.commit()

            df = pd.read_sql(query_sql, mysqlconn)
        mysqlcur.execute(optimize_sql)
        mysqlconn.commit()
    except:
       mysqlconn.rollback()

  • 表分区,删除直接删除过期日期所在的分区(最终方案—秒杀)

 MySQL表分区有几种方式,包括RANGE、KEY、LIST、HASH,具体参见官方文档。因为这里的应用场景日期在变化,所以不适合用RANGE设置固定的分区名称,HASH分区更适应此处场景

(1)分区表定义,SQL语句如下:

ALTER TABLE table_name PARTITION BY HASH(TO_DAYS(cnt_date)) PARTITIONS 7;

TO_DAYS将日期(必须为日期类型,否则会报错:Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed)转换为天数(按一年的天数计算),然后HASH;分区建立7个。实际上,就是 days MOD 7 。


(2)查询出需要老化的日期所在的分区,SQL语句如下:

"explain partitions select * from g_visit_relation_asset where cnt_date = "%s"" % expired_day

(3)OPTIMIZE or REBUILD partition,SQL语句如下:

"ALTER TABLE g_visit_relation_asset OPTIMIZE PARTITION "%s"" % partition

完整代码如下【Python实现】,循环删除小于指定日期的数据:

def clear_partition_data(mysqlconn, day):
    mysqlcur = mysqlconn.cursor()
    expired_day = day
    query_partition_sql = "explain partitions select * from table_name where cnt_date = "%s"" % expired_day
    # OPTIMIZE or REBUILD after truncate partition
    try: 
        while True:
            df = pd.read_sql(query_partition_sql, mysqlconn)
            if df is None or df.empty:
                break
            partition = df.loc[0, "partitions"]
            if partition is not None:
                clear_partition_sql = "alter table table_name TRUNCATE PARTITION %s" % partition
                mysqlcur.execute(clear_partition_sql)
                mysqlconn.commit()

                optimize_partition_sql = "ALTER TABLE table_name OPTIMIZE PARTITION %s" % partition
                mysqlcur.execute(optimize_partition_sql)
                mysqlconn.commit()
            
            expired_day = (expired_day - timedelta(days = 1)).strftime("%Y-%m-%d")
            df = pd.read_sql(query_partition_sql, mysqlconn)
    except:
       mysqlconn.rollback()

 

  •  其它

 如果删除的数据超过表数据的百分之50,建议拷贝所需数据到临时表,然后删除原表,再重命名临时表为原表,附MySQL如下:

   INSERT INTO New
      SELECT * FROM Main
         WHERE ...;  -- just the rows you want to keep
   RENAME TABLE main TO Old, New TO Main;
   DROP TABLE Old;   -- Space freed up here

参考:

1)https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html 具体分区说明

2)http://mysql.rjweb.org/doc.php/deletebig#solutions   删除大数据的解决方案

  本文版权归作者和思创斯聊编程共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。

************************************************************************

精力有限,想法太多,专注做好一件事就行

  • 我只是一个程序猿。5年内把代码写好,技术博客字字推敲,坚持零拷贝和原创
  • 写博客的意义在于打磨文笔,训练逻辑条理性,加深对知识的系统性理解;如果恰好又对别人有点帮助,那真是一件令人开心的事

************************************************************************

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
转载请注明出处: https://daima100.com/7534.html

(0)
上一篇 2023-03-18
下一篇 2023-03-18

相关推荐

  • MySQL必知必会–第二章~第四章–MySQL简介[通俗易懂]

    MySQL必知必会–第二章~第四章–MySQL简介[通俗易懂]1.DBMS可分为两类:一类为基于共享文件系统的DBMS,另一类为基于客户机—服务器的DBMS。服务器部分是 负责所有数据访问和处理的一个软件。这个软件运行在称为数据库服务 器的计算机上。 2.My…

    2023-02-11
    141
  • mysql 的root 用户无法授权,navicat 远程授权提示1044解决方案

    mysql 的root 用户无法授权,navicat 远程授权提示1044解决方案先看解决方案 # mysql root 用户无法赋权问题解决 1,登录 mysql -u root -p 2,use mysql; 选择mysql数据库 3,执行以下操作(查询用户的赋权权限,更改赋权

    2023-01-29
    155
  • 教学:使用conda安装OpenCV

    教学:使用conda安装OpenCVOpenCV是一个开源计算机视觉和机器学习软件库。它由英特尔公司开发,用C ++、Python和Java编写,可在Linux,Windows和macOS等操作系统上运行。它包括一系列计算机视觉和图像处理函数,并且是进行计算机视觉研究和开发的主要工具之一。

    2024-05-04
    70
  • docker运行mysql「终于解决」

    docker运行mysql「终于解决」
    以mysql:5.7镜像为例 linux根目录下创建/docker/mysql文件夹,然后分别创建conf和data文件夹存放配置和数据 # 启动容器,初始…

    2023-04-06
    167
  • MySQL默认隔离级别为什么是RR

    MySQL默认隔离级别为什么是RR曾多次听到“MySQL为什么选择RR为默认隔离级别”的问题,其实这是个历史遗留问题,当前以及解决,但是MySQL的各个版本沿用了原有习惯。历史版本中的问题是什么,本次就通过简单的测试来说明一下。 1、

    2023-02-14
    142
  • MySQL——视图[通俗易懂]

    MySQL——视图[通俗易懂]视图是一张虚拟的表。与包含数据的表不一样,视图只是包含使用时动态检索数据的查询。 注意:视图只适用于MySQL5及之后的版本 为什么使用视图? 重用SQL语句。 简化复杂的SQL操作。在编写查询后,…

    2023-01-28
    155
  • Oracle数据库简介[通俗易懂]

    Oracle数据库简介[通俗易懂]简介 Oracle Database,又名Oracle RDBMS,或简称Oracle。是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。可以说Oracle数据库系统是目…

    2023-02-06
    143
  • 在Linux上使用PyCharm进行安装

    在Linux上使用PyCharm进行安装PyCharm是一款Python IDE,可用于快速开发Python应用程序。在Linux上安装PyCharm非常简单,本文将为您介绍如何安装PyCharm及其相关依赖项,以及在Linux上使用PyCharm进行Python开发的简单步骤。

    2024-08-01
    27

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注