加快mysql insert速度_加速 mysql insert

因为最近在做一个爬虫，发现性能瓶颈卡在了insert 的速度上面。

Write IOPS 120/min 。因为后面数据原来越多，插入的速度越来越慢。所以考虑如何加快insert的速度。

文档查询

To optimize insert speed, combine many small operations into a single large operation. Ideally, you make a single connection, send the data for many new rows at once, and delay all index updates and consistency checking until the very end.

The time required for inserting a row is determined by the following factors, where the numbers indicate approximate proportions:

* Connecting: (3)

* Sending query to server: (2)

* Parsing query: (2)

* Inserting row: (1 × size of row)

* Inserting indexes: (1 × number of indexes)

* Closing: (1)

This does not take into consideration the initial overhead to open tables, which is done once for each concurrently running query.

The size of the table slows down the insertion of indexes by log *`N`*, assuming B-tree indexes.

You can use the following methods to speed up inserts:

* If you are inserting many rows from the same client at the same time, use [`INSERT`](https://dev.mysql.com/doc/refman/5.7/en/insert.html "13.2.5 INSERT Syntax") statements with multiple `VALUES` lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row [`INSERT`](https://dev.mysql.com/doc/refman/5.7/en/insert.html "13.2.5 INSERT Syntax") statements. If you are adding data to a nonempty table, you can tune the [`bulk_insert_buffer_size`](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_bulk_insert_buffer_size) variable to make data insertion even faster. See [Section 5.1.5, “Server System Variables”](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html "5.1.5 Server System Variables").

* When loading a table from a text file, use [`LOAD DATA INFILE`](https://dev.mysql.com/doc/refman/5.7/en/load-data.html "13.2.6 LOAD DATA INFILE Syntax"). This is usually 20 times faster than using [`INSERT`](https://dev.mysql.com/doc/refman/5.7/en/insert.html "13.2.5 INSERT Syntax") statements. See [Section 13.2.6, “LOAD DATA INFILE Syntax”](https://dev.mysql.com/doc/refman/5.7/en/load-data.html "13.2.6 LOAD DATA INFILE Syntax").

* Take advantage of the fact that columns have default values. Insert values explicitly only when the value to be inserted differs from the default. This reduces the parsing that MySQL must do and improves the insert speed.

* See [Section 8.5.5, “Bulk Data Loading for InnoDB Tables”](https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-bulk-data-loading.html "8.5.5 Bulk Data Loading for InnoDB Tables") for tips specific to `InnoDB` tables.

* See [Section 8.6.2, “Bulk Data Loading for MyISAM Tables”](https://dev.mysql.com/doc/refman/5.7/en/optimizing-myisam-bulk-data-loading.html "8.6.2 Bulk Data Loading for MyISAM Tables") for tips specific to `MyISAM` tables.

翻译：

把很多的小的操作合并成一个大的操作，理想情况下，你可以一口气完成 建立连接，发送多条数据，并且在最后才进行索引的更新，推迟频繁的checking

insert的时间因素主要是一下几个：数字代表权重

connect ：3

sending query: 2

parse query: 2

insert row: 1 * size of row

insert index: 1 * num of index

close: 1

不要去考虑打开表的初始的开销，这个只有一次，剩下每次查询就不需要了

table的size会以logN 的速度 减慢insert 性能，假设是使用的b-tree-index

采用下面两种方法去加速insert

- 如果每次插入很多row,一次插入多个values，加速效果很明显，也可以调整`bulk_insert_buffer_size`去加速

- 如果是从text file 导入数据， 使用 [`LOAD DATA INFILE`](https://dev.mysql.com/doc/refman/5.7/en/load-data.html "13.2.6 LOAD DATA INFILE Syntax").

去加速，比使用insert 会快20倍左右。

- 如果使用了默认值，使用好默认值。

实践

使用multi values

使用index delay ，即先把index 卸掉，最后再生成。

效果总结

完美解决了问题。