logo
搜索
我的图书馆

发文章

发文工具

其他工具

留言交流
搜索
分享

mysql存储过程插入千万数据性能优化（实测）

 hongjing_z 2023-11-29 发布于上海

一、前言

1、工作过程中，经常遇到需要造数的情况，比如为了测试接口性能，需要往数据库插入大量数据，这种情况下，如果仅靠普通的方式插入而不进行优化的话，太慢，太耗时，不能忍

2、网上给出的优化方式大多类似，但是并没有进行实测验证，为了验证这些方式是否有效，且给出真正实用的优化方案，本文将对千万级数据插入进行实测，为了提高测试准确性，会对不同的数据量进行多轮测试，每轮测3次取平均值，测试结果应该具有一定的参考性，希望对大家有帮助

二、文章概览

1、测试环境说明，表结构
2、每次插入一条数据测试
3、每次插入多条数据测试
4、批量提交事务测试
5、一次性提交所有事务测试
6、在一次性提交所有事务的情况下，数据插入前加索引与数据插入后加索引对比
7、修改参数：bulk_insert_buffer_size、unique_checks、autocommit测试
8、修改参数：innodb_flush_log_at_trx_commit测试
9、批量提交事务与修改参数对比
10、在修改mysql参数情况下，数据插入前加索引与数据插入后加索引对比
11、总结

三、准备工作

1、测试环境说明：

  • mysql版本：5.7.21

  • 环境配置：6C8G SSD

  • 系统：win10

2、表结构：
drop table if exists user;

create table user(
    id int not null auto_increment comment '主键',
    username varchar(255) not null comment '用户名',
    password varchar(255) not null comment '密码',
    password_salt varchar(255) not null comment '密码随机盐值',
    nickname varchar(255) not null comment '昵称',
    user_no int default 0 not null comment '用户编码',
    ip varchar(255) comment 'IP地址',
    mobile varchar(11) comment '手机号',
    mail varchar(255) comment '邮箱',
    gender int default 0 not null comment '性别（0：男，1：女）',
    type int default 0 not null comment '类型（0：普通用户，1：超级管理员）',
    status int default 0 not null comment '状态（0：正常，1：黑名单，2：已注销）',
    is_deleted int default 0 not null comment '是否删除（0：有效，1：无效删除）',
    created_time datetime default now() not null comment '创建时间',
    updated_time datetime default now() not null comment '更新时间',
    primary key(id)
) comment = '用户表';

create unique index unq_idx_user_username on user(username);

四、测试（不修改mysql参数情况下）

1、每次插入一条数据

1.1 伪代码：

insert into user(username, password) values('aaa', '123');
insert into user(username, password) values('bbb', '456');
insert into user(username, password) values('ccc', '789');

1.2 存储过程：

# 1、每次插入一条数据
drop procedure if exists insertIntoUser;

delimiter $$
 
create procedure insertIntoUser(in num int)
    begin
        declare i int default 0;
        
        while i < num do
            set i = i + 1;
            set @username = concat('beigua', LPAD(i, 9, 0));
            set @nickname = concat('北瓜', LPAD(i, 9, 0));
            set @password = replace(uuid(), "-", "");
            set @password_salt = replace(uuid(), "-", "");
            set @user_no = i;

            INSERT INTO user(username, password, password_salt, nickname, user_no, ip, mobile, mail, gender, type, status, is_deleted, created_time, updated_time) 
            VALUES (@username, @password, @password_salt, @nickname, @user_no, '192.168.1.1', '18888888888', '18888888888@163.com', '0', '0', '0', '0', now(), now());
        end while;
    end $$

1.3 调用存储过程，进行测试：

truncate user;
call insertIntoUser(100000);

truncate user;
call insertIntoUser(300000);

1.4 测试结果：


image.png

可以看到，插入非常慢，10w数据188s左右，30w数据563s左右

2、每次插入多条数据

2.1 伪代码：

insert into user(username, password) values
('aaa', '123'),
('bbb', '456'),
('ccc', '789');

2.2 存储过程：

# 2、每次插入多条数据
drop procedure if exists insertIntoUserBatch;

delimiter $$

# 创建存储过程，num表示插入的总行数，batch表示每次插入的行数
create procedure insertIntoUserBatch(in num int, in batchNum int)
begin
    declare i int default 0;
    set @insert_value = '';
    set @count = 0;
    set @batch_count = 0;
    
    while @count < num do
        # 内层while循环用于拼接insert into user(username, password) values('aaa', '123'), ('bbb', '456'), ('ccc', '789')...语句中values后面的部分
        while (@batch_count < batchNum and @count < num) do
            set i = i + 1;
            set @username = concat('beigua', LPAD(i, 9, 0));
            set @nickname = concat('北瓜', LPAD(i, 9, 0));
            set @password = replace(uuid(), "-", "");
            set @password_salt = replace(uuid(), "-", "");
            set @user_no = i;
            
            if @batch_count > 0 then 
                set @insert_value = concat(@insert_value, ',');
            end if;
            
            set @insert_value = concat(@insert_value, 
                "("
                , "'", @username, "'"
                , ",'", @password, "'" 
                , ",'", @password_salt, "'"
                , ",'", @nickname, "'" 
                , ",'", @user_no, "'" 
                , ",'192.168.1.1'"
                , ",'18888888888'"
                , ",'18888888888@163.com'"
                , ",0"
                , ",0"
                , ",0"
                , ",0"
                , ",'", now(), "'" 
                , ",'", now(), "'" 
                , ")"
                );
                
            set @batch_count = @batch_count + 1;    
        end while;
 
        set @count = @count + @batch_count;
        # 拼接SQL语句并执行
        set @exesql = concat("insert into user(username, password, password_salt, nickname, user_no, ip, mobile, mail, gender, type, status, is_deleted, created_time, updated_time) 
        values ", @insert_value);   
        prepare stmt from @exesql;
        execute stmt;
        deallocate prepare stmt;
        
        # 重置变量值
        set @insert_value = '';
        set @batch_count = 0;
    end while;
    # 数据插入完成后，查看表中总记录数
    select count(id) from user;
end $$

2.3 测试结果：


image.png

2.4 分析

  • 每次插入一条数据和每次插入多条数据性能相差很大，相差5-6倍左右

  • 为了找到效率最高的batchNum，这里进行了2组10w、30w的测试，测试发现，每次插入数量并不是越大越好，也不是越小越好，而是在某个区间有个最合适的值，比如这里100就是最合适的值，即当每次插入100条数据时，性能最好，增大数值，性能不一定提升，而且还有可能报错：PacketTooBigException: Packet for query is too large，可以通过调大参数max_allowed_packet或其他方式解决，可以参考之前写的一篇文章：https://www.jianshu.com/p/f010c8d2fae1

  • 小结：每次插入多条数据可以显著提升效率，最佳batchNum需要根据测试结果得出，太小太慢，太大可能报错，另外，可以看到每次插入多条数据存储过程麻烦很多，易出错

3、批量提交事务

3.1 伪代码：

set autocommit = 0;
insert into user(username, password) values('aaa', '123');
insert into user(username, password) values('bbb', '456');
insert into user(username, password) values('ccc', '789');
...
if 1000 commit;

3.2 存储过程：

# 3、批量提交事务
drop procedure if exists insertIntoUser;

delimiter $$
 
create procedure insertIntoUser(in num int, in batchNum int)
    begin
        declare i int default 0;
        
        while i < num do
            set i = i + 1;
            set @username = concat('beigua', LPAD(i, 9, 0));
            set @nickname = concat('北瓜', LPAD(i, 9, 0));
            set @password = replace(uuid(), "-", "");
            set @password_salt = replace(uuid(), "-", "");
            set @user_no = i;

            set autocommit = 0;

            INSERT INTO user(username, password, password_salt, nickname, user_no, ip, mobile, mail, gender, type, status, is_deleted, created_time, updated_time) 
            VALUES (@username, @password, @password_salt, @nickname, @user_no, '192.168.1.1', '18888888888', '18888888888@163.com', '0', '0', '0', '0', now(), now());
        
            if i mod batchNum = 0 then
                commit;
            end if;
        end while;
    end $$

3.3 测试结果：


image.png

3.4 分析

  • 与四.1、每次插入一条数据相比，极大的提升了效率，同样插入10w数据，188s提升至30s

  • 与四.2、每次插入多条数据相比，旗鼓相当，差不太多，但各有优劣：四.2、每次插入多条数据方式，拼接values参数存储过程比较麻烦，容易出错，且batchNum不好把握，太大太小效率都不太高，太大还有可能报错；而四.3、批量提交事务方式，理论上来说，批量提交条数越大效率越高，但是风险也越高，插入过程中如果程序异常退出，将导致这一批次的数据都被回滚

  • 小结：第1种方式不推荐，pass，第2、3种方式，更推荐第3种，因为存储过程简单，而且batchNum相对来说更好把握，同时程序异常退出也毕竟是小概率事件，所以更合适

4、一次性提交所有事务

4.1 伪代码：

set autocommit = 0;
insert into user(username, password) values('aaa', '123');
insert into user(username, password) values('bbb', '456');
insert into user(username, password) values('ccc', '789');
...
commit;

4.2 存储过程：

# 4、一次性提交事务
drop procedure if exists insertIntoUser;

delimiter $$
 
create procedure insertIntoUser(in num int)
    begin
        declare i int default 0;

        set autocommit = 0;
        
        while i < num do
            set i = i + 1;
            set @username = concat('beigua', LPAD(i, 9, 0));
            set @nickname = concat('北瓜', LPAD(i, 9, 0));
            set @password = replace(uuid(), "-", "");
            set @password_salt = replace(uuid(), "-", "");
            set @user_no = i;

            INSERT INTO user(username, password, password_salt, nickname, user_no, ip, mobile, mail, gender, type, status, is_deleted, created_time, updated_time) 
            VALUES (@username, @password, @password_salt, @nickname, @user_no, '192.168.1.1', '18888888888', '18888888888@163.com', '0', '0', '0', '0', now(), now());
        end while;
        
        commit;
    end $$

4.3 测试结果：


image.png

4.4 分析

  • 很明显，该方式是四.3、批量提交事务方式的一种特例，即所有数据插入完成后，再一次性提交所有事务，与3相比，效率确实高一点，不过还是那个问题，程序异常导致回滚的问题更为凸显，所以，折中方案还是推荐方式3

  • 有兴趣的小伙伴也可以试试将第2、3种方式结合起来，看效率是否还能提升

5、数据插入前加索引与数据插入后加索引对比

5.1 说明：索引可以提高查询效率，但同时也要承担维护索引（创建、更新）耗时的代价，通常情况下，建表时就创建好索引了，但是随着数据量的增大，如果每插入一条数据就更新一次索引，势必会影响插入效率，这里通过测试验证一下数据插入前加索引与数据插入后加索引对于数据插入的性能影响

5.2 为了节约时间，采用四.4、一次性提交所有事务方式插入数据，建表时，都为username加了个唯一索引，不考虑该变量的影响，另外再创建4个普通索引，观察这4个索引对于插入耗时的影响即可，脚本：

create index idx_user_password on user(password);
create index idx_user_password_salt on user(password_salt);
create index idx_user_nickname on user(nickname);
create index idx_user_user_no on user(user_no);

5.3 测试结果：


image.png

5.4 分析
可以看到，数据插入前加索引与数据插入后加索引对于插入性能的影响还是挺大的：

  • 当数据量小的时候，差距不明显，如100w之前两种方式都差不多，耗时270s左右

  • 但是随着数据量越来越大，差距也逐渐拉大，可以预料，当数据量大几千万的时候，耗时差距甚至可能达到倍数级别，所以，当需要插入千万级数据的时候，如果可以的话，尽量在数据插入完成后再加索引，可以节省很多时间

五、测试（修改mysql参数情况下）

目前为止，都是在不修改mysql参数的情况下进行的性能优化，而如果条件允许，也可以通过修改mysql参数提升插入效率

这里主要是2组参数：

  • bulk_insert_buffer_size、unique_checks、autocommit

  • innodb_flush_log_at_trx_commit

分别对以上2组参数进行测试，注意，这里都采用四.1、每次插入一条数据方式的存储过程进行测试

1、第一组参数：bulk_insert_buffer_size、unique_checks、autocommit

1.1 查看默认值：

show VARIABLES like 'bulk_insert_buffer_size';
show VARIABLES like 'autocommit';
show VARIABLES like 'unique_checks';

1.2 修改参数：

set global bulk_insert_buffer_size = 104857600;
set session unique_checks = off; 
set session autocommit = off;

1.3 测试结果：


image.png

1.4 分析：

  • 可以看到，修改bulk_insert_buffer_size、unique_checks参数并没有提升效率

  • 而修改autocommit参数极大地提升了效率，关闭自动提交事务，通过合并事务一次提交，可以极大的提升效率，其实这个测试结果跟上面四.3、批量提交事务、四.4、一次性提交所有事务结果一致，原理一样，同样也要注意，程序异常也将导致事务回滚

  • 测试autocommit时，记得需要显式提交事务，否则容易导致长事务的产生，测试时这样调用：

set session autocommit = off;
truncate user;
call insertIntoUser(100000);
commit;

1.5 参数还原：

  • set global bulk_insert_buffer_size = 8388608;

  • session级别的参数修改不会影响其他，无需还原

2、第二组参数：innodb_flush_log_at_trx_commit

2.1 查看默认值，默认值是1：
show VARIABLES like 'innodb_flush_log_at_trx_commit';

2.2 修改参数：
set global innodb_flush_log_at_trx_commit = 0;

2.3 :测试结果：


image.png

可以看到，修改该参数后，效率显著提升，插入相同的10w数据，原本耗时206s现在只需要27s

2.4 参数还原：
set global innodb_flush_log_at_trx_commit = 1;

2.5 innodb_flush_log_at_trx_commit参数说明：

  • 0：log buffer将每秒一次地写入log file中，并且log file的flush（刷到磁盘）操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作，当设置为0，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。

  • 1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush（刷到磁盘）中去，该模式为系统默认，当设置为1，该模式是最安全的，但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。

  • 2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush（刷到磁盘）操作，当设置为2，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失

3、批量提交事务与修改参数对比

从上面的测试结果发现，批量提交事务与修改参数都可以极大的提高插入效率，现在就测一下这2种方式之间的性能差异，数据量从10w - 1000w不等，依次测试然后对比

3.1 测试结果


image.png

3.2 分析

  • 插入性能由高到低依次是：四.4、一次性提交所有事务> 五.2、修改参数：innodb_flush_log_at_trx_commit> 四.3、批量提交事务：10w提交一次

  • 四.3、批量提交事务：10w提交一次：虽降低了风险，但是效率不太高

  • 四.4、一次性提交所有事务：性能最高，但是万一程序异常，前面插入的数据都将回滚，风险比较高

  • 五.2、修改参数：innodb_flush_log_at_trx_commit：效率居中，实现简单，存储过程也不用修改什么，但是，并不是所有的开发、测试环境都支持修改参数，因系统而异，个人认为如果可以，这种方式更合适

4、数据插入前加索引与数据插入后加索引对比

从上面测试结果可以看出，修改参数：innodb_flush_log_at_trx_commit比较合适，现在再测一下在修改了该参数的情况下，数据插入前加索引与数据插入后加索引性能是否有区别

4.1 参照四.5小节，再测试一下，测试结果：

image.png

4.2 分析
修改了innodb_flush_log_at_trx_commit参数后，数据插入前加索引与数据插入后加索引同样有挺大差异，跟四.5测试结果一样，数据插入后再加索引性能更高，所以，造数时，尽可能在数据插入后再加索引

六、总结

1、大批量造数时，如果msyql参数不方便修改，推荐采用四.3、批量提交事务方式，即设定合适的batchNum，比如10w（仅供参考），每10w提交一次事务，既能保证插入效率，又能保证程序异常不至于回滚所有数据

2、如果可以修改mysql参数，推荐采用修改参数：innodb_flush_log_at_trx_commit的方式，原因五.3、批量提交事务与修改参数对比中已说明

3、不论可不可以修改mysql参数，都推荐在数据插入后加索引，可以很大程度上提高插入效率

4、本文所有数据均实测得出，脚本也都可以直接运行，10w - 100w数据量都测了3次取平均值，而500w、1000w的数据量都只测了一次，太费时间，，，希望路过的小伙伴点个赞呀，tks

    本站是提供个人知识管理的网络存储空间，所有内容均由用户发布，不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息，谨防诈骗。如发现有害或侵权内容，请点击一键举报。
    转藏 分享 献花（0）

    来自： hongjing_z > 《Mysql》

    举报/认领

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多