MySQL插入千万级记录

西木风落

已于 2022-03-13 21:17:36 修改

阅读量5.3k

点赞数

分类专栏：数据库学习文章标签： mysql 千万级数据插入

于 2017-05-01 22:36:14 首次发布

本文链接：https://blog.csdn.net/chenwiehuang/article/details/71075882

版权

数据库学习专栏收录该内容

15 篇文章 1 订阅

订阅专栏

场景：当产品中需要对用户进行过滤的时候，需要对用户添加白名单。如果一次需要导入千万级的用户量，普通的SQL插入/更新耗时长久，执行效率低下。为加快数据库的插入效率，需要对程序和数据库设置进行优化。

1. 为什么批量插入比逐条插入快

一条SQL的执行过程可参见：MySQL语句执行过程_西木风落-CSDN博客

数据库的一个插入动作，包含了连接，传输，执行，提交/回滚等的动作。

网络传输方面来说，批量插入多条数据，更省空间。
连接数量来说，批量插入使用一个连接，在使用数据库连接池情况下，逐个插入可能会占用多个连接。
通过合并SQL语句，同时也能减少SQL语句解析的次数；
从事务方面来说，逐条插入每次都会新建一个事务，批量插入只会使用一个事务。
从日志方面来说，由于逐条插入每次都会插入binlog事务日志，合并后日志量减少了，降低日志刷盘的数据量和频率，从而提高效率。
如果存在主键索引，逐条多线程插入抢锁竞争激烈；

批量插入比逐条插入并不一定快，比如插入的数据是业务表，每行的数据量较大，且该表访问频繁，插入1条的时候可能不会锁表，而插入10000行的时候，会遇到锁表的情况。如果并发插入，甚至发生死锁。

2. 插入语句优化

2.1 合并sql插入

INSERT INTO `tb` (`id`, `name`) VALUES ('1', 'lianggzone'）;
INSERT INTO `tb` (`id`, `name`) VALUES ('2', 'lianggzone'）;

-- 转换成

INSERT INTO `tb` (`id`, `name`) VALUES ('1', 'lianggzone'）, ('2', 'lianggzone'）；

2.2 在一个事务中插入

start transaction;
INSERT INTO `tb` (`id`, `name`) VALUES ('1', 'lianggzone'）, ('2', 'lianggzone'）；

INSERT INTO `tb` (`id`, `name`) VALUES ('3', 'lianggzone'）, ('4', 'lianggzone'）；
commit;

2.3 insert into 改成insert delayed into

INSERT DELAYED INTO，是客户端提交数据给MySQL，MySQL返回OK状态给客户端，这时并不是已经将数据插入表，而是存储在内存里面等待排队。当mysql有空余时，再插入。这样的好处是，提高插入的速度，客户端不需要等待太长时间。坏处是，不能返回自动递增的ID，以及系统崩溃时，MySQL还没有来得及插入数据的话，这些数据将会丢失。

3. 配置优化

3.1 尽量将数据一次性写入DataFile和减少数据库的checkpoint操作

将 innodb_flush_log_at_trx_commit 配置设定为0；

0：表示每秒钟将日志缓存写到日志文件，但是事务提交的时候不做操作；

1：表示日志缓存每一次事务提交都写入日志文件，并且日志文件会刷新到磁盘；

2：表示日志缓存每一次事务提交都会写入日志文件，但不执行磁盘操作。

增大innodb_log_buffer_size：此配置项作用是设定innodb 数据库引擎写日志缓存区，增大可以减少数据库写数据文件次数。
增大innodb_log_file_size：此配置项作用是设定innodb 数据库引擎UNDO日志的大小，增大配置可以减少数据库checkpoint操作。
增大bulk_insert_buffer_size：修改Max_allowed_packet=8M（默认1M），Net_buffer_length=8k，此配置的作用是可以增大insert 语句的大小，便于批量插入。