mysql千万数据快速插入-实战

---

前言

数据量太大了，每天半夜要同步很大数据到 mysql 数据库，其中一张表就上2千万，总计上亿条数据。同步任务每天0点之后开始任务（因为到0之后才能统计前一天数据），业务需求每天早上8点要看到数据。
so, 解决mysql 插入效率就成了重中之重

---

环境

虚拟机
linux
cpu: 16核
内存：64G
mysql5.7

一、配置

附：配置内容有点多，部分属于主从复制相关，mysql主从复制-使用心得
mysql 默认配置对于插入速度比较慢，所以需要做相关的调整。
我的主要部分如下：

back_log=3000
binlog_cache_size=2M
binlog_order_commits=OFF
bulk_insert_buffer_size=64M
innodb_autoextend_increment=64
innodb_buffer_pool_size=32G
innodb_checksums=OFF
innodb_doublewrite=OFF
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT
innodb_io_capacity=2000
innodb_io_capacity_max=4000
innodb_large_prefix=OFF
innodb_log_buffer_size=16M
innodb_log_file_size=1500M
innodb_read_io_threads=32
innodb_write_io_threads=32
innodb_support_xa=OFF
interactive_timeout=7200
join_buffer_size=1M
key_buffer_size=32M
log_output=FILE
log_slave_updates=0
master_info_repository=TABLE
max_allowed_packet=1024M
max_connect_errors=100
max_connections=2000
max_heap_table_size=64M
open_files_limit=40000
query_cache_type=1
query_cache_limit=2M
query_cache_size=64M
read_buffer_size=1M
read_rnd_buffer_size=1M
relay_log_info_repository=TABLE
relay_log_recovery=ON
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=16
slave_pending_jobs_size_max=1536M
skip_external_locking
skip-name-resolve
sort_buffer_size=2M
sync_binlog=0
thread_cache_size=100
tmp_table_size=32M
wait_timeout=7200

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

二、效果

2千多万条数据（20个字段的大表），用时 11771s, 合为 3.3个小时（没有优化前，20小时左右），完全满足需求。

---

总结

1.配置上牺牲了一点点数据安全性来获得更高效的数据插入，如果系统突然宕机或者其它客观因素是可能影响数据完整性的，这个对于我目前业务不影响，我认为这个事情发生概率很小。上述配置使用要具体根据自己实际情况衡量。

2.开始数据库服务器内存一直满载，自己也没注意看，就以为是mysql的问题，但是后面仔细看 top 命令的内存情况，才发现有个 auditd 搞事情，重启后内存一下降了很多，网上有人说我使用的版本有问题。

---

—————— 但行好事莫问前程，你若盛开蝴蝶自来