JDBC实现往MySQL插入百万级数据

最新推荐文章于 2024-04-20 00:18:17 发布
最新推荐文章于 2024-04-20 00:18:17 发布
阅读量1k 收藏 22
点赞数 23
分类专栏： 后端技术 文章标签： mysql android 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ximaiyao1984/article/details/135088638
版权

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

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分钟能搞定吧

在这里插入图片描述

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

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

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

输出结果：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,用时：3901OK,用时：3343OK,用时：44242OK,用时：39798
OK,用时：4142OK,用时：2949OK,用时：44248OK,用时：39959
OK,用时：3664OK,用时：2689OK,用时：44389OK,用时：39367

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

预编译+批量操作

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

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

如：

private String url = "jdbc:mysql://localhost:3306/test01?useServerPrepStmts=true&rewriteBatchedStatements=true"
讓丄帝愛伱
关注
专栏目录
如何利用jdbc快速插入百万条数据
斑斑的博客
11-15 5158
如何利用jdbc快速插入百万条数据当须要向数据库插入百万条数据时，利用hibernate，mybatis等持久层框架时耗时较久，此时使用jdbc插入效率会更高。此种场景特别适用于读取文件导入到数据库。可以利用批处理来加快jdbc的插入效率。String sql = "insert into person(id,name) values(?,?)"; Connection c = Connection
jdbc批量插入的4种方式【百万条数据插入只需几秒】
m0_55613022的博客
06-08 631
jar包准备 引入依赖【不知道什么是依赖的可查看Maven的配置与使用】 方式一：普通插入 用时：62分钟多 数据库插入结果 方式二：使用批处理插入 用时 用时：【10秒左右】 用时：【4秒左右】 * ps.addBatch(); 将sql语句打包到一个容器中 * ps.executeBatch(); 将容器中的sql语句提交 * ps.clearBatch(); 清空容器，为下一次打包做准备...
jdbc插入百万级数据
qq_19396391的博客
04-12 2058
1.背景介绍 快速插入百万级数据 我们一般用JDBC向数据库插入数据时，是用statement或preparedstatment中的update或execute方法来实现，但当要插入的数据为百万级别甚至千万级别时，这种方法显然无法满足我们的需求，可以尝试写一个循环,循环以百万次statement.excute(sql)方法，我这边测试是半个小时还没有插完。 缺点:一条一条的s...
SpringBoot高效批量插入百万数据
seeyouagain_的博客
02-05 4195
前言：我相信很多小伙伴和我一样在初学的时候，面对几万几十万数据插入，不知道如何下手，面对百万级别数据时更是不知所措，我们大部分初学者，很多人都喜欢for循环插入数据，或者是开启多个线程，然后分批使用for循环插入，当我们需要将大量数据存储到数据库中时，传统的逐条插入方式显然效率低下，并且容易导致性能瓶颈。总结：经过上面的示例演示我们心里已经有谱了，知道什么时候该使用哪一种数据插入方式，针对对不同线程数的测试，发现不是线程数越多越好，具体多少合适，通常的算法：CPU核心数量*2 +2 个线程。
JDBC连接MySQL数据库----以插入数据为例
最新发布
2201_75652367的博客
04-20 1281
JDBC，全称为Java Database Connectivity，是Java语言中用来规范客户端程序如何访问数据库的一套API（应用程序编程接口），它是用于执行SQL语句的Java API，允许应用程序与各种数据库建立连接并进行操作。
JDBC连接MySQL数据库批量插入数据过程详解
08-25
在本文中，我们提供了一个完整的Java实现代码，展示了JDBC连接MySQL数据库批量插入数据的过程详解。该代码使用FastJSON库读取本地JSON数据，然后使用JDBC连接MySQL数据库批量插入数据。 六、结论 本文详细介绍了...
Java使用JDBC向MySQL数据库批次插入10W条数据(测试效率)
08-31
在Java开发中，当需要向MySQL数据库大量插入数据时，使用JDBC的批量处理功能可以显著提高效率。本文将详细讲解如何使用JDBC批量插入10万条数据，并探讨其背后的原理和优化策略。 批量处理在JDBC中主要通过Statement...
教你如何6秒钟往MySQL插入100万条数据的实现
09-08
本文将详细解析如何在6秒钟内向MySQL数据库中插入100万条数据的实现方法。以下将围绕标题和描述中的关键点展开讨论。 首先，要实现快速插入，我们需要使用`PreparedStatement`对象。`PreparedStatement`是SQL语句的...
JDBC链接mysql插入数据后显示问号的原因及解决办法
01-19
1.在cmd中进入mysql查看默认的编码格式: mysql> show variables like “%char%”; 若不是utf8(因为我用的是utf8)，关掉mysql服务，在my.ini中添加 [client] default-character-set=utf8 [mysqld] default-storage...
MySQL + JDBC 实现批量插入（千万数据）
Java是世界上最好的语言
12-08 5962
1. 配置my.ini文件 bulk_insert_buffer_size=120M 或者更大 将insert语句的长度设为最大。 Max_allowed_packet=1M Net_buffer_length=8k 可以通过navicat直接查询修改： SHOW VARIABLES where Variable_name = 'bulk_insert_buffer_size' set bulk_insert_buffer_size = 104857600 2. 查看设置的参选有没有生效
mysql快速插入大量数据
热门推荐
m0_58160096的博客
07-26 1万+
最近做性能测试，需要模拟生产环境的数据量，在插入mysql百万、千万级数据的时候发现直接使用插入sql效率极低，百度了一翻，找到几种方式，但用下来还是有很快速的方式，推荐第四种。
【JDBC】-- Java向数据库批量插入数据操作
张修宇的博客
07-22 1万+
在Java项目中可能会出现大量向数据库中插入的情况，下面有四种方式实现批量插入，四种方式效率由低到高、循序渐进。下面使用idea+Mysql8。
数据库批量插入数据的三种方法
qq_59837950的博客
11-07 7138
数据库（MySQL）批量插入数据的三种方法，对比起来，速度起飞！！！
13 秒插入 30 万条数据，这才是批量插入正确的姿势！
Java技术栈，分享最主流的Java技术
07-28 684
点击关注公众号，Java干货及时送达国内最强微服务框架，没有之一！几乎覆盖 Spring Boot 所有操作！2023全新 Java面试题（2500＋）本文主要讲述通过MyBatis、JDBC等做大数据量数据插入的案例和结果。30万条数据插入插入数据库验证实体类、mapper和配置文件定义User实体mapper接口mapper.xml文件jdbc.propertiessqlMapCon...
批量插入百万数量级的数据到mysql的解决方案
haohao_ding的博客
10-22 3349
项目场景：需要从上报的单个文件中解析出百万数据入库，项目中无论是使用jpa 还是 mybatis 存入数据达到10000时速度明显变慢，达到100000时就让人难以接受。所以就考虑使用存储过程或者是使用原生jdbc实现，该案例使用原生jdbc实现。 单线程实现案例： import lombok.Data; import lombok.extern.slf4j.Slf4j; import...
mysql百万级数据批量插入与更新
weixin_45248225的博客
12-26 1143
这个方法的主要目的是使用Spring的JdbcTemplate来批量插入数据到数据库中
JDBC多线程插入大量数据.md
小布1994的博客
12-08 710
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
Java JDBC百万级数据MySQL插入实例与优化
首先，文章提到了一个不推荐的简单做法，即创建一个存储过程，通过循环10万次来逐条插入数据。这种方式不仅耗时且效率极低，作者亲身经历了一个明显的延迟，即使只插入10万条数据，也耗时过长。这表明单条插入的瓶颈...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

讓丄帝愛伱

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值