事务对mysql批量插入数据的影响
前言
本人一年开发经验的小白,在项目中遇到一个API中需要一次向多张表中插入多条数据的情况,发现API响应接口很慢(mysql直接部署在linux中的时候不明显,mysql部署在docker中较明显)。最后通过开启事务解决问题(阴差阳错发现),让我百撕不得骑姐,最后决定自己创建一个数据库测试一下。
测试环境
mysql安装环境:windows10(CPU:i7-8700,RAM:16GB ,64位)
mysql版本:8.0.12
存储引擎:InnoDB(MyISAM不支持事务)
mysql图形化工具:DataGrip【推荐大家关注一个公众号—— 雨梦coder 可以免费获取JetBrains激活码】
mysql事务说明
1、用 BEGIN, ROLLBACK, COMMIT来实现
- BEGIN 开始一个事务
- ROLLBACK 事务回滚
- COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
- SET AUTOCOMMIT=0 禁止自动提交
- SET AUTOCOMMIT=1 开启自动提交
对于一个MYSQL数据库(InnoDB),事务的开启与提交模式无非下面这两种情况:
1>若参数autocommit=0,事务则在用户本次对数据进行操作时自动开启,在用户执行commit命令时提交,用户本次对数据库开始进行操作到用户执行commit命令之间的一系列操作为一个完整的事务周期。若不执行commit命令,系统则默认事务回滚。总而言之,当前情况下事务的状态是自动开启手动提交。
2>若参数autocommit=1(系统默认值),事务的开启与提交又分为两种状态:
①手动开启手动提交:当用户执行start transaction命令时(事务初始化),一个事务开启,当用户执行commit命令时当前事务提交。从用户执行start transaction命令到用户执行commit命令之间的一系列操作为一个完整的事务周期。若不执行commit命令,系统则默认事务回滚。
②自动开启自动提交:如果用户在当前情况下(参数autocommit=1)未执行start transaction命令而对数据库进行了操作,系统则默认用户对数据库的每一个操作为一个孤立的事务,也就是说用户每进行一次操作系都会即时提交或者即时回滚。这种情况下用户的每一个操作都是一个完整的事务周期。
来源:https://www.cnblogs.com/jiangxiaobo/p/11648943.html
测试代码
#删除表
drop table if exists users;
drop table if exists failed_jobs;
drop table if exists followers;
#创建表
create table if not exists users
(
id bigint unsigned auto_increment
primary key,
name varchar(191) not null,
password varchar(191) not null,
remember_token varchar(100) null,
is_admin tinyint(1) default 0 not null comment '是否为管理员',
activation_token varchar(191) null comment '激活令牌',
activated tinyint(1) default 0 null comment '是否激活'
) engine = InnoDB,
collate = utf8mb4_unicode_ci;
create table if not exists failed_jobs
(
id bigint unsigned auto_increment
primary key,
connection text not null,
queue text not null,
payload longtext not null,
exception longtext not null,
failed_at int null
) engine = InnoDB,
collate = utf8mb4_unicode_ci;
create table if not exists followers
(
id int unsigned auto_increment
primary key,
user_id int not null,
follower_id int not null,
created_at int null,
updated_at int null
) engine = InnoDB,
collate = utf8mb4_unicode_ci;
drop procedure if exists transaction_off;
drop procedure if exists transaction_on;
drop procedure if exists truncate_all;
#创建存储过程
#不开启事务
create PROCEDURE transaction_off(IN total INT)
BEGIN
DECLARE i INT;
SET i = 0;
set autocommit = 1;#设置自动提交
#插入50000条数据
WHILE(i < total)
DO
#分别插入数据
insert into users(name, password, remember_token, is_admin, activation_token,
activated) value (1, 2, 3, 4, 7, 8);
insert into failed_jobs(connection, queue, payload, exception) VALUE (1, 1, 1, 1);
insert into followers(user_id, follower_id, created_at, updated_at) VALUE (1, 1, 1, 1);
SET i = i + 1;
END WHILE;
END;
#开启事务
create PROCEDURE transaction_on(IN total INT)
BEGIN
DECLARE i INT;
SET i = 0;
set autocommit = 0;#设置不自动提交
START TRANSACTION;
WHILE(i < total)
DO
insert into users(name, password, remember_token, is_admin, activation_token,
activated) value (1, 2, 3, 6, 7, 8);
insert into failed_jobs(connection, queue, payload, exception) VALUE (1, 1, 1, 1);
insert into followers(user_id, follower_id, created_at, updated_at) VALUE (1, 1, 1, 1);
SET i = i + 1;
END WHILE;
COMMIT;
END;
#创建清空表存储过程
create procedure truncate_all()
BEGIN
truncate table users;#先删除表再创建表
truncate table failed_jobs;
truncate table followers;
end;
#调用存储过程
call truncate_all();
call transaction_off(500);#具体数量可自行设置
call transaction_on(500);
执行结果
注释:为了结果准确请每次每次测试了数据之后用“call truncate_all()”清空表数据
500条数据
test_ransaction> call transaction_on(500)
[2020-08-31 16:38:30] completed in 136 ms
test_ransaction> call transaction_off(500)
[2020-08-31 16:40:18] 1 row affected in 1 m 41 s 909 ms
5000条数据
test_ransaction> call transaction_on(5000)
[2020-08-31 16:44:47] completed in 2 s 147 ms
test_ransaction> call transaction_off(500)
[2020-08-31 16:40:18] 大于5分钟,具体我没有等它跑完
10万条数据
test_ransaction> call transaction_on(100000)
[2020-08-31 16:46:37] completed in 13 s 664 ms
不开启事务没有测试…有兴趣的朋友可以测试一下
总结
1.批量插入数据开启事务可以提高效率
2.mysql服务器用docker部署可能会影响mysql的I/O操作效率
3.开发和生产环境最好一致,不然系统在上线之后可能会一些意想不到的问题
参考文献
高性能mysql(第三版)
mysql必知必会
交流
欢迎大家多多指教、共同进步 微信:lxs346886407