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

一、前言

1、 在工作的过程中，我们经常会遇到需要计算的情况。例如，为了测试接口的性能，需要将大量数据插入到数据库中。这种情况下，如果不优化只按普通方式插入的话，速度太慢，太费时间了，无法承受

2、 网上给出的优化方法大都大同小异，但都没有经过测试验证。为了验证这些方法是否有效，并给出一个切实可行的优化方案，本文将对千万条数据插入进行实测。为了提高测试的准确性，将对不同的数据量进行多轮测试，每轮取3次平均值。测试结果应该有一定的参考价值，希望对大家有帮助

二、文章概览

1、测试环境描述、表结构

2、每次插入一个数据测试

3、每次插入多条数据进行测试

4、批量提交交易测试

5、一次提交所有交易测试

6、在一次提交所有事务的情况下，插入数据前加索引和插入数据后加索引的对比

7、修改参数：ize,, test

8、修改参数：test

9、批量提交交易与修改参数对比

10、在修改mysql参数的情况下，插入数据前加索引，插入数据后加索引。

11、总结

三、准备工作1、测试环境说明：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 测试结果：

图像.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  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 测试结果：

图像.png

2.4 分析

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 测试结果：

图像.png

3.4 分析

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 测试结果：

图像.png

4.4 分析

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

5.1 注：索引可以提高查询效率，但同时也要承担维护索引（创建和更新）的耗时成本。正常情况下，建表时会创建索引，但是随着数据量的增加，如果每次插入一条数据都更新索引mysql存储过程循环插入数据，势必会影响插入的效率。这里验证数据插入前加索引和数据插入后加索引对数据插入的性能影响

5.2 为了节省时间，使用四.4、一次提交所有交易插入数据。建表时，无论变量如何，都会添加唯一索引。影响，再创建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 测试结果：

图像.png

5.4 分析

可以看出数据插入前加索引和数据插入后加索引对插入性能有相当大的影响：

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

到此为止，已经在不修改mysql参数的情况下进行了性能优化，如果条件允许，还可以修改mysql参数以提高插入效率

这里主要是2组参数：

分别测试以上两组参数。注意这里使用四.1、一次插入一条数据的存储过程进行测试

1、第一组参数：ize,,

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 测试结果：

图像.png

1.4 分析：

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

1.5 参数恢复：

2、第二组参数：

2.1 查看默认值，默认值为1：

显示喜欢”;

2.2 修改参数：

设置 = 0;

2.3：测试结果：

图像.png

可以看到，修改这个参数后，效率有了明显的提升。插入同样的10w数据原来需要206s，现在只需要27s

2.4 参数恢复：

设置 = 1;

2.5 参数说明：

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

从上面的测试结果可以发现，批量提交交易和修改参数都可以大大提高插入效率。现在让我们测试这两种方法之间的性能差异。数据量从10w到1000w不等，依次测试。然后比较

3.1 个测试结果

图像.png

3.2 分析

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

从上面的测试结果可以看出，修改参数：比较合适。现在，如果修改了参数，在数据插入前添加索引和数据插入后添加索引的性能是有区别的

4.1 参考四.的第5节，再次测试。测试结果：

图像.png

4.2 分析

修改参数后，数据插入前加索引和数据插入后加索引也有很大区别。和四.5的测试结果一样，数据插入后添加索引性能更高，所以数量尽可能多，数据插入后添加索引

六、总结

1、 量产，如果msyql参数不方便修改，建议使用四.3、批量提交交易，即设置一个合适的，比如10w（仅供参考））mysql存储过程循环插入数据，每10w提交一个事务，既能保证插入效率，又能保证程序异常时不会回滚所有数据

2、如果可以修改mysql参数，建议修改参数：方法，原因是五.3、在批量提交的对比中已经说明交易和修改参数

3、无论是否可以修改mysql参数，建议插入数据后添加索引，可以大大提高插入效率

4、 本文所有数据均为实测，脚本也可直接运行。 10w-100w的数据量测了3次取平均值，而500w和1000w的数据量只测了一次。太费时间了，，，希望路过的朋友给我点个赞，tks

文章来源:https://www.jianshu.com/p/127e79e20d1b

------本页内容已结束，喜欢请分享------

感谢您的来访，获取更多精彩文章请收藏本站。