事务对mysql批量插入数据的影响

最新推荐文章于 2024-06-28 13:05:12 发布

人潮max

最新推荐文章于 2024-06-28 13:05:12 发布

阅读量2.6k

点赞数

分类专栏： mysql 文章标签： docker 数据库 mysql innodb 大数据

本文链接：https://blog.csdn.net/qq_38483583/article/details/108325175

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

事务对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