网页模板在线设计_手机开发人员选项怎么打开_站长工具端口检测_百度百家号官网

插入优化主要包括：

批量插入条数据，而不是单个记录逐条插入。
手动提交事务，避免自动提交事务带来的额外开销。
使用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;

不过上面两种优化实测没有明显优化。

总结：

手动提交事务可以很大程度优化数据插入。
批量插入优化的批次大小视情况而定。

单条自动提交事务 < 单条手动提交事务 ≈ 批量插入自动提交事务 < 批量插入手动提交事务