How to optimize MYSQL Data insertion

How can I optimize MYSQL Data insertion into the database? The following lists the optimizations.

1. if many rows are inserted from the same client at the same time, use the INSERT statement containing multiple values to INSERT several rows at the same time. This is faster than using a single-row INSERT Statement (several times faster in some cases ). If you add data to a non-empty table, you can adjust the bulk_insert_buffer_size variable to make data insertion faster. For more information, see section 5.3.3 of MYSQL document, "server system variables.

2. If you INSERT many rows from different clients, you can use the insert delayed statement to speed up. For more information, see section 13.2.4 of MYSQL, "INSERT Syntax.

3. If no row is deleted in the table, the MyISAM engine can be used to insert rows while the SELECT statement is running.

4. When loading a table from a text file, load data infile is used. This is usually 20 times faster than using many INSERT statements with the same amount of data. Because the DATA is inserted into the table using load data infile, no indexes are updated, so it is very fast. For more information, see section 13.2.5 of MYSQL, "load data infile Syntax.

5. Locking a table can accelerate the INSERT operation with multiple statements:

Lock tables a WRITE;
Insert into a VALUES );
Insert into a VALUES (8, 26), (6, 29 );
Unlock tables;

This improves the performance because the index cache is refreshed to the disk only once after all INSERT statements are completed. Generally, the number of INSERT statements is the index cache refresh. If you can use one statement to insert all rows, you do not need to lock them.

For transaction TABLES, BEGIN and COMMIT should be used instead of lock tables to speed up insertion.

Articles you may be interested in

• MySQL large data volume quick insert method and statement Performance Optimization
• Improves the query speed of millions of data records for MySQL
• Improve database performance secret SQL optimization skills
• Optimize paging SQL statements for large data volumes in MySQL
• Use the PHP function memory_get_usage to obtain the current PHP memory consumption for program performance optimization.
• MYSQL performance optimization sharing (database/table sharding)
• How to increase the number of MYSQL database connections
• How to locate, eliminate and avoid MySQL database performance problems