mysql批量插入数据实现语句性能分析

最新推荐文章于 2024-07-25 17:06:50 发布
最新推荐文章于 2024-07-25 17:06:50 发布
阅读量386 收藏 1
点赞数 2
分类专栏： java mysql sql 文章标签： mysql java
java 同时被 3 个专栏收录
22 篇文章 0 订阅
订阅专栏
mysql
4 篇文章 0 订阅
订阅专栏
sql
3 篇文章 0 订阅
订阅专栏

想往某个表中插入几百万条数据做下测试，

原先的想法，直接写个循环10W次随便插入点数据试试吧，好吧，我真的很天真....

DROP PROCEDURE IF EXISTS proc_initData;--如果存在此存储过程则删掉
DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i<=100000 DO
        INSERT INTO text VALUES(i,CONCAT('姓名',i),'XXXXXXXXX');
        SET i = i+1;
    END WHILE;
END $
CALL proc_initData();

执行CALL proc_initData()后，本来想想，再慢10W条数据顶多30分钟能搞定吧，结果我打了2把LOL后，回头一看，还在执行，此时心里是彻底懵逼的....待我打完第三把结束后，终于执行完了，这种方法若是让我等上几百万条数据，是不是早上去上班，下午下班回来还没结束呢？10W条数据，有图有真相

JDBC往数据库中普通插入方式

后面查了一下，使用JDBC批量操作往数据库插入100W+的数据貌似也挺快的，

先来说说JDBC往数据库中普通插入方式，简单的代码大致如下，循环了1000条，中间加点随机的数值，毕竟自己要拿数据测试，数据全都一样也不好区分

 

private String url = "jdbc:mysql://localhost:3306/test01";
    private String user = "root";
    private String password = "123456";
    @Test
    public void Test(){
        Connection conn = null;
        PreparedStatement pstm =null;
        ResultSet rt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);        
            String sql = "INSERT INTO userinfo(uid,uname,uphone,uaddress) VALUES(?,CONCAT('姓名',?),?,?)";
            pstm = conn.prepareStatement(sql);
            Long startTime = System.currentTimeMillis();
            Random rand = new Random();
            int a,b,c,d;
            for (int i = 1; i <= 1000; i++) {
                    pstm.setInt(1, i);
                    pstm.setInt(2, i);
                    a = rand.nextInt(10);
                    b = rand.nextInt(10);
                    c = rand.nextInt(10);
                    d = rand.nextInt(10);
                    pstm.setString(3, "188"+a+"88"+b+c+"66"+d);
                    pstm.setString(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);27                     pstm.executeUpdate();
            }
            Long endTime = System.currentTimeMillis();
            System.out.println("OK,用时：" + (endTime - startTime)); 
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
            if(pstm!=null){
                try {
                    pstm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
        }
    }

输出结果：OK,用时：738199，单位毫秒，也就是说这种方式与直接数据库中循环是差不多的。

在讨论批量处理之前，先说说遇到的坑，首先，JDBC连接的url中要加rewriteBatchedStatements参数设为true是批量操作的前提，其次就是检查mysql驱动包时候是5.1.13以上版本（低于该版本不支持），因网上随便下载了5.1.7版本的，然后执行批量操作（100W条插入），结果因为驱动器版本太低缘故并不支持，导致停止掉java程序后，mysql还在不断的往数据库中插入数据，最后不得不停止掉数据库服务才停下来...

那么低版本的驱动包是否对100W+数据插入就无力了呢？实际还有另外一种方式，效率相比来说还是可以接受的。

使用事务提交方式

先将命令的提交方式设为false，即手动提交conn.setAutoCommit(false);最后在所有命令执行完之后再提交事务conn.commit();

private String url = "jdbc:mysql://localhost:3306/test01";
    private String user = "root";
    private String password = "123456";
    @Test
    public void Test(){
        Connection conn = null;
        PreparedStatement pstm =null;
        ResultSet rt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);        
            String sql = "INSERT INTO userinfo(uid,uname,uphone,uaddress) VALUES(?,CONCAT('姓名',?),?,?)";
            pstm = conn.prepareStatement(sql);
            conn.setAutoCommit(false);
            Long startTime = System.currentTimeMillis();
            Random rand = new Random();
            int a,b,c,d;
            for (int i = 1; i <= 100000; i++) {
                    pstm.setInt(1, i);
                    pstm.setInt(2, i);
                    a = rand.nextInt(10);
                    b = rand.nextInt(10);
                    c = rand.nextInt(10);
                    d = rand.nextInt(10);
                    pstm.setString(3, "188"+a+"88"+b+c+"66"+d);
                    pstm.setString(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);
                    pstm.executeUpdate();
            }
            conn.commit();
            Long endTime = System.currentTimeMillis();
            System.out.println("OK,用时：" + (endTime - startTime)); 
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
            if(pstm!=null){
                try {
                    pstm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
        }
    }

 

以上代码插入10W条数据，输出结果：OK,用时：18086，也就十八秒左右的时间，理论上100W也就是3分钟这样，勉强还可以接受。

批量处理

接下来就是批量处理了，注意，一定要5.1.13以上版本的驱动包。

private String url = "jdbc:mysql://localhost:3306/test01?rewriteBatchedStatements=true";
    private String user = "root";
    private String password = "123456";
    @Test
    public void Test(){
        Connection conn = null;
        PreparedStatement pstm =null;
        ResultSet rt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);        
            String sql = "INSERT INTO userinfo(uid,uname,uphone,uaddress) VALUES(?,CONCAT('姓名',?),?,?)";
            pstm = conn.prepareStatement(sql);
            Long startTime = System.currentTimeMillis();
            Random rand = new Random();
            int a,b,c,d;
            for (int i = 1; i <= 100000; i++) {
                    pstm.setInt(1, i);
                    pstm.setInt(2, i);
                    a = rand.nextInt(10);
                    b = rand.nextInt(10);
                    c = rand.nextInt(10);
                    d = rand.nextInt(10);
                    pstm.setString(3, "188"+a+"88"+b+c+"66"+d);
                    pstm.setString(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);
                    pstm.addBatch();
            }
            pstm.executeBatch();
            Long endTime = System.currentTimeMillis();
            System.out.println("OK,用时：" + (endTime - startTime)); 
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
            if(pstm!=null){
                try {
                    pstm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
        }
    }

10W输出结果：OK,用时：3386，才3秒钟.

批量操作+事务

然后我就想，要是批量操作+事务提交呢？会不会有神器的效果？

private String url = "jdbc:mysql://localhost:3306/test01?rewriteBatchedStatements=true";
    private String user = "root";
    private String password = "123456";
    @Test
    public void Test(){
        Connection conn = null;
        PreparedStatement pstm =null;
        ResultSet rt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);        
            String sql = "INSERT INTO userinfo(uid,uname,uphone,uaddress) VALUES(?,CONCAT('姓名',?),?,?)";
            pstm = conn.prepareStatement(sql);
            conn.setAutoCommit(false);
            Long startTime = System.currentTimeMillis();
            Random rand = new Random();
            int a,b,c,d;
            for (int i = 1; i <= 100000; i++) {
                    pstm.setInt(1, i);
                    pstm.setInt(2, i);
                    a = rand.nextInt(10);
                    b = rand.nextInt(10);
                    c = rand.nextInt(10);
                    d = rand.nextInt(10);
                    pstm.setString(3, "188"+a+"88"+b+c+"66"+d);
                    pstm.setString(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);
                    pstm.addBatch();
            }
            pstm.executeBatch();
            conn.commit();
            Long endTime = System.currentTimeMillis();
            System.out.println("OK,用时：" + (endTime - startTime)); 
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
            if(pstm!=null){
                try {
                    pstm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
        }
    }

以下是100W数据输出对比：（5.1.17版本MySql驱动包下测试，交替两种方式下的数据测试结果对比）

批量操作(10W)批量操作+事务提交(10W)批量操作(100W)批量错作+事务提交(100W)

OK,用时：3901

OK,用时：3343

OK,用时：44242

OK,用时：39798

OK,用时：4142

OK,用时：2949

OK,用时：44248

OK,用时：39959

OK,用时：3664

OK,用时：2689

OK,用时：44389

OK,用时：39367

可见有一定的效率提升，但是并不是太明显，当然因为数据差不算太大，也有可能存在偶然因数，毕竟每项只测3次。

预编译+批量操作

网上还有人说使用预编译+批量操作的方式能够提高效率更明显，但是本人亲测，效率不高反降，可能跟测试的数据有关吧。

预编译的写法，只需在JDBC的连接url中将写入useServerPrepStmts=true即可，

如：

private String url = "jdbc:mysql://localhost:3306/test01?useServerPrepStmts=true&rewriteBatchedStatements=true"

Conclusion：
普通插入方式：也就是每次发送一次insert ...；数据库操作本身就是文件操作，频繁的文件操作是很费时的，（频繁打开关闭数据库文件）。
事务方式：把所有的insert都整成一个事务，等到事务提交commit的时候一次性的写入数据库，数据库文件只被打开关闭一次，效率肯定大大的提高。
批量操作：最终生成的是一条insert语句，如insert into table values(...),(...),...;这种方式比事务{insert；insert；...}是快很多的，为什么快很多得看看数据库的插入原理了。

双普拉斯
关注
  • 2
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
C++向mysql批量插入数据
小米的修行之路
04-14 3852
一、以下共统计了3种批量插入的方法： 1、普通方法：一条一条插入； 2、使用拼接语句一次性插入，拼接语句的语法如下： insert into table (col1，col2，col3) values (’a’，’b‘，’c‘)， (’a1’，’b1‘，’c1‘)，(’a2‘，’b2‘，’c2'),...... 对于拼接语句sql有一个长度限制:max_allowed_packet,查看限制最大值：show variables like ‘%max_allowed_packet%’，使用Navic
mysql批量添加数据sql语句_MYSQL批量插入数据库实现语句性能分析
weixin_39997311的博客
01-19 4461
https://www.cnblogs.com/caicaizi/p/5849979.html假定我们的表结构如下代码如下CREATE TABLE example (example_id INT NOT NULL,name VARCHAR( 50 ) NOT NULL,value VARCHAR( 50 ) NOT NULL,other_value VARCHAR( 50 ) NOT NULL)通...
mysql 批量插入 性能_MySQL批量插入数据性能比较
weixin_39722759的博客
01-19 763
目前遇到比较多的开发同学来问有关insert批量插入相关的事情，现在测试了下两种批量insert写法的效率。本次测试只比较相同环境下insert单条插入和合并插入的效率问题，不考虑类似“机器性能对mysql插入的影响”或者“每秒能插多少条数据”这种课题表结构如下root:test> show create table t_insert_single\G********************...
MySQL批量插入数据详解
良月柒
06-04 2257
批量插入数据是MySQL中提高插入效率、减少开销的重要手段。本文详细介绍了几种常见的批量插入方法，包括使用单条INSERT语句、命令、存储过程以及使用事务保证原子性等。同时，提供了一些优化和最佳实践建议。
MySQL批量插入sql语句性能优化
故常无，欲以观其妙；常有，欲以观其徼；
04-18 1058
MySQL批量插入性能优化
MYSQL批量插入数据库实现语句性能分析
铁柱的博客
11-27 1277
mysql批量插入数据库的时间比较，以及如何优化
mysql批量插入数据
热门推荐
抱猫人的博客
07-23 2万+
在此还记录了一些mysql基础知识，可以用作参考 优化方案 插入语句 普通使用的插入语句： insert into table (col1，col2，col3) values (’ 1’，’ John ‘，’ stu ‘)； insert into table (col1，col2，col3) values (’ 2’，’ Jan ‘，’ stu ‘)； insert into table (col1，col2，col3) values (’ 3 ‘，’ Billy ‘，’ stu ')； 插入操作时
mysql插入500条数据sql语句
牛肉胡辣汤
05-23 1015
在实际应用中，需要替换示例代码中的数据库连接信息和表字段信息，并确保数据的格式和顺序与目标表匹配。这样的批量插入操作可以有效地提高数据插入的效率，特别是在需要处理大量数据时非常实用。替换为你要插入数据的目标表名，并确保列名和数值的顺序和类型与目标表匹配。这种批量插入数据的方法能够提高数据插入的效率，特别是在需要大量数据初始化或导入时非常实用。库建立了与MySQL数据库的连接，然后准备了500条员工信息的数据，存储在一个列表。接着，我们构造了插入数据的SQL语句，使用。在上面的示例代码中，我们首先使用。
MySQL 插入10万条数据性能分析
Ezrealwj的博客
02-17 1894
笔者想复现一个索引失效的场景，故需要一定规模的数据作支撑，所以需要向数据库中插入大约一百万条数据。那问题就来了，我们应该怎样插入才能使插入的速度最快呢？为了更加贴合实际，下面的演示只考虑使用 Mybaits 作为 ORM 框架的情况，不使用原生的 JDBC。下面，我们只向数据库中插入十万条数据作为演示。
MySQL批量插入数据的四种方案（性能测试对比）
好好学java
11-16 1635
本文记录个人使用MySQL插入大数据总结较实用的方案，通过对常用插入大数据的4种方式进行测试，即for循环单条、拼接SQL、批量插入saveBatch()、循环 + 开启批处理模式，得出比较实用的方案心得。一、前言最近趁空闲之余，在对MySQL数据库进行插入数据测试，对于如何快速插入数据的操作无从下手，在仅1W数据量的情况下，竟花费接近47s，实在不忍直视！在不断摸索之后，整理出一些较实用的方案。...
MYSQL批量插入数据的实现代码第1/3页
09-06
本文将深入探讨MySQL批量插入数据的实现原理，并通过一个具体的Java程序示例来展示如何高效地进行批量数据插入。 #### 二、MySQL批量插入数据概述 批量插入是指一次性向数据库表中插入多条记录的操作。相比于单条...
JDBC连接MySQL数据库批量插入数据过程详解
08-25
在本文中，我们提供了一个完整的Java实现代码，展示了JDBC连接MySQL数据库批量插入数据的过程详解。该代码使用FastJSON库读取本地JSON数据，然后使用JDBC连接MySQL数据库批量插入数据。 六、结论 本文详细介绍了...
MYSQL开发性能研究之批量插入数据的优化方法
12-16
批量插入数据是一种常见的优化手段，能够显著提升数据插入的效率。本文主要探讨了在MySQL中如何通过批量插入优化性能，对比了几种不同的批量插入方法，并进行了实验验证。 首先，传统的单条插入方式在面对大量数据...
MySQL大量数据插入各种方法性能分析与比较
12-14
本文将对几种常见的数据插入策略进行分析和比较，包括单条插入、批量插入、事务处理和使用`LOAD DATA INFILE`命令。 首先，了解MySQL插入一条记录的时间构成有助于我们理解优化的必要性。插入过程涉及连接、发送...
mysql高阶语句
qq_59169480的博客
07-24 922
高级语法的查询语句。
MySQL的存储过程
最新发布
2402_84844434的博客
07-25 1165
完成特定功能的sql语句的集合。把定义好的sql集合在一个特定的sql函数中每次执行调用函数即可。还可以实现传参的调用。
mysql中where与on区别
m0_63227888的博客
07-24 765
使用ON子句来定义表之间的连接条件。使用WHERE子句来过滤查询结果，或者作为连接后结果的进一步筛选条件。在进行表连接时，应优先使用ON子句来指定连接条件，因为这样可以提高查询效率，避免不必要的全表扫描或笛卡尔积。WHERE子句在连接操作后使用，用于过滤连接后的结果集。
Mysql重大更新，三个版本遭下架！
xiaofan23z的专栏
07-24 718
个时就会触发这个重大bug，7/23日mysql发布重要更新，发布了如下三个版本。书接上文，7/11日开源数据库软件服务商percona发布重要警告，最新的。后面官方确认，确实存在重大bug，但是不是表超过10000个，而是超过。当mysql表超过10000张时重启可能会崩溃。
mysql 批量插入千条数据 语句 怎么实现
04-28
你可以使用 MySQL 中的 INSERT INTO 语句来批量插入千条数据。具体实现方法如下： ...注意：批量插入数据时，需要注意数据的类型和格式，确保数据的正确性。同时，不建议一次插入过多数据，以免影响数据库性能。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值