Five tips for optimizing the MySQL Insertion Method

The following are five ways to improve the query of inserted tables:

1) using load data infile to download DATA from text is 20 times faster than using the insert statement.

2) INSERT several rows at a time using an INSERT statement with multiple VALUES lists, which is several times faster than using a single row INSERT statement. Adjusting the bulk_insert_buffer_size variable can also increase the insert speed (to a table containing rows.

3) The system variable Concurrent_insert can be inserted to the myisam table in parallel and can be set to modify the concurrent-insert Process. This variable is set to 1 by default. If concurrent_insert is set to 0, parallel insert is disabled. If this variable is set to 2, it can be inserted at the end of the table in parallel, even if some rows of the table have been deleted.

4) Insert latency

This is useful if your customers cannot or do not have to wait until the insertion is complete. When you use MySQL to store and regularly run SELECT and UPDATE statements that take a long time to complete, you will find this situation very common. When the customer uses the insert delay, the server immediately returns the result. If the table is not called by other threads, the row will wait for the table to be inserted. Another advantage of using insert latency is that insertion from multiple customers is bound and recorded in the same block. This is much faster than processing multiple independent inserts.

5) Lock the table before insertion (only for non-transactional tables)

This will improve the database performance, because the index buffer only refresh the disk after all the insert statements are completed. Under normal circumstances, the index buffer refresh will occur as many insert statements as possible. If you can use an insert statement to insert all rows, you do not need to use an explicit lock statement.

To insert a TRANSACTION table more quickly, use the start transaction and COMMIT statements instead of the lock tables statement.