【MySQL专题】MySQL百万级数据插入效率优化

CREATE TABLE `tb_data` (
  `id` int(11) DEFAULT NULL,
  `user_name` varchar(100) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `random` double DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
复制代码

package com.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class InsertDataDemo {
    static Connection conn = null;
    public static void initConn() throws ClassNotFoundException, SQLException {
        String url = "jdbc:mysql://localhost:3306/testdb?"
                + "user=root&password=root&useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=UTC";
        try {
            // 动态加载mysql驱动
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("成功加载MySQL驱动程序");
            conn = DriverManager.getConnection(url);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static String randomStr(int size) {
        //定义一个空字符串
        String result = "";
        for (int i = 0; i < size; ++i) {
            //生成一个97~122之间的int类型整数
            int intVal = (int) (Math.random() * 26 + 97);
            //强制转换（char）intVal 将对应的数值转换为对应的字符，并将字符进行拼接
            result = result + (char) intVal;
        }
        //输出字符串
        return result;
    }
    public static void insert(int insertNum) {
        // 开时时间
        Long begin = System.currentTimeMillis();
        System.out.println("开始插入数据...");
        // sql前缀
        String prefix = "INSERT INTO tb_data (id, user_name, create_time, random) VALUES ";
        try {
            // 保存sql后缀
            StringBuffer suffix = new StringBuffer();
            // 设置事务为非自动提交
            conn.setAutoCommit(false);
            //为继承了Statement对象所有功能的预编译对象，性能和防SQL注入优于Statement对象，常用于重复执行的批处理命令
            PreparedStatement pst = conn.prepareStatement("");
            for (int i = 1; i <= insertNum; i++) {
                // 构建sql后缀(并一次生成8条数据)
                suffix.append("(" + i +",'"+ randomStr(8)  + "', SYSDATE(), " + i * Math.random() + "),");
            }
            // 构建完整sql
            String sql = prefix + suffix.substring(0, suffix.length() - 1);
            // 添加执行sql
            pst.addBatch(sql);
            // 执行操作(批处理)
            pst.executeBatch();
            // 提交事务
            conn.commit();
            // 关闭连接
            pst.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        // 结束时间
        Long end = System.currentTimeMillis();
        System.out.println("插入"+insertNum+"条数据数据完成！");
        System.out.println("耗时 : " + (end - begin) / 1000 + " 秒");
    }
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        initConn();
        insert(1000000);
    }
}
复制代码

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Successfully loaded MySQL driver
Start Inserting Data...
java.sql.SQLException: SQL String cannot be empty
复制代码

<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
            <scope>runtime</scope>
        </dependency>
复制代码

java.sql.BatchUpdateException: Packet for query is too large (50235460 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.
复制代码

mysql> show VARIABLES like '%max_allowed_packet%';
复制代码

mysql> mysql --max_allowed_packet=500M
或
set global max_allowed_packet = 4*1024*1024*10
复制代码

Successfully loaded MySQL driver
Start Inserting Data...
insert1000000 data data is completed!
Time-consuming : 7seconds
Process finished with exit code 0
复制代码

[mysqld]
# 没有不需要添加
net_buffer_length=512k
max_allowed_packet=500M
复制代码