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

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

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

其中使用场景如下:

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

下面我列举几种常见的 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 行数据。

下面是测试脚本:


测试结果如下:

行数 分钟
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 数据写入功能了。而且接入性能测试框架之后,这个写入行数也会变得更加稳定。


↙↙↙阅读原文可查看相关链接,并与作者交流