首页 / Mysql / MySQL 快速删除大量数据(千万级别)的几种实践方案详解

MySQL 快速删除大量数据(千万级别)的几种实践方案详解

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

MySQL 快速删除大量数据(千万级别)的几种实践方案详解

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

TRUNCATE TABLE就好。

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

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

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

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

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

(2)增加key_buffer_size

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

  • DELETE QUICK + OPTIMIZETABLE

适用场景:MyISAM Tables

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

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

具体代码如下:

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

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

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

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

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

执行结果如下(partitions列即为所在分区):

+—-+————-+——————+————+——+—————-+——+———+——+———+———-+————-+
| id | select_type | table            | partitions | type | possible_keys  | key  | key_len | ref  | rows    | filtered | Extra       |
+—-+————-+——————+————+——+—————-+——+———+——+———+———-+————-+
|  1 | SIMPLE      | table_name       | p1         | ALL  | cnt_date_index | NULL | NULL    | NULL | 1325238 |   100.00 | Using where |
+—-+————-+——————+————+——+—————-+——+———+——+———+———-+————-+
1 row in set, 2 warnings (0.00 sec)

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

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

  • 其它

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

可通过: ALTER TABLE table_name REMOVE PARTITIONING 删除分区,而不会删除相应的数据

参考:

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年内把代码写好,技术博客字字推敲,坚持零拷贝和原创写博客的意义在于打磨文笔,训练逻辑条理性,加深对知识的系统性理解;如果恰好又对别人有点帮助,那真是一件令人开心的事

到此这篇关于MySQL 快速删除大量数据(千万级别)的几种实践方案详解的文章就介绍到这了,更多相关MySQL 快速删除大量数据内容请搜索我是攻城狮以前的文章或继续浏览下面的相关文章希望大家以后多多支持我是攻城狮!

本文来自网络,不代表我是攻城狮立场,转载请注明出处:https://www.54gcs.cn/sql/mysql/5929.html

helwo作者

上一篇
下一篇

为您推荐

发表评论

联系我们

联系我们

在线咨询: QQ交谈

邮箱: web@5vo.cn

工作时间:周一至周五,9:00-17:30,节假日休息
关注微信
微信扫一扫关注我们

微信扫一扫关注我们

手机访问
手机扫一扫打开网站

手机扫一扫打开网站

返回顶部
跳至工具栏