Java教程

MySQL实战中,Insert语句的使用心得总结,Java开发从零开始

本文主要是介绍MySQL实战中,Insert语句的使用心得总结,Java开发从零开始,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

=======================================================================

[](

)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_info DISABLE KEYS;

开启:ALTER TABLE user_info ENABLE 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面试题解析+核心总结学习笔记+最新讲解视频】](

)**

秋招我借这份PDF的复习思路,收获美团,小米,京东等Java岗offer

更多笔记分享

秋招我借这份PDF的复习思路,收获美团,小米,京东等Java岗offer

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)]

这篇关于MySQL实战中,Insert语句的使用心得总结,Java开发从零开始的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!