写点什么

MySQL 写入压测几种方式

作者:FunTester
  • 2024-05-29
    河北
  • 本文字数:2860 字

    阅读完需:约 9 分钟

最近跟在粉丝群先聊到一个问题,数据库的写入方式,最多能写入多少行数据。经过一些网络搜索和查询,据悉 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.FunMySqlimport com.funtester.frame.SourceCodeimport 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();// 关闭资源 }}
复制代码


测试结果如下:


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();// 关闭资源      }  }
复制代码


测试结果如下:


多行插入

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


下面是测试脚本:


 
复制代码


测试结果如下:



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

发布于: 20 分钟前阅读数: 5
用户头像

FunTester

关注

公众号:FunTester,800篇原创,欢迎关注 2020-10-20 加入

Fun·BUG挖掘机·性能征服者·头顶锅盖·Tester

评论

发布
暂无评论
MySQL写入压测几种方式_FunTester_InfoQ写作社区