MySQL manual version 5.0.20-MySQL optimization (IV) (1) (4) _ PHP Tutorial

MySQL manual version 5.0.20-MySQL optimization (4) (1) (4 ). After the table is locked, several statements can be executed together to accelerate the INSERT operation: LOCKTABLESaWRITE; INSERTINTOaVALUES (), (), (); INSERTINTOaVALUES ); the UNLOCKT can execute several statements together after the table is locked to accelerate the INSERT operation:

Lock tables a WRITE;

Insert into a VALUES );

Insert into a VALUES (8, 26), (6, 29 );

Unlock tables;

This improves the performance by refresh the index cache to the disk at a time until all INSERT statements are completed. In general, there will be an overhead for refreshing the index cache to the disk for many INSERT statements. If multiple values can be inserted in one statement at a time, the displayed lock table 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 waiting time for each independent connection to wait for the lock increases. For example:

Connection 1 does 1000 inserts

Connections 2, 3, and 4 do 1 insert

Connection 5 does 1000 inserts

If no lock table exists, connection 2, 3, 4 will be completed before 1, 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's INSERT, UPDATE, and DELETE operations are very fast. 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 perform many inserts at a time, it is best to add lock tables and unlock tables before and after each loop (about 1000 times) 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

The system variable key_buffer_size value can be added during INFILE and INSERT operations. for details, see "7.5.2 Tuning Server Parameters ".

7.2.13 accelerate UPDATE

The optimization of the UPDATE statement is the same as that of the SELECT statement, except that it has additional write overhead. The write overhead depends on the number of records to be updated and the number of indexes. If the index does not change, no update is required.

Insert into: lock tables a WRITE; insert into a VALUES (), (), (); insert into a VALUES ); unlock t...