Mysql批量插入更新如何拆分大事务?

已于 2023-12-06 11:29:13 修改
阅读量1.3k 收藏
点赞数 2
分类专栏： 数据库开发 文章标签： mysql 数据库
于 2023-10-21 09:45:39 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/nzbing/article/details/133957515
版权
本文探讨了Mysql大事务导致的binlog阈值超出问题，以及由此引发的数据库性能下降和锁冲突。解决方案是通过limit分页拆分大事务，使用while循环遍历数据进行批量操作，以避免回滚日志过大和锁冲突。然而，这种方法可能导致auto_increment值不连续增长，即使在仅更新数据的情况下。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

拆分大事务

  • 之前在运行Mysql任务的时候报错:binlog(1610646347 bytes) write threshold exceeded,
  • 问题原因是Mysql任务提交的是个大事务,超出binlog设定阈值,使得系统自动终止事务
  • Mysql中的每条记录在更新的时候都会记录一条回滚操作,同一记录在系统中可存在多个版本,导致Mysql的回滚日志越来越大,不过回滚日志会进行删除,Mysql会删除比较老的回滚日志,如果是大事务的话,一直不提交,相当于系统里有很多老的任务视图无法删数,就导致了binlog溢出,同时,大事务还会造成锁冲突,甚至拖垮数据库

一、解决方案

  • 使用limit分页拆分大事务
  • limit x,y表示从第x行开始往后偏移y行
  • 使用while循环遍历数据量按照偏移量拆分
CREATE  PROCEDURE `split_trans`()
了解本专栏 订阅专栏 解锁全文
ORACLE批量插入更新如何拆分大事务?
nzbing的博客
11-07 523
上述语句中若test_tmp数据量比较大,merge到test的时候就会引起大事务,可以考虑对test_tmp的数据进行拆分,采用分页的思路,如下所示。说明了Mysql如何拆分,本篇文章探讨Oracle或OceanBase批量插入更新拆分大事务的问题。
MySQL一次到底插入多少条数据合适啊？
墨夶的博客
11-29 394
没有一刀切的答案，最佳实践是根据你的具体环境和需求进行测试和调整。考虑到性能和资源的平衡，找到最适合你应用场景的批量大小。
大事务拆分项目应用-再看事务嵌套
Think In JAVA—Max
04-16 3224
一、何为事务嵌套最简单的事务嵌套情况：开启了一个事务的情况下，再开启一个事务2。或者：在t1、t2分别为两个事务操作，而外层t3事务包含t1（t2），t3跟t1（t2）         OR          例如在支付系统中，在进行支付校验时，事务1负责更新账单状态&插入历史表，事务2负责创建支付记录及明细，如果假设更新账单状态成功，二创建支付记录失败，则导致账单状态与支付状态丢失数据一...
一个复杂系统的拆分改造实践
wpw1205的博客
03-01 382
1 为什么要拆分？先看一段对话。从上面对话可以看出拆分的理由：1）  应用间耦合严重。系统内各个应用之间不通，同样一个功能在各个应用中都有实现，后果就是改一处功能，需要同时改系统中的所有应用。这种情况多存在于历史较长的系统，因各种原因，系统内的各个应用都形成了自己的业务小闭环；2）  业务扩展性差。数据模型从设计之初就只支持某一类的业务，来了新类型的业务后又得重新写代码实现，结果就是项目延期，大大...
大事务拆分是一种优化策略，用于将一个大型事务分解为多个小型事务，以提高系统性能、避免长时间锁定资源，从而增强数据库的并发处理能力
靡不有初，鲜克有终
07-10 688
大事务拆分是一种优化策略，用于将一个大型事务分解为多个小型事务，以提高系统性能、避免长时间锁定资源，从而增强数据库的并发处理能力。- 如果涉及跨数据库或跨服务的操作，使用两阶段提交或补偿事务等分布式事务解决方案。- 使用适当的机制（如分布式事务）确保跨多个小事务的数据一致性。- 根据拆分点，将大事务分解为具有明确边界的小事务。- 考虑事务拆分对用户体验的影响，并进行适当调整。- 在事务拆分前后进行性能测试，确保拆分方案有效。- 设计异常处理机制，以应对拆分后的事务执行失败。
Spring中大事务拆分方案
weixin_39172079的博客
06-01 2311
spring中大事务导致数据库连接不足，死锁的问题 通过编程式事务将事件完成方法和激活方法拆分为单独的事务，每个事件只关注当前事件的操作，后续操作异常时由定时任务进行补偿。
Mysql事务原理拆解
qq_14998113的博客
03-19 2905
1.mysql事务基本概念 事务特性 ACID A 原子性 I 隔离性 D 持久性 C 一致性 并发问题 脏读 读取到未提交的数据。 不可重复读 两次读取结果不同 幻读 select操作得到的结果所表征的数据状态无法支撑后续的业务操作。 隔离级别 ...
mysql批量插入亿级别的数据
weixin_37701609的博客
01-05 823
mysql如何快速插入亿级别的数据。
java向mysql数据库批量插入大量数据
m0_67698950的博客
03-31 1万+
首先看下我们的目标：向mysql数据库中批量插入10000条数据 操作环境：Mysql和Java代码都运行在我本地Windows电脑(i7处理器，4核，16G运行内存，64位操作系统 1、JPA单线程执行 代码省略，大概需要39S左右 2、JPA多线程执行 大概需要37S左右，并没有想象中的快很多 原因： 多线程只是大大提高了程序处理数据的时间，并不会提高插入数据库的时间，相反在我这边JPA的框架下，多线程也就意味着多连接，反而更加消耗数据库性能 package com.exampl...
Win10系统提示“抱歉，目前Cortana不支持中文(中华人民共和国）”
热门推荐
皮皮鱼
11-15 6万+
Windows 10 2004版本小娜运行时会报错， “抱歉，目前Cortana不支持中文(中华人民共和国）”，需要在应用商店中更新一下小娜应用。 报错界面： 操作步骤: 1. 开始菜单，找到微软应用商店Mircosoft Store，打开； 2. 右上角选择三个小点，选择下载和更新； 3. 获取更新，更新的软件列表中，更新Cortana即可。 4. 更新成功以后打开小娜就正常了。 5. 如果搜索不到，小娜的下载链接如下： ...
SqlServer 复制中将大事务分成小事务分发的方法
12-16
在sql server 复制中，当在发布数据库执行1个大事务时，如一次性操作 十万或百万以上的数据。当操作数据在发布数据库执行完成后 ，日志读取器代理将扫描事务日志，一次性传递到分发数据库中。若上个事务未传递完成，连续执行多个事务，日志读取器代理将扫描日志中多个事务同时传递到分发数据库中，默认最大扫描500个事务。如果执行多次上百万或千万的数据将堵塞很久。 日志读取器代理可配置将大事务划分为多个小事务进行传递到分发数据库中，分发队列则按照小事务分发到订阅数据库中，这样数据就很快同步！ 在没改代理参数之前，本人执行1次插入30万的数据到发布表中。插入完成后，监控发布到分发的记录如下： 可以看到
mysql操作线上数据时建议大事务拆小事务
少说，多做。
07-29 1万+
在MySQL中，一个大事务会占用较多的资源，包括锁定资源和内存资源。这可能会导致以下问题： 降低并发性：大事务在执行过程中可能会锁定大量的数据行或表，这会阻塞其他事务的执行，影响数据库的并发处理能力。 增加回滚时间和复杂性：如果大事务执行过程中出现问题需要回滚，回滚操作会消耗大量的时间和系统资源，这会影响数据库性能，并可能导致其他事务长时间等待。 可能导致锁超时：大事务在执行时，如果超过了锁等待超时的设定值，可能导致事务失败。 因此，为了优化性能和资源使用，推荐将大事务拆分为多
mysql拆分时尽量规避最大事务边界
wade1010的专栏
11-24 410
尽量减少事务边界 事务边界：单个sql语句在后端数据库上同时执行的数量 如果每一条SQL语句都能带有分库分表键，通过分布式服务层在对于SQL的解析后都能精确地将这条SQL语句推送到该数据所在的数据库上执行；但是如果数据库访问不带有分库分表键，则会出现全表扫描，导致事务边界的数量增大。会出现的问题：系统中锁冲突的概率增加，系统难以扩展（整个平台的数据库连接能力是取决于后端单个数据库的连接能力），整体性能降低（大数据量的聚合、排序、分组计算，会占用较大的内存和CPU计算资源）。 买家在查...
mysql insert select 涉及到多个表_学习笔记之mysql之insert into select
weixin_31807383的博客
02-07 1585
以MySQL 的InnoDB 为例，预设的Tansaction isolation level 为REPEATABLE READ，在SELECT 的读取锁定主要分为两种方式: SELECT ... LOCK IN SHARE MODE SELECT ... FOR UPDATE 这两种方式在事务(Transaction) 进行当中SELECT 到同一个数据表时，都必须等待其语法：MySQL中INS...
mysql事物into,记mysql Insert into select语句问题
weixin_27013291的博客
03-23 332
问题：由于数据数据库中 order_today 数据量过大，当时好像有 700W 了，并且每天在以 30W 的速度增加。所以要 将 order_today 内的部分数据迁移到 order_record 中，并将 order_today 中的数据删除，这样来降低 order_today 表中的数据量。错误操作：在默认的事务隔离级别下：insert into order_record select *...
大事务回滚估算
井底观天园
03-25 752
在系统上线、日常运维时，难免会出现由于SQL效率不佳，或者写错，导致出现回滚的现象；在大事务时，回滚常常耗时很久，如何较为准确得估算回滚的时间，可以帮助我们制定相应的计划
mysql大数据量删除以及插入导致max_binlog_cache_size的问题
花&败
04-16 847
1.业务概述 每天需要跑一个定时任务，需要向一张表更新300万条数据，每天数据量都是全量下发，由于事务的问题会导致max_binlog_cache_size问题。 2.解决办法 建立一张临时表，表结构和要更新的表一样，我们将下发的数据全量写入到这张临时表中，并且我们不使用事务，一旦中间出现问题就停止程序的运行， 当写操作完成后直接将临时表和业务部名互换即可。例如：业务表：ISS_SERVICE,那么临时表就是ISS_SERVICE_TEM， 第二天下发数据前直接将临时表数据truncate掉
Mysql据库报错[HY000][10000] binlog write threshold(1610612736) exceeded
捡星星的博客
10-08 1012
Binlog_cache_disk_use表示因为我们binlog_cache_size设计的内存不足导致缓存二进制日志用到了临时文件的次数；Binlog_cache_use 表示用binlog_cache_size缓存的次数，当对应的Binlog_cache_disk_use 值比较大的时候 我们可以考虑适当的调高 binlog_cache_size 对应的值；没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。登录对应的数据库通过。
mysql批量插入sql语句
最新发布
01-05
### 实现 MySQL 中高效批量插入 为了提高性能，在处理大批量数据插入操作时，应当尽可能减少与数据库服务器之间的通信次数。通过构建单个 `INSERT` 语句来一次性提交多组值是一种有效的方法。 #### 单次连接执行多个插入命令 利用 C# 进行编程时，可以通过创建一次性的数据库连接并在此期间发送一系列预编译好的 SQL 命令给 MySQL 数据库[^1]： ```csharp using (MySqlConnection conn = new MySqlConnection(MySqlHelper.ConnStr)) { string query = "INSERT INTO table_name(column1, column2) VALUES (@col1,@col2)"; using MySqlCommand cmd = new MySqlCommand(query, conn); { // 添加参数定义 cmd.Parameters.AddWithValue("@col1", MySqlDbType.VarChar); cmd.Parameters.AddWithValue("@col2", MySqlDbType.Int32); try { await conn.OpenAsync(); foreach(var item in itemsToInsert) { ((IDataParameter)cmd.Parameters["@col1"]).Value = item.Property1; ((IDataParameter)cmd.Parameters["@col2"]).Value = item.Property2; await cmd.ExecuteNonQueryAsync(); } } catch(Exception ex) { Console.WriteLine(ex.Message); } } } ``` 需要注意的是上述例子虽然展示了如何在一个事务内重复调用同一个命令对象以达到批量化的效果，但这并不是最优化的方式；因为每次循环都会触发网络往返以及解析过程开销较大。 #### 构建单一的大规模 INSERT 语句 更推荐的做法是在应用程序端先收集好所有的待插入记录，之后再拼接成一个包含所有这些记录的长字符串形式的 `VALUES()` 列表作为整个 `INSERT` 的一部分传送给 MySQL 执行引擎去处理[^2]： ```sql -- 插入500条数据到表名为table_name的数据库表中 INSERT INTO table_name (column1, column2, column3,...) VALUES (value1_1,value1_2,value1_3,...), (value2_1,value2_2,value2_3,...), ... (valueN_1,valueN_2,valueN_3,...); ``` 这种方式减少了客户端和服务端之间交互频度的同时也降低了服务端对于每笔请求单独分析的成本，从而提高了整体效率。 #### 使用 LOAD DATA INFILE 加速导入速度 如果要加载非常庞大的文件或外部源的数据集，则应考虑采用 `LOAD DATA INFILE` 方式来进行快速装载作业[^4]: ```sql LOAD DATA LOCAL INFILE '/path/to/file.csv' INTO TABLE tbl_name FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'; ``` 此方法允许直接从本地磁盘读取 CSV 文件并将其中的内容按照指定分隔符拆分成字段后写入目标表格之中，通常情况下它的吞吐率会远高于常规手段。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

我爱夜来香A

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值