今天做了一个有趣的测试，着实把自己吓了一大跳，差点一口气没缓过来。

回顾一下上次的测试《MySQL批量插入性能对比》，我们再简要描述下，往表中插入10万数据，批量插入，每批次分别为100、500、1000、3000、5000、10000。

测试结果如下：

• MySQL版本：8.1.0

今天下载了一个最新的 PostgreSQL 也用相同的方式测试了一把，相关数据如下：

• PostgreSQL版本：16.0-1

MySQL代码：

public static void mysql() {        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, 1);                    stmt.setInt(20, 2);                    stmt.setString(21, "sentence1");                    stmt.setString(22, "sentence2");                    stmt.addBatch();                    if (i % 10000 == 0) {                        stmt.executeBatch(); // 执行批处理                        stmt.clearBatch(); // 清空批处理                    }                }                stmt.executeBatch();            }        } catch (SQLException e) {            e.printStackTrace();        }    }

PostgreSQL代码

与MySQL代码基本一样，只是驱动和用户名密码进行了修改：

public static void postgresql() {        String url = "jdbc:postgresql://localhost/postgres"; // 替换为你的 MySQL 数据库连接 URL        String username = "postgres"; // 替换为你的数据库用户名        String password = "postgres"; // 替换为你的数据库密码        try (Connection conn = DriverManager.getConnection(url, username, password)) {            String insertSql = "INSERT INTO user_postgresql (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.setBoolean(19, true);                    stmt.setBoolean(20, false);                    stmt.setString(21, "sentence1");                    stmt.setString(22, "sentence2");                    stmt.addBatch();                    if (i % 10000 == 0) {                        stmt.executeBatch(); // 执行批处理                        stmt.clearBatch(); // 清空批处理                    }                }                stmt.executeBatch();            }        } catch (SQLException e) {            e.printStackTrace();        }    }

测试结果

最终只使用 3 秒钟！！！！！！！！！！！！！！！！！！！！

改成100每批也是5秒以内完成插入，至少每秒2万以上的插入性能，相当于clickhouse这种列式数据库的性能级别，比MySQL至少快30倍！！！！？？？？？

思考

感觉有点不可思议，在思考原因，也请 MySQL 和 PostgreSQL 大佬不吝赐教。

下面是我创建表的语句：

MySQL：

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(50),                            date_of_birth DATE,                            gender VARCHAR(10),                            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));

PostgreSQL：

CREATE TABLE user_postgresql (                                 id SERIAL 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(50),                                 date_of_birth DATE,                                 gender VARCHAR(10),                                 occupation VARCHAR(100),                                 education_level VARCHAR(50),                                 registration_date TIMESTAMP,                                 last_login TIMESTAMP,                                 is_active BOOLEAN,                                 is_admin BOOLEAN,                                 additional_field1 VARCHAR(100),                                 additional_field2 VARCHAR(100));