mysql提高insert into 插入速度的方法

mysql提高insert into 插入速度的方法


innodb_flush_log_at_trx_commit

就是说设为1时：会在每个事务提交后会执行往磁盘写日志的操作。设为0或2可减少日志对磁盘IO的负担。0表示每秒往磁盘写一次日志，2表示每次事务都往内存提交日志，但每秒往磁盘仅写入一次。现在想想为什么之前插入这么慢，就是因为每个inset完成后都往磁盘写日志，导致占满了磁盘IO（我观察了磁盘IO，参数设为1的时候磁盘占用时间保持100%）

插入速度只有20-30 条/秒，后来查资料后，将MySQL的1个参数：innodb_flush_log_at_trx_commit，1改为了0（修改方法，可以直接修改my.ini(windows)/my.cnf(linux)中的对应参数，似乎用命令直接设置没有效果），插入速度就提升到了3000+每S，

更新的速度会比插入的速度快很多。本地测试发现插入40-50/s,更新在200-250/s


mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log%';
临时改:set global innodb_flush_log_at_trx_commit=2;重启数据库后失效


加大mysql配置中的 bulk_insert_buffer_size，这个参数默认为8M


本地测试结果：
innodb_flush_log_at_trx_commit=0时，20个线程，20的连接池，平均5000/s. 2500~7500.
innodb_flush_log_at_trx_commit=1时，20个线程，20的连接池，平均420/s. 250~740.
innodb_flush_log_at_trx_commit=2时，20个线程，20的连接池，平均2700/s. 2000~3500.


本地测试结果（SSD）：
innodb_flush_log_at_trx_commit=0时，20个线程，20的连接池，平均7000/s. 5000~8000.
innodb_flush_log_at_trx_commit=1时，20个线程，20的连接池，平均5000/s. 4000-6000.
innodb_flush_log_at_trx_commit=2时，20个线程，20的连接池，平均4000/s. 2000-4500.


要提高写入速度可以使用批量写入的方式进行提高：
一个客户端进行测试：
INSERT test (id,name,value,sex,createDate)VALUES(UUID(),'xingxingxingxing','huangyongxinghuangyongxing',71,NOW());

1条插入：48条/s
10条插入：400条/s
20条插入：700条/s
40条插入：1350条/s
120条插入：3500条/s
200条插入：5500条/s
240条插入：7000条/s
480条插入：11000条/s
960条插入：15000条/s

在系统中可以使用队列的方式先保存下来，另一个线程进行获取并批量写入







https://www.cnblogs.com/snailmanlilin/p/7017137.html(MySQL插入10万数据时间（结论：最快14.967s，每秒插入6681条）)

[url]https://blog.csdn.net/hwhua1986/article/details/53501882 [/url]（insert插入数据过慢如何解决，设置innodb_flush_log_at_trx_commit为0就能解决）

https://blog.csdn.net/codepen/article/details/52160715（有关mysql的innodb_flush_log_at_trx_commit参数）

https://blog.csdn.net/thundermeng/article/details/50448614(innodb_flush_log_at_trx_commit和sync_binlog参数详解)