MySQL批量插入性能测试，结果令人意外

迷路的架构师 05-13 215

前言：

现在看官们对“mysql批量执行”大约比较看重，看官们都想要剖析一些“mysql批量执行”的相关内容。那么小编同时在网摘上搜集了一些有关“mysql批量执行””的相关内容，希望同学们能喜欢，兄弟们快快来学习一下吧！

我们经常对 MySQL 进行批量插入，那么一批到底插入多少数据合适呢？接下来就做一下测试，每批次分别为100、500、1000、3000、5000、10000，插入数据总量为10万。

准备工作

建表：

CREATE TABLE user_mysql (    id INT PRIMARY KEY,    username VARCHAR(50),    email VARCHAR(100),    password VARCHAR(100),    first_name VARCHAR(50),    last_name VARCHAR(50),    address VARCHAR(200),    city VARCHAR(50),    state VARCHAR(50),    zip_code VARCHAR(10),    country VARCHAR(50),    phone_number VARCHAR(20),    date_of_birth DATE,    gender ENUM('Male', 'Female', 'Other'),    occupation VARCHAR(100),    education_level VARCHAR(50),    registration_date DATETIME,    last_login DATETIME,    is_active TINYINT(1),    is_admin TINYINT(1),    additional_field1 VARCHAR(100),    additional_field2 VARCHAR(100));

代码准备：

package mysql;import com.github.javafaker.Faker;import java.sql.*;import java.time.LocalDateTime;import java.util.Random;public class PerformanceTest {    public static void main(String[] args) {        String url = "jdbc:mysql://localhost/mydatabase"; // 替换为你的 MySQL 数据库连接 URL        String username = "root"; // 替换为你的数据库用户名        String password = "root"; // 替换为你的数据库密码        try (Connection conn = DriverManager.getConnection(url, username, password)) {            String insertSql = "INSERT INTO user_mysql (id, username, email, password, first_name, last_name, address, city, state, zip_code, country, phone_number, date_of_birth, gender, occupation, education_level, registration_date, last_login, is_active, is_admin, additional_field1, additional_field2) "                    + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";            try (PreparedStatement stmt = conn.prepareStatement(insertSql)) {                Faker faker = new Faker();                Random random = new Random();                for (int i = 1; i <= 100000; i++) {                    stmt.setInt(1, i);                    stmt.setString(2, "username");                    stmt.setString(3, "emailAddress");                    stmt.setString(4, "password");                    stmt.setString(5, "firstName");                    stmt.setString(6, "lastName");                    stmt.setString(7, "fullAddress");                    stmt.setString(8, "city");                    stmt.setString(9, "state");                    stmt.setString(10, "zipCode");                    stmt.setString(11, "china");                    stmt.setString(12, "phoneNumber");                    stmt.setDate(13, new Date(System.currentTimeMillis()));                    stmt.setString(14, randomGender());                    stmt.setString(15, "title");                    stmt.setString(16, "course");                    stmt.setTimestamp(17, Timestamp.valueOf(LocalDateTime.now()));                    stmt.setTimestamp(18, Timestamp.valueOf(LocalDateTime.now().minusDays(random.nextInt(365))));                    stmt.setInt(19, random.nextInt(2));                    stmt.setInt(20, random.nextInt(2));                    stmt.setString(21, "sentence1");                    stmt.setString(22, "sentence2");                    stmt.addBatch();                    if (i % 100 == 0) {                        stmt.executeBatch(); // 执行批处理                        stmt.clearBatch(); // 清空批处理                    }                }                stmt.executeBatch();            }        } catch (SQLException e) {            e.printStackTrace();        }    }    private static String randomGender() {        Random random = new Random();        int index = random.nextInt(3);        switch (index) {            case 0:                return "Male";            case 1:                return "Female";            default:                return "Other";        }    }}

运行环境：

MySQL版本：8.1.0-1.el8电脑配置：运行环境：Windows Docker每批100

耗时17分47秒。

每批500

耗时14分35秒。

每批1000

耗时14分28秒。

每批3000

耗时14分21秒。

每批5000

耗时15分08秒。

每批10000