MySQL big data fast insert method and statement optimization sharing
- 2020-05-13 03:37:13
- OfStack
Locking will also reduce the overall time for multi-connection testing, although the maximum wait time will rise because they are waiting for locking. Such as:
If locking is not used, 2, 3, and 4 will be completed before 1 and 5. If you use locking, 2, 3, and 4 will probably not finish before 1 or 5, but the overall time should be about 40% faster.
The INSERT, UPDATE, and DELETE operations are fast in MySQL, and better overall performance can be achieved by locking more than about 5 consecutive inserts or updates in a row. If you insert multiple times in a row 1, you can perform LOCK TABLES, followed immediately by UNLOCK TABLES(approximately per 1000 rows) to allow other threads to access the table. This also results in good performance.
INSERT loads data much more slowly than LOAD DATA INFILE, even using the above strategy.
To get faster speeds for LOAD DATA INFILE and INSERT in MyISAM table, expand the key cache by adding key_buffer_size system variables.
INSERT grammar
Or:
Or:
1. Use of DELAYED
The delayed insert operation DELAYED regulator is applied to the INSERT and REPLACE statements. When the DELAYED insert arrives, the server places the row in a queue and immediately returns a status message to the client so that the client can continue before the table is actually inserted into the record. If the reader reads from the table, the data in the queue is held until there are no readers.
The server then begins to insert the rows in the delayed row (delayed-row) queue. At the same time as the insert operation, the server also checks if any new read requests arrive and wait. If so, the delayed data row queue is suspended, allowing the reader to continue. When there is no reader, the server again begins to insert delayed rows of data. This process continues until the queue is empty.
A few points to note:
INSERT DELAYED should only be used for INSERT statements that specify the list of values. Server ignored for INSERT DELAYED... DELAYED of SELECT statement. Server ignored for INSERT DELAYED... DELAYED of the ON DUPLICATE UPDATE statement.
Because the statement returns immediately before the row is inserted, you cannot use LAST_INSERT_ID() to get the AUTO_INCREMENT value. The AUTO_INCREMENT value may be generated by a statement.
For SELECT statements, the DELAYED lines are not visible until the lines are actually inserted.
DELAYED is ignored in slave replication servers because DELAYED does not generate data in a slave that is different from that of the primary server. Note that currently the rows in the queue are only kept in storage until they are inserted into the table. This means that if you forcibly abort mysqld(for example, using kill-9) or if mysqld stops unexpectedly, all lines that were not written to disk will be lost.
Connection 1 does 1000 inserts
Connections 2, 3, and 4 do 1 insert
Connection 5 does 1000 inserts
If locking is not used, 2, 3, and 4 will be completed before 1 and 5. If you use locking, 2, 3, and 4 will probably not finish before 1 or 5, but the overall time should be about 40% faster.
The INSERT, UPDATE, and DELETE operations are fast in MySQL, and better overall performance can be achieved by locking more than about 5 consecutive inserts or updates in a row. If you insert multiple times in a row 1, you can perform LOCK TABLES, followed immediately by UNLOCK TABLES(approximately per 1000 rows) to allow other threads to access the table. This also results in good performance.
INSERT loads data much more slowly than LOAD DATA INFILE, even using the above strategy.
To get faster speeds for LOAD DATA INFILE and INSERT in MyISAM table, expand the key cache by adding key_buffer_size system variables.
INSERT grammar
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
The delayed insert operation DELAYED regulator is applied to the INSERT and REPLACE statements. When the DELAYED insert arrives, the server places the row in a queue and immediately returns a status message to the client so that the client can continue before the table is actually inserted into the record. If the reader reads from the table, the data in the queue is held until there are no readers.
The server then begins to insert the rows in the delayed row (delayed-row) queue. At the same time as the insert operation, the server also checks if any new read requests arrive and wait. If so, the delayed data row queue is suspended, allowing the reader to continue. When there is no reader, the server again begins to insert delayed rows of data. This process continues until the queue is empty.
A few points to note:
INSERT DELAYED should only be used for INSERT statements that specify the list of values. Server ignored for INSERT DELAYED... DELAYED of SELECT statement. Server ignored for INSERT DELAYED... DELAYED of the ON DUPLICATE UPDATE statement.
Because the statement returns immediately before the row is inserted, you cannot use LAST_INSERT_ID() to get the AUTO_INCREMENT value. The AUTO_INCREMENT value may be generated by a statement.
For SELECT statements, the DELAYED lines are not visible until the lines are actually inserted.
DELAYED is ignored in slave replication servers because DELAYED does not generate data in a slave that is different from that of the primary server. Note that currently the rows in the queue are only kept in storage until they are inserted into the table. This means that if you forcibly abort mysqld(for example, using kill-9) or if mysqld stops unexpectedly, all lines that were not written to disk will be lost.