MySQL写入压测几种方式

2024-05-29 14:30:11 浏览数 (1)

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

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

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

其中使用场景如下：

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

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

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

测试准备

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

代码语言：javascript复制
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循环往数据库里面写入数据。这应该是最常见、最简单的一种方式，理论上也是性能最差的一种方式，虽然我使用预编译的功能，但实际上也是提升了客户端的性能，并不能对服务端的性能造成影响。

脚本如下：

代码语言：javascript复制
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批处理也是可以大幅提升性能的，如果有同样的想法小伙伴，可以先不用着急，所以我们先来看看测试程序。

代码语言：javascript复制
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行数据。

下面是测试脚本：

代码语言：txt复制
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

性能确实有所提升，但是很不稳定。最高和最低也得两倍差距了。

单线程的已经测试完了。相信各位已经有所了解，其实把这些单线程方式拓展成多线程就变成了更高性能的MySQL数据写入功能了。而且接入性能测试框架之后，这个写入行数也会变得更加稳定。

数据库 mysql 测试 数据 性能

0 人点赞