前言：
现时朋友们对“mysqlminus”大约比较注意，咱们都想要学习一些“mysqlminus”的相关资讯。那么小编同时在网上搜集了一些有关“mysqlminus””的相关资讯，希望我们能喜欢，同学们快快来学习一下吧！今天做了一个有趣的测试，着实把自己吓了一大跳，差点一口气没缓过来。
回顾一下上次的测试《MySQL批量插入性能对比》，我们再简要描述下，往表中插入10万数据，批量插入，每批次分别为100、500、1000、3000、5000、10000。
测试结果如下：
MySQL版本：8.1.0
今天下载了一个最新的 PostgreSQL 也用相同的方式测试了一把，相关数据如下：
PostgreSQL版本：16.0-1MySQL代码：
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));
版权声明：
本站文章均来自互联网搜集，如有侵犯您的权益，请联系我们删除，谢谢。
标签： #mysqlminus #mysql对比数据差异 #mysql minus
