您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

MySQL批量插入数据的效率

时间:2021-07-19 11:08:12  来源:  作者:程序猿集锦

背景

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语句的大小也要合适,不能无限制地使用一个特别大的事务,这样效率也不高。



Tags:MySQL   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  Tags: MySQL  点击:(6)  评论:(0)  加入收藏
一、为什么要搭建主从架构呢1.数据安全,可以进行数据的备份。2.读写分离,大部分的业务系统来说都是读数据多,写数据少,当访问压力过大时,可以把读请求给到从服务器。从而缓解数据...【详细内容】
2021-12-15  Tags: MySQL  点击:(10)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  Tags: MySQL  点击:(17)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  Tags: MySQL  点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  Tags: MySQL  点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  Tags: MySQL  点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  Tags: MySQL  点击:(16)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  Tags: MySQL  点击:(19)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  Tags: MySQL  点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Tags: MySQL  点击:(31)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(6)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(17)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(16)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(19)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(27)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条