MySQL large data volume quick insert method and statement Performance Optimization

For transaction TABLES, BEGIN and COMMIT should be used instead of lock tables to speed up insertion. Locking also reduces the overall time for multi-connection testing, although the maximum wait time for them to wait for LOCK will increase. For example:

Connection 1 does 1000 inserts Connections 2, 3, and 4 do 1 insert Connection 5 does 1000 inserts

If no lock is used, 2, 3, and 4 are completed before 1 and 5. If locking is used, 2, 3, and 4 may not be completed before 1 or 5, but the overall time should be about 40% faster.

INSERT, UPDATE, and DELETE operations are fast in MySQL. By locking more than five consecutive INSERT or UPDATE operations in a row, you can achieve better overall performance. If you insert a table multiple times in a row, you can execute lock tables and then immediately execute unlock tables (about every 1000 rows) to allow other threads to access the table. This will also achieve good performance.

INSERT loading DATA is much slower than load data infile, even if the above policy is used.

To speed up load data infile and INSERT in the MyISAM table, increase the key-speed buffer by adding the key_buffer_size system variable.

INSERT syntax

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ] 

Or

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name SET col_name={expr | DEFAULT}, ... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ] 

Or

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT ... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ] 

1. Use of DELAYED

Use the DELAYED modifier of the delayed insert operation to apply to INSERT and REPLACE statements. When the DELAYED insert operation arrives, the server puts the data row into a queue and immediately returns a status message to the client, in this way, the client can continue the operation before the data table is actually inserted into the record. If the reader reads data from the data table, the data in the queue will be kept until there is no reader.

Then, the server inserts data rows in the delayed-row queue. During the insert operation, the server also checks whether new read requests arrive and wait. If yes, the delayed data row queue is suspended and the reader is allowed to continue the operation. When no reader is available, the server inserts delayed data rows again. This process continues until the queue is empty.

Notes:

Insert delayed should be used only for the INSERT statement that specifies the Value List. The server ignores the delayed used for the insert delayed... SELECT statement. The server ignores the delayed used for the insert delayed... on duplicate update statement.

Because the statement returns immediately before the row is inserted, you cannot use LAST_INSERT_ID () to obtain the AUTO_INCREMENT value. The AUTO_INCREMENT value may be generated by the statement.

For SELECT statements, DELAYED rows are invisible until these rows are indeed inserted.

DELAYED is ignored in the slave replication server, because DELAYED does not generate data different from the master server in the slave server. Note: Currently, each row in the queue is only stored in the memory until they are inserted into the table. This means that if you forcibly stop mysqld (for example, use kill-9) or if mysqld stops unexpectedly, all rows that are not written to the disk will be lost.

Articles you may be interested in

• Optimize paging SQL statements for large data volumes in MySQL
• Mysql database cache function analysis, debugging, and performance summary
• Use the PHP function memory_get_usage to obtain the current PHP memory consumption for program performance optimization.
• Improves the query speed of millions of data records for MySQL
• MySQL replace function replacement string statement usage
• Array_walk and foreach, for efficiency comparison, php Performance Optimization
• Summarize the causes and solutions for the slow MySQL Database Server
• Improve database performance secret SQL optimization skills