mysql 提高update性能,如何提高MySQL INSERT和UPDATE的性能？ (How to improve MySQL INSERT and UPDATE performance?)...

INSERT and UPDATE get progressively slower when the number of rows increases on a table with an index. Innodb tables are even slower than MyISAM tables for inserts and the delayed key write option is not available.

The most effective way to speed things up would be to save the data first into a flat file and then do LOAD DATA , this is about 20x faster.

The second option would be create a temporary in memory table, load the data into it and then do a INSERT INTO SELECT in batches. That is once you have about 100 rows in your temp table, load them into the permanent one.

Additionally you can get an small improvement in speed by moving the Index file into a separate physical hard drive from the one where the data file is stored. Also try to move any bin logs into a different device. Same applies for the temporary file location.