How to optimize MySQL insert performance

For some systems with large data volumes, the problem is that the query is inefficient, and there is a very important problem is that the insertion time is long. We have a business system that takes 4-5 clocks per day for data import. This time-consuming operation is actually very risky, assuming that the program is out of the question, want to re-run operation that is a painful thing. Therefore, it is necessary to improve the MySQL insert efficiency of the big data volume system.

After the test of MySQL, we find some ways to improve the efficiency of insert, for reference.

1. One SQL statement inserts multiple data.

Common INSERT statements such as:

2. insert into  ' insert_table ' ( ' datetime ' ,  ' UID ' ,  ' content ' ,  ' type ' )   Values  ( ' 0 ' ,  ' userid_0 ' ,  ' content_0 ' , 0
4. INSERT into ' insert_table ' (' datetime ', ' uid ', ' content ', ' Type ') VALUES (' 1 ', ' userid_1 ', ' content_1 ', 1 );

Modified to:

1. INSERT into' Insert_table ' (' DateTime ',' UID ',  ' content ' , ' type ' )  values  ( ' 0 ' ,  ' userid_0 ' ,  ' content_0 ' , 0< Span class= "pun"),   ( ' 1 '   ' userid_1 ' ,  ' content_1 ' , 1) ;

The modified insert operation can improve the efficiency of inserting the program. Here the second main reason for the high efficiency of SQL is two, one is to reduce the SQL statement parsing operations, only need to parse one time to be able to insert the data, the second is the short SQL statement, can reduce the network transmission of IO.

Here are some test comparison data, the import and conversion of a single piece of data into a SQL statement to import, Test 100, 1000, 10,000 data records respectively.

2. Insert processing in a transaction. To modify the INSERT into:

2. START TRANSACTION;
4. insert into  ' insert_table ' ( ' datetime ' ,  ' UID ' ,  ' content ' ,  ' type ' )   Values  ( ' 0 ' ,  ' userid_0 ' ,  ' content_0 ' , 0
6. INSERT into ' insert_table ' (' datetime ', ' uid ', ' content ', ' Type ') VALUES (' 1 ', ' userid_1 ', ' content_1 ', 1 );
8. ...
10. COMMIT;

Using transactions can improve the efficiency of data insertion, because when an insert operation occurs, a transaction is created inside MySQL to perform a true insert processing within the transaction. By using transactions, you can reduce the cost of creating transactions, and all inserts are executed before committing.

Test comparisons are also provided here, where transactions are not used with transactions in the case of 100, 1000, 10,000 records.

Performance testing: This provides a test that optimizes insert efficiency using both of the above methods. That is, multiple data is merged into the same SQL and inserted in the transaction.

From the test results can be seen, insert efficiency is about 50 times times the increase, this is a very objective figure.

Precautions:

1. SQL statements are limited in length, and in the same SQL the data merge must not exceed the SQL length limit, which can be modified by the Max_allowed_packet configuration, which is 1M by default.

2. Transactions need to be controlled in size, and transactions are too large to affect the efficiency of execution. MySQL has innodb_log_buffer_size configuration items, exceeding this value will log the use of disk data, then the efficiency will be reduced. It is therefore a good practice to commit a transaction before the transaction size reaches the configuration item data level.

How to optimize MySQL insert performance