MySQL千万级数据库数据插入速度调优
问题描述：
普通台式机，采集数据，表中已经有>1000万数据量。采集回来的数据插入表中的时候很慢，每条约100毫秒。
解决方法：
1、加大mysql配置中的bulk_insert_buffer_size，这个参数默认为8M
bulk_insert_buffer_size=100M
2、改写所有insert语句为insert delayed
这个insert delayed不同之处在于：立即返回结果，后台进行处理插入。
还有一个技巧是在一跳insert中插入多条数据，类似insert into tablename values('xxx','xxx'),('yyy','yyy'),('zzz','zzz')...;但是在我采集程序中改sql比较麻烦，故未采用
插入记录时，影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。根据这些情况，可以分别进行优化，本节将介绍优化插入记录速度的几种方法。
一.对于MyISAM引擎表常见的优化方法如下：
1.禁用索引。对于非空表插入记录时，MySQL会根据表的索引对插入记录建立索引。如果插入大量数据，建立索引会降低插入记录的速度。为了解决这种情况可以在插入记录之前禁用索引，数据插入完毕后在开启索引。禁用索引的语句为： ALTER TABLE tb_name DISABLE KEYS; 重新开启索引的语句为： ALTER TABLE table_name ENABLE KEYS; 对于空表批量导入数据，则不需要进行此操作，因为MyISAM引擎的表是在导入数据之后才建立索引的。
2.禁用唯一性检查：数据插入时，MySQL会对插入的记录进行唯一性校验。这种唯一性校验也会降低插入记录的速度。为了降低这种情况对查询速度的影响，可以在插入记录之前禁用唯一性检查，等到记录插入完毕之后再开启。禁用唯一性检查的语句为： SET UNIQUE_CHECKS=0; 开启唯一性检查的语句为： SET UNIQUE_CHECKS=1;
3.使用批量插入。使用一条INSERT语句插入多条记录。如 INSERT INTO table_name VALUES(....),(....),(....)
4.使用LOAD DATA INFILE批量导入 当需要批量导入数据时，使用LOAD DATA INFILE语句导入数据的速度比INSERT语句快。
二.对于InnoDB引擎的表，常见的优化方法如下：
1.禁用唯一性检查。同MyISAM引擎相同，通过 SET global UNIQUE_CHECKS=0; 导入数据之后将该值置1。
2.禁用外键检查。插入数据之前执行禁止对外键的查询，数据插入完成之后再恢复对外键的检查。禁用外键检查语句为：
SET global FOREIGN_KEY_CHECKS=0; 恢复对外键的检查语句为： SET FOREIGN_KEY_CHECKS=1;
3.禁止自动提交。插入数据之前禁止事务的自动提交，数据导入完成之后，执行恢复自动提交操作。禁止自动提交语句为： SET AUTOCOMMIT=0; 恢复自动提交只需将该值置1。
4.set global innodb_flush_log_at_trx_commit=2; #禁止时时同步日志到磁盘
5. max_allowed_packet=1073741824; 配置max_allowed_packet为1G
6. innodb_io_capacity =2000
innodb_io_capacity_max =20000
7. vim /etc/my.cnf ->innodb_flush_method=O_DIRECT
8. 将 innodb_autoextend_increment 配置由于默认8M 调整到 128M innodb_autoextend_increment=128
此配置项作用主要是当tablespace 空间已经满了后，需要MySQL系统需要自动扩展多少空间，每次tablespace 扩展都会让各个SQL 处于等待状态。增加自动扩展Size可以减少tablespace自动扩展次数。
9.将 innodb_log_buffer_size 配置由于默认1M 调整到 128M innodb_log_buffer_size=128M
此配置项作用设定innodb 数据库引擎写日志缓存区；将此缓存段增大可以减少数据库写数据文件次数。
10.将 innodb_log_file_size 配置由于默认 8M 调整到 128M innodb_log_file_size=128M
此配置项作用设定innodb 数据库引擎UNDO日志的大小；从而减少数据库checkpoint操作。
11.锁定表
LOCK TABLES `erp_order_2018` WRITE;
--禁用键
ALTER TABLE erp_order_2018 DISABLE KEYS ;
--插入数据
INSERT INTO `erp_order_2018` VALUES (1,11,'UPDATED');
INSERT INTO `erp_order_2018 ` VALUES (2,11,'UPDATED');
--启用键
ALTER TABLE `erp_order_2018 ` ENABLE KEYS;
--解锁表
UNLOCK TABLES;
12.删除表上所有的索引：ALTER TABLE `table_name` DROP INDEX `column`;
经过以上调整，系统插入速度由于原来10分钟几万条提升至1秒1W左右；注：以上参数调整，需要根据不同机器来进行实际调整。特别是 innodb_flush_log_at_trx_commit、innodb_log_buffer_size和 innodb_log_file_size 需要谨慎调整；因为涉及MySQL本身的容灾处理。
最后生效的my.cnf配置：
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 6144M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 200M
sort_buffer_size = 10M
read_rnd_buffer_size = 5M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
max_allowed_packet=1073741824
bulk_insert_buffer_size=100M
innodb_log_buffer_size=128M
innodb_log_file_size=128M
innodb_autoextend_increment=100
innodb_flush_method=O_DIRECT
innodb_io_capacity=2000
innodb_io_capacity_max=20000
innodb_flush_log_at_trx_commit=2
很好