MySQL插入千万级数据记录

FROM:

https://www.toutiao.com/a6635503876340449799/

 

场景：

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

1、尽量将数据一次性写入DataFile和减少数据库的checkpoint操作，具体有：

1）将 innodb_flush_log_at_trx_commit 配置设定为0；

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

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

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

2）增大innodb_log_buffer_size

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

3）增大innodb_log_file_size

此配置项作用是设定innodb 数据库引擎UNDO日志的大小，增大配置可以减少数据库checkpoint操作。

4）增大bulk_insert_buffer_size，

Max_allowed_packet=1M

Net_buffer_length=8k

此配置的作用是可以增大insert 语句的大小，便于批量插入。

2、改写SQL语句

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

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

2）批量一次插入多条语句

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

3、数据库表可以先去除索引

4、程序上采用多线程处理，主子任务减少系统IO的等待时间。