1亿条数据批量插入 MySQL，哪种方式最快_终码一生的博客

1、先上Mysql数据库，随机生成的人员数据图。分别是ID、姓名、性别、年龄、Email、电话、住址。

下图一共三千三百万数据：
在数据量在亿级别时，别点下面按钮，会导致Navicat持续加载这亿级别的数据，导致电脑死机。~觉着自己电脑配置不错的可以去试试，可能会有惊喜
2、本次测评一共通过三种策略，五种情况，进行大批量数据插入测试

策略分别是：
Mybatis 轻量级框架插入（无事务）
采用JDBC直接处理（开启事务、无事务）
采用JDBC批处理（开启事务、无事务）
测试结果：
Mybatis轻量级插入 -> JDBC直接处理 -> JDBC 批处理。
JDBC 批处理，效率最高
第一种策略测试：

2.1 Mybatis 轻量级框架插入（无事务）
Mybatis是一个轻量级框架，它比hibernate轻便、效率高。
但是处理大批量的数据插入操作时，需要过程中实现一个ORM的转换，本次测试存在实例，以及未开启事务，导致mybatis效率很一般。
这里实验内容是：
利用Spring框架生成mapper实例、创建人物实例对象
循环更改该实例对象属性、并插入。
//代码内无事务
 private long begin = 33112001;//起始id
    private long end = begin+100000;//每次循环插入的数据量
    private String url = "jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&amp;characterEncoding=UTF-8";
    private String user = "root";
    private String password = "0203";
@org.junit.Test
    public void insertBigData2()
        //加载Spring，以及得到PersonMapper实例对象。这里创建的时间并不对最后结果产生很大的影响
        ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
        PersonMapper pMapper = (PersonMapper) context.getBean("personMapper");
        //创建一个人实例
        Person person = new Person();
        //计开始时间
        long bTime = System.currentTimeMillis();
        //开始循环，循环次数500W次。
        for(int i=0;i<5000000;i++)
            //为person赋值
            person.setId(i);
            person.setName(RandomValue.getChineseName());
            person.setSex(RandomValue.name_sex);
            person.setAge(RandomValue.getNum(1, 100));
            person.setEmail(RandomValue.getEmail(4,15));
            person.setTel(RandomValue.getTel());
            person.setAddress(RandomValue.getRoad());
            //执行插入语句
            pMapper.insert(person);
            begin++;
        //计结束时间
        long eTime = System.currentTimeMillis();
        System.out.println("插入500W条数据耗时："+(eTime-bTime));
本想测试插入五百万条数据，但是实际运行过程中太慢，中途不得不终止程序。最后得到52W数据，大约耗时两首歌的时间（7~9分钟）。随后，利用mybatis向mysql插入10000数据。 
结果如下： 
 利用mybatis插入 一万 条数据耗时：28613，即28.6秒 
第二种策略测试： 
2.2 采用JDBC直接处理（开启事务、关闭事务） 
采用JDBC直接处理的策略，这里的实验内容分为开启事务、未开启事务是两种，过程均如下： 
 利用PreparedStatment预编译
 
 循环，插入对应数据，并存入
  
事务对于插入数据有多大的影响呢？ 看下面的实验结果: 
//该代码为开启事务
 private long begin = 33112001;//起始id
    private long end = begin+100000;//每次循环插入的数据量
    private String url = "jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&amp;characterEncoding=UTF-8";
    private String user = "root";
    private String password = "0203";
@org.junit.Test
    public void insertBigData3() {
        //定义连接、statement对象
        Connection conn = null;
        PreparedStatement pstm = null;
        try {
            //加载jdbc驱动
            Class.forName("com.mysql.jdbc.Driver");
            //连接mysql
            conn = DriverManager.getConnection(url, user, password);
             //将自动提交关闭
             conn.setAutoCommit(false);
            //编写sql
            String sql = "INSERT INTO person VALUES (?,?,?,?,?,?,?)";
            //预编译sql
            pstm = conn.prepareStatement(sql);
            //开始总计时
            long bTime1 = System.currentTimeMillis();
            //循环10次，每次一万数据，一共10万
            for(int i=0;i<10;i++) {
                //开启分段计时，计1W数据耗时
                long bTime = System.currentTimeMillis();
                //开始循环
                while (begin < end) {
                    pstm.setLong(1, begin);
                    pstm.setString(2, RandomValue.getChineseName());
                    pstm.setString(3, RandomValue.name_sex);
                    pstm.setInt(4, RandomValue.getNum(1, 100));
                    pstm.setString(5, RandomValue.getEmail(4, 15));
                    pstm.setString(6, RandomValue.getTel());
                    pstm.setString(7, RandomValue.getRoad());
                    //执行sql
                    pstm.execute();
                    begin++;
                //提交事务
                conn.commit();
                //边界值自增10W
                end += 10000;
                //关闭分段计时
                long eTime = System.currentTimeMillis();
                System.out.println("成功插入1W条数据耗时："+(eTime-bTime));
            //关闭总计时
            long eTime1 = System.currentTimeMillis();
            System.out.println("插入10W数据共耗时："+(eTime1-bTime1));
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e1) {
            e1.printStackTrace();
1、我们首先利用上述代码测试无事务状态下，插入10W条数据需要耗时多少。 
成功插入1W条数据耗时：21603
成功插入1W条数据耗时：20537
成功插入1W条数据耗时：20470
成功插入1W条数据耗时：21160
成功插入1W条数据耗时：23270
成功插入1W条数据耗时：21230
成功插入1W条数据耗时：20372
成功插入1W条数据耗时：22608
成功插入1W条数据耗时：20361
成功插入1W条数据耗时：20494
插入10W数据共耗时：212106
实验结论如下： 
 在未开启事务的情况下，平均每 21.2 秒插入 一万 数据。 
接着我们测试开启事务后，插入十万条数据耗时，如图： 
成功插入1W条数据耗时：4938
成功插入1W条数据耗时：3518
成功插入1W条数据耗时：3713
成功插入1W条数据耗时：3883
成功插入1W条数据耗时：3872
成功插入1W条数据耗时：3873
成功插入1W条数据耗时：3863
成功插入1W条数据耗时：3819
成功插入1W条数据耗时：3933
成功插入1W条数据耗时：3811
插入10W数据共耗时：39255
实验结论如下： 
 开启事务后，平均每 3.9 秒插入 一万 数据 
第三种策略测试： 
2.3 采用JDBC批处理（开启事务、无事务） 
采用JDBC批处理时需要注意一下几点： 
1、在URL连接时需要开启批处理、以及预编译 
String url = “jdbc:mysql://localhost:3306/User?rewriteBatched
-Statements=true&useServerPrepStmts=false”;
2、PreparedStatement预处理sql语句必须放在循环体外 
代码如下： 
private long begin = 33112001;//起始id
private long end = begin+100000;//每次循环插入的数据量
private String url = "jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&amp;characterEncoding=UTF-8";
private String user = "root";
private String password = "0203";
@org.junit.Test
public void insertBigData() {
    //定义连接、statement对象
    Connection conn = null;
    PreparedStatement pstm = null;
    try {
        //加载jdbc驱动
        Class.forName("com.mysql.jdbc.Driver");
        //连接mysql
        conn = DriverManager.getConnection(url, user, password);
  //将自动提交关闭
  // conn.setAutoCommit(false);
        //编写sql
        String sql = "INSERT INTO person VALUES (?,?,?,?,?,?,?)";
        //预编译sql
        pstm = conn.prepareStatement(sql);
        //开始总计时
        long bTime1 = System.currentTimeMillis();
        //循环10次，每次十万数据，一共1000万
        for(int i=0;i<10;i++) {
            //开启分段计时，计1W数据耗时
            long bTime = System.currentTimeMillis();
            //开始循环
            while (begin < end) {
                pstm.setLong(1, begin);
                pstm.setString(2, RandomValue.getChineseName());
                pstm.setString(3, RandomValue.name_sex);
                pstm.setInt(4, RandomValue.getNum(1, 100));
                pstm.setString(5, RandomValue.getEmail(4, 15));
                pstm.setString(6, RandomValue.getTel());
                pstm.setString(7, RandomValue.getRoad());
                //添加到同一个批处理中
                pstm.addBatch();
                begin++;
            //执行批处理
            pstm.executeBatch();
           //提交事务
  //        conn.commit();
            //边界值自增10W
            end += 100000;
            //关闭分段计时
            long eTime = System.currentTimeMillis();
            System.out.println("成功插入10W条数据耗时："+(eTime-bTime));
        //关闭总计时
        long eTime1 = System.currentTimeMillis();
        System.out.println("插入100W数据共耗时："+(eTime1-bTime1));
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException e1) {
        e1.printStackTrace();
首先开始测试 
无事务，每次循环插入10W条数据，循环10次，一共100W条数据。 
结果如下图： 
成功插入10W条数据耗时：3832
成功插入10W条数据耗时：1770
成功插入10W条数据耗时：2628
成功插入10W条数据耗时：2140
成功插入10W条数据耗时：2148
成功插入10W条数据耗时：1757
成功插入10W条数据耗时：1767
成功插入10W条数据耗时：1832
成功插入10W条数据耗时：1830
成功插入10W条数据耗时：2031
插入100W数据共耗时：21737
实验结果： 
 使用JDBC批处理，未开启事务下，平均每 2.1 秒插入 十万 条数据 
开启事务，每次循环插入10W条数据，循环10次，一共100W条数据。 
结果如下图： 
成功插入10W条数据耗时：3482
成功插入10W条数据耗时：1776
成功插入10W条数据耗时：1979
成功插入10W条数据耗时：1730
成功插入10W条数据耗时：1643
成功插入10W条数据耗时：1665
成功插入10W条数据耗时：1622
成功插入10W条数据耗时：1624
成功插入10W条数据耗时：1779
成功插入10W条数据耗时：1698
插入100W数据共耗时：19003
实验结果： 
 使用JDBC批处理，开启事务，平均每 1.9 秒插入 十万 条数据 
能够看到，在开启事务下 JDBC直接处理 和 JDBC批处理 均耗时更短。 
 Mybatis 轻量级框架插入 , mybatis在我这次实验被黑的可惨了，哈哈。实际开启事务以后，差距不会这么大（差距10倍）。大家有兴趣的可以接着去测试
 
 JDBC直接处理，在本次实验，开启事务和关闭事务，耗时差距5倍左右，并且这个倍数会随着数据量的增大而增大。因为在未开启事务时，更新10000条数据，就得访问数据库10000次。导致每次操作都需要操作一次数据库。
 
 JDBC批处理，在本次实验，开启事务与关闭事务，耗时差距很微小（后面会增加测试，加大这个数值的差距）。但是能够看到开启事务以后，速度还是有提升。
  
结论：设计到大量单条数据的插入，使用JDBC批处理和事务混合速度最快 
实测使用批处理+事务混合插入1亿条数据耗时：174756毫秒 
JDBC批处理事务，开启和关闭事务，测评插入20次，一次50W数据，一共一千万数据耗时： 
1、开启事务(数据太长不全贴了) 
插入1000W数据共耗时：197654 
2、关闭事务(数据太长不全贴了) 
插入1000W数据共耗时：200540 
还是没很大的差距~ 
 不用批处理，不用事务；
 
 只用批处理，不用事务；
 
 只用事务，不用批处理；
 
 既用事务，也用批处理；（很明显，这个最快，所以建议在处理大批量的数据时，同时使用批处理和事务）
  
PS：防止找不到本篇文章，可以收藏点赞，方便翻阅查找哦。
                                    使用JDBC连接MySQL数据库进行数据插入的时候，特别是大批量数据连续插入（10W+），如何提高效率呢？
在JDBC编程接口中Statement 有两个方法特别值得注意：
void addBatch() throws SQLException
Adds a set of parameters to this PreparedStatement object’s batch of commands.
int[] executeBatch() throws SQLException
Submits a batch of commands to the database for exec
INSERT INTO TBL_TEST (id) VALUES(1);
很显然,在MYSQL中，这样的方式也是可行的。但是当我们需要批量插入数据的时候，这样的语句却会出现性能问题。例如说，如果有需要插入100000条数据，那么就需要有100000条insert语句，每一句都需要提交到关系引擎那里去解析，优化，然后才能够到达存储引擎做真的插入工作。
正是由于性能的瓶颈问题，MYSQL官方文档也就提到了使用批量化插入的方式，也就是在一句INSERT语句里面插入多个值。即，
INSERT INTO TBL
start TRANSACTION;
while i <= args DO
insert into A_student(id,name) VALUES (i, concat(“陈瓜皮-”, i));
set i = i+1;
end while;
COMMIT;
                                    点击上方“Java基基”，选择“设为星标”做积极的人，而不是积极废人！每天14:00更新文章，每天掉亿点点头发...源码精品专栏原创 | Java 2021超神之路，很肝~中文详细注释的开源项目RPC 框架 Dubbo 源码解析网络应用框架 Netty 源码解析消息中间件 RocketMQ 源码解析数据库中间件 Sharding-JDBC 和 MyCAT 源码解析...
                                    最近接触 redis，发现其读取速度快，突然想到，redis 怎么和数据库同步呢，怎么能把数据库的所有数据存到redis里面，能使用户更快速的查找。方法1：mysql 同步到redis：解析mysql的binlog，然后做同步处理，可以使用的库有：open-replicator（https://github.com/whitesock/open-replicator）方法2：同步redis数据到my
                                    前言在高并发系统当中，分库分表是必不可少的技术手段之一，同时也是BAT等大厂面试时，经常考的热门考题。你知道我们为什么要做分库分表吗？这个问题要从两条线说起：垂直方向 和 水平方向。1 ...
                                    假设场景：
某大型网站，活跃用户上亿个。（当然不是指同时在线人数，这里指的是再一段时间内有访问操作的用户数量，比如一个小时内）。
现在要每隔1小时，统计一次活跃用户排行榜（用户点击本网站的一个连接，活跃度就加1，按活跃度进行排名）。
首先，在此场景下，解决此问题不涉及数据库操作（也不可能用户点击一下，就更新一下数据库！），访问记录就是记录在日志文件中，例如：
zhangsan, h
在使用Mongo这种非关系型数据库过程中，往往存储了海量的数据，这些数据的数据结构非常松散，数据类型比较复杂。对于从海量数据中提取或分析数据，需要遍历Mongo库的情况经常出现，这里介绍两种比较有效的方法，均在亿级别的生产库中，多次使用过。
方法一：利用时间戳
在存储的数据中，对时间戳字段，建立索引，如下方数据时间戳字段为update_time...
有这样一张用户别名表，一个用户ID可以对应多个用户别名，但是系统分配的别名只能有一个。
由于user_id 行没有设置唯一主键，一个user_id可以有多个number_nick_name和多个string_nick_name。但是系统自动分配希望每个user_id只有一条，实际程序bug导致一个user_id现在存在多个set_type=1的行(实际上当前表中全部都是 set_type=1 的行)。
此表为mysql单表,表中已经有9千万数据，user_id为1-16位int64整数型(例：450
                                    mysql优化--1亿条数据效率COUNT(*)最近发现了一个Mysql快速导入数据方法load data infile，具体参考http://www.taobaodba.com/html/558_loaddata.html,这个文章。下面用几条命令来给大家看看，效率结果。简单说下：1.txt ,开始只有10万数据，后来用vim 添加到了2000万行，用Windows下的编辑器直接卡机的，Wind...