mysql删除表内所有数据(mysql 大表批量删除大量数据的实现方法)
mysql删除表内所有数据
mysql 大表批量删除大量数据的实现方法问题参考自:https://www.zhihu.com/question/440066129/answer/1685329456 ,mysql中,一张表里有3亿数据,未分表,其中一个字段是企业类型,企业类型是一般企业和个体户,个体户的数据量差不多占50%,根据条件把个体户的行都删掉。请问如何操作?答案为个人原创
假设表的引擎是 Innodb, MySQL 5.7+
删除一条记录,首先锁住这条记录,数据原有的被废弃,记录头发生变化,主要是打上了删除标记。也就是原有的数据 deleted_flag 变成 1,代表数据被删除。但是数据没有被清空,在新一行数据大小小于这一行的时候,可能会占用这一行。这样其实就是存储碎片。
之后,相关数据的索引需要更新,清除这些数据。并且,会产生对应的 binlog 与 redolog 日志。
如果 delete 的数据是大量的数据,则会:
- 如果不加 limit 则会由于需要更新大量数据,从而索引失效变成全扫描导致锁表,同时由于修改大量的索引,产生大量的日志,导致这个更新会有很长时间,锁表锁很长时间,期间这个表无法处理线上业务。
- 由于产生了大量 binlog 导致主从同步压力变大
- 由于标记删除产生了大量的存储碎片。由于 MySQL 是按页加载数据,这些存储碎片不仅大量增加了随机读取的次数,并且让页命中率降低,导致页交换增多。
- 由于产生了大量日志,我们可以看到这张表的占用空间大大增高。
解决方案
我们很容易想到,在 delete 后加上 limit 限制控制其数量,这个数量让他会走索引,从而不会锁整个表。
但是,存储碎片,主从同步,占用空间的问题并没有解决。可以在删除完成后,通过如下语句,重建表:
|
alter table 你的表 engine=InnoDB, ALGORITHM=INPLACE, LOCK=NONE; |
注意这句话其实就是重建你的表,虽然你的表的引擎已经是 innodb 了,加上后面的, ALGORITHM=INPLACE, LOCK=NONE 可以不用锁表就重建表。
还有一种方案是,新建一张同样结构的表,在原有表上加上触发器:
|
create trigger person_trigger_update AFTER UPDATE on 原有表 for each row begin set @x = "trigger UPDATE" ; Replace into 新表 SELECT * from 原有表 where 新表.id = 原有表.id; END IF; end ; |
这样可以保证线上业务有新数据会同步。之后,将所有企业类型的数据,插入新表,同时如果已存在则证明发生了更新同步就不插入。个体户数据由于业务变化,并不在这个表上更新,所以这样通过了无表锁同步实现了大表的数据清理
到此这篇关于mysql 大表批量删除大量数据的实现方法的文章就介绍到这了,更多相关mysql 大表批量删除内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网!
原文链接:https://blog.csdn.net/zhxdick/article/details/112798605
- 为什么mysql主键要设置自增列(浅谈MySQL中的自增主键用完了怎么办)
- mysql数据库主键选择的详解(图文详解MySQL中的主键与事务)
- mysqldump导入导出(MySQL官方导出工具mysqlpump的使用)
- mariadb导入数据库命令(MySQL/MariaDB 如何实现数据透视表的示例代码)
- mysql主键自增策略(MySQL的主键命名策略相关)
- navicat怎么和mysql连接(Navicat Premium远程连接MySQL数据库的方法)
- mysql字符串的表示方法(详解mysql中的字符集和校验规则)
- phpstudymysql启动又自动停止(Win下解决phpStudy MySQL启动失败)
- 查看mysql所有权限(MySQL 权限控制细节分析)
- mysql的视图和临时表区别(MySQL 内存表和临时表的用法详解)
- mysql数据库三种模式(MySQL数据库是如何实现XA规范的)
- 如何在cmd里开启mysql(Windows下通过cmd进入DOS窗口访问MySQL数据库)
- mysql索引原理及使用(再有人问你MySQL索引原理,就把这篇文章甩给他!)
- mysql分区怎么实现(MySql分表、分库、分片和分区知识深入详解)
- mysql索引详解及基本用法(Mysql普通索引与唯一索引的选择详析)
- 阿里云mysql如何接入(阿里云服务器安装Mysql数据库的详细教程)
- 经常发这三种 朋友圈 的人,要迅速屏蔽(经常发这三种朋友圈)
- 有种尴尬叫朋友圈忘屏蔽,大学生上演社死现场,父母亲自下场吐槽(有种尴尬叫朋友圈忘屏蔽)
- 朋友圈屏蔽你的人,可以直接看淡了(朋友圈屏蔽你的人)
- 金球奖只青睐那些会戴珠宝的女人(金球奖只青睐那些会戴珠宝的女人)
- 浙江省一个县,人口超40万,建县历史超1100年(浙江省一个县人口超40万)
- 五代十国南唐历代国君(五代十国南唐历代国君)
热门推荐
- docker怎么设置远程访问(Docker开启远程访问的实现方式)
- vue-router的安装(详解Vue-Router的安装与使用)
- submit与button的区别
- docker容器端口和内部进程(Docker动态给容器Container暴露端口操作)
- html5 spellcheck属性
- yii框架的常用组件使用(Yii框架的布局文件实例分析)
- idea如何运行tomcat项目(在IDEA 2020.3.1中部署Tomcat并且创建第一个web项目的过程详解)
- SQL中的ISNULL的用法
- python数值基本运算方法(Python常见数字运算操作实例小结)
- dedecms文章点击量(Dedecms文章标题及文章摘要长度修改的方法)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9