=======================================================================
[](
)2-1、三种处理方式
[](
)2-1-1、单条循环插入
我们取10w条数据进行了一些测试，如果插入方式为程序遍历循环逐条插入。在mysql上检测插入一条的速度在0.01s到0.03s之间。
逐条插入的平均速度是0.02*100000，也就是33分钟左右。
下面代码是测试例子：
1普通循环插入100000条数据的时间测试
@Test
public void insertUsers1() {
User user = new User();
user.setUserName("提莫队长");
user.setPassword("正在送命");
user.setPrice(3150);
user.setHobby("种蘑菇");
for (int i = 0; i < 100000; i++) {
user.setUserName("提莫队长" + i);
// 调用插入方法
userMapper.insertUser(user);
}
}
执行速度是30分钟也就是0.018*100000的速度。可以说是很慢了
发现逐条插入优化成本太高。然后去查询优化方式。发现用批量插入的方法可以显著提高速度。
将100000条数据的插入速度提升到1-2分钟左右↓
[](
)2-1-2、修改SQL语句批量插入
insert into user_info （user_id,username,password,price,hobby）
values （null,'提莫队长1','123456',3150,'种蘑菇'）,(null,'盖伦','123456',450,'踩蘑菇');
用批量插入插入100000条数据，测试代码如下：
@Test
public void insertUsers2() {
List<User> list= new ArrayList<User>();
User user = new User();
user.setPassword("正在送命");
user.setPrice(3150);
user.setHobby("种蘑菇");
for (int i = 0; i < 100000; i++) {
user.setUserName("提莫队长" + i);
// 将单个对象放入参数list中
list.add(user);
}
userMapper.insertListUser(list);
}
批量插入使用了0.046s 这相当于插入一两条数据的速度，所以用批量插入会大大提升数据插入速度，当有较大数据插入操作是用批量插入优化
批量插入的写法：
dao定义层方法:
Integer insertListUser(List<User> user);
mybatis Mapper中的sql写法：
<insert id="insertListUser" parameterType="java.util.List">
INSERT INTO `db`.`user_info`
( `id`,
`username`,
`password`,
`price`,
`hobby`)
values
<foreach collection="list" item="item" separator="," index="index">
(null,
#{item.userName},
#{item.password},
#{item.price},
#{item.hobby})
</foreach>
</insert>
这样就能进行批量插入操作：
注：但是当批量操作数据量很大的时候。例如我插入10w条数据的SQL语句要操作的数据包超过了1M，MySQL会报如下错:
报错信息：
Mysql You can change this value on the server by setting the max_allowed_packet' variable. Packet for query is too large (6832997 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.
解释：
用于查询的数据包太大（6832997> 1048576）。 您可以通过设置max_allowed_packet的变量来更改服务器上的这个值。
通过解释可以看到用于操作的包太大。这里要插入的SQL内容数据大小为6M 所以报错。
解决方法：
数据库是MySQL57，查了一下资料是MySQL的一个系统参数问题:
max_allowed_packet，其默认值为1048576(1M)，
查询：
show VARIABLES like '%max_allowed_packet%';
修改此变量的值：MySQL安装目录下的my.ini(windows)或/etc/mysql.cnf(linux) 文件中的[mysqld]段中的
max_allowed_packet = 1M，如更改为20M(或更大，如果没有这行内容，增加这一行)，如下图
保存，重启MySQL服务。现在可以执行size大于1M小于20M的SQL语句了。
但是如果20M也不够呢？
[](
)2-1-3、分批量多次循环插入
如果不方便修改数据库配置或需要插入的内容太多时，也可以通过后端代码控制，比如插入10w条数据，分100批次每次插入1000条即可，也就是几秒钟而已；当然，如果每条的内容很多的话，另说。。
[](
)2-2、插入速度慢的其他几种优化途径
A、通过show processlist;命令，查询是否有其他长进程或大量短进程抢占线程池资源 ？看能否通过把部分进程分配到备库从而减轻主库压力；或者，先把没用的进程kill掉一些？（手动挠头o_O）
B、大批量导数据，也可以先关闭索引，数据导入完后再打开索引
关闭：ALTER TABLE
user_infoDISABLE KEYS;
开启：ALTER TABLE
user_infoENABLE KEYS;
[](
)三、REPLACE INTO语法的“坑”
===================================================================================
上面曾提到REPLACE可能影响3条以上的记录，这是因为在表中有超过一个的唯一索引。在这种情况下，REPLACE将考虑每一个唯一索引，并对每一个索引对应的重复记录都删除，然后插入这条新记录。假设有一个table1表，有3个字段a, b, c。它们都有一个唯一索引，会怎么样呢？我们早一些数据测试一下。
-- 测试表创建，a,b,c三个字段均有唯一索引
CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE);
-- 插入三条测试数据
INSERT into table1 VALUES(1,1,1);
INSERT into table1 VALUES(2,2,2);
INSERT into table1 VALUES(3,3,3);
读者福利
**[CodeChina开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频】](
)**
更多笔记分享
NULL UNIQUE,c INT NOT NULL UNIQUE);
– 插入三条测试数据
INSERT into table1 VALUES(1,1,1);
INSERT into table1 VALUES(2,2,2);
INSERT into table1 VALUES(3,3,3);
# **读者福利**
**[CodeChina开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频】](
)**
[外链图片转存中...(img-Bl9hbS8u-1631177794260)]
**更多笔记分享**
[外链图片转存中...(img-Z6SQZX1Y-1631177794262)]