插入优化主要包括：
- 批量插入条数据，而不是单个记录逐条插入。
- 手动提交事务，避免自动提交事务带来的额外开销。
- 使用load命令从本地文件导入。
性能对比
创建数据库表
CREATE TABLE if not exists `tb_sku`
( `id` int(20) NOT NULL primary key AUTO_INCREMENT, `sn` varchar(64) NOT NULL, `name` varchar(64) NOT NULL, `price` decimal(10, 2) NOT NULL, `stock` int(11) NOT NULL, `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `category_name` varchar(64) NOT NULL, `brand_name` varchar(64) NOT NULL, `status` tinyint(4) NOT NULL DEFAULT '1', `description` varchar(1024) DEFAULT NULL
);
1. 单条插入自动提交事务
创建存储过程构建数据
drop procedure if exists `insertSkuData`;
DELIMITER //
create procedure `insertSkuData`(in num int)
begin declare i int default 1; declare sn varchar(64); declare name varchar(64); declare sql_stmt TEXT; -- 临时表作为数组,目的是为了随机取值 CREATE TABLE if not exists `tmp_name` ( id int primary key auto_increment, t_name varchar(64) ); insert into tmp_name(t_name) values ('华为Mate60'), ('华为Mate70'), ('华为Mate80'), ('华为Mate90'), ('华为Mate100'), ('华为Mate110'), ('华为Mate120'), ('华为Mate130'), ('华为Mate140'), ('华为Mate150'); insert into tmp_name(t_name) values ('小米Mate60'), ('小米Mate70'), ('小米Mate80'), ('小米Mate90'), ('小米Mate100'), ('小米Mate110'), ('小米Mate120'), ('小米Mate130'), ('小米Mate140'), ('小米Mate150'); insert into tmp_name(t_name) values ('oppoMate60'), ('oppoMate70'), ('oppoMate80'), ('oppoMate90'), ('oppoMate100'), ('oppoMate110'), ('oppoMate120'); while i <= num do SET sql_stmt = 'insert into tb_sku(sn, name, price, stock, category_name, brand_name, description) values'; SET sn = UPPER(REPLACE(UUID(), '-', '')); SET name = (select t_name from tmp_name order by rand() limit 1); SET sql_stmt = CONCAT(sql_stmt, '(\'', sn, '\',\'', name, '\',', 999.0, ',', 100, ',', '\'手机\'', ',\'', SUBSTR(name, 1, 2), '\',', '\'由存储过程insertSkuData生成\'', ')'); -- 执行SQL语句 set @sql_stmt = sql_stmt; prepare stmt from @sql_stmt; execute stmt; deallocate prepare stmt; SET i = i + 1; end while; drop table if exists tmp_name;
end// DELIMITER ; call insertSkuData(1000000);
执行结果耗时：2m52s
2. 单条插入手动提交事务
创建存储过程构建数据
drop procedure if exists `insertSkuData`;
DELIMITER //
create procedure `insertSkuData`(in num int)
begin declare i int default 1; declare sn varchar(64); declare name varchar(64); declare sql_stmt TEXT; -- 临时表作为数组,目的是为了随机取值 CREATE TABLE if not exists `tmp_name` ( id int primary key auto_increment, t_name varchar(64) ); insert into tmp_name(t_name) values ('华为Mate60'), ('华为Mate70'), ('华为Mate80'), ('华为Mate90'), ('华为Mate100'), ('华为Mate110'), ('华为Mate120'), ('华为Mate130'), ('华为Mate140'), ('华为Mate150'); insert into tmp_name(t_name) values ('小米Mate60'), ('小米Mate70'), ('小米Mate80'), ('小米Mate90'), ('小米Mate100'), ('小米Mate110'), ('小米Mate120'), ('小米Mate130'), ('小米Mate140'), ('小米Mate150'); insert into tmp_name(t_name) values ('oppoMate60'), ('oppoMate70'), ('oppoMate80'), ('oppoMate90'), ('oppoMate100'), ('oppoMate110'), ('oppoMate120'); -- 手动提交事务，将所有数据作为一次事务提交 start transaction ; while i <= num do SET sql_stmt = 'insert into tb_sku(sn, name, price, stock, category_name, brand_name, description) values'; SET sn = UPPER(REPLACE(UUID(), '-', '')); SET name = (select t_name from tmp_name order by rand() limit 1); SET sql_stmt = CONCAT(sql_stmt, '(\'', sn, '\',\'', name, '\',', 999.0, ',', 100, ',', '\'手机\'', ',\'', SUBSTR(name, 1, 2), '\',', '\'由存储过程insertSkuData生成\'', ')'); -- 执行SQL语句 set @sql_stmt = sql_stmt; prepare stmt from @sql_stmt; execute stmt; deallocate prepare stmt; SET i = i + 1; end while; commit ; drop table if exists tmp_name;
end// DELIMITER ; call insertSkuData(1000000);
执行结果耗时：1m7s：
3. 批量插入自动提交事务
创建存储过程构建数据：
drop procedure if exists `insertSkuData`;
DELIMITER //
create procedure `insertSkuData`(in num int)
begin declare i int default 1; declare j int default 1; declare batch_size int default 10; declare cnt int default 0; declare sn varchar(64); declare name varchar(64); declare sql_stmt TEXT; -- 计算批次，比如10000条记录，则分10批次，一个批次1000条记录插入数据 if MOD(num, batch_size) = 0 then set cnt = num / batch_size; else set cnt = num / batch_size + 1; end if; -- 临时表作为数组,目的是为了随机取值 CREATE TABLE if not exists `tmp_name` ( id int primary key auto_increment, t_name varchar(64) ); insert into tmp_name(t_name) values ('华为Mate60'), ('华为Mate70'), ('华为Mate80'), ('华为Mate90'), ('华为Mate100'), ('华为Mate110'), ('华为Mate120'), ('华为Mate130'), ('华为Mate140'), ('华为Mate150'); insert into tmp_name(t_name) values ('小米Mate60'), ('小米Mate70'), ('小米Mate80'), ('小米Mate90'), ('小米Mate100'), ('小米Mate110'), ('小米Mate120'), ('小米Mate130'), ('小米Mate140'), ('小米Mate150'); insert into tmp_name(t_name) values ('oppoMate60'), ('oppoMate70'), ('oppoMate80'), ('oppoMate90'), ('oppoMate100'), ('oppoMate110'), ('oppoMate120'); while i <= cnt do if i = cnt then set batch_size = num - (cnt - 1) * batch_size; end if; SET sql_stmt = 'insert into tb_sku(sn, name, price, stock, category_name, brand_name, description) values'; SET j = 1; while j <= batch_size do SET sn = UPPER(REPLACE(UUID(), '-', '')); SET name = (select t_name from tmp_name order by rand() limit 1); SET sql_stmt = CONCAT(sql_stmt, '(\'', sn, '\',\'', name, '\',', 999.0, ',', 100, ',', '\'手机\'', ',\'', SUBSTR(name, 1, 2), '\',', '\'由存储过程insertSkuData生成\'', ')'); IF j < batch_size THEN SET sql_stmt = CONCAT(sql_stmt, ', '); END IF; SET j = j + 1; end while; -- 执行SQL语句 set @sql_stmt = sql_stmt; prepare stmt from @sql_stmt; execute stmt; deallocate prepare stmt; SET i = i + 1; end while;drop table if exists tmp_name;
end// DELIMITER ;
call insertSkuData(1000000);
执行结果耗时：1m5s：
4. 批量插入手动提交事务
创建存储过程构建数据：
drop procedure if exists `insertSkuData`;
DELIMITER //
create procedure `insertSkuData`(in num int)
begin declare i int default 1; declare j int default 1; declare batch_size int default 10; declare cnt int default 0; declare sn varchar(64); declare name varchar(64); declare sql_stmt TEXT; -- 计算批次，比如10000条记录，则分10批次，一个批次1000条记录插入数据 if MOD(num, batch_size) = 0 then set cnt = num / batch_size; else set cnt = num / batch_size + 1; end if; -- 临时表作为数组,目的是为了随机取值 CREATE TABLE if not exists `tmp_name` ( id int primary key auto_increment, t_name varchar(64) ); insert into tmp_name(t_name) values ('华为Mate60'), ('华为Mate70'), ('华为Mate80'), ('华为Mate90'), ('华为Mate100'), ('华为Mate110'), ('华为Mate120'), ('华为Mate130'), ('华为Mate140'), ('华为Mate150'); insert into tmp_name(t_name) values ('小米Mate60'), ('小米Mate70'), ('小米Mate80'), ('小米Mate90'), ('小米Mate100'), ('小米Mate110'), ('小米Mate120'), ('小米Mate130'), ('小米Mate140'), ('小米Mate150'); insert into tmp_name(t_name) values ('oppoMate60'), ('oppoMate70'), ('oppoMate80'), ('oppoMate90'), ('oppoMate100'), ('oppoMate110'), ('oppoMate120'); -- 整个数据作为一次事务提交 start transaction ; while i <= cnt do if i = cnt then set batch_size = num - (cnt - 1) * batch_size; end if; SET sql_stmt = 'insert into tb_sku(sn, name, price, stock, category_name, brand_name, description) values'; SET j = 1; while j <= batch_size do SET sn = UPPER(REPLACE(UUID(), '-', '')); SET name = (select t_name from tmp_name order by rand() limit 1); SET sql_stmt = CONCAT(sql_stmt, '(\'', sn, '\',\'', name, '\',', 999.0, ',', 100, ',', '\'手机\'', ',\'', SUBSTR(name, 1, 2), '\',', '\'由存储过程insertSkuData生成\'', ')'); IF j < batch_size THEN SET sql_stmt = CONCAT(sql_stmt, ', '); END IF; SET j = j + 1; end while; -- 执行SQL语句 set @sql_stmt = sql_stmt; prepare stmt from @sql_stmt; execute stmt; deallocate prepare stmt; SET i = i + 1; end while; commit; drop table if exists tmp_name;
end// DELIMITER ;
call insertSkuData(1000000);
执行结果耗时：45s：
Note：批量插入的大小根据物理性能而定。
- 可以在插入数据前，禁用唯一性检查，结束后开启
-- 关闭唯一性检查
SET UNIQUE_CHECKS=0;-- 开启
SET UNIQUE_CHECKS=1;
- 可以在插入数据前，禁用外键检查，结束后开启
-- 关闭外键检查
SET FOREIGN_KEY_CHECKS=0;-- 开启
SET FOREIGN_KEY_CHECKS=1;
不过上面两种优化实测没有明显优化。
总结：
- 手动提交事务可以很大程度优化数据插入。
- 批量插入优化的批次大小视情况而定。
单条自动提交事务 < 单条手动提交事务 ≈ 批量插入自动提交事务 < 批量插入手动提交事务
