mysql 如何快速写入大量数据

abments

已于 2023-01-29 16:32:25 修改

阅读量3.5k

点赞数

分类专栏：数据库文章标签： mysql 数据库 java

于 2023-01-29 15:24:58 首次发布

本文链接：https://blog.csdn.net/abments/article/details/128788218

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

前言

一、硬件方向

二、程序开发方向

2.1 建议开启 rewriteBatchedStatements=true 配置项

2.2 load data infile

总结

前言

在开发过程中可能会遇到需要快速入库大量数据的场景。理论上来说通过批量写入应该能够快速的插入数据，但是实际过程中批量插入速度并不是很理想。接下来介绍一下优化常用的操作。

一、硬件方向

在其他条件不变的情况下，服务器的配置越高数据处理速度越快，cpu核心数，内存大小以及硬盘读些速度都会都数据入库有很大影响。另外,如果条件允许建议数据库应用与程序应用部署在同一台服务器中，这样避免了网络传输的速度限制。

二、程序开发方向

2.1 建议开启 rewriteBatchedStatements=true 配置项

对于mysql数据库驱动来说，如果不开始rewriteBatchedStatements=true，即使使用了preparedStatement.executeBatch()方法来执行sql语句，jdbc驱动仍然是单个语句发送到数据库中。

测试示例：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;


/*
create table sql:
CREATE TABLE `jdbc_student` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8



 */
public class JdbcTest {


    public static void main(String[] args) {
        testSingleDefault();
        //testDefault();
        // testBatchOn();

    }

    /**
     * 测试不使用批量执行语句 新增数据
     */
    public static void testSingleDefault() {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/test1?useUnicode=true&characterEncoding=utf-8";
            conn = DriverManager.getConnection(jdbcUrl, "root", "root");

            int batchSize = 500000;
            conn.setAutoCommit(false);    //设置自动提交为false
            PreparedStatement ps = conn.prepareStatement("insert into jdbc_student (name, age) values (?,?)");
            Long t1 = System.currentTimeMillis();
            System.out.println("开始执行: " + t1);
            for (int i = 1; i <= batchSize; i++) {
                ps.setString(1, "name: " + i);    //设置第2个参数, name
                ps.setInt(2, i % 30 + 10);        //设置第3个参数, age
                ps.execute();
            }
            conn.commit();    //提交
            ps.close();
            Long t2 = System.currentTimeMillis();
            System.out.println("执行结束：" + t2 + ", 耗时：" + (t2 - t1) / 1000 + "秒, batchRows: " + batchSize + "条, affectedRows: " + 500000);
            if (conn != null) {
                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception ee) {
                    //TODO
                }
            }
        }
    }

    /*
        测试使用批量方法但不开启rewriteBatchedStatements配置 插入数据
     */
    public static void testDefault() {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/test1?useUnicode=true&characterEncoding=utf-8";
            conn = DriverManager.getConnection(jdbcUrl, "root", "root");

            int batchSize = 500000;
            conn.setAutoCommit(false);    //设置自动提交为false
            PreparedStatement ps = conn.prepareStatement("insert into jdbc_student (name, age) values (?,?)");
            for (int i = 1; i <= batchSize; i++) {
                ps.setString(1, "name: " + i);    //设置第2个参数, name
                ps.setInt(2, i % 30 + 10);        //设置第3个参数, age
                ps.addBatch();        //将该条记录添加到批处理中
            }
            Long t1 = System.currentTimeMillis();
            System.out.println("开始执行: " + t1);
            int rows[] = ps.executeBatch();
            conn.commit();    //提交
            ps.close();
            Long t2 = System.currentTimeMillis();
            System.out.println("执行结束：" + t2 + ", 耗时：" + (t2 - t1) / 1000 + "秒, batchRows: " + batchSize + "条, affectedRows: " + rows.length);
            if (conn != null) {
                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception ee) {
                    //TODO
                }
            }
        }
    }


    /*
    测试开启rewriteBatchedStatements=true 批量新增数据
     */
    public static void testBatchOn() {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/test1?rewriteBatchedStatements=true&useUnicode=true&characterEncoding=utf-8";
            conn = DriverManager.getConnection(jdbcUrl, "root", "root");

            int batchSize = 500000;
            conn.setAutoCommit(false);    //设置自动提交为false
            PreparedStatement ps = conn.prepareStatement("insert into jdbc_student (name, age) values (?,?)");
            for (int i = 1; i <= batchSize; i++) {
                ps.setString(1, "name: " + i);    //设置第2个参数, name
                ps.setInt(2, i % 30 + 10);        //设置第3个参数, age
                ps.addBatch();        //将该条记录添加到批处理中
            }
            Long t1 = System.currentTimeMillis();
            System.out.println("开始执行: " + t1);
            int rows[] = ps.executeBatch();
            conn.commit();    //提交
            ps.close();
            Long t2 = System.currentTimeMillis();
            System.out.println("执行结束：" + t2 + ", 耗时：" + (t2 - t1) / 1000 + "秒, batchRows: " + batchSize + "条, affectedRows: " + rows.length);
            if (conn != null) {
                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception ee) {
                }
            }
        }
    }

}

结果统计：

方法	耗时
testSingleDefault	40秒
testDefault	42秒
testBatchOn	3秒

在数据量一致的情况下在未开始rewriteBatchedStatements=true配置之前，单条数据新增与多条数剧批量新增效果是一致的，在开启配置后写入速度有明显提升。

通过抓包工具分析，最终发送到数据库的语句应该是这个样子的

方法	语句
batchDelete(10条记录)	发送一次请求，内容为”delete from t where id = 1; delete from t where id = 2; delete from t where id = 3; ….”
batchUpdate(10条记录)	发送一次请求，内容为”update t set … where id = 1; update t set … where id = 2; update t set … where id = 3 …”
batchInsert(10条记录)	发送一次请求，内容为”insert into t (…) values (…) , (…), (…)”

需要注意的是，即使rewriteBatchedStatements=true, batchDelete()和batchUpdate()也不一定会走批量：当batchSize <= 3时，驱动会宁愿一条一条地执行SQL。所以，如果你想验证rewriteBatchedStatements在你的系统里是否已经生效，记得要使用较大的batch.

对delete和update，驱动所做的事就是把多条sql语句累积起来再一次性发出去；而对于insert，驱动则会把多条sql语句重写成一条风格很酷的sql语句，然后再发出去。官方文档说，这种insert写法可以提高性能（”This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements”）

2.2 load data infile

当需要导入的数据存在文件中时，可以使用数据库提供的命令（load data infile ...）加载数据到数据库中。导入速度比JDBC 还要快些。详细教程参考LOAD DATA INFILE使用与详解_longzhoufeng的博客-CSDN博客_load data infile

总结

文章主要内容参考介绍MySQL Jdbc驱动的rewriteBatchedStatements参数 - chenjianjx - 博客园 jdbc mysql设置rewriteBatchedStatements参数实现高性能批量处理 executeBatch返回值问题_老马历写记的博客-CSDN博客_jdbc rewritebatchedstatements mysql官网，LOAD DATA INFILE使用与详解_longzhoufeng的博客-CSDN博客_load data infile后面有其他优化方式，再继续更新。

abments

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
mysql 如何快速写入大量数据

在开发过程中可能会遇到需要快速入库大量数据的场景。理论上来说通过批量写入应该能够快速的插入数据，但是实际过程中批量插入速度并不是很理想。接下来介绍一下优化常用的操作。文章主要内容参考介绍MySQL Jdbc驱动的rewriteBatchedStatements参数 - chenjianjx - 博客园。
复制链接

扫一扫