问题描述:
普通台式机,采集数据,表中已经有>1000万数据量。
采集回来的数据插入表中的时候很慢,每条约100毫秒。
解决方法:
1、加大mysql配置中的bulk_insert_buffer_size,这个参数默认为8M
bulk_insert_buffer_size=100M
2、改写所有insert into语句为insert delayed into
这个insert delayed不同之处在于:立即返回结果,后台进行处理插入。
3、还有一个技巧是在一条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为1G
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