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

最新推荐文章于 2024-06-04 09:48:22 发布
最新推荐文章于 2024-06-04 09:48:22 发布
阅读量389 收藏 1
点赞数 2
分类专栏： java mysql sql 文章标签： mysql java
java 同时被 3 个专栏收录
31 篇文章 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
    评论
专栏目录
如何利用jdbc快速插入百万条数据
斑斑的博客
11-15 5147
如何利用jdbc快速插入百万条数据当须要向数据库插入百万条数据时，利用hibernate，mybatis等持久层框架时耗时较久，此时使用jdbc插入效率会更高。此种场景特别适用于读取文件导入到数据库。可以利用批处理来加快jdbc的插入效率。String sql = "insert into person(id,name) values(?,?)"; Connection c = Connection
MYSQL批量插入数据的实现代码第1/3页
09-06
本文将深入探讨MySQL批量插入数据的实现原理，并通过一个具体的Java程序示例来展示如何高效地进行批量数据插入。 #### 二、MySQL批量插入数据概述 批量插入是指一次性向数据库表中插入多条记录的操作。相比于单条...
MySQL批量插入数据详解
最新发布
良月柒
06-04 3878
批量插入数据是MySQL中提高插入效率、减少开销的重要手段。本文详细介绍了几种常见的批量插入方法，包括使用单条INSERT语句、命令、存储过程以及使用事务保证原子性等。同时，提供了一些优化和最佳实践建议。
JDBC插入百万数据
kwame211的博客
12-29 355
1 long startTime=System.currentTimeMillis(); 2 3 Connection conn=null; 4 try{ 5 conn=getConnection(); 6 conn.setAutoCommit(false); 7
使用JDBC插入大量数据的性能测试
技术交流
07-28 206
使用JDBC插入大量数据的性能测试 转载至http://tech.sina.com.cn/s/2009-12-09/11281166988.shtml，转载请注明地址，谢谢！       【IT168 技术文档】使用jdbc向数据库插入100000条记录，分别使用statement，PreparedStatement，及PreparedStatement+批处理3种方式进行测试： 　　...
MYSQL开发性能研究之批量插入数据的优化方法
12-16
批量插入数据是一种常见的优化手段，能够显著提升数据插入的效率。本文主要探讨了在MySQL中如何通过批量插入优化性能，对比了几种不同的批量插入方法，并进行了实验验证。 首先，传统的单条插入方式在面对大量数据...
JDBC连接MySQL数据库批量插入数据过程详解
08-25
在本文中，我们提供了一个完整的Java实现代码，展示了JDBC连接MySQL数据库批量插入数据的过程详解。该代码使用FastJSON库读取本地JSON数据，然后使用JDBC连接MySQL数据库批量插入数据。 六、结论 本文详细介绍了...
MySQL实现批量插入以优化性能的教程
12-15
使用MySQL的性能分析工具如`EXPLAIN`和`SHOW PROFILE`，以及性能监控工具如`SHOW STATUS`和`SHOW ENGINE INNODB STATUS`，可以帮助识别性能瓶颈并进行针对性优化。 9. **硬件升级**： 考虑增加内存以缓存更多数据...
MySQL批量SQL插入各种性能优化
weixin_33883178的博客
07-04 177
对于一些数据量较大的系统。数据库面临的问题除了查询效率低下，还有就是数据入库时间长。特别像报表系统，每天花费在数据导入上的时间可能会长达几个小时或十几个小时之久。因此。优化数据库插入性能是非常有意义的。 经过对MySQL innodb的一些性能測试，发现一些能够提高insert效率的方法。供大家參考參考。 1、一条SQL语句插入多条数据。 经常使用的插入语句如： ...
Java使用JDBC向MySQL数据库批次插入10W条数据(测试效率)
08-31
使用JDBC连接MySQL数据库进行数据插入的时候，特别是大批量数据连续插入（100000），如何提高效率呢?今天小编通过本教程给大家介绍下
java中JDBC实现往MySQL插入百万级数据的实例代码
08-31
主要介绍了java中JDBC实现往MySQL插入百万级数据的实例代码，具有一定的参考价值，感兴趣的小伙伴们可以参考一下。
JDBC 导入MYSQL驱动方法
02-24
解决JDBC异常:Exception in thread "main" java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
mysql 语句 批量查询
u010805100的专栏
04-03 937
&lt;?php /** * 使用 mysqli 的mysqli::multi_query()一次性 查询并 显示 * users表的结构 和 内容！ * * * *///第一步： 取得mysqli对象$mysqli = new mysqli('localhost','root','love','test'); if($mysqli-&gt;connect_error){die(...
mysql百级数据写入_JDBC实现往MySQL插入百万级数据
weixin_34160181的博客
01-21 168
想往某个表中插入几百万条数据做下测试，原先的想法，直接写个循环10W次随便插入点数据试试吧，好吧，我真的很天真....DROP PROCEDURE IF EXISTS proc_initData;--如果存在此存储过程则删掉DELIMITER $CREATE PROCEDUREproc_initData()BEGINDECLARE i INT DEFAULT 1;WHILE i<=10000...
JDBC多线程插入大量数据.md
小布1994的博客
12-08 693
package com.xiaobu.base.util; import com.google.common.util.concurrent.ThreadFactoryBuilder; import lombok.extern.slf4j.Slf4j; import java.sql.Connection; import java.sql.PreparedStatement; import java.util.concurrent.*; import java.util.concurrent.atomi
jdbc连接中间库mysql。插入数据
沉淀的博客
01-15 396
public void addHousefeedbackcount(HouseFeedbackCount houseFeedbackCount){ Connection conn = null; String url = System.getProperty("yuncai.jdbcurl"); try { Class.forName("com.mysql.
mysql 批量插入千条数据 语句 怎么实现
04-28
你可以使用 MySQL 中的 INSERT INTO 语句来批量插入千条数据。具体实现方法如下： ...注意：批量插入数据时，需要注意数据的类型和格式，确保数据的正确性。同时，不建议一次插入过多数据，以免影响数据库性能。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值