玖叶教程网

前端编程开发入门

2024年04月25日

MySQL批量插入数据的效率（mysql 批量写入）

关注我「程序猿集锦」，获取更多分享。

  • 背景
  • 环境准备
    • 准备表
    • 主备数据
  • 总结

背景

MySQL在插入数据的时候，应该怎么插入速度最快，效率最高？

我们经常使用的插入方式有如下几种：

  • 第一种：直接一行一个insert语句，如下所示：
insert into user(name, birthday) values('A','19950902');
...... /*此处省略*/
insert into user(name, birthday) values('C','19950904');
  • 第二种：使用begin和commit事务包裹起来的每一行一个插入语句。
begin;
insert into user(name, birthday) values('A','19950902');
...... /*此处省略*/
insert into user(name, birthday) values('C','19950904');
commit;
  • 第三种：values关键字后面跟着很多行数据，一起批量插入。
insert into user(name, birthday) values('A','19950902'),('B','19950903'),('C','19950904');
...... /*此处省略*/
insert into user(name, birthday) values('A','19950902'),('B','19950903'),('C','19950904');
  • 第四种：使用begin和commit事务包裹起来的批量插入的语句。
begin;
insert into user(name, birthday) values('A','19950902'),('B','19950903'),('C','19950904');
...... /*此处省略*/
insert into user(name, birthday) values('A','19950902'),('B','19950903'),('C','19950904');
end;

以上几种插入的语句，哪个SQL更快？下面让我们验证一下。

环境准备

准备表

我们在本地的MySQL中准备4张表，这样可以避免网络延迟导致的测试结果不准确的因素，本地直接连接MySQL数据库，网络因素就可以排除了。这四张表结构一样，然后针对这4张表，我们分别使用前面提到的4张插入数据的方式，分别测试。

4张表的结构如下，表名称不同，表结构一样。

CREATE TABLE `user_info_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(64) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `cellphone` varchar(11) DEFAULT NULL,
  `address` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='第一种插入方式使用的表';

CREATE TABLE `user_info_2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(64) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `cellphone` varchar(11) DEFAULT NULL,
  `address` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='第二种插入方式使用的表';

CREATE TABLE `user_info_3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(64) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `cellphone` varchar(11) DEFAULT NULL,
  `address` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='第三种插入方式使用的表';

CREATE TABLE `user_info_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(64) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `cellphone` varchar(11) DEFAULT NULL,
  `address` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='第四种插入方式使用的表';

主备数据

初始化数据的时候，大家可以参考这个存储过程，这个存储过程的作用就是在user_info表中生成指定行数的测试数据，该存储过程如下：

CREATE PROCEDURE `initial_user_info`(max_id int)
begin
    declare m int default 0;
    declare _username varchar(64) default '';
    declare _age int default 0;
    declare _cellphone varchar(11) default '';
    declare _address varchar(255) default '';
		
		start transaction;
		
			while m < max_id do
					set m = m + 1;
					set _username = substring( uuid(), 1, floor(8 + rand() * (29)));
					set _age = floor(18 + rand() * (43));
					set _cellphone = substr(rand(),-11);
					set _address = concat(substring( uuid(), 1, floor(8 + rand() * (29))), substring( uuid(), 1, floor(8 + rand() * (29))));
					insert into user_info (user_name, age, cellphone, address) values(_username, _age, _cellphone, _address);
			end while;
		
		commit;

end

如下的SQL是向user_info表中插入24W测试数据。

CREATE TABLE `user_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(64) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `cellphone` varchar(11) DEFAULT NULL,
  `address` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用于存储生成的测试数据的表';

call initial_user_info(240000);

当user_info表中的数据准备好之后，我们可以把这表中的数据用数据库客户端工具导出来，导出的时候可以导出两种格式：一种是一行一行的insert语句，一种是批量insert的语句。基于导出来的这两种SQL文件，我们再手动patch成下面四个SQL文件。

准备待导入的SQL文件的时候，我们按照前面提到的4种方式，分别为user_info_1.sql、user_info_2.sql、user_info_3.sql、user_info_4.sql四个SQL文件，每一个文件中待导入的记录数都是24W行数据。它们里面的内容也是按照四中插入方式准备的。

  • 第一种，user_info_1.sql文件。里面的内容是一行一行的insert语句，每一行数据一个insert。没有使用事务begin和commit包裹起来。
  • 第二种，user_info_2.sql文件。里面的内容是一行一行的insert语句，每一行数据一个insert。与第一个SQL文件的区别是，在文件第一行有begin，在文件的最后一行有commit。文件中的所有的语句使用一个事务begin和commit包裹起来。
  • 第三种，user_info_3.sql文件。里面的内容是一行一行的insert语句，每一个insert语句后面跟着大概700~800个values值。是批量插入的一种方式，但是所有的批量插入的语句，没有使用事务begin和commit包裹起来。
  • 第四种，user_info_4.sql文件。里面的内容是一行一行的insert语句，每一个insert语句后面跟着大概700~800个values值。是批量插入的一种方式，但是文件的开头是begin，文件的最后一行是commit。所有的批量插入的语句，使用一个事务begin和commit包裹起来。

为了方便记录每一个SQL导入消耗的实际，我们使用一个简单的shell脚本来导入SQL文件，脚本内如下：

echo "`date '+%Y-%m-%d %H:%M:%S'`，开始导入..."
mysql -uroot -proot insert_by_batch_test < $1
echo "`date '+%Y-%m-%d %H:%M:%S'`，导入结束..."

执行导入的结果如下：

?  ~ ./mysql_import.sh ~/Downloads/user_info_1.sql
2021-07-16 15:08:15，开始导入...
mysql: [Warning] Using a password on the command line interface can be insecure.
2021-07-16 15:09:33，导入结束...
?  ~ #耗时 78s
?  ~
?  ~ ./mysql_import.sh ~/Downloads/user_info_2.sql
2021-07-16 15:10:24，开始导入...
mysql: [Warning] Using a password on the command line interface can be insecure.
2021-07-16 15:11:20，导入结束...
?  ~ #耗时 56s
?  ~
?  ~ ./mysql_import.sh ~/Downloads/user_info_3.sql
2021-07-16 15:11:37，开始导入...
mysql: [Warning] Using a password on the command line interface can be insecure.
2021-07-16 15:11:39，导入结束...
?  ~ #耗时 2s
?  ~
?  ~ ./mysql_import.sh ~/Downloads/user_info_4.sql
2021-07-16 15:11:46，开始导入...
mysql: [Warning] Using a password on the command line interface can be insecure.
2021-07-16 15:11:47，导入结束...
?  ~ #耗时 1s
?  ~

通过上面的实验过程可以发现，第一种导入的效率最差，最后一种导入的效率最高。四种导入方式的效率为：

第一种方式 < 第二种方式 < 第三种方式 < 第四种方式

总结

  • 第一种每一次都需要进行语法解析和语法判断，数据量特别大的时候每次解析的时间累计起来就很耗时，这是它慢的主要原因。
  • 第二种相对一对一种有一些优化，主要是对日志的操作有原先的一个SQL语句一个事务日志改为所有的SQL语句一个事务日志，这里的日志主要是binlog日志和回滚日志。
  • 第三种执行效率高的主要缘由是合并了SQL语句，采用批量的插入方式，降低了SQL语句解析的次数。此时的日志量binlog日志和回滚日志也合并在一起，下降日志刷盘的数据量和频率，从而提升效率。
  • 和第三种相比，这种方式把所有的操作合并为一个事务，日志合并后，降低了日志刷盘次数。所以此时效率更高。

所以在初始化一些数据的时候，我们尽量使用批量insert的方式，与此同时在批量insert语句前后使用事务包裹起来。当然事务包裹的SQL语句的大小也要合适，不能无限制地使用一个特别大的事务，这样效率也不高。

标签：mysqlinsert 

作者:gojiuye , 分类:技术精选 , 浏览:19 , 评论:0

发表评论:

控制面板
您好，欢迎到访网站！
  查看权限
网站分类
最新留言