MySQL提高批量insert的性能

比眉伴天荒 2024-03-30 11:53 10阅读 0赞

一. 使用批量插入，将多条单独的 insert 合并成一次操作

即：insert into table values (a1, b1, c1), (a2, b2, c2);

解析：将多条 insert 合并后，减少MySQL日志量(即MySQL的 binlog 和 innodb 的事务日志)，降低日志刷盘的数据量和频率，从而提高效率。通过合并多条 insert 语句，还能减少SQL语句的解析次数，减少和数据库的交互，减少了网络传输的IO。

二. 修改批量插入缓冲区大小参数: bulk\_insert\_buffer\_size，调大批量插入的缓存

这个参数默认为 8M，例如设置成 100M

set global bulk\_insert\_buffer\_size = 1024\*1024\*100;

三. 设置 innodb\_flush\_log\_at\_trx\_commit = 0

相对于 innodb\_flush\_log\_at\_trx\_commit = 1, 设置为 0 可以明显的提高导入的速度。

set global innodb\_flush\_log\_at\_trx\_commit=0;

解析：

0: log buffer 中的数据将以每秒一次的频率写入到 log file 中，且同时会进行文件系统到磁盘的同步操作，但是每个事务的 commit 并不会触发任何的 log buffer 到 log file 的刷新或者从文件系统到磁盘的刷新操作。

1: 在每次事务提交的时候将 log buffer 中的数据都会写入到 log file，同时也会触发文件系统到磁盘的同步；

2: 事务提交会触发 log buffer 到 log file 的刷新，但并不会触发磁盘文件系统到磁盘的同步。此外，每秒会有一次文件系统到磁盘的同步操作。

四. 设置事务提交为 手动提交

因为 MySQL 事务默认是自动提交的(autocommit)，这样每插入一条数据，都会进行一次 commit，所以通过手动提交事务，可以减少创建事务时的消耗。一般可以设置为 1000 条insert 提交一次。  
**【解决方案1】：**

通过在 my.cnf中添加以下行。我能够做到。

innodb_autoinc_lock_mode =2
    sync_binlog=1
    bulk_insert_buffer_size=512M
    key_buffer_size=512M
    read_buffer = 50M
    Max_allowed_packet=1M
    Net_buffer_length=8k

和 **innodb\_flush\_log\_at\_trx\_commit=2**，我在另一个链接中看到它说它将速度提高到 160 倍。 输出性能：超过 24 小时到 2 小时;

通常将innodb\_buffer\_pool\_size其配置为物理内存的50％到75％

innodb\_flush\_log\_at\_trx\_commit，1改为了0

2.尝试修改参数加快导入速度

在MySQL中，有一对大名鼎鼎的“双一”参数，即 innodb\_flush\_log\_at\_trx\_commit 与 sync\_binlog 。为了安全性这两个参数默认值为1，为了快速导入脚本，我们可以临时修改下这两个参数，下面简单介绍下这两个参数：

> **innodb\_flush\_log\_at\_trx\_commit默认值为1，可设置为0、1、2**
> 
> 如果innodb\_flush\_log\_at\_trx\_commit设置为0，log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行.该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。 如果innodb\_flush\_log\_at\_trx\_commit设置为1，每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去. 如果innodb\_flush\_log\_at\_trx\_commit设置为2，每次事务提交时MySQL都会把log buffer的数据写入log file.但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。
> 
> **sync\_binlog默认值为1，可设置为\[0,N)**
> 
> 当sync\_binlog =0，像操作系统刷其他文件的机制一样，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。 当sync\_binlog =N (N>0) ，MySQL 在每写 N次 二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

这两个参数可以在线修改，若想快速导入，可以按照下面步骤来操作：

# 1.进入MySQL命令行 临时修改这两个参数setglobalinnodb_flush_log_at_trx_commit = 2;setglobalsync_binlog = 2000;# 2.执行SQL脚本导入mysql -uroot -pxxxxxx testdb < testdb.sql# 3.导入完成 再把参数改回来setglobalinnodb_flush_log_at_trx_commit = 1;setglobalsync_binlog = 1;

还有另外一种场景是你的需求是新建从库，或者是不需要产生binlog，这时候导入SQL脚本时可以设置暂时不记录binlog，可以在脚本开头增加 set sql\_log\_bin=0; 然后再执行导入，这样速度会进一步加快。如果你的MySQL实例没有开启binlog则不需要再执行该语句了。

max\_connection=1000

innodb\_file\_per\_table=1

innodb\_buffer\_pool\_size=1G

innodb\_log\_file\_size=256M

innodb\_log\_buffer\_size=256M

innodb\_flush\_log\_at\_trx\_commit=2 需要放mysqlld\_safe节点下。

![a7f74952656d4f6f9088c5b50708917a.png][]

symbolic-links=0  
innodb\_lru\_scan\_depth=256  
\#sync\_binlog=0  
\#innodb\_flush\_log\_at\_trx\_commit=0  
wait\_timeout=2880000  
\#interactive\_time=2880000  
max\_allowed\_packet=1G  
max\_connections = 1000  
innodb\_file\_per\_table=1  
innodb\_buffer\_pool\_size=10G  
innodb\_log\_file\_size=256M  
innodb\_log\_buffer\_size=256M  
bulk\_insert\_buffer\_size = 1G  
innodb\_flush\_log\_at\_trx\_commit=0  
innodb\_autoinc\_lock\_mode =2  
sync\_binlog=0  
bulk\_insert\_buffer\_size=512M  
key\_buffer\_size=512M  
\#read\_buffer=50M  
net\_buffer\_length=8k  
character\_set\_server = utf8mb4  
sql\_mode = NO\_ENGINE\_SUBSTITUTION

## MySQL大数据量插入数据参数优化 ##

关于一些配置项优化  
\[client\]  
port = 3306  
socket = /var/lib/mysql/mysql.sock

\[mysql.server\]  
pid-file = /var/lib/mysql/localhost.pid

\[mysqld\]  
port = 3306  
socket =/var/lib/mysql/mysql.sock

\# 网络传输中一次消息传输量的最大值。系统默认值 为1MB，最大值是1GB，必须设置1024的倍数。  
\# 因为后续我们在代码中是使用prepareStatement.addBatch()，单次传输的值会比较大，所以这个参数必须加大。  
max\_allowed\_packet = 32M  
\# 缓冲池大小，只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。  
innodb\_buffer\_pool\_size = 5120M  
innodb\_buffer\_pool\_instances=3  
innodb\_additional\_mem\_pool\_size = 1024M  
innodb\_log\_files\_in\_group = 3  
innodb\_lock\_wait\_timeout = 120  
\# 刷新日志频率  
open\_files\_limit = 10240  
back\_log = 600  
innodb\_log\_file\_size=110M  
\# 连接数  
max\_connections = 3000  
max\_connect\_errors = 6000  
\# 多线程  
thread\_cache\_size = 300  
thread\_concurrency = 16  
thread\_stack = 192KB  
innodb\_write\_io\_threads = 32  
innodb\_purge\_threads=1  
\# 刷新脏页频率  
innodb\_max\_dirty\_pages\_pct=90  
wait\_timeout=300

详细的参数配置  
[innodb缓冲池相关配置][innodb]

[MySQL性能优化][MySQL]

[a7f74952656d4f6f9088c5b50708917a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/5cc7ffa1570342138ce628d858095820.png
[innodb]: https://links.jianshu.com/go?to=https%3A%2F%2Fwww.cnblogs.com%2Fwanbin%2Fp%2F9530833.html
[MySQL]: https://links.jianshu.com/go?to=https%3A%2F%2Fwww.cnblogs.com%2Fshoshana-kong%2Fp%2F10517396.html