当前位置：首页 > article >正文

MySQL插入优化-性能对比

article 2024/10/26 7:26:52

插入优化主要包括：

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

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

总结：

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

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

查看全文

http://www.kler.cn/news/350763.html

self-supervised learning(BERT和GPT)

AI智能监测系统：全面赋能燃气安全管理的智能化转型方案

K3s部署指南

【算法篇】动态规划类（4）——子序列（笔记）

CentOS 7 将 YUM 源更改为国内镜像源

原理代码解读：基于DiT结构视频生成模型的ControlNet

【v5.3.0】修复订单批量发货提示 isPicUpload is not defined

【笔记】Day2.5.1查询运费模板列表（未完

windows mysql 8.0版本重置root密码

【STM32】C语言复习以及底层寄存器映射

深度学习论文: T-Rex2: Towards Generic Object Detection via Text-Visual Prompt Synergy

利用Python filestream实现文件流读

Python设计方差分析实验

【AI绘画】Midjourney进阶：留白构图详解

Docker 安装 Oracle创建表空间并导入数据库

初尝类型萃取--typename、模板偏特化、和traits之（一）typename

【DBA Part03】国产Linux上Oracle RAC安装-升级-ADG-迁移

TEI text-embeddings-inference文本嵌入模型推理框架

【MySQL】表的查询操作——SELECT

Redis 数据结构与操作详解：从基本命令到高效缓存策略mget-0707

C++之多继承

开通微信小程序需要哪些资料？集师知识付费小程序

性能对比

1. 单条插入自动提交事务

2. 单条插入手动提交事务

3. 批量插入自动提交事务

4. 批量插入手动提交事务

相关文章：