MySQL Manual Version 5.0.20-MySQL Optimization (4) (1) (4)_PHP Tutorial

You can execute several statements together to speed up the INSERT operation after locking the table:

LOCK TABLES a WRITE;

INSERT INTO a VALUES (1,23),(2,34),(4,33);

INSERT INTO a VALUES (8,26),(6,29);

UNLOCK TABLES;

The benefit of this performance improvement is that until all INSERT statements are completed, the index cache is flushed to disk at once. Typically, as many INSERT statements as there are, there will be a cost of flushing the index cache to disk. If multiple values ​​could be inserted at once in one statement, the explicit table lock operation would be unnecessary. For transactional tables, use BEGIN/COMMIT instead of LOCK TABLES to improve speed. Lock tables also reduce the total time for multiple connection tests, although the maximum wait time for each independent connection to wait for the lock will also increase. For example:

Connection 1 does 1000 inserts

Connections 2, 3, and 4 do 1 insert

Connection 5 does 1000 inserts

If there is no lock table, connections 2, 3, and 4 will be completed before 1 and 5. If the table is locked, connections 2, 3, and 4 may not be completed until 1, 5, but the total time may only take 40%. MySQL's INSERT, UPDATE, and DELETE operations are very fast, but if there are more than 5 inserts or updates in one statement, it is best to lock them to get better performance. If you want to do many inserts at once, 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; the performance is still good. INSERT is always slower than LOAD DATA INFILE to insert data, because the implementation strategies of the two are clearly different.

To make the MyISAM table faster, you can increase the value of the system variable key_buffer_size during LOAD DATA

INFILE and INSERT. For details, please see "7.5.2 Tuning Server Parameters".

7.2.13 Accelerating UPDATE

The optimization of UPDATE statement is the same as SELECT, except that it has additional writing overhead. The cost of writing depends on the number of records to be updated and the number of indexes. If the index has not changed, there is no need to update.

http://www.bkjia.com/PHPjc/630975.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/630975.htmlTechArticleYou can execute several statements together to speed up the INSERT operation after locking the table: LOCK TABLES a WRITE; INSERT INTO a VALUES (1,23),(2,34),(4,33); INSERT INTO a VALUES (8,26),(6,29); UNLOCK T...