mysql批量数据处理性能提升

于 2023-11-20 18:12:01 发布
阅读量168 收藏
点赞数 1
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_24084169/article/details/134515487
版权

        目前商户后台、管理后台项目中有很多批处理的功能，mysql提供了一个参数rewriteBatchedStatements，代码上使用bacth处理新增和更新，实际上mysql还是会拆分为多条入库，只是减少了一层循环调用， 实际性能并未提升很大，但是官方也有提供的批量难道只是迷惑人的吗？ 实际上需要参数配置才可以实现高性能的批量插入的性能，减少了IO和DB开销，合并sql为一条，从而达到性能提升。

MySQL JDBC驱动在默认情况下会无视executeBatch()语句，把我们期望批量执行的一组sql语句拆散，一条一条地发给MySQL数据库，批量插入实际上是单条插入，直接造成较低的性能。

如图：10条数据的批量更新，执行930ms


只需要在MySQL的JDBC连接的url中要加rewriteBatchedStatements参数，并保证5.1.13以上版本的驱动，就能实现高性能的批量插入。

如图：10条数据的批量更新组合成一条完整的SQL，执行430ms

10条数据插入性能提升1倍以上，此场景适用批量导入时数据大提升性能，也需要针对场景数据拼接SQL进行控制，尽量300条以内拆分，自行处理多个List执行Bacth

实践发现：rewriteBatchedStatements参数置为true, 驱动才会帮你批量执行SQL时才会有效提升性能

批量操作场景：INSERT/UPDATE/DELETE 都是有效的，但是要注意：必须是全部属性的SET，即使是不更新的字段也要有值，这里是源自空属性值不更新，导致SQL拼接无效会自动转为单条执行

MaAidan
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql批量更新数据性能_mysql 批量更新多条记录的不同值的性能分析
weixin_32022555的博客
01-19 1513
一，mysql批量更新mysql更新语句，更新一条数据的某个字段：UPDATE mytable SET myfield = 'value' WHERE other_field = 'other_value';更新同一字段为同一个值，修改下where即可：UPDATE mytable SET myfield = 'value' WHERE other_field in ('other_values'...
mysql 批量更新及效率对比
09-16
比较集中mysql批量更新的方式，推荐一种效率最高的更新语句写法
MySQL管理之道 性能调优、高可用与监控.part2.rar
09-28
《mysql管理之道：性能调优、高可用与监控》由资深mysql专家撰写，以最新的mysql版本为基础，以构建高性能mysql服务器为核心，从故障诊断、表设计、sql优化、性能参数调优、mydumper逻辑、xtrabackup热备份与恢复、mysql高可用集群搭建与管理、mysql服务器性能和服务监控等方面多角度深入讲解了如何去管理与维护mysql服务器。 书中内容以实战为导向，所有内容均来自于笔者多年实践经验的总结和对新知识的拓展，同时也针对运维人员、dba等相关工作者会遇到的有代表性的疑难问题给出了实用的情景模拟，并给出了解决方案。不论你目前有没有遇到过此类问题，相信对你以后处理相关问题都会有所借鉴。本书适合所有希望构建和管理高性能、高可用性的mysql数据库系统的开发者和dba阅读。 目录 · · · · · · 前言 第一部分　mysql5.5 新特性篇 第1章　mysql5.5介绍 2 1.1　性能上的显著改变 2 1.1.1　mysql5.5默认存储引擎的调整 2 1.1.2　充分利用cpu多核的处理能力 7 1.1.3　提高刷新脏页数量和合并插入数量，改善磁盘i/o处理能力 8 1.1.4　增加自适应刷新脏页功能 9 1.1.5　让innodb_buffer_pool缓冲池中的热数据存活更久 9 1.1.6　innodb的数据恢复时间加快 11 1.1.7　innodb同时支持多个bufferpool实例 15 1.1.8　可关闭自适应哈希索引 17 1.1.9　在innodb中可选择使用内存分配程序 18 1.1.10　提高默认innodb线程并发数 21 1.1.11　预读算法的变化 22 1.1.12　首次在linux上实现了异步i/o 23 1.1.13　恢复组提交 24 1.1.14　innodb使用多个回滚段提升性能 26 1.1.15　改善清除程序进度 26 .1.1.16　添加删除缓冲和清除缓冲 27 1.1.17　控制自旋锁spin lock轮训间隔 28 1.1.18　快速创建、删除、更改索引 29 1.1.19　innodb支持创建压缩数据页 30 1.1.20　可动态关闭innodb更新元数据的统计功能 37 1.2　安全性、稳定性的显著改变 38 1.2.1　复制功能加强 38 1.2.2　中继日志relay-log可自我修复 39 1.2.3　开启innodb严格检查模式 39 1.3　动态更改系统配置参数 39 1.3.1　支持动态更改独立表空间 39 1.3.2　支持动态更改innodb锁超时时间 40 1.4　innodb新参数汇总 40 1.5　同步复制新参数汇总 48 1.6　sql语句写法的改变 53 1.6.1　delete表连接语法改变 53 1.6.2　mysql5.5存储过程支持limit变量 54 1.7　mysql5.1升级为mysql5.5 55 1.7.1　采用mysql_upgrade升级授权表方式升级 55 1.7.2　直接安装mysql5.5，采用数据导出/导入方式升级 59 1.8　性能测试：mysql5.5与mysql5.1 60 第2章　半同步复制 62 2.1　半同步复制简介 62 2.2　半同步复制安装配置 63 2.3　参数说明 63 2.4　功能测试 64 2.4.1　如何验证半同步复制是否正常工作 64 2.4.2　半同步复制与异步复制的切换 65 2.5　性能测试 68 2.6　小结 70 第二部分　故障诊断与性能优化篇 第3章　故障诊断 72 3.1　影响mysql性能的因素 72 3.2　系统性能评估标准 73 3.2.1　影响linux服务器性能的因素 73 3.2.2　系统性能评估指标 74 3.2.3　开源监控和评估工具介绍 76 3.3　故障与处理 79 3.3.1　连接数过多导致程序连接报错的原因 79 3.3.2　记录子查询引起的宕机 84 3.3.3　诊断事务量突高的原因 87 3.3.4　谨慎设置binlog_format=mixed 90 3.3.5　未设置swap分区导致内存耗尽，主机死机 94 3.3.6　mysql故障切换之事件调度器注意事项 95 3.3.7　人工误删除innodb ibdata数据文件，如何恢复 97 3.3.8　update忘加where条件误操作恢复（模拟oracle闪回功能） 99 3.3.9　delete忘加where条件误操作恢复（模拟oracle闪回功能） 108 第4章　同步复制报错故障处理 112 4.1　最常见的3种故障 112 4.1.1　在master上删除一条记录时出现的故障 112 4.1.2　主键重复 114 4.1.3　在master上更新一条记录，而slave上却找不到 115 4.2　特殊情况：slave的中继日志relay-log损坏 116 4.3　人为失误 118 4.4　避免在master上执行大事务 119 4.5　slave_exec_mode参数可自动处理同步复制错误 120 4.6　如何验证主从数据一致 121 4.7　binlog_ignore_db引起的同步复制故障 123 4.8　mysql5.5.19/20同步一个bug 124 4.9　恢复slave从机上的某几张表的简要方法　 126 4.10　如何干净地清除slave同步信息 127 第5章　性能调优 129 5.1　表设计 129 5.2　字段类型的选取 133 5.2.1　数值类型 134 5.2.2　字符类型 139 5.2.3　时间类型 141 5.2.4　小技巧：快速修改表结构 148 5.2.5　pt-online-schema-change在线更改表结构 152 5.2.6　mysql5.6在线ddl更改表测试 158 5.3　采用合适的锁机制 161 5.3.1　表锁的演示 161 5.3.2　行锁的演示 164 5.3.3　innodb引擎与myisam引擎的性能对比 166 5.4　选择合适的事务隔离级别 168 5.4.1　事务的概念 168 5.4.2　事务的实现 169 5.4.3　事务隔离级别介绍 171 5.5　sql优化与合理利用索引 177 5.5.1　如何定位执行很慢的sql语句 177 5.5.2　sql优化案例分析 178 5.5.3　合理使用索引 188 5.6　my.cnf配置文件调优 198 5.6.1　per_thread_buffers优化 198 5.6.2　global_buffers优化 200 5.6.3　query cache在不同环境下的使用 201 5.6.4　tuning-primer.sh性能调试工具的使用 205 5.6.5　72 gb内存的my.cnf配置文件 208 5.6.6　谨慎使用分区表功能 211 5.7　mysql5.6同步复制新特性详解 213 第6章　备份与恢复 223 6.1　冷备份 224 6.2　逻辑备份 224 6.2.1　mysqldump增加了一个重要参数 225 6.2.2　取代mysqldump的新工具mydumper 226 6.2.3　逻辑备份全量、增量备份脚本 229 6.3　热备份与恢复 230 第三部分　高可用集群管理篇 第7章　目前流行的4种高可用架构 236 7.1　采用mysql自带的replication架构 237 7.1.1　keepalived+mysql replication架构的搭建演示 237 7.1.2　mmm+mysql replication架构的搭建演示 241 7.2　heartbeat+drbd+mysql架构的搭建演示 249 7.3　红帽rhcs共享存储架构的搭建演示 254 7.3.1　安装过程 257 7.3.2　红帽rhcs集群的维护 265 7.4　mysql高可用集群ha解决方案的测试评估 267 第8章　批量管理服务器 270 8.1　开源工具pssh的使用方法 270 8.2　自己编写的ssh服务器批量管理工具 273 第四部分　监控篇 第9章　性能监控 278 第10章　服务监控 283 10.1　nagios搭建与维护 283 10.2　mysql数据库的监控脚本 288 第五部分　项目案例 第11章　项目案例讲解 292 11.1　数据碎片整理方案 292 11.2　用户信息表水平拆表方案 296 11.3　阿里巴巴中间件cobar水平拆表方案 299
mysql大量插入优化_MySQL批量SQL插入性能优化
weixin_29854481的博客
01-19 540
对于一些数据量较大的系统，数据库面临的问题除了查询效率低下，还有就是数据入库时间长。特别像报表系统，可能每天花费在数据导入上的时间就会长达几个小时之久。因此，优化数据库插入性能是很有意义的。网络上的牛人很多，总会有一些手段可以提高insert效率，大家跟我一起分享一下吧：1. 一条SQL语句插入多条数据。我们常用的插入语句大都是一条一个insert，如：INSERTINTO`insert_ta...
mysql批量更新update性能
计算机毕业论文源码，学生个人网页制作html源码。贴近用户做网络推广和互联网优化。
11-17 860
关于mysql的批量更新 mysql数据批量更新 updateasetb=1whereidin($ids)andid!=$_GET['id']; updateasetb=0whereidnotin($ids)andid!=$_GET['id']; 本回答由网友推荐 mysql 批量更新10000+的数据，有什...
MySQL加快批量更新 UPDATE优化
weixin_34310785的博客
06-30 1210
如果是更新为同样的内容，没啥难度，直接在where里面下功夫就好了，大家都懂，我要说的是针对更新内容不一样的情况 首先，先看看网上转载的方法： mysql 批量更新如果一条条去更新效率是相当的慢,循环一条一条的更新记录,一条记录update一次，这样性能很差，也很容易造成阻塞。 mysql 批量更新共有以下四种办法 1、.replace into 批量更新 replace in...
对MySQL数据库进行批量处理操作
weixin_53387031的博客
01-11 2302
由于数据库本身就可以批量进行选择（例如 select * from customers）， 删除（例如delete * from customers）。所以本章主要讲解数据库如何实现数据的批量插入
mysql批处理
编程小学生
04-02 2802
在调用prepareStatement.executeBatch();代码时 开启rewriteBatchedStatements会成倍提高执行效率 不开启则是单条sql提交 开启则是多条sql提交 但 提交的sql不能超过max_allowed_packet的设置值 不然报错 jdbc:mysql://localhost:3306/shopxxb2b2c?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8
mysql批量更新方式的比较
m0_52133525的博客
02-03 1910
sql mysql 批量更新 Update 效率 replace duplicate
MySQL批量更新的四种方法
热门推荐
xiaoweids的博客
05-28 1万+
到此这篇关于MySQL批量更新的四种方式的文章就介绍到这了,希望可以对你有所帮助。微点阅读。
Mysql批处理备份数据库同时执行计划任务之一
beiback的博客
08-22 279
备份数据库文件【代码】Mysql批处理备份数据同时执行计划任务。
mysql批量执行sql文件批处理
03-15
mysql 批量执行 sql文件 批处理，参数和执行方法见附件txt文档
mysql批处理文件+数据库脚本执行.zip
04-01
一键安装mysql数据库，同时可以执行数据库脚本 经过测试当前文件可以兼容各个版本的系统，压缩包内包括说明文档，下载后如果有什么问题可以私信我
10万条数据下MySQL批量更新6种方式的性能测试.zip
05-11
通过实际测试对比了Spring Boot中6种MySQL批量更新方式的效率，并详细记录了每种方法在处理500,1000,5000,10000,50000,100000条数据的平均时间、最小时间和最大时间。包括MyBatis-Plus提供的批量更新,JdbcTemplate...
MySQL批量更新性能大比拼：六种方法的实战测试.zip
05-11
通过实际测试对比了Spring Boot中6种MySQL批量更新方式的效率，并详细记录了每种方法在处理500,1000,5000,10000,50000,100000条数据的平均时间、最小时间和最大时间。包括MyBatis-Plus提供的批量更新,JdbcTemplate...
从效率角度看6种MySQL批量更新方式的优劣.zip
05-11
通过实际测试对比了Spring Boot中6种MySQL批量更新方式的效率，并详细记录了每种方法在处理500,1000,5000,10000,50000,100000条数据的平均时间、最小时间和最大时间。包括MyBatis-Plus提供的批量更新,JdbcTemplate...
springboot mysql 批量更新 springboot batch批量更新.zip
05-10
通过实际测试对比了Spring Boot中6种MySQL批量更新方式的效率，并详细记录了每种方法在处理500,1000,5000,10000,50000,100000条数据的平均时间、最小时间和最大时间。包括MyBatis-Plus提供的批量更新,JdbcTemplate...
6种MySQL批量更新方式的优缺点比较.zip
最新发布
05-11
通过实际测试对比了Spring Boot中6种MySQL批量更新方式的效率，并详细记录了每种方法在处理500,1000,5000,10000,50000,100000条数据的平均时间、最小时间和最大时间。包括MyBatis-Plus提供的批量更新,JdbcTemplate...
MySQL提高批量更新性能的方法
一入IT深似海
11-17 842
其中Yj_CPhone有1500万数据，Yj_TempPhone有31万数据，语句执行时间超过半个小时还没有执行完。场景：批量更新A表字段1，条件为B表中的字段2等于A表中的字段2。执行时间为3.185秒。
mysql delete批量删除缓慢
07-18
### 回答1： MySQL中的DELETE操作是一个很常见的操作，但如果删除的数据量很大，就可能出现删除缓慢的情况。 可能导致MySQL DELETE操作缓慢的原因有以下几点： 1. 索引问题：如果需要删除的表没有正确的索引，MySQL将需要遍历整个表来查找需要删除的数据，这将导致删除操作变得非常缓慢。确保在需要删除的字段上创建了正确的索引可以大大加速删除操作。 2. 锁问题：MySQL在进行DELETE操作时会对需要删除的行进行加锁，避免其他操作同时对同一行进行修改。但如果有其他操作正在持有锁并且锁的争夺比较激烈，就可能导致DELETE操作等待锁释放而变得缓慢。可以通过优化事务执行时间，合理设置事务的隔离级别以及减少并发操作来解决锁问题。 3. 日志问题：MySQL的写操作会生成日志，包括二进制日志和redo日志。DELETE操作会产生大量的日志记录，这些日志的写入可能会成为性能瓶颈。可以通过优化日志的写入速度，如调整日志的刷新频率或更换更快的存储设备来加速DELETE操作。 4. 服务器资源问题：如果服务器的内存、CPU或磁盘等资源有限或者配置不合理，就可能导致DELETE操作缓慢。通过增加服务器的硬件资源、优化服务器的配置或提高服务器性能可以加速DELETE操作的执行。 综上所述，如果MySQL的DELETE操作缓慢，可以通过创建正确的索引、优化事务隔离级别、调整日志写入速度、优化服务器资源等方法来提高删除的速度。 ### 回答2： MySQL的DELETE语句在批量删除数据时可能会变得缓慢的几个原因。 首先，删除操作会导致数据库的写入操作。当删除大量数据时，MySQL需要遍历和修改索引，同时写入事务日志，这些都是耗时的操作。如果数据库中存在大量的索引，那么删除操作的效率会更低。 其次，如果删除操作没有使用合适的WHERE条件，那么MySQL可能需要全表扫描来找到符合删除条件的记录。全表扫描对于大表来说是非常耗时的。 另外，如果在删除操作期间有其他会话正在读取需要删除的数据，那么MySQL会进行锁定以保证数据的一致性。这样一来，其他会话就需要等待锁释放，导致删除操作变慢。 为了提高MySQL DELETE语句的批量删除效率，可以采取以下几个措施： 1. 添加合适的WHERE条件，尽量减少需要扫描的记录数量。可以使用索引来加速查找符合条件的记录。 2. 考虑将DELETE语句分批执行，不要一次性删除大量数据，可以设置循环批量删除一定数量的记录。 3. 如果没有必要的话，可以暂时禁用或减少对删除操作的读取请求，减少锁冲突。 4. 可以调整MySQL的配置参数，如innodb_buffer_pool_size（增大缓冲池）、innodb_log_file_size（增大日志文件大小）等，来提升删除操作的性能。 综上所述，MySQL的DELETE批量删除操作变慢可能是由于写入操作的耗时、全表扫描、锁冲突等原因所导致的。通过合适的WHERE条件、分批执行、减少锁冲突和调整配置参数等方法，可以提高其执行效率。 ### 回答3： MySQL的DELETE语句在批量删除数据时可能会变得缓慢的几个原因： 1. 事务处理：如果删除操作在事务中执行，MySQL会为每个删除操作生成一个事务日志，并进行回滚和恢复的操作。这些额外的步骤会导致删除操作变慢。在批量删除数据时，建议禁用事务处理或将多个删除操作合并为一个事务，从而减少事务日志的生成和处理。 2. 索引：如果要删除的表有多个索引，那么每次删除一条记录时，MySQL需要更新索引。在批量删除数据时，可以考虑删除索引，进行删除操作后再重新创建索引，以减少更新索引的次数。 3. 对应关系：如果要删除的表与其它表存在关联关系，那么MySQL需要检查和更新相关表的约束和触发器。在进行批量删除操作时，可以禁用或延迟触发器和约束的检查，以加快删除操作的速度。 4. 锁机制：删除操作可能会引起行级锁、表级锁或页面锁的产生，当发生锁冲突时，MySQL将会等待锁的释放。在批量删除数据时，可以尝试使用更高级别的锁模式，如表级锁或段级锁，以减少锁冲突和等待时间。 5. 查询优化：MySQL的查询优化器会根据表的统计信息和索引情况来选择最优的执行计划。在批量删除数据时，可以使用ANALYZE TABLE命令来更新表的统计信息，以帮助查询优化器选择更优的执行计划。 总之，如果MySQL的DELETE语句批量删除数据的速度较慢，可以考虑禁用事务处理、删除索引、禁用或延迟触发器和约束的检查、优化锁机制和更新表的统计信息等方法来提高删除操作的效率。具体的优化方法需要根据具体的数据库和表结构进行调整和测试。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值