首页 > 数据库 >mysql如何批量删除海量数据

mysql如何批量删除海量数据

时间:2024-11-07 11:48:26浏览次数:6  
标签:批量 删除 海量 表名 索引 mysql table 数据 delete

一、删除大表的部分数据

一个表有1亿6000万的数据,有一个自增ID。最大值就是1亿6000万,需要删除大于250万以后的数据,有什么办法可以快速删除?

看到mysql文档有一种解决方案:http://dev.mysql.com/doc/refman/5.0/en/delete.html

If you are deleting many rows from a large table, you may exceed the lock table size for an InnoDB table. To avoid this problem, or simply to minimize the time that the table remains locked, the following strategy (which does not use DELETE at all) might be helpful:

Select the rows not to be deleted into an empty table that has the same structure as the original table:
INSERT INTO t_copy SELECT * FROM t WHERE ... ;

Use RENAME TABLE to atomically move the original table out of the way and rename the copy to the original name:
RENAME TABLE t TO t_old, t_copy TO t;

Drop the original table:
DROP TABLE t_old;

删除大表的多行数据时,会超出innod block table size的限制,最小化的减少锁表的时间的方案是:
1、选择不需要删除的数据,并把它们存在一张相同结构的空表里
2、重命名原始表,并给新表命名为原始表的原始表名
3、删掉原始表

二、删除带索引的表

在My SQL数据库使用中,有的表存储数据量比较大,达到每天三百万条记录左右,此表中建立了三个索引,这些索引都是必须的,其他程序要使用。由于要求此表中的数据只保留当天的数据,所以每当在凌晨的某一时刻当其他程序处理完其中的数据后要删除该表中昨天以及以前的数据,使用delete删除表中的上百万条记录时,MySQL删除速度非常缓慢,每一万条记录需要大概4分钟左右,这样删除所有无用数据要达到八个小时以上,这是难以接受的。
 

查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的,于是删除掉其中的两个索引后测试,发现此时删除速度相当快,一百万条记录在一分钟多一些,可是这两个索引其他模块在每天一次的数据整理中还要使用,于是想到了一个折中的办法:

在删除数据之前删除这两个索引,此时需要三分钟多一些,然后删除其中无用数据,此过程需要不到两分钟,删除完成后重新创建索引,因为此时数据库中的数据相对较少,约三四十万条记录(此表中的数据每小时会增加约十万条),创建索引也非常快,约十分钟左右。这样整个删除过程只需要约15分钟。对比之前的八个小时,大大节省了时间。

三、表的删除

1) 批量删除少量数据

delete from 表名 where key in(值1,...,值n);

1、先确定需要删除数据的主键或者条件;

2、利用主键删除记录。

2) 分批删除小批量数据

如果你要删除一个表里面的前 10000 行数据,有以下三种方法可以做到:

1、delete from 表名 where 条件 limit 10000;

2、在一个连接中循环执行 20 次 delete from 表名 limit 500;

3、在 20 个连接中同时执行 delete from 表名 limit 500。

三种方法的比较

1、一次占用锁的时间比较长,可能导致其他客户端一致等待资源。

2、分多次占用锁,串行化执行,不占有锁的间隙,其他客户端可以工作。

3、自己制造锁竞争,加剧并发。可能锁住同一记录导致死锁的可能性增大。

注意:

执行大批量删除的时候最好使用limit,否则很有可能造成死锁。

如果delete的where语句不在索引上,可以先找主键,然后根据主键删除数据。

如果需要删除的数据远远大于不用删除的数据

1、先选择不需要删除的数据,并把它们存在一张相同结构的空表里;

2、再重命名原始表,并给新表命名为原始表的原始表名;

3、然后删掉原始表。

标签:批量,删除,海量,表名,索引,mysql,table,数据,delete
From: https://blog.csdn.net/2301_79362607/article/details/143567911

相关文章

  • win11中使用docker-nacos连接容器中的mysql实例记录
     二.方式11.拉取nacosdockerpullnacos/nacos-server2.在dockerdesktop中进行配置如下图相比较’方式2‘这种方式更简单,mysqlip地址需要使用ipv4地址,具体的自己查看ipconfig的ipv4地址(注意:localhsot/127.0.0.1/容器名称都是不行的)下面这几个参数在application.proper......
  • mysql 游标 示例
    提示:mysql游标必须要在在存储过程中才能使用,不能单独使用数据表CREATETABLE`student_score`(`id`intunsignedNOTNULLAUTO_INCREMENT,`student_id`varchar(128)DEFAULTNULL,`student_course_name`varchar(128)DEFAULTNULL,`score`intDEFAULTNULL,......
  • Java面试系列-MySQL面试题20道,InnoDB,索引类型,事务隔离级别,锁机制,MVCC,主从复制,慢查询,分
    文章目录1.MySQL中的InnoDB和MyISAM存储引擎有什么区别?2.MySQL中的索引类型有哪些?3.MySQL中的索引是如何工作的?4.MySQL中的事务隔离级别有哪些?5.MySQL中的锁机制有哪些?6.MySQL中的MVCC(多版本并发控制)是如何工作的?7.MySQL中的主从复制是如何工作的?8.MySQL中的分区......
  • mysql学习必备四大语法 增 删 改 查
    SQL(结构化查询语言)是用于管理关系型数据库的标准语言。增、删、改、查是对数据库表中数据进行基本操作的四种主要功能,通常称为CRUD(Create,Read,Update,Delete)。以下是这四个操作的详细讲解和示例1.增(Create)功能:简单来说插入数据库表中新记录SQL语法:表名:你想要插入数......
  • 抖音蓝V批量采集器精选联盟商家批量采集软件使用方案
    抖音蓝V批量采集器精选联盟商家批量采集软件使用方案importrequests假设这是抖音开放平台提供的API接口URL(注意:这只是一个示例URL,并非真实的抖音API)api_url='https://api.douyin.com/some/endpoint'你的API密钥或其他认证信息(注意:这只是一个示例,并非真实的抖音API密钥)api_......
  • 淘宝商家电话采集工具 批量导出淘宝天猫商家联系方式软件 Python使用教程
    淘宝商家电话采集工具批量导出淘宝天猫商家联系方式软件Python使用教程作者V553813195以Python为例,下面是一个简单的教程,来介绍如何使用python语音进行淘宝天猫商家爬虫。首先,我们需要安装以下库:requests,beautifulsoup4。可以使用以下命令进行安装:pipinstallrequestspip......
  • 服务器上mysqld,java的进程Out of Memory,被kernel kill 掉了
    /var/log/messages里面日志如下Aug1019:47:16VM-0-7-centoskernel:8936totalpagecachepagesAug1019:47:16VM-0-7-centoskernel:0pagesinswapcacheAug1019:47:16VM-0-7-centoskernel:Swapcachestats:add0,delete0,find0/0Aug1019:47:16VM-0......
  • 抖音新店批量提取工具新开店铺采集软件深度研究
    importrequestsimportjson假设的API端点(这不是抖音的API,只是一个示例)API_ENDPOINT="https://api.example.com/shops/new"假设的API密钥(在实际应用中,你应该从安全的地方获取这个密钥)API_KEY="your_api_key_here"请求头,包含API密钥headers={"Authorization":f"Bear......
  • 抖音商家电话采集工具 批量导出抖音小店联系方式软件
    抖音商家电话采集工具批量导出抖音小店联系方式软件作者V553813195抖音电商平台已成为当前电商行业中的热点,众多商家通过抖音平台推广,吸引大量用户注意。在这样的背景下,提取商家电话成为了一项重要的任务。本教程将介绍一个抖音精选联盟商家电话提取工具,通过这个工具可以快速、......
  • 系统变量group_replication_group_seeds为空导致MySQL节点无法启动组复制
    MySQLInnoDBCluster集群中一个节点,在服务器重启过后,启动MySQL实例后,发现status为MISSING,另外memberState为OFFLINE状态。如下所示: MySQL  mysqldbu02:7306 ssl  JS > cluster.status(){    "clusterName": "yssps",     "defaultReplicaSet": {      ......