（转）sql批量执行效率对比

最新推荐文章于 2023-10-25 15:27:01 发布
最新推荐文章于 2023-10-25 15:27:01 发布
阅读量512 收藏 1
点赞数
分类专栏： Java 文章标签： SQL MySQL C C++ C#
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/iteye_9188/article/details/81863247
版权

　环境：

　　MySQL 5.1

　　RedHat Linux AS 5

　　JavaSE 1.5

　　DbConnectionBroker 微型数据库连接池

　　测试的方案：

　　执行10万次Insert语句，使用不同方式。

　　A组：静态SQL，自动提交，没事务控制(MyISAM引擎)

　　1、逐条执行10万次

　　2、分批执行将10万分成m批，每批n条，分多种分批方案来执行。

　　B组：预编译模式SQL，自动提交，没事务控制(MyISAM引擎)

　　1、逐条执行10万次

　　2、分批执行将10万分成m批，每批n条，分多种分批方案来执行。

　　-------------------------------------------------------------------------------------------

　　C组：静态SQL，不自动提交，有事务控制(InnoDB引擎)

　　1、逐条执行10万次

　　2、分批执行将10万分成m批，每批n条，分多种分批方案来执行。

　　D组：预编译模式SQL，不自动提交，有事务控制(InnoDB引擎)

　　1、逐条执行10万次

　　2、分批执行将10万分成m批，每批n条，分多种分批方案来执行。

　　本次主要测试C、D组，并得出测试结果。

DROP TABLE IF EXISTS tuser; 
　　CREATE TABLE tuser ( 
　　id bigint(20) NOT NULL AUTO_INCREMENT, 
　　name varchar(12) DEFAULT NULL, 
　　remark varchar(24) DEFAULT NULL, 
　　createtime datetime DEFAULT NULL, 
　　updatetime datetime DEFAULT NULL, 
　　PRIMARY KEY (id) 
　　) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  
　//　C、D组测试代码： 
　　package testbatch; 
　　import java.io.IOException; 
　　import java.sql.*; 
　　/** 
　　* JDBC批量Insert优化(下) 
　　* 
　　* @author leizhimin 2009-7-29 10:03:10 
　　*/ 
　　public class TestBatch { 
　　public static DbConnectionBroker myBroker = null; 
　　static { 
　　try { 
　　myBroker = new DbConnectionBroker("com.mysql.jdbc.Driver", 
　　"jdbc:mysql://192.168.104.163:3306/testdb", 
　　"vcom", "vcom", 2, 4, 
　　"c:\\testdb.log", 0.01); 
　　} catch (IOException e) { 
　　e.printStackTrace(); 
　　} 
　　} 
　　/** 
　　* 初始化测试环境 
　　* 
　　* @throws SQLException 异常时抛出 
　　*/ 
　　public static void init() throws SQLException { 
　　Connection conn = myBroker.getConnection(); 
　　conn.setAutoCommit(false); 
　　Statement stmt = conn.createStatement(); 
　　stmt.addBatch("DROP TABLE IF EXISTS tuser"); 
　　stmt.addBatch("CREATE TABLE tuser (\n" + 
　　" id bigint(20) NOT NULL AUTO_INCREMENT,\n" + 
　　" name varchar(12) DEFAULT NULL,\n" + 
　　" remark varchar(24) DEFAULT NULL,\n" + 
　　" createtime datetime DEFAULT NULL,\n" + 
　　" updatetime datetime DEFAULT NULL,\n" + 
　　" PRIMARY KEY (id)\n" + 
　　") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8"); 
　　stmt.executeBatch(); 
　　conn.commit(); 
　　myBroker.freeConnection(conn); 
　　} 
　　/** 
　　* 100000条静态SQL插入 
　　* 
　　* @throws Exception 异常时抛出 
　　*/ 
　　public static void testInsert() throws Exception { 
　　init(); //初始化环境 
　　Long start = System.currentTimeMillis(); 
　　for (int i = 0; i < 100000; i++) { 
　　String sql = "\n" + 
　　"insert into testdb.tuser \n" + 
　　"\t(name, \n" + 
　　"\tremark, \n" + 
　　"\tcreatetime, \n" + 
　　"\tupdatetime\n" + 
　　"\t)\n" + 
　　"\tvalues\n" + 
　　"\t('" + RandomToolkit.generateString(12) + "', \n" + 
　　"\t'" + RandomToolkit.generateString(24) + "', \n" + 
　　"\tnow(), \n" + 
　　"\tnow()\n" + 
　　")"; 
　　Connection conn = myBroker.getConnection(); 
　　conn.setAutoCommit(false); 
　　Statement stmt = conn.createStatement(); 
　　stmt.execute(sql); 
　　conn.commit(); 
　　myBroker.freeConnection(conn); 
　　} 
　　Long end = System.currentTimeMillis(); 
　　System.out.println("单条执行100000条Insert操作，共耗时：" + (end - start) / 1000f + "秒!"); 
　　} 
　　/** 
　　* 批处理执行静态SQL测试 
　　* 
　　* @param m 批次 
　　* @param n 每批数量 
　　* @throws Exception 异常时抛出 
　　*/ 
　　public static void testInsertBatch(int m, int n) throws Exception { 
　　init(); //初始化环境 
　　Long start = System.currentTimeMillis(); 
　　for (int i = 0; i < m; i++) { 
　　//从池中获取连接 
　　Connection conn = myBroker.getConnection(); 
　　conn.setAutoCommit(false); 
　　Statement stmt = conn.createStatement(); 
　　for (int k = 0; k < n; k++) { 
　　String sql = "\n" + 
　　"insert into testdb.tuser \n" + 
　　"\t(name, \n" + 
　　"\tremark, \n" + 
　　"\tcreatetime, \n" + 
　　"\tupdatetime\n" + 
　　"\t)\n" + 
　　"\tvalues\n" + 
　　"\t('" + RandomToolkit.generateString(12) + "', \n" + 
　　"\t'" + RandomToolkit.generateString(24) + "', \n" + 
　　"\tnow(), \n" + 
　　"\tnow()\n" + 
　　")"; 
　　//加入批处理 
　　stmt.addBatch(sql); 
　　} 
　　stmt.executeBatch(); //执行批处理 
　　conn.commit(); 
　　// stmt.clearBatch(); //清理批处理 
　　stmt.close(); 
　　myBroker.freeConnection(conn); //连接归池 
　　} 
　　Long end = System.currentTimeMillis(); 
　　System.out.println("批量执行" + m + "*" + n + "=" + m * n + "条Insert操作，共耗时：" + (end - start) / 1000f + "秒!"); 
　　} 
　　/** 
　　* 100000条预定义SQL插入 
　　* 
　　* @throws Exception 异常时抛出 
　　*/ 
　　public static void testInsert2() throws Exception { //单条执行100000条Insert操作，共耗时：40.422秒! 
　　init(); //初始化环境 
　　Long start = System.currentTimeMillis(); 
　　String sql = "" + 
　　"insert into testdb.tuser\n" + 
　　" (name, remark, createtime, updatetime)\n" + 
　　"values\n" + 
　　" (?, ?, ?, ?)"; 
　　for (int i = 0; i < 100000; i++) { 
　　Connection conn = myBroker.getConnection(); 
　　conn.setAutoCommit(false); 
　　PreparedStatement pstmt = conn.prepareStatement(sql); 
　　pstmt.setString(1, RandomToolkit.generateString(12)); 
　　pstmt.setString(2, RandomToolkit.generateString(24)); 
　　pstmt.setDate(3, new Date(System.currentTimeMillis())); 
　　pstmt.setDate(4, new Date(System.currentTimeMillis())); 
　　pstmt.executeUpdate(); 
　　conn.commit(); 
　　pstmt.close(); 
　　myBroker.freeConnection(conn); 
　　} 
　　Long end = System.currentTimeMillis(); 
　　System.out.println("单条执行100000条Insert操作，共耗时：" + (end - start) / 1000f + "秒!"); 
　　} 
　　/** 
　　* 批处理执行预处理SQL测试 
　　* 
　　* @param m 批次 
　　* @param n 每批数量 
　　* @throws Exception 异常时抛出 
　　*/ 
　　public static void testInsertBatch2(int m, int n) throws Exception { 
　　init(); //初始化环境 
　　Long start = System.currentTimeMillis(); 
　　String sql = "" + 
　　"insert into testdb.tuser\n" + 
　　" (name, remark, createtime, updatetime)\n" + 
　　"values\n" + 
　　" (?, ?, ?, ?)"; 
　　for (int i = 0; i < m; i++) { 
　　//从池中获取连接 
　　Connection conn = myBroker.getConnection(); 
　　conn.setAutoCommit(false); 
　　PreparedStatement pstmt = conn.prepareStatement(sql); 
　　for (int k = 0; k < n; k++) { 
　　pstmt.setString(1, RandomToolkit.generateString(12)); 
　　pstmt.setString(2, RandomToolkit.generateString(24)); 
　　pstmt.setDate(3, new Date(System.currentTimeMillis())); 
　　pstmt.setDate(4, new Date(System.currentTimeMillis())); 
　　//加入批处理 
　　pstmt.addBatch(); 
　　} 
　　pstmt.executeBatch(); //执行批处理 
　　conn.commit(); 
　　// pstmt.clearBatch(); //清理批处理 
　　pstmt.close(); 
　　myBroker.freeConnection(conn); //连接归池 
　　} 
　　Long end = System.currentTimeMillis(); 
　　System.out.println("批量执行" + m + "*" + n + "=" + m * n + "条Insert操作，共耗时：" + (end - start) / 1000f + "秒!"); 
　　} 
　　public static void main(String[] args) throws Exception { 
　　init(); 
　　Long start = System.currentTimeMillis(); 
　　System.out.println("--------C组测试----------"); 
　　testInsert(); 
　　testInsertBatch(100, 1000); 
　　testInsertBatch(250, 400); 
　　testInsertBatch(400, 250); 
　　testInsertBatch(500, 200); 
　　testInsertBatch(1000, 100); 
　　testInsertBatch(2000, 50); 
　　testInsertBatch(2500, 40); 
　　testInsertBatch(5000, 20); 
　　Long end1 = System.currentTimeMillis(); 
　　System.out.println("C组测试过程结束，全部测试耗时：" + (end1 - start) / 1000f + "秒!"); 
　　System.out.println("--------D组测试----------"); 
　　testInsert2(); 
　　testInsertBatch2(100, 1000); 
　　testInsertBatch2(250, 400); 
　　testInsertBatch2(400, 250); 
　　testInsertBatch2(500, 200); 
　　testInsertBatch2(1000, 100); 
　　testInsertBatch2(2000, 50); 
　　testInsertBatch2(2500, 40); 
　　testInsertBatch2(5000, 20); 
　　Long end2 = System.currentTimeMillis(); 
　　System.out.println("D组测试过程结束，全部测试耗时：" + (end2 - end1) / 1000f + "秒!"); 
　　} 
　　}

 　　

 

执行结果：

 

　　--------C组测试----------

　　单条执行100000条Insert操作，共耗时：103.656秒!

　　批量执行100*1000=100000条Insert操作，共耗时：31.328秒!

　　批量执行250*400=100000条Insert操作，共耗时：31.406秒!

　　批量执行400*250=100000条Insert操作，共耗时：31.75秒!

　　批量执行500*200=100000条Insert操作，共耗时：31.438秒!

　　批量执行1000*100=100000条Insert操作，共耗时：31.968秒!

　　批量执行2000*50=100000条Insert操作，共耗时：32.938秒!

　　批量执行2500*40=100000条Insert操作，共耗时：33.141秒!

　　批量执行5000*20=100000条Insert操作，共耗时：35.265秒!

　　C组测试过程结束，全部测试耗时：363.656秒!

　　--------D组测试----------

　　单条执行100000条Insert操作，共耗时：107.61秒!

　　批量执行100*1000=100000条Insert操作，共耗时：32.64秒!

　　批量执行250*400=100000条Insert操作，共耗时：32.641秒!

　　批量执行400*250=100000条Insert操作，共耗时：33.109秒!

　　批量执行500*200=100000条Insert操作，共耗时：32.859秒!

　　批量执行1000*100=100000条Insert操作，共耗时：33.547秒!

　　批量执行2000*50=100000条Insert操作，共耗时：34.312秒!

　　批量执行2500*40=100000条Insert操作，共耗时：34.672秒!

　　批量执行5000*20=100000条Insert操作，共耗时：36.672秒!

　　D组测试过程结束，全部测试耗时：378.922秒!

　　测试结果意想不到吧，最短时间竟然超过上篇。观察整个测试结果，发现总时间很长，原因是逐条执行的效率太低了。

　　结论：

　　在本测试条件下，得出结论：

　　数据库连接池控制下，不自动提交，事务控制(InnoDB引擎)

　　1、逐条执行的效率很低很低，尽可能避免逐条执行。

　　2、事务控制下，静态SQL的效率超过预处理SQL。

　　3、分批的大小对效率影响挺大的，一般来说，事务控制下，分批大小在100-1000之间比较合适。

　　4、谈到优化方式，上面的批处理就是很好的优化策略。

　　大总结：

　　对比上篇没事务的测试结果，得出一个全面的结论：

　　1、连接池最基本的也是最重要的优化策略，总能大幅提高性能。

　　2、批处理在效率上总是比逐条处理有优势，要处理的数据的记录条数越大，批处理的优势越明显，批处理还有一个好处就是减少了对数据库的链接次数，从而减轻数据库的压力。

　　3、批处理执行SQL的时候，批处理的分批的大小与数据库的吞吐量以及硬件配置有很大关系，需要通过测试找到最佳的分批大小，一般在50-1000之间。

　　4、预处理SQL在没事务的表上效率较高，在有实物的情况下比静态SQL稍有不及。但预定义SQL还有个好处就是消耗的内存较少，静态SQL串会占用大量的内存资源，容易导致内存溢出的问题。因此批量执行时候可以优先选择预定义SQL。

　　5、在批处理执行的时候，每批执行完成后，最好显式的调用pstmt.close()或stmt.close()方法，以便尽快释放执行过的SQL语句，提高内存利用率。

　　6、对于有大量SELECT操作，MyISAM是更好的选择;对于有大量INSERT和UPDATE操作的表，InnoDB效率更好。

　　7、虽然测试结果只能反映特定情况下的一些事实，以上的优化策略是普遍策略，可以明显缩短寻找最优策略的时间，对于效率要求很高的程序，还应该做并发性等测试。

　　8、测试是件很辛苦的事情，你需要有大量的事实来证明你的优化是有效的，而不能单单凭经验，因为每个机器的环境都不一样，使用的方式也不同。

iteye_9188
关注
专栏目录
面试官：如何分析SQL执行效率
m0_72391097的博客
01-18 1165
从开篇词我们了解到，本专栏首先会一起讨论一下 SQL 优化，而优化 SQL 的前提是能定位到慢 SQL 并对其进行分析，因此在专栏的开始，会跟大家分享如何定位慢查询和如何分析 SQl 执行效率。在前面两节，会用一些简单的例子让大家学会这些分析技巧。
mysql 批量更新及效率对比
09-16
批量 Update 是最原始的批量更新方法，每条记录执行一次 Update 语句，例如： ```sql update test_tbl set dr='2' where id=1; ``` 这种方法的性能非常差，例如更新 100000 条数据需要 15.557 秒。 方法二：...
数据库批处理和一般处理的快慢比较
我的青春一去不复返
04-24 524
下面有两个方法：ct1方法是标准的处理模式，ct2方法是批处理的模式。 最终显示：批处理模式的速度比一般处理模式块的很多，在实际开发中，一个表的字段越多，一般二三十个很常见，SQL语句越复杂，越能体现出批处理的速度之快！ package com.wei.employee.common; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.ut.
mybatis 插入大量数据效率对比，foreach插入、SqlSession批量插入、sql插入
热门推荐
龙池小生
05-14 4万+
新增xml执行效率测试：xml执行时间比sql稍慢一些，50000条数据插入时间约为2000毫秒左右，平均时间是sql的2倍左右。sql插入的效率最高，sqlsession次之，mybatis框架foreach插入效率最低。2 使用mybatis-plus框架的insert方法，for循环，每次执行一次插入。使用mybatis插入数据执行效率对比，对比三种方式（测试数据库为MySQL），1 使用 SqlSessionFactory，每一批数据执行一次提交(3 使用ibatis，纯sql插入。
mysql每秒执行多少条sql,mysql执行疑惑，为什么执行4条sql比执行1条还快。
weixin_35899659的博客
03-18 389
今天晚上突然发现执行4条sql比执行1条还快，也就是说执行多条单条语句在一定条件下执行效率也是不错的。4条sql语句如下：customer是生意关系表，store_id和customer_id都是店铺的id，有索引。store是店铺表，id是自增主键。我的数据库引擎是MyISAM.SELECT * FROM customer WHERE store_id=2 AND customer_id=8;S...
mysql多表联合查询快还是分开查询快_[实验] 两个表关联查询快，还是分开多次连库查询快...
weixin_28520495的博客
02-05 1465
private static string connectionStr = "data source=192.168.6.37;database=Tidebuy;uid=sa;pwd=123456;";private static string idStr= @"73498,73514,73546,73716,73798,74029,74042";static void Main(string[]...
sql语句批量执行多条语句和执行一句IN语句对比
weixin_43034862的博客
07-01 944
--一共费时0.005秒 DELETE from pt_data_inbound_cms_h where id in(1,2,3,4,5); --一共费时0.004*5 DELETE from pt_data_inbound_cms_h where id=1; DELETE from pt_data_inbound_cms_h where id=2; DELETE from pt_data_inbound_cms_h where id=3; DELETE from pt_data_inbound_cms_h
sqlserver批量执行脚本文件
09-30
在SQL Server环境中，批量执行SQL脚本文件是数据库管理员和开发者日常工作中常见的一项任务。本文将深入探讨如何在.NET环境中高效地实现这一功能，并对比使用sqlcmd工具的方法。以下是一些关键知识点： 1. **SQL ...
thinkPHP框架中执行原生SQL语句的方法
10-19
本篇文章将深入探讨如何在ThinkPHP中执行原生SQL语句，并对比`query()`和`execute()`这两个方法的使用场景和差异。 首先，执行原生SQL语句的方法通常涉及创建一个模型对象。在ThinkPHP中，你可以通过以下方式实例化...
多条SQL批量运行的时间比单独运行累计的时间慢太多了，怎么提升速度？
junlife88的博客
06-30 739
我有几十条update语句要更新，每条更新有10W+的记录，我在SQLyog中将十多条SQL全选然后执行查询，时间要好久，对比一下用手动一条一条SQL执行查询的时间加起来要慢好几倍，这是为什么？要怎么提升速度才行？怎么让批量运行多条update SQL语句速度更快，至少也得让批量运行的时间比一条一条单独运行的时间加起来相差不大吧 ...
不知道一次执行几条sql语句和分批次执行那个更有效率
爬行
10-16 1063
听说要少请求。于是我想一次执行几条sql语句。 declare @Y int declare @D int declare @S int select @S=count(*) from Sys_MenPiaoOrder select @Y=count(*) from Sys_MenPiaoOrder where status='已支付'; select @D=count(*) from S
执行一条sql语句update多条记录实现思路
Elvis技术小屋
06-21 2万+
如果你想更新多行数据，并且每行记录的各字段值都是各不一样，你会怎么办呢？本文以一个示例向大家讲解下如何实现如标题所示的情况，有此需求的朋友可以了解下 通常情况下，我们会使用以下SQL语句来更新字段值： 复制代码 代码如下: UPDATE mytable SET myfield='value' WHERE other_field='other_value';
mysql 批量插入 性能_MySQL批量插入数据性能比较
weixin_39722759的博客
01-19 788
目前遇到比较多的开发同学来问有关insert批量插入相关的事情，现在测试了下两种批量insert写法的效率。本次测试只比较相同环境下insert单条插入和合并插入的效率问题，不考虑类似“机器性能对mysql插入的影响”或者“每秒能插多少条数据”这种课题表结构如下root:test> show create table t_insert_single\G********************...
Mysql 批量修改四种方式效率对比
最新发布
y19910825的博客
10-25 3375
这句sql的意思是，更新display_order 字段，如果id=1 则display_order 的值为3，如果id=2 则 display_order 的值为4，如果id=3 则 display_order 的值为5。使用的时候建议分批插入。replace into 操作本质是对重复的记录先delete 后insert，如果更新的字段不全会将缺失的字段置为缺省值,而且要保证传的参数id和之前的id是一样的,避免出现改变id的问题.-- 批量更新第三种方法，通过 replace into -->
SQL语句的优化
zeus444的专栏
11-28 633
1）选择最有效率的表名顺序(只在基于规则的优化器中有效)： Oracle的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他
一波骚操作，我把 SQL 执行效率提高了N倍！
11-16 362
作者：风过无痕的博客https://www.cnblogs.com/tangyanbo/p/4462734.html场景用的数据库是mysql5.6，下面简单的介绍下场景课程表create table Course(c_id int PRIMARY KEY,name varchar(10))数据100条学生表:create table Student(id int PRIMARY KEY,nam...
mysql foreach 写法，多条 sql 与一条 sql 的性能消耗分析
JellyfishMIX 的博客
11-09 1026
比如有 100 条 sql，如果不手动开启事务把这 100 条 sql 放在同一个事务中执行，那么这 100 条 sql 会分别在 100 个隐式事务中。每个事务占用一个连接，这样就占用了 100 个连接。
mybatis批量更新效率对比：for循环与MySQL caseWhen方法
1. **循环传递参数的SQL批量更新** - 这种方法利用for循环遍历一个参数集合，每次循环生成一条单独的SQL语句，如`UPDATE t_customer SET c_name = #{cus.name}, c_age = #{cus.age}, ... WHERE c_namelike concat('...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值