MySQL批量SQL插入性能优化详解-6ren

MySQL批量SQL插入性能优化详解

转载作者：qq735679552 更新时间：2022-09-29 22:32:09

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章MySQL批量SQL插入性能优化详解由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

对于一些数据量较大的系统，数据库面临的问题除了查询效率低下，还有就是数据入库时间长。特别像报表系统，每天花费在数据导入上的时间可能会长达几个小时或十几个小时之久。因此，优化数据库插入性能是很有意义的.

经过对MySQL innodb的一些性能测试，发现一些可以提高insert效率的方法，供大家参考参考.

1. 一条SQL语句插入多条数据.

常用的插入语句如:

 
    ? 
   
         INSERT 
         INTO 
         `insert_table` (`datetime`, `uid`, `content`, `type`)  
        
         VALUES 
         ( 
         '0' 
         ,  
         'userid_0' 
         ,  
         'content_0' 
         , 0);  
        
         INSERT 
         INTO 
         `insert_table` (`datetime`, `uid`, `content`, `type`)  
        
         VALUES 
         ( 
         '1' 
         ,  
         'userid_1' 
         ,  
         'content_1' 
         , 1);

修改成:

 
    ? 
   
         INSERT 
         INTO 
         `insert_table` (`datetime`, `uid`, `content`, `type`)  
        
         VALUES 
         ( 
         '0' 
         ,  
         'userid_0' 
         ,  
         'content_0' 
         , 0), ( 
         '1' 
         ,  
         'userid_1' 
         ,  
         'content_1' 
         , 1);

修改后的插入操作能够提高程序的插入效率。这里第二种SQL执行效率高的主要原因是合并后日志量（MySQL的binlog和innodb的事务让日志）减少了，降低日志刷盘的数据量和频率，从而提高效率。通过合并SQL语句，同时也能减少SQL语句解析的次数，减少网络传输的IO.

这里提供一些测试对比数据，分别是进行单条数据的导入与转化成一条SQL语句进行导入，分别测试1百、1千、1万条数据记录.

MySQL批量SQL插入性能优化详解

2. 在事务中进行插入处理.

把插入修改成:

 
    ? 
   
         START  
         TRANSACTION 
         ;  
        
         INSERT 
         INTO 
         `insert_table` (`datetime`, `uid`, `content`, `type`)  
        
         VALUES 
         ( 
         '0' 
         ,  
         'userid_0' 
         ,  
         'content_0' 
         , 0);  
        
         INSERT 
         INTO 
         `insert_table` (`datetime`, `uid`, `content`, `type`)  
        
         VALUES 
         ( 
         '1' 
         ,  
         'userid_1' 
         ,  
         'content_1' 
         , 1);  
        
         ...  
        
         COMMIT 
         ;

使用事务可以提高数据的插入效率，这是因为进行一个INSERT操作时，MySQL内部会建立一个事务，在事务内才进行真正插入处理操作。通过使用事务可以减少创建事务的消耗，所有插入都在执行后才进行提交操作.

这里也提供了测试对比，分别是不使用事务与使用事务在记录数为1百、1千、1万的情况.

MySQL批量SQL插入性能优化详解

3. 数据有序插入.

数据有序的插入是指插入记录在主键上是有序排列，例如datetime是记录的主键:

 
    ? 
   
         INSERT 
         INTO 
         `insert_table` (`datetime`, `uid`, `content`, `type`)  
        
         VALUES 
         ( 
         '1' 
         ,  
         'userid_1' 
         ,  
         'content_1' 
         , 1);  
        
         INSERT 
         INTO 
         `insert_table` (`datetime`, `uid`, `content`, `type`)  
        
         VALUES 
         ( 
         '0' 
         ,  
         'userid_0' 
         ,  
         'content_0' 
         , 0);  
        
         INSERT 
         INTO 
         `insert_table` (`datetime`, `uid`, `content`, `type`)  
        
         VALUES 
         ( 
         '2' 
         ,  
         'userid_2' 
         ,  
         'content_2' 
         ,2);

修改成:

 
    ? 
   
         INSERT 
         INTO 
         `insert_table` (`datetime`, `uid`, `content`, `type`)  
        
         VALUES 
         ( 
         '0' 
         ,  
         'userid_0' 
         ,  
         'content_0' 
         , 0);  
        
         INSERT 
         INTO 
         `insert_table` (`datetime`, `uid`, `content`, `type`)  
        
         VALUES 
         ( 
         '1' 
         ,  
         'userid_1' 
         ,  
         'content_1' 
         , 1);  
        
         INSERT 
         INTO 
         `insert_table` (`datetime`, `uid`, `content`, `type`)  
        
         VALUES 
         ( 
         '2' 
         ,  
         'userid_2' 
         ,  
         'content_2' 
         ,2);

由于数据库插入时，需要维护索引数据，无序的记录会增大维护索引的成本。我们可以参照innodb使用的B+tree索引，如果每次插入记录都在索引的最后面，索引的定位效率很高，并且对索引调整较小；如果插入的记录在索引中间，需要B+tree进行分裂合并等处理，会消耗比较多计算资源，并且插入记录的索引定位效率会下降，数据量较大时会有频繁的磁盘操作.

下面提供随机数据与顺序数据的性能对比，分别是记录为1百、1千、1万、10万、100万.

MySQL批量SQL插入性能优化详解

从测试结果来看，该优化方法的性能有所提高，但是提高并不是很明显.

性能综合测试:

这里提供了同时使用上面三种方法进行INSERT效率优化的测试.

MySQL批量SQL插入性能优化详解

从测试结果可以看到，合并数据+事务的方法在较小数据量时，性能提高是很明显的，数据量较大时（1千万以上），性能会急剧下降，这是由于此时数据量超过了innodb_buffer的容量，每次定位索引涉及较多的磁盘读写操作，性能下降较快。而使用合并数据+事务+有序数据的方式在数据量达到千万级以上表现依旧是良好，在数据量较大时，有序数据索引定位较为方便，不需要频繁对磁盘进行读写操作，所以可以维持较高的性能.

注意事项:

1. SQL语句是有长度限制，在进行数据合并在同一SQL中务必不能超过SQL长度限制，通过max_allowed_packet配置可以修改，默认是1M，测试时修改为8M.

2. 事务需要控制大小，事务太大可能会影响执行的效率。MySQL有innodb_log_buffer_size配置项，超过这个值会把innodb的数据刷到磁盘中，这时，效率会有所下降。所以比较好的做法是，在数据达到这个这个值前进行事务提交.

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持我.

最后此篇关于MySQL批量SQL插入性能优化详解的文章就讲到这里了,如果你想了解更多关于MySQL批量SQL插入性能优化详解的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！。

文章推荐：浅谈PHP的数据库接口和技术

文章推荐：寻找sql注入的网站的方法(必看)

文章推荐： magento后台无法登录解决办法的两种方法

文章推荐：细说MySQL死锁与日志二三事

java - JPA/Hibernate 批量(批量)插入
这是我在阅读了几个关于 jpa 批量插入的主题后创建的简单示例，我有 2 个持久对象用户和站点。一个用户可以有多个站点，所以我们在这里有一对多的关系。假设我想创建用户并将多个站点创建/链接到用户帐户。
azure - 如何在文档数据库中上传多个文档(批量)
我有文档列表(对象)，该对象有多个文档，即存在 Json 记录，但是当我尝试上传文档束(记录)时，它没有上传到文档数据库，但当我上传单个文档记录时，它上传成功。 List listObj = ne
perl - 如何检查域名是否可用(批量)？
我希望进行批量域名查找，看看是否有一些域名可供购买。我找不到 perl 模块，但似乎应该有一种方法可以在 perl 中执行此操作。我正在寻找免费的东西。谢谢! 最佳答案从这里:http://www.
axapta - 批量 FTPWebRequest
我制作了一个批处理类来检查 FTP 上的文件、下载它们并在 FTP 上删除它们。当我手动运行它(不是批量运行)时，它运行完美，下载 FTP 中的所有文件并在下载完成后删除它们。当我尝试批量运行时，
string - 批量 * 通配符替换
我有一个 *+* 形式的字符串 base。我想得到+之前的所有内容。例如，如果 base=foo+bar，我想获取 foo。我尝试过使用字符串替换来实现 set left=%base:+*=% 但这
mysql - 如何使MySQL默认引擎为innodb？(批量)
我需要创建几十个表，并且我需要它们是innodb，有没有办法做到这一点，而不是将 engine=innodb 附加到每个 create table 语句？最佳答案可以在服务器级别指定默认引擎，在
linux - 如何在windows中获取unix风格的提示符(批量)
我正在尝试制作显示 unix/linux 提示符的 dos shell。代码是: @echo off :hi set tmpdrv=%cd:~0,2% if %homedrive% == %tmpdr
matlab - 批量/并行进行一维卷积
我有以下代码，基本上是在二维矩阵的每一行上进行一维卷积。卷积核是一样的。所以真的是 SIMD 案例。 a = [ 1,2,3,4,5; 6,7,8,9,7; 7,6
windows - 如何通过文件夹循环移动文件夹(批量)？
情况: 我尝试在 shell 中的循环内移动文件，但我的代码无法正常工作。 for /D %%F in (*) do ( if "%%F" NEQ "%directoryToPutFilesIn
windows - 批量 |回显多个变量到文件
目录包含 2 个(或更多)任意名称的视频文件。 video1.mkv video2.mkv 需要找出每个视频的持续时间。为此，我们使用 MediaInfo . setlocal EnableDelay
windows - 如何从文件名中删除空格(批量)
如何在 Windows 中批量删除数千个文件中的空格(而不是替换为下划线)？我可以从 DOS 命令执行此操作吗？目前: file one.mp3 file two.mp3 所有文件需要变成: fil
windows - 批量 IF 变量比较神秘地不起作用
我想创建一个批处理文件，它读取 2 个不同的值，并根据它们的比较方式进行相应处理。但是，比较永远不会起作用。代码是: REM string1 and string2 contain the follo
windows - 批量 - 使用通配符将文件夹复制到多个文件夹
我正在尝试将一个文件夹的子文件夹复制到许多其他名称未知的文件夹中。目的是在所有使用它的员工文件夹中备份程序的源文件。如果在员工文件夹中找不到程序文件夹，则不应执行任何操作。这看起来如下: 来源: F:
python - 检测文本是否为英文(批量)
我正在寻找一种简单的方法来检测一小段文本(几句话)是否为英语。在我看来，这个问题比尝试检测任意语言要容易得多。有没有可以做到这一点的软件？我正在用 python 编写，并且更喜欢 python 库，但
android - 批量 Firebase 云消息传递的限制
我们正在尝试向 8k 种不同的设备发送促销推送消息。我们正在成功响应推送通知 URL https://fcm.googleapis.com/fcm/send 但只有部分用户收到此通知，并非全部。那么
batch-file - 批量，用延迟扩展参数替换延迟扩展字符串
基本上我只是用这一段来替换我的 var 中的一个字符串，但我无法让嵌套延迟扩展正常工作。这甚至可能吗？ set replace=!replace:!search!=!replaceVal!! 我知道执
bash - FFmpeg:一次编码多个视频文件(批量)
如何使用 ffmpeg 对一批视频文件进行编码，使用相同的设置？我找到了 one-line solution将当前文件夹中的 .avi 文件转换为 .mov。请注意，我要编码 .mov -> .mo
batch-file - "Counter"批量
我正在尝试制作一个批处理文件，每次循环时都会将变量增加 1，然后检查变量是否等于 5，如果不是，则再次循环。我知道这可能有一个 while 循环，但我不知道如何做到这一点，我现在只是享受学习 Batc
读取带有变量行的 CSV 以跳过、批量
我正在尝试创建一个循环，读取多个 CSV 文件，这些文件都具有相同类型的气温数据。但是，我想跳过数据上方的行。这些是数据集中的“警报”。每个文件可能有不同数量的警报，因此要跳过不同数量的行。见下文:
batch-file - 在文件中回显多个单词会使它崩溃。 (批量)
因此，我正在批量创建一个Mail程序，而消息传递部分出现了问题。消息传递部分是无限循环。当我输入多个单词时，它会崩溃。这是代码。请帮忙! :rep set line= set /p line=

qq735679552

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

MySQL批量SQL插入性能优化详解