mysql千万级数据库插入速度和读取速度的调整记录

╰+哭是因爲堅強的太久メ 2022-12-23 09:49 86阅读 0赞

一般情况下mysql上百万数据读取和插入更新是没什么问题了，但到了上千万级就会出现很慢，下面我们来看mysql千万级数据库插入速度和读取速度的调整记录吧。

（1）提高[数据库][Link 1]插入性能中心思想：尽量将数据一次性写入到Data File和减少数据库的checkpoint 操作。这次修改了下面四个配置项：   
1）将 innodb\_flush\_log\_at\_trx\_commit 配置设定为0；按过往经验设定为0，插入速度会有很大提高。

0: Write the log buffer to the log file and flush the log file every second, but do nothing at transaction commit.   
1：the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file   
2：the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it   
2）将 innodb\_autoextend\_increment 配置由于默认8M 调整到 128M

此配置项作用主要是当tablespace 空间已经满了后，需要MySQL系统需要自动扩展多少空间，每次tablespace 扩展都会让各个SQL 处于等待状态。增加自动扩展Size可以减少tablespace自动扩展次数。

3）将 innodb\_log\_buffer\_size 配置由于默认1M 调整到 16M

此配置项作用设定innodb 数据库引擎写日志缓存区；将此缓存段增大可以减少数据库写数据文件次数。

4）将 innodb\_log\_file\_size 配置由于默认 8M 调整到 128M

此配置项作用设定innodb 数据库引擎UNDO日志的大小；从而减少数据库checkpoint操作。

经过以上调整，系统插入速度由于原来10分钟几万条提升至1秒1W左右；注：以上参数调整，需要根据不同机器来进行实际调整。特别是 innodb\_flush\_log\_at\_trx\_commit、innodb\_log\_buffer\_size和 innodb\_log\_file\_size 需要谨慎调整；因为涉及MySQL本身的容灾处理。

（2）提升数据库读取速度，重数据库层面上读取速度提升主要由于几点：简化SQL、加索引和分区； 经过检查程序SQL已经是最简单，查询条件上已经增加索引。我们只能用武器：表分区。

\-----------------------------------mysql提高insert into 插入速度的3种方法---------------------------------------

当mysql大批量插入数据的时候就会变的非常慢, mysql提高insert into 插入速度的方法有三种:

### 第一种插入提速方法: ###

如果数据库中的数据已经很多(几百万条), 那么可以 加大mysql配置中的 bulk\_insert\_buffer\_size，这个参数默认为8M

1.  bulk\_insert\_buffer\_size=100M

### 第二种mysql插入提速方法:  ###

改写所有 insert into 语句为 insert delayed into

这个insert delayed不同之处在于：立即返回结果，后台进行处理插入。

### 第三个方法: 一次插入多条数据: ###

insert中插入多条数据，举例:

insert into table values('11','11'),('22','22'),('33','33')...;