MySQL写入压测几种方式

FunTester

发布于 2024-05-29 14:30:11

1840

发布于 2024-05-29 14:30:11

文章被收录于专栏：FunTester

最近跟在粉丝群先聊到一个问题，数据库的写入方式，最多能写入多少行数据。经过一些网络搜索和查询，据悉MySQL单表插入极限是3w~5w。

席间大家也都说了几种写入方式，其中有一个非压测的方式在可查阅的资料中是最厉害的，据悉是程序写入的20倍以上性能，就是load data。

MySQL的LOAD DATA语句是一种用于高效导入大量数据到数据库表中的命令。它从一个纯文本文件（通常是CSV文件）中读取数据，然后将其批量插入到指定的数据库表中。这种方式比逐行插入效率更高，特别适合于导入大数据集。

其中使用场景如下：

批量数据导入：特别适合于从外部系统迁移数据到MySQL。
数据备份与恢复：可以快速导入导出的数据文件。
数据初始化：在应用部署初期，从预定义的数据文件中加载初始数据。

这种开挂的方式暂时不列入本次实验范围了，主要无法使用压测方式控制压力大小，不太适合做写入的性能测试。

下面我列举几种常见的 MySQL 写入方式，并简单测试写入性能。本文只分享单线程的方案，至于性能

测试准备

首先本地创建一个MySQL服务，默认参数，没有任何优化。其次创建一张表，建表语句如下：

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `name` varchar(32) DEFAULT 'FunTester' COMMENT '用户名',
  `age` int NOT NULL COMMENT '用户年龄',
  `level` int DEFAULT NULL COMMENT '用户等级',
  `region` varchar(32) DEFAULT '小八超市' COMMENT '用户所在地区',
  `address` varchar(512) DEFAULT NULL COMMENT '用户地址',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

其次测试方法，就是使用MySQL语句统计每秒的写入行数。看最大每秒的写入数量。

SELECT COUNT(*),create_time FROM user group by create_time;

每次测试结束之后，清理表：

TRUNCATE TABLE user;

while循环

使用JDBC连接MySQL服务，然后使用单线程while循环往数据库里面写入数据。这应该是最常见、最简单的一种方式，理论上也是性能最差的一种方式，虽然我使用预编译的功能，但实际上也是提升了客户端的性能，并不能对服务端的性能造成影响。

脚本如下：

package com.funtest.mysql

import com.funtester.db.mysql.FunMySql
import com.funtester.frame.SourceCode
import com.funtester.utils.StringUtil
/**
 * 通过 JDBC 向 MySQL 数据库写入数据
 */
class MysqlWriteWhile extends SourceCode {

    public static void main(String[] args) {
        String sqlFormat = "insert into user (name, age, level, region, address) values (?, ?, ?, ?, ?)";
        String ipPort = "127.0.0.1:3306";// 服务端地址
        String database = "funtester"// 服务端地址
        String user = "root";// 用户名
        String password = "funtester";// 密码
        def base = new FunMySql(ipPort, database, user, password);// 创建数据库操作基础类
        def preparedStatement = base.connection.prepareStatement(sqlFormat);// 预编译 SQL 语句
        while (true) {
            String name = StringUtil.getString(16);// 随机生成姓名
            int age = getRandomInt(100);// 随机生成年龄
            int level = getRandomInt(10);// 随机生成等级
            String region = StringUtil.getString(32);// 随机生成地区
            String address = StringUtil.getString(512);// 随机生成地址
            preparedStatement.setString(1, name);// 设置参数
            preparedStatement.setInt(2, age);// 设置参数
            preparedStatement.setInt(3, level);// 设置参数
            preparedStatement.setString(4, region);// 设置参数
            preparedStatement.setString(5, address);// 设置参数
            preparedStatement.executeUpdate();// 执行插入语句
        }
        preparedStatement.close();// 关闭资源
        base.close();// 关闭资源
    }
}

测试结果如下：

行数	分钟	秒
6374	12	0
6197	12	1
6156	12	2
6176	12	3
6332	12	4
6545	12	5
7088	12	6
7309	12	7
7408	12	8
6099	12	9
看来7k多一些，这一点比我之前的一次结果好很多。

JDBC批处理

一开始我也觉得JDBC批处理也是可以大幅提升性能的，如果有同样的想法小伙伴，可以先不用着急，所以我们先来看看测试程序。

package com.funtest.mysql  
  
import com.funtester.db.mysql.FunMySql  
import com.funtester.frame.SourceCode  
import com.funtester.utils.StringUtil  
/**  
 * 通过 JDBC 向 MySQL 数据库写入数据  
 */  
class MysqlWriteBatch extends SourceCode {  
  
    public static void main(String[] args) {  
        String sqlFormat = "insert into user (name, age, level, region, address) values (?, ?, ?, ?, ?)";  
        String ipPort = "127.0.0.1:3306";// 服务端地址  
        String database = "funtester"// 服务端地址  
        String user = "root";// 用户名  
        String password = "funtester";// 密码  
        def base = new FunMySql(ipPort, database, user, password);// 创建数据库操作基础类  
        def preparedStatement = base.connection.prepareStatement(sqlFormat);// 预编译 SQL 语句  
        while (true) {  
            for (int j = 0; j < 10; j++) {  
                String name = StringUtil.getString(16);// 随机生成姓名  
                int age = getRandomInt(100);// 随机生成年龄  
                int level = getRandomInt(10);// 随机生成等级  
                String region = StringUtil.getString(32);// 随机生成地区  
                String address = StringUtil.getString(512);// 随机生成地址  
                preparedStatement.setString(1, name);// 设置参数  
                preparedStatement.setInt(2, age);// 设置参数  
                preparedStatement.setInt(3, level);// 设置参数  
                preparedStatement.setString(4, region);// 设置参数  
                preparedStatement.setString(5, address);// 设置参数  
                preparedStatement.addBatch();// 添加到批处理  
            }  
            preparedStatement.executeBatch();// 批量执行  
  
        }  
        preparedStatement.close();// 关闭资源  
        base.close();// 关闭资源  
    }  
}

测试结果如下：

行数	分钟	秒
7308	27	17
6817	27	18
6871	27	19
6367	27	20
6631	27	21
7310	27	22
6903	27	23
7258	27	24
7180	27	25
7309	27	26
7208	27	27
6640	27	28
跟 while 循环结果也差不多。下面分享一下我查到的资料结果。批处理只是减少了往服务器来回发送数据的效率，仅此而已。对于服务器实际处理MySQL操作，并没有很大提升。

多行插入

这里的批量插入指的是一条MySQL语句包含N行MySQL数据，这与批处理不一样。批处理是一次性将很多条MySQL发送给服务端，而多行插入一条MySQL插入N行数据。

下面是测试脚本：

package com.funtest.mysql

import com.funtester.db.mysql.FunMySql
import com.funtester.frame.SourceCode
import com.funtester.utils.StringUtil
/**
 * 通过 JDBC 向 MySQL 数据库写入数据
 */
class MysqlWriteWhile extends SourceCode {

    public static void main(String[] args) {
        String sqlFormat = "insert into user (name, age, level, region, address) values (?, ?, ?, ?, ?)";
        String ipPort = "127.0.0.1:3306";// 服务端地址
        String database = "funtester"// 服务端地址
        String user = "root";// 用户名
        String password = "funtester";// 密码
        def base = new FunMySql(ipPort, database, user, password);// 创建数据库操作基础类
        def preparedStatement = base.connection.prepareStatement(sqlFormat);// 预编译 SQL 语句
        while (true) {
            String name = StringUtil.getString(16);// 随机生成姓名
            int age = getRandomInt(100);// 随机生成年龄
            int level = getRandomInt(10);// 随机生成等级
            String region = StringUtil.getString(32);// 随机生成地区
            String address = StringUtil.getString(512);// 随机生成地址
            preparedStatement.setString(1, name);// 设置参数
            preparedStatement.setInt(2, age);// 设置参数
            preparedStatement.setInt(3, level);// 设置参数
            preparedStatement.setString(4, region);// 设置参数
            preparedStatement.setString(5, address);// 设置参数
            preparedStatement.executeUpdate();// 执行插入语句
        }
        preparedStatement.close();// 关闭资源
        base.close();// 关闭资源
    }
}

测试结果如下：

行数	分钟	秒
12360	46	28
11460	46	29
14800	46	30
22110	46	31
23950	46	32
24750	46	33
24030	46	34
15230	46	35
12360	46	28
11460	46	29
14800	46	30
22110	46	31
性能确实有所提升，但是很不稳定。最高和最低也得两倍差距了。