不同数据库批量插入方式效率也迥异,使用不同数据批量插入方式也存在一些需要注意、容易引起错误的点。
用于实验的数据和表
create DATABASE if exists batch_insert_performance_test;
CREATE TABLE `goods` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`name` varchar(128) NOT NULL DEFAULT '' COMMENT '商品名字',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=64667 DEFAULT CHARSET=utf8mb4;
-- 注意点1: 数据引擎设置的最小粒度为表
-- 注意点2: 表名和列名上面的反引号加上之后,能够保证当表明和列名使用了mysql关键字不会报错。
-- 注意点3: 字符编码uft8和utf8mb4区别: utf8mb4是utf8的超集,utf8能够支持字符编码范围0x000~0xFFF,
-- 但是后来发现有些汉字和emoji超过了编码番位,编码长度增加至4位,变成utf8mb4,utf8可以直接升级为uft8mb4,不会有出现编码和解码错误的问题。
循环单条插入:
private void batchCrate1() {
for (int i = 0; i < 1000; i++) {
Goods goods = new Goods();
goods.setName("name_1_"+i);
goodsStorage.create(goods);
}
}
mapper文件
<insert id="create" parameterType="com.example.demo.mybatis.model.Goods">
INSERT INTO goods(name)
VALUES (#{name})
</insert>
运行耗时:
分批插入:
private void batchCrate2() {
List<Goods> goodsList = new ArrayList<Goods>(BATCH_CRATE_COUNT);
for (int i = 0; i < BATCH_CRATE_COUNT; i++) {
Goods goods = new Goods();
goods.setName("name_1_"+i);
goodsList.add(goods);
if((i+1)%100 == 0) {
goodsStorage.batchCreate(goodsList);
goodsList = new ArrayList<Goods>(BATCH_CRATE_COUNT);
}
}
goodsStorage.batchCreate(goodsList);
}
mapper文件:
<insert id="batchCreate" parameterType="list" useGeneratedKeys="false">
INSERT INTO goods(name)
VALUES
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.name}
)
</foreach>
</insert>
运行耗时:
整批插入:
private void batchCrate3() {
List<Goods> goodsList = new ArrayList<Goods>(BATCH_CRATE_COUNT);
for (int i = 0; i < BATCH_CRATE_COUNT; i++) {
Goods goods = new Goods();
goods.setName("name_1_"+i);
goodsList.add(goods);
}
goodsStorage.batchCreate(goodsList);
}
运行耗时:
插入时记录重复进行更新:
方式1:insert into table_name(column1,column2,…) values(value1,value2,…)
<insert id="batchCreate" parameterType="list" useGeneratedKeys="false">
INSERT INTO goods(id, name)
VALUES
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.id}, #{item.name}
)
</foreach>
ON DUPLICATE KEY UPDATE
name = values(name)
</insert>
方式2: 一个sql多包含多条语句(不推荐)
<insert id="batchCreate" parameterType="list" useGeneratedKeys="false">
<foreach collection="list" item="item" index="index" separator=";">
INSERT INTO goods
set id = #{item.id}, name = #{item.name}
</foreach>
ON DUPLICATE KEY UPDATE
name = values(name)
</insert>
报错:
SQL []; java.lang.StringIndexOutOfBoundsException: String index out of range: -45; nested exception is java.sql.SQLException: java.lang.StringIndexOutOfBoundsException: String index out of range: -45] with root cause
java.lang.StringIndexOutOfBoundsException: String index out of range: -45
at java.lang.String.substring(String.java:1967) ~[na:1.8.0_321]
at com.mysql.jdbc.PreparedStatement$ParseInfo.extractValuesClause(PreparedStatement.java:441) ~[mysql-connector-java-5.1.44.jar:5.1.44]
at com.mysql.jdbc.PreparedStatement$ParseInfo.buildRewriteBatchedParams(PreparedStatement.java:370) ~[mysql-connector-java-5.1.44.jar:5.1.44]
at com.mysql.jdbc.PreparedStatement$ParseInfo.<init>(PreparedStatement.java:357) ~[mysql-connector-java-5.1.44.jar:5.1.44]
at com.mysql.jdbc.PreparedStatement$ParseInfo.<init>(PreparedStatement.java:174) ~[mysql-connector-java-5.1.44.jar:5.1.44]
at com.mysql.jdbc.PreparedStatement.<init>(PreparedStatement.java:831) ~[mysql-connector-java-5.1.44.jar:5.1.44]
at com.mysql.jdbc.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:45) ~[mysql-connector-java-5.1.44.jar:5.1.44]
at com.mysql.jdbc.JDBC42PreparedStatement.<init>(JDBC42PreparedStatement.java:39) ~[mysql-connector-java-5.1.44.jar:5.1.44]
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_321]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_321]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_321]
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_321]
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.44.jar:5.1.44]
at com.mysql.jdbc.PreparedStatement.getInstance(PreparedStatement.java:761) ~[mysql-connector-java-5.1.44.jar:5.1.44]
at com.mysql.jdbc.ConnectionImpl.clientPrepareStatement(ConnectionImpl.java:1405) ~[mysql-connector-java-5.1.44.jar:5.1.44]
at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4137) ~[mysql-connector-java-5.1.44.jar:5.1.44]
at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4040) ~[mysql-connector-java-5.1.44.jar:5.1.44]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_321]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_321]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_321]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_321]
at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126) ~[tomcat-jdbc-8.5.6.jar:na]
at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108) ~[tomcat-jdbc-8.5.6.jar:na]
at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81) ~[tomcat-jdbc-8.5.6.jar:na]
at com.sun.proxy.$Proxy70.prepareStatement(Unknown Source) ~[na:na]
at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:87) ~[mybatis-3.4.0.jar:3.4.0]
at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88) ~[mybatis-3.4.0.jar:3.4.0]
at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59) ~[mybatis-3.4.0.jar:3.4.0]
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:85) ~[mybatis-3.4.0.jar:3.4.0]
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49) ~[mybatis-3.4.0.jar:3.4.0]
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.4.0.jar:3.4.0]
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) ~[mybatis-3.4.0.jar:3.4.0]
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198) ~[mybatis-3.4.0.jar:3.4.0]
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185) ~[mybatis-3.4.0.jar:3.4.0]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_321]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_321]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_321]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_321]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433) ~[mybatis-spring-1.3.1.jar:1.3.1]
at com.sun.proxy.$Proxy56.insert(Unknown Source) ~[na:na]
at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278) ~[mybatis-spring-1.3.1.jar:1.3.1]
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:56) ~[mybatis-3.4.0.jar:3.4.0]
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53) ~[mybatis-3.4.0.jar:3.4.0]
at com.sun.proxy.$Proxy57.batchCreate(Unknown Source) ~[na:na]
at c
原因分析:每个;
是一条语句,当开启批量操作语句重写开始时,rewriteBatchedStatements=true
,会对该语句进行重写。mysql preparedstatement_MySQL开启RewriteBatchedStatements后PreparedStatement的一个异常。
解决办法:在设置数据库连接设置 rewriteBatchedStatements=true
,批量插入使用insert into table_name ... values...
方式。
使用set方式插入,缺点,只能用于单条语句插入,优点,同时插入的列很多,使用set方式会清晰将列和值进行对齐;使用values方式缺点当列太多时,容易因为列和值顺序不一致出错,优点,可以进行批量插入。
实验遇到问题:
问题1:移动网多次连接获得相同的ip
移动网络、电脑采用动态获取ip无法保证每次连接的ip都一样。可以设置手动设置ip,这样每次连接上的移动网络的ip都是手动设置的。
win10系统将无线网自动获取IP改为固定IP方法
查看可用的ip、网关、DNS和默认网关的快捷方式,win+R
打开cmd,然后输入ipconfig
。
问题2: 两台电脑只能单向ping通
A可以ping通B,B不能ping通A,大概率是A的防火墙没有关闭。
问题3: root未设置密码登陆无法创建数据库
root没有设置密码登陆属于匿名登陆,匿名登陆下不具有创建数据库权限,解决办法,给root设置密码重新密码登陆。root设置或者重置密码
问题4: 无法远程访问mysql
mysql默认没有开启远程访问权限,开启远程访问权限重新连接即可。mysql1130_设置mysql可以被远程连接
总结:
应用程序操作数据库,网络为主要耗时,减少批量操作访问数据库的次数可以有效提高程序运行效率,能写成一条语句的尽量写成一条语句,在数据批量操作复杂的情况下,尽量将多条批量操作发送一个请求,同时打开 rewriteBatchedStatements=true
,默认为false
,让jdbc帮助进行多条sql的重写。