Mysql insert Performance Optimization

The time it takes to INSERT a record in Mysql-optimized accelerated INSERT statements is determined by the following factors, and the number indicates the proportion of the impact:
 
Connection: (3)
 
Send query to server: (2)
 
Resolution query: (2)
 
Insert record: (1 * record size)
 
Insert index: (1 * Number of indexes)
 
Close: (1)
 
The overhead of opening the data table during initialization is not considered here, because each query is performed only once.
 
If it is a B-tree index, as the number of indexes increases, the speed of inserting records decreases in the proportion of logN.
 
You can use the following methods to increase the insert speed:
 
If you want to INSERT many records at the same time on the same client, you can use the INSERT statement with multiple values. This method is much faster (in some cases, it is faster) than an INSERT statement that uses a single value ). To add a record to a non-empty data table, you can adjust the value of the variable bulk_insert_buffer_size to make it faster.
 
If you want to INSERT a large number of records from unused clients, you can use the insert delayed statement to increase the speed.
 
Corresponding to MyISAM, you can insert records when the SELECT statement is running, as long as the records are not deleted at this time.
 
To LOAD a text file to a DATA table, you can use load data infile. This is usually 20 times the use of a large number of INSERT statements.
 
With some additional work, load data infile can run faster when a DATA table has a large number of indexes. The procedure is as follows:
 
Use create table to create a table with the table
 
Run the flush tables statement or the mysqladmin flush-tables command.
 
Run the myisamchk-keys-used = 0-rq/path/to/db/tbl_name command to delete all indexes of the data table.
 
Execute load data infile and insert the DATA into the table. Because you do not need to update the table index, this will be very fast.
 
If you only want to read the table in the future, run myisampack to make the data table smaller.
 
Run myisamchk-r-q/path/to/db/tbl_name to recreate the index. The created index tree is saved in memory before being written to the disk, which saves the disk search speed. After reconstruction, the index tree distribution is very balanced.
 
Run the flush tables statement or the mysqladmin flush-tables command.
 
Note: From Mysql 4.0 onwards, you can run alter table tbl_name disable keys to replace myisamchk-keys-used = 0-rq/path/to/db/tbl_name. run alter table tbl_name enable keys to replace myisamchk-r-q/path/to/db/tbl_name. to do this, you can skip the flush tables step.
 
 
 
You can execute several statements to accelerate the INSERT operation after locking the table:
 
Lock tables a WRITE;
 
Insert into a VALUES (1, 23), (2, 23 );
 
Insert into a VALUES (8, 7 );
 
Unlock tables;
 
This improves the performance by refresh the index cache to the disk at a time until all INSERT statements are completed. The overhead of refreshing the index cache to the disk is usually calculated based on the number of INSERT statements. If you can insert multiple values in one statement at a time, it is clear that the table Lock operation is unnecessary. For transaction TABLES, use BEGIN/COMMIT instead of lock tables to increase the speed. The lock table also reduces the total time for multiple connection tests, even though the maximum wait time for each independent connection to wait for the lock increases.
 
Connection 1 does 1000 inserts
 
Connection 2, 3 and 4 do 1 insert
 
Connection 5 does 1000 inserts
 
If no lock table exists, the join 2, 3, and 4 will be completed before 1 and 5. If the table is locked, the join 2, 3, 4 may be completed after 1, 5, but the total time may only need 40%. Mysql performs INSERT, UPDATE, and DELETE operations very quickly. However, if there are more than five inserts or updates in a statement, it is best to lock them for better performance. If you want to insert data multiple times at a time, you 'd better add lock tables and unlock tables before and after each loop so that other processes can access the data table. This performance is still good. INSERT is always slower than load data infile to insert data, because the implementation policies of the two are different.
 
To make MyISAM tables faster, you can increase the value of the system variable key_buffer_size when loading data infile and INSERT.