插入优化主要包括：
- 批量插入条数据，而不是单个记录逐条插入。
- 手动提交事务，避免自动提交事务带来的额外开销。
- 使用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;
不过上面两种优化实测没有明显优化。
总结：
- 手动提交事务可以很大程度优化数据插入。
- 批量插入优化的批次大小视情况而定。
单条自动提交事务 < 单条手动提交事务 ≈ 批量插入自动提交事务 < 批量插入手动提交事务
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
