MySQL 通过存储过程高效插入100w条数据

一、前言

最近在做SQL索引优化的时候经常需要批量插入一些数据，采用存储过程来进行批量插入是一个很好的选择，但是在插入100w数据时我本地耗时需要24分钟有点顶不住，本文会讲解如何通过存储过程批量插入数据，并且提供两个提升插入速度的方法。

二、创建表

DROP TABLE IF EXISTS `order_info`;
CREATE TABLE `order_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  `order_no` varchar(100) NOT NULL COMMENT '订单编号',
  `customer_id` bigint(20) NOT NULL COMMENT '客户编号',
  `goods_id` bigint(20) NOT NULL COMMENT '商品ID',
  `goods_title` varchar(100) NULL COMMENT '商品标题',
  `order_status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '订单状态 1：待支付 2：已支付 3：已发货 4、已收货',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='订单信息表';
1
2
3
4
5
6
7
8
9
10
11

三、编写存储过程插入数据

在测试的时候插入数据量可以调小一点，一次别插入太多，如果存储过程不加事务插入10w条数据我本地耗时143秒，插入100w数据我本地耗时24分钟太慢了，可以先看下面高效插入数据方案。

## 创建一个插入数据的存储过程
DROP PROCEDURE IF EXISTS insert_procedure;
delimiter;;
CREATE PROCEDURE insert_procedure () 
BEGIN
  # 定义循环值
  DECLARE i INT DEFAULT 1;
  # 开启事务
  START TRANSACTION;
  # 开始循环插入
  WHILE ( i <= 1000000 ) DO
    INSERT INTO `order_info`(`order_no`,`customer_id`, `goods_id`, `goods_title`, `order_status`, `create_time`) VALUES (CONCAT('ON00000',i), CEIL(RAND() * 100), CEIL(RAND() * 100), CONCAT('笔记本电脑',i), MOD(i, 4)+1, NOW());
    SET i = i + 1;
  END WHILE;
END;;
delimiter;

# 调用存储过程插入数据
CALL insert_procedure ();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

四、高效插入数据方案

MySQL版本：8.0.18

如果MySQL不做任何配置，我本地固态盘使用MySQL8.0插入10w条数据耗时142s，插入数据量越大可能等比耗时更长，一般表中都会创建一些索引，在插入数据的时候也会变更索引，尤其是唯一索引会增长插入数据的时间，要想加快插入速度有多种方法，硬件上的优化就不说了，这里只说三个方法够我们做SQL索引优化测试即可。

4.1、插入数据时删除表中全部索引

将表中索引全部删除，包括主键索引，尤其是自增主键索引还有唯一索引，自己生成ID保证自增不重复即可，这里以10w条数据做测试对比，插入100w数据耗时太长。

我本地10w条数据插入有自增主键索引插入耗时142s，删除主键索引改用自己生成ID值插入耗时139s，这个数据量还比较小，有兴趣可以加大数据量测试，数据量越大差值越明显。

只需要把把存储过程中的SQL改一下把让 ID 使用 i 的值即可

INSERT INTO `order_info`(id,`order_no`,`customer_id`, `goods_id`, `goods_title`, `order_status`, `create_time`) VALUES (i, CONCAT('ON00000',i), CEIL(RAND() * 100), CEIL(RAND() * 100), CONCAT('笔记本电脑',i), MOD(i, 4)+1, NOW());
1

4.2、存储过程中使用统一事务插入（性能显著提升）

在存储过程中添加事务，存储过程中的每次新增语句都会开启一个自己的事务，控制所有新增都在一个事务中，10w条数据插入耗时从142s提升到20s，速度大大提升，但是有个问题这20s其它插入操作需要等待，线上业务需要考量一下，本地SQL索引优化测试倒是一个很不错的选择。

• 给存储过程添加上统一事务

## 创建一个插入数据的存储过程
DROP PROCEDURE IF EXISTS insert_procedure;
delimiter;;
CREATE PROCEDURE insert_procedure () 
BEGIN
  # 定义循环值
  DECLARE i INT DEFAULT 1;
  #定义一个错误的变量,类型是整形，默认是0
  DECLARE t_error INTEGER DEFAULT 0;
  #捕获到sql的错误，就设置t_error为1
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
  # 开启事务
  START TRANSACTION;
  # 开始循环插入
  WHILE ( i <= 1000000 ) DO
    INSERT INTO `order_info`(`order_no`,`customer_id`, `goods_id`, `goods_title`, `order_status`, `create_time`) VALUES (CONCAT('ON00000',i), CEIL(RAND() * 100), CEIL(RAND() * 100), CONCAT('笔记本电脑',i), MOD(i, 4)+1, NOW());
    SET i = i + 1;
  END WHILE;

  #如果捕获到错误
  IF t_error=1 THEN
    #回滚
    ROLLBACK;
  ELSE
    #提交
    COMMIT;
  END IF;
END;;
delimiter;

# 调用存储过程插入数据
CALL insert_procedure ();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

4.3、调整MySQL系统配置（性能显著提升，适合存储过程没有使用统一事务）

这种方案是适合存储过程没有使用统一事务插入，每一次插入都需要开启事务然后提交，对存储过程中使用了统一事务插入提升不大。

MySQL有两个配置是控制日志文件写入的，在计算器中最耗时的操作就是IO，MySQL默认是会同步写入redo日志和binlog日志的，我们插入100w数据就需要同步写入100w次redo日志和100w次binlog日志，这是非常耗时的，如果能改成异步批量写入则可以大大加快新增数据的速度，但是可能会导致数据库宕机时数据丢失问题，这里不做详细说明。

• innodb_flush_log_at_trx_commit （控制redo日志写入模式）
  • 等于0： log buffer每秒就会被刷写日志文件到磁盘，提交事务的时候不做任何操作（执行是由mysql的master thread线程来执行的。
  • 等于1： 每次提交事务的时候，都会将log buffer刷写到日志 （默认）
  • 等于2： 表示在每次事务提交的时候会把log buffer刷到文件系统中去，但并不会立即刷写到磁盘。如果只是MySQL数据库挂掉了，由于文件系统没有问题，那么对应的事务数据并没有丢失。只有在数据库所在的主机操作系统损坏或者突然掉电的情况下，数据库的事务数据可能丢失1秒之类的事务数据。这样的好处，减少了事务数据丢失的概率，而对底层硬件的IO要求也没有那么高(log buffer写到文件系统中，一般只是从log buffer的内存转移的文件系统的内存缓存中，对底层IO没有压力)。
• sync_binlog （控制binlog日志写入模式）
  • 在提交n次事务后，进行binlog的落盘，0为不进行强行的刷新操作，而是由文件系统控制刷新日志文件，如果是在线交易和账有关的数据建议设置成1，如果是其他数据可以保持为0即可

查看MySQL这两个配置默认值（一般默认都是1）

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW VARIABLES LIKE 'sync_binlog';
1
2

修改MySQL配置文件

我的MySQL是Linux版的配置文件在/etc/mysql/my.cnf，window 上的 MySQL 配置文件默认是在 C:\Program Files\MySQL\MySQL Server 8.0\my-default.ini。

# 打开/etc/mysql/my.cnf
vi /etc/mysql/my.cnf
1
2

• 在配置文件中的[mysqld]下添加如下配置

## 2表示在每次事务提交的时候会把log buffer刷到文件系统中去，但并不会立即刷写到磁盘。
innodb_flush_log_at_trx_commit = 2
## 0为不进行强行的刷新操作，而是由文件系统控制刷新日志文件
sync_binlog = 0
1
2
3
4

• 重启MySQL

service mysqld restart
# 或 
service mysql restart
1
2
3

插入10w数据测试

• 修改前
  

• 修改后
  
  插入速度还是比使用统一事务插入差很多。

五、总结

我的需求是为了做SQL索引优化测试需要批量插入一些数据，这里最适合我的是4.2中添加统一事务来插入方案。

• 4.2方案存储过程中使用统一事务，插入100w数据耗时217秒差不多3.6分钟，没有调整前耗时24分钟插入速度提升6.6倍多。
• 4.3方案调整MySQL配置，插入100w数据耗时415秒差不多7分钟，没有调整前耗时24分钟插入速度提升3.4倍多。

要想高效插入数据还有很多种方法，我这里只是为了做SQL索引优化测试使用，这个插入耗时我还可以接受，有其它好的方法可以一起交流。