Locking also reduces the overall time for multiple-connection tests, although the maximum wait time will rise as they wait for locking. For example:
Copy Code code as follows:
Connection 1 Does 1000 inserts
Connections 2, 3, and 4 do 1 insert
Connection 5 does 1000 inserts
If you do not use locks, 2, 3, and 4 will be completed before 1 and 5. If you use locks, 2, 3, and 4 will probably not be completed before 1 or 5, but the overall time should be approximately 40%.
Insert, update, and delete operations are quick in MySQL, and better overall performance can be achieved by locking an operation that is continuously inserted or updated more than approximately 5 times in a row. If you insert multiple times on one line, you can execute lock tables, and then immediately execute unlock tables (approximately every 1000 lines) to allow other threads to access the table. This will also achieve good performance.
Insert load data is much slower than the load infile, even if the above strategy is used.
To increase the speed of the load DATA infile and insert in the MyISAM table, the key high-speed buffer is enlarged by adding key_buffer_size system variables.
Insert Syntax
Copy Code code as follows:
INSERT [Low_priority | Delayed | High_priority] [IGNORE]
[Into] tbl_name [(Col_name,...)]
VALUES ({expr | DEFAULT},...), (...),...
[on DUPLICATE KEY UPDATE col_name=expr, ...]
Or:
Copy Code code as follows:
INSERT [Low_priority | Delayed | High_priority] [IGNORE]
[Into] Tbl_name
SET col_name={expr | DEFAULT}, ...
[on DUPLICATE KEY UPDATE col_name=expr, ...]
Or:
Copy Code code as follows:
INSERT [Low_priority | High_priority] [IGNORE]
[Into] tbl_name [(Col_name,...)]
SELECT ...
[on DUPLICATE KEY UPDATE col_name=expr, ...]
first, the use of delayed
Use the deferred insert operation to apply the delayed modifier to the INSERT and replace statements. When the delayed insert operation arrives, the server puts the data row into a queue and returns a status message to the client immediately, so that the client can continue to operate before the data table is actually inserted into the record. If the reader reads data from the datasheet, the data in the queue is persisted until no reader is present.
The server then starts inserting data rows from the deferred data row (Delayed-row) queue. At the same time as the insert operation, the server also checks to see if a new read request arrives and waits. If so, the deferred data row queue is suspended, allowing the reader to continue the operation. When there is no reader, the server starts inserting the deferred data rows again. This process continues until the queue is empty.
A few things to note:
Insert delayed should be used only for INSERT statements that specify a list of values. Server ignored for insert delayed ... The delayed of the SELECT statement. Server ignored for insert delayed ... On the delayed of the 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 the statement.
For a SELECT statement, the delayed row is not visible until the rows are actually inserted.
Delayed is ignored in the slave replication server because delayed does not produce data that is not the same as the primary server on the secondary server. Note that the rows currently in the queue are only in storage until they are inserted into the table. This means that if you forcibly abort mysqld (for example, by using kill-9) or if mysqld stops unexpectedly, all rows that are not written to the disk will be lost.
