一、前言

1瘫怜、工作過(guò)程中术徊，經(jīng)常遇到需要造數(shù)的情況，比如為了測(cè)試接口性能鲸湃，需要往數(shù)據(jù)庫(kù)插入大量數(shù)據(jù)赠涮，這種情況下，如果僅靠普通的方式插入而不進(jìn)行優(yōu)化的話暗挑，太慢笋除，太耗時(shí)，不能忍

2炸裆、網(wǎng)上給出的優(yōu)化方式大多類(lèi)似垃它，但是并沒(méi)有進(jìn)行實(shí)測(cè)驗(yàn)證，為了驗(yàn)證這些方式是否有效，且給出真正實(shí)用的優(yōu)化方案国拇，本文將對(duì)千萬(wàn)級(jí)數(shù)據(jù)插入進(jìn)行實(shí)測(cè)洛史，為了提高測(cè)試準(zhǔn)確性，會(huì)對(duì)不同的數(shù)據(jù)量進(jìn)行多輪測(cè)試酱吝，每輪測(cè)3次取平均值虹菲，測(cè)試結(jié)果應(yīng)該具有一定的參考性，希望對(duì)大家有幫助

二掉瞳、文章概覽

1毕源、測(cè)試環(huán)境說(shuō)明，表結(jié)構(gòu)
2陕习、每次插入一條數(shù)據(jù)測(cè)試
3霎褐、每次插入多條數(shù)據(jù)測(cè)試
4、批量提交事務(wù)測(cè)試
5该镣、一次性提交所有事務(wù)測(cè)試
6冻璃、在一次性提交所有事務(wù)的情況下，數(shù)據(jù)插入前加索引與數(shù)據(jù)插入后加索引對(duì)比
7损合、修改參數(shù)：bulk_insert_buffer_size省艳、unique_checks、autocommit測(cè)試
8嫁审、修改參數(shù)：innodb_flush_log_at_trx_commit測(cè)試
9跋炕、批量提交事務(wù)與修改參數(shù)對(duì)比
10、在修改mysql參數(shù)情況下律适，數(shù)據(jù)插入前加索引與數(shù)據(jù)插入后加索引對(duì)比
11辐烂、總結(jié)

三、準(zhǔn)備工作

1捂贿、測(cè)試環(huán)境說(shuō)明：

• mysql版本：5.7.21
• 環(huán)境配置：6C8G SSD
• 系統(tǒng)：win10

2纠修、表結(jié)構(gòu)：

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 '密碼隨機(jī)鹽值',
    nickname varchar(255) not null comment '昵稱',
    user_no int default 0 not null comment '用戶編碼',
    ip varchar(255) comment 'IP地址',
    mobile varchar(11) comment '手機(jī)號(hào)',
    mail varchar(255) comment '郵箱',
    gender int default 0 not null comment '性別（0：男，1：女）',
    type int default 0 not null comment '類(lèi)型（0：普通用戶厂僧，1：超級(jí)管理員）',
    status int default 0 not null comment '狀態(tài)（0：正常扣草，1：黑名單，2：已注銷(xiāo)）',
    is_deleted int default 0 not null comment '是否刪除（0：有效颜屠，1：無(wú)效刪除）',
    created_time datetime default now() not null comment '創(chuàng)建時(shí)間',
    updated_time datetime default now() not null comment '更新時(shí)間',
    primary key(id)
) comment = '用戶表';

create unique index unq_idx_user_username on user(username);

四辰妙、測(cè)試（不修改mysql參數(shù)情況下）

1、每次插入一條數(shù)據(jù)

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 存儲(chǔ)過(guò)程：

# 1汽纤、每次插入一條數(shù)據(jù)
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 調(diào)用存儲(chǔ)過(guò)程上岗，進(jìn)行測(cè)試：

truncate user;
call insertIntoUser(100000);

truncate user;
call insertIntoUser(300000);

1.4 測(cè)試結(jié)果：

image.png

可以看到，插入非常慢蕴坪，10w數(shù)據(jù)188s左右肴掷，30w數(shù)據(jù)563s左右

2敬锐、每次插入多條數(shù)據(jù)

2.1 偽代碼：

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

2.2 存儲(chǔ)過(guò)程：

# 2、每次插入多條數(shù)據(jù)
drop procedure if exists insertIntoUserBatch;

delimiter $$

# 創(chuàng)建存儲(chǔ)過(guò)程呆瞻，num表示插入的總行數(shù)台夺，batch表示每次插入的行數(shù)
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
        # 內(nèi)層while循環(huán)用于拼接insert into user(username, password) values('aaa', '123'), ('bbb', '456'), ('ccc', '789')...語(yǔ)句中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語(yǔ)句并執(zhí)行
        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;
    # 數(shù)據(jù)插入完成后，查看表中總記錄數(shù)
    select count(id) from user;
end $$

2.3 測(cè)試結(jié)果：

image.png

2.4 分析

• 每次插入一條數(shù)據(jù)和每次插入多條數(shù)據(jù)性能相差很大痴脾，相差5-6倍左右
• 為了找到效率最高的batchNum颤介，這里進(jìn)行了2組10w、30w的測(cè)試赞赖，測(cè)試發(fā)現(xiàn)滚朵，每次插入數(shù)量并不是越大越好，也不是越小越好前域，而是在某個(gè)區(qū)間有個(gè)最合適的值辕近，比如這里100就是最合適的值，即當(dāng)每次插入100條數(shù)據(jù)時(shí)匿垄，性能最好移宅，增大數(shù)值，性能不一定提升椿疗，而且還有可能報(bào)錯(cuò)：PacketTooBigException: Packet for query is too large漏峰，可以通過(guò)調(diào)大參數(shù)max_allowed_packet或其他方式解決，可以參考之前寫(xiě)的一篇文章：http://www.reibang.com/p/f010c8d2fae1
• 小結(jié)：每次插入多條數(shù)據(jù)可以顯著提升效率届榄，最佳batchNum需要根據(jù)測(cè)試結(jié)果得出浅乔，太小太慢，太大可能報(bào)錯(cuò)痒蓬，另外童擎，可以看到每次插入多條數(shù)據(jù)存儲(chǔ)過(guò)程麻煩很多，易出錯(cuò)

3攻晒、批量提交事務(wù)

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 存儲(chǔ)過(guò)程：

# 3、批量提交事務(wù)
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 測(cè)試結(jié)果：

image.png

3.4 分析

• 與四.1班挖、每次插入一條數(shù)據(jù)相比鲁捏，極大的提升了效率，同樣插入10w數(shù)據(jù)萧芙，188s提升至30s
• 與四.2给梅、每次插入多條數(shù)據(jù)相比，旗鼓相當(dāng)双揪，差不太多动羽，但各有優(yōu)劣：四.2、每次插入多條數(shù)據(jù)方式渔期，拼接values參數(shù)存儲(chǔ)過(guò)程比較麻煩运吓，容易出錯(cuò)渴邦，且batchNum不好把握，太大太小效率都不太高拘哨，太大還有可能報(bào)錯(cuò)谋梭；而四.3、批量提交事務(wù)方式倦青，理論上來(lái)說(shuō)瓮床，批量提交條數(shù)越大效率越高，但是風(fēng)險(xiǎn)也越高产镐，插入過(guò)程中如果程序異常退出隘庄，將導(dǎo)致這一批次的數(shù)據(jù)都被回滾
• 小結(jié)：第1種方式不推薦，pass癣亚，第2峭沦、3種方式，更推薦第3種逃糟，因?yàn)榇鎯?chǔ)過(guò)程簡(jiǎn)單吼鱼，而且batchNum相對(duì)來(lái)說(shuō)更好把握，同時(shí)程序異常退出也畢竟是小概率事件绰咽，所以更合適

4菇肃、一次性提交所有事務(wù)

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 存儲(chǔ)過(guò)程：

# 4、一次性提交事務(wù)
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 測(cè)試結(jié)果：

image.png

4.4 分析

• 很明顯取募，該方式是四.3琐谤、批量提交事務(wù)方式的一種特例，即所有數(shù)據(jù)插入完成后玩敏，再一次性提交所有事務(wù)斗忌，與3相比，效率確實(shí)高一點(diǎn)旺聚，不過(guò)還是那個(gè)問(wèn)題织阳，程序異常導(dǎo)致回滾的問(wèn)題更為凸顯，所以砰粹，折中方案還是推薦方式3
• 有興趣的小伙伴也可以試試將第2唧躲、3種方式結(jié)合起來(lái)，看效率是否還能提升

5碱璃、數(shù)據(jù)插入前加索引與數(shù)據(jù)插入后加索引對(duì)比

5.1 說(shuō)明：索引可以提高查詢效率弄痹，但同時(shí)也要承擔(dān)維護(hù)索引（創(chuàng)建、更新）耗時(shí)的代價(jià)嵌器，通常情況下肛真，建表時(shí)就創(chuàng)建好索引了，但是隨著數(shù)據(jù)量的增大爽航，如果每插入一條數(shù)據(jù)就更新一次索引蚓让，勢(shì)必會(huì)影響插入效率乾忱，這里通過(guò)測(cè)試驗(yàn)證一下數(shù)據(jù)插入前加索引與數(shù)據(jù)插入后加索引對(duì)于數(shù)據(jù)插入的性能影響

5.2 為了節(jié)約時(shí)間，采用四.4凭疮、一次性提交所有事務(wù)方式插入數(shù)據(jù)饭耳，建表時(shí)，都為username加了個(gè)唯一索引执解，不考慮該變量的影響寞肖，另外再創(chuàng)建4個(gè)普通索引，觀察這4個(gè)索引對(duì)于插入耗時(shí)的影響即可衰腌，腳本：

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 測(cè)試結(jié)果：

image.png

5.4 分析
可以看到新蟆，數(shù)據(jù)插入前加索引與數(shù)據(jù)插入后加索引對(duì)于插入性能的影響還是挺大的：

• 當(dāng)數(shù)據(jù)量小的時(shí)候，差距不明顯右蕊，如100w之前兩種方式都差不多琼稻，耗時(shí)270s左右
• 但是隨著數(shù)據(jù)量越來(lái)越大，差距也逐漸拉大饶囚，可以預(yù)料帕翻，當(dāng)數(shù)據(jù)量大幾千萬(wàn)的時(shí)候，耗時(shí)差距甚至可能達(dá)到倍數(shù)級(jí)別萝风，所以嘀掸，當(dāng)需要插入千萬(wàn)級(jí)數(shù)據(jù)的時(shí)候，如果可以的話规惰，盡量在數(shù)據(jù)插入完成后再加索引睬塌，可以節(jié)省很多時(shí)間

五、測(cè)試（修改mysql參數(shù)情況下）

目前為止歇万，都是在不修改mysql參數(shù)的情況下進(jìn)行的性能優(yōu)化揩晴，而如果條件允許，也可以通過(guò)修改mysql參數(shù)提升插入效率

這里主要是2組參數(shù)：

• bulk_insert_buffer_size贪磺、unique_checks硫兰、autocommit
• innodb_flush_log_at_trx_commit

分別對(duì)以上2組參數(shù)進(jìn)行測(cè)試，注意缘挽，這里都采用四.1瞄崇、每次插入一條數(shù)據(jù)方式的存儲(chǔ)過(guò)程進(jìn)行測(cè)試

1、第一組參數(shù)：bulk_insert_buffer_size壕曼、unique_checks、autocommit

1.1 查看默認(rèn)值：

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

1.2 修改參數(shù)：

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

1.3 測(cè)試結(jié)果：

image.png

1.4 分析：

• 可以看到等浊，修改bulk_insert_buffer_size腮郊、unique_checks參數(shù)并沒(méi)有提升效率
• 而修改autocommit參數(shù)極大地提升了效率，關(guān)閉自動(dòng)提交事務(wù)筹燕，通過(guò)合并事務(wù)一次提交轧飞，可以極大的提升效率衅鹿，其實(shí)這個(gè)測(cè)試結(jié)果跟上面四.3、批量提交事務(wù)过咬、四.4大渤、一次性提交所有事務(wù)結(jié)果一致，原理一樣掸绞，同樣也要注意泵三，程序異常也將導(dǎo)致事務(wù)回滾
• 測(cè)試autocommit時(shí)，記得需要顯式提交事務(wù)衔掸，否則容易導(dǎo)致長(zhǎng)事務(wù)的產(chǎn)生烫幕，測(cè)試時(shí)這樣調(diào)用：

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

1.5 參數(shù)還原：

• set global bulk_insert_buffer_size = 8388608;
• session級(jí)別的參數(shù)修改不會(huì)影響其他，無(wú)需還原

2敞映、第二組參數(shù)：innodb_flush_log_at_trx_commit

2.1 查看默認(rèn)值较曼，默認(rèn)值是1：
show VARIABLES like 'innodb_flush_log_at_trx_commit';

2.2 修改參數(shù)：
set global innodb_flush_log_at_trx_commit = 0;

2.3 :測(cè)試結(jié)果：

image.png

可以看到，修改該參數(shù)后振愿，效率顯著提升捷犹，插入相同的10w數(shù)據(jù)，原本耗時(shí)206s現(xiàn)在只需要27s

2.4 參數(shù)還原：
set global innodb_flush_log_at_trx_commit = 1;

2.5 innodb_flush_log_at_trx_commit參數(shù)說(shuō)明：

• 0：log buffer將每秒一次地寫(xiě)入log file中冕末，并且log file的flush（刷到磁盤(pán)）操作同時(shí)進(jìn)行萍歉。該模式下在事務(wù)提交的時(shí)候，不會(huì)主動(dòng)觸發(fā)寫(xiě)入磁盤(pán)的操作栓霜，當(dāng)設(shè)置為0翠桦，該模式速度最快，但不太安全胳蛮，mysqld進(jìn)程的崩潰會(huì)導(dǎo)致上一秒鐘所有事務(wù)數(shù)據(jù)的丟失销凑。

• 1：每次事務(wù)提交時(shí)MySQL都會(huì)把log buffer的數(shù)據(jù)寫(xiě)入log file，并且flush（刷到磁盤(pán)）中去仅炊，該模式為系統(tǒng)默認(rèn)斗幼，當(dāng)設(shè)置為1，該模式是最安全的抚垄，但也是最慢的一種方式蜕窿。在mysqld 服務(wù)崩潰或者服務(wù)器主機(jī)crash的情況下，binary log 只有可能丟失最多一個(gè)語(yǔ)句或者一個(gè)事務(wù)呆馁。

• 2：每次事務(wù)提交時(shí)MySQL都會(huì)把log buffer的數(shù)據(jù)寫(xiě)入log file桐经，但是flush(刷到磁盤(pán))操作并不會(huì)同時(shí)進(jìn)行。該模式下浙滤，MySQL會(huì)每秒執(zhí)行一次 flush（刷到磁盤(pán)）操作阴挣，當(dāng)設(shè)置為2，該模式速度較快纺腊，也比0安全畔咧，只有在操作系統(tǒng)崩潰或者系統(tǒng)斷電的情況下茎芭，上一秒鐘所有事務(wù)數(shù)據(jù)才可能丟失

3、批量提交事務(wù)與修改參數(shù)對(duì)比

從上面的測(cè)試結(jié)果發(fā)現(xiàn)誓沸，批量提交事務(wù)與修改參數(shù)都可以極大的提高插入效率梅桩，現(xiàn)在就測(cè)一下這2種方式之間的性能差異，數(shù)據(jù)量從10w - 1000w不等拜隧，依次測(cè)試然后對(duì)比

3.1 測(cè)試結(jié)果

image.png

3.2 分析

• 插入性能由高到低依次是：四.4宿百、一次性提交所有事務(wù) > 五.2、修改參數(shù)：innodb_flush_log_at_trx_commit > 四.3虹蓄、批量提交事務(wù)：10w提交一次
• 四.3犀呼、批量提交事務(wù)：10w提交一次：雖降低了風(fēng)險(xiǎn)，但是效率不太高
• 四.4薇组、一次性提交所有事務(wù)：性能最高外臂，但是萬(wàn)一程序異常，前面插入的數(shù)據(jù)都將回滾律胀，風(fēng)險(xiǎn)比較高
• 五.2宋光、修改參數(shù)：innodb_flush_log_at_trx_commit：效率居中，實(shí)現(xiàn)簡(jiǎn)單炭菌，存儲(chǔ)過(guò)程也不用修改什么罪佳，但是，并不是所有的開(kāi)發(fā)黑低、測(cè)試環(huán)境都支持修改參數(shù)赘艳，因系統(tǒng)而異，個(gè)人認(rèn)為如果可以克握，這種方式更合適

4蕾管、數(shù)據(jù)插入前加索引與數(shù)據(jù)插入后加索引對(duì)比

從上面測(cè)試結(jié)果可以看出，修改參數(shù)：innodb_flush_log_at_trx_commit比較合適菩暗，現(xiàn)在再測(cè)一下在修改了該參數(shù)的情況下掰曾，數(shù)據(jù)插入前加索引與數(shù)據(jù)插入后加索引性能是否有區(qū)別

4.1 參照四.5小節(jié)，再測(cè)試一下停团，測(cè)試結(jié)果：

image.png

4.2 分析
修改了innodb_flush_log_at_trx_commit參數(shù)后旷坦，數(shù)據(jù)插入前加索引與數(shù)據(jù)插入后加索引同樣有挺大差異，跟四.5測(cè)試結(jié)果一樣佑稠，數(shù)據(jù)插入后再加索引性能更高秒梅，所以，造數(shù)時(shí)舌胶，盡可能在數(shù)據(jù)插入后再加索引

六番电、總結(jié)

1、大批量造數(shù)時(shí)辆琅，如果msyql參數(shù)不方便修改漱办，推薦采用四.3、批量提交事務(wù)方式婉烟，即設(shè)定合適的batchNum娩井，比如10w（僅供參考），每10w提交一次事務(wù)似袁，既能保證插入效率洞辣，又能保證程序異常不至于回滾所有數(shù)據(jù)

2、如果可以修改mysql參數(shù)昙衅，推薦采用修改參數(shù)：innodb_flush_log_at_trx_commit的方式扬霜，原因五.3、批量提交事務(wù)與修改參數(shù)對(duì)比中已說(shuō)明

3而涉、不論可不可以修改mysql參數(shù)著瓶，都推薦在數(shù)據(jù)插入后加索引，可以很大程度上提高插入效率

4啼县、本文所有數(shù)據(jù)均實(shí)測(cè)得出材原，腳本也都可以直接運(yùn)行，10w - 100w數(shù)據(jù)量都測(cè)了3次取平均值季眷，而500w余蟹、1000w的數(shù)據(jù)量都只測(cè)了一次，太費(fèi)時(shí)間子刮，威酒，，希望路過(guò)的小伙伴點(diǎn)個(gè)贊呀挺峡，tks