Shulou(Shulou.com)05/31 Report--

This article shows you how to optimize insert performance in Mysql, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Acceleration of Mysql optimization the time it takes for INSERT to insert a record is determined by the following factors, and the numbers represent the percentage of impact:

Connection: (3)

Send the query to the server: (2)

Parsing 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 it is done only once each time the query is run.

In the case of B-tree indexes, the speed of inserting records decreases in the proportion of logN as the number of indexes increases.

You can increase insertion speed in the following ways:

If you want to insert many records on the same client at the same time, you can use the insert statement with multiple values. This is much faster (and in some cases faster) than using a single-valued INSERT statement. If you are adding records to a non-empty data table, you can adjust the value of the variable bulk_insert_buffer_size to make it faster.

Using INSERT DELAYED statements can also increase speed if you want to insert a large number of records from unused clients.

Corresponding to MyISAM, you can insert records while the SELECT statement is running, as long as no records are being deleted at this time.

To load a text file into a datasheet, you can use LOAD DATA INFILE. This is usually 20 times as much as using a large number of INSERT statements.

With some extra work, you can make LOAD DATA INFILE run faster when the data table has a large number of indexes. The steps are as follows:

Use create table to create a table with a table

Execute FLUSH TABLES statements or admin flush-tables commands

Execute the myisamchk-keys-used=0-rq / path/to/db/tbl_name command to delete all indexes of the data table.

LOAD DATA INFILE is performed and the data is inserted into the table, which is very fast because there is no need to update the table index.

If you just read the table in the future, run myisampack to make the data table smaller.

Run myisamchk-r-Q / path/to/db/tbl_name to rebuild the index. The index tree created is saved in memory before it is written to disk, which eliminates disk search, so it is much faster. The distribution of the reconstructed index tree is very balanced.

Execute FLUSH TABLES statements or mysqladmin flush-tables commands

Note that since Mysql 4. 0, you can run ALTER TABLE tbl_name DISABLE KEYS instead of myisamchk-keys-used=0-rq / path/to/db/tbl_name. Run ALTER TABLE tbl_name ENABLE KEYS instead of myisamchk-r-Q / path/to/db/tbl_name. By doing so, you can save the FLUSH TABLES step.

After locking the table, you can execute several statements together to speed up the INSERT operation:

LOCK TABLES a WRITE

INSERT INTO a VALUES (1mem23), (2mem23)

INSERT INTO a VALUES (8. 7)

UNLOCK TABLES

The benefit of this for performance improvement is that the index cache is flushed to disk at one time until all the INSERT statements are complete. Typically, the cost of flushing the index cache to disk is as many times as there are INSERT statements. If you can insert multiple values in a statement at a time, it is obvious that table locking is not necessary. For transaction tables, use BEGIN/COMMIT instead of LOCK TABLES to speed up. Locking tables also reduces the total time for multiple connection tests, although the maximum wait time for each individual connection to wait for a lock also increases.

Connection 1 does 1000 inserts

Connection 2,3 and 4 do 1 insert

Connection 5 does 1000 inserts

If there is no lock on the table, the connection between 2Magne3 and 4 will be completed before 1p5. If the table is locked, it may take only 40% of the total time to complete the connection between 2Magne3 and 4 after 1p5. Mysql's INSERT, UPDATE, and DELETE operations are all very fast, but if there are more than 5 inserts or updates in a statement, it's best to lock them for better performance. If you want to insert many times at once, it's best to add LOCK TABLES and UNLOCK TABLES before and after each loop so that other processes can access the data table; this still performs well. INSERT is always slower than LOAD DATA INFILE to insert data, because the two implementation strategies are clearly different.

If you want MyISAM tables to be faster, you can increase the value of the system variable key_buffer_size in both LOAD DATA INFILE and INSERT.

The above is how to optimize insert performance in Mysql. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.