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

来源:脚本之家  责任编辑:小易  

利用shell的do....done循环写一个脚本,在系2113统环境下进5261行删除。利用excle的编辑便利性写好4102sql脚本再导入文本中,进入数据库后使1653用source /tmp/xxx.txt。(愚见:数据库一般没有批量删除的功能,这太危险了。)www.zgxue.com防采集请勿采集本网。

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

先进入一个表的查询结果界面,按住shift选中多条数据,按最下面的“-”按钮就可以批量删除了

TRUNCATE TABLE就好。

您好,提问者: 清空全部数据,不写日志,不可恢复,速度极快 truncate table 表名;清空全部数据,写日志,数据可恢复,速度慢 delete from 表名

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

换台NB服务器

delete from table_name where cnt_date <= target_date

-清空全部数据,不写日志,不可恢复,速度极快 truncate table 表名;清空全部数据,写日志,数据可恢复,速度慢 delete from 表名

后经过研究,最终实现了飞一般(1秒左右)的速度删除770多万条数据,单张表总数据量在4600万上下,优化过程的方案层层递进,详细记录如下: 批量删除(每次限定一定数量),然后循环删除直到全部数据删除完毕;同时key_buffer_size 由默认的8M提高到512M

delete from table_name where 1=1 order by order_name limit row_num table_name是你希望删除数据的所在表表名,order_name 是你希望按照哪个字段来进行排序的字段名,如果是用系统自动默认的

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

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

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 + OPTIMIZETABLE

适用场景:MyISAM Tables

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

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

具体代码如下:

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

执行结果如下(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语句如下:

"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

可通过: 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 快速删除大量数据内容请搜索真格学网以前的文章或继续浏览下面的相关文章希望大家以后多多支持真格学网!

一、使用phpmyadmin工具批量删除2113mysql数据库表5261使用phpmyadmin数据库管理工具进行删除,这4102是一个传统的方法,在任何1653php虚拟主机中,你都可以操作。下面是操作过程介绍:1、登录phpmyadmin。选择你的mysql数据库名进入——点击结构——选择您要删除的数据表——“选中项”中选择删除,执行删除表操作。如下图:PHPMYADMIN使用技巧:如何快速批量删除Mysql数据库中的数据表 image00114 150x150二、使用php脚本完成批量删除无忧主机(www.51php.com)小编从网上找了一段php代码,也可以帮我们快速实现删除。如果你对phpmyadmin工具“敏感”,可以使用下面的方式来实现快速删除mysql数据库表。 在下面的代码复制到记事本后,配置过程中你要注意几个问题:1、 正确配置你mysql数据库信息。这个你可以直接咨询你的空间商就可以获得啦。2、 正确设置要删除的数据库表前缀,这个非常重要,填写错误,将操作错误。请你务必确认你的数据库表前缀哦,数据库表前缀你可以在phpmyadmin中查询到。一般都是xx_开头的。 设置好后,将这段代码另存为.php文件(如51php.php)然后上传到您空间的网站根目录(public_html)。上传完毕,直接用域名/51php.php,就可以删除了。 代码如下:<?php mysql_connect('localhost','数据库用户名','数据库密码'); mysql_select_db('数据库名称'); $rs=mysql_query('show tables'); while($arr=mysql_fetch_array($rs)) { $TF=strpos($arr[0],'51php_'); if($TF===0){ $FT=mysql_query("drop table $arr[0]"); if($FT){ echo "$arr[0] 删除成功!<br>"; }}}?>以上两种方法都可批量删除数据表,特别提醒一下,数据是无价的,操作数据表的时候一定要先做好备份。免得误删除数据是无法恢复的。如果您不会备份,麻烦您参考:360度全方位讲解无忧网站数据库备份Mysql数据库管理利器:phpmyadmin详细使用教程,删除所有表2113,可以删除数据库删除一个表的5261所有数4102据,可以使用delete * from 'table'MySQL是一个关系1653型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 旗下公司。MySQL 最流行的关系型数据库管理系统,在 WEB 应用方面 MySQL 是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。MySQL 是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。MySQL 所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策(本词条“授权政策”),它分为社区版和商业版,由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站的开发都选择 MySQL 作为网站数据库。由于其社区版的性能卓越,搭配 PHP 和 Apache 可组成良好的开发环境内容来自www.zgxue.com请勿采集。


  • 本文相关:
  • mysql数据误删除的快速解决方法(mysql闪回工具)
  • mysql实现快速删除所有表而不删除数据库的方法
  • 服务器数据库编码格式问题解决方案
  • mysql联合索引用法示例
  • mysql5.6启动内存占用过高解决方案
  • linux下安装升级mysql到新版本(5.1-5.7)
  • mysql 中如何取得汉字字段的各汉字首字母
  • mysql常用运算符与函数汇总
  • mysql5.7.17压缩包安装配置方法图文教程
  • mysql的query cache原理分析
  • mysql dba教程:mysql性能优化之缓存参数优化
  • phpmyadmin报错:#2003 无法登录 mysql服务器的解决方法
  • 如何快速批量删除Mysql数据库中的数据表
  • 如何快速批量删除mysql数据库中的数据表
  • mysql 一次性删除一千万条以上的数据时,可能会出现什么问题?
  • mysql 在删除大量表数据时怎么释放空间
  • Navicat for MySQL这个软件,如何批量删除数据,求大神指导.
  • 怎样删除mysql一个表中的全部数据
  • SQL如何快速删除大量指定数据
  • 怎么快速删除mysql中的表里的数据
  • mysql怎样删除前几行数据
  • MySQL 一秒钟可以删除多少条数据?delete 数据的时候会用到索引吗?
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页mysql数据误删除的快速解决方法(mysql闪回工具)mysql实现快速删除所有表而不删除数据库的方法服务器数据库编码格式问题解决方案mysql联合索引用法示例mysql5.6启动内存占用过高解决方案linux下安装升级mysql到新版本(5.1-5.7)mysql 中如何取得汉字字段的各汉字首字母mysql常用运算符与函数汇总mysql5.7.17压缩包安装配置方法图文教程mysql的query cache原理分析mysql dba教程:mysql性能优化之缓存参数优化phpmyadmin报错:#2003 无法登录 mysql服务器的解决方法mysql安装图解 mysql图文安装教程can""""t connect to mysql servwindows下mysql5.6版本安装及配置mysql字符串截取函数substring的mysql创建用户与授权方法mysql提示:the server quit withmysql日期数据类型、时间类型使用mysql——修改root密码的4种方法mysql update语句的用法详解mysql 的case when 语句使用说明mysql dns的使用过程详细分析借助php的mysql_query()函数来创建mysql数linux下安装与使用mysql详细介绍mysql 5.7安装好后打开命令行窗口闪退的解win中安装mysql的详细步骤mysql 5.7.11 winx64.zip安装配置方法图文mysql数据库优化推荐的编译安装参数小结mysql 5.7 zip archive版本安装教程新手把mysql装进docker中碰到的各种问题mysql建立外键失败几种情况记录can""t cr
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved