How to solve the performance problem of insert ignore in mysql8.x

Insert ignore performance issues in mysql 8.x

The efficiency of replace into in mysql is very poor. If multiple processes are concurrently used, the table will be locked, so on duplicate should be used for updates. This is well known. I didn't expect that even if I use the insert ignore operation, I may encounter performance problems. I want to record this problem I encountered.

In order to insert initialization data into a table, I opened 10 processes to concurrently insert data into the database using insert ignore. Each insert ignore statement contains 7 rows of data. Unexpectedly, after going online and starting to run the script, many lock tables appeared in the database. Judging from the innodb status log, what is locked is the primary key of the table's auto-incrementing ID.

After debugging for a long time, I found that it turns out that when insert ignore, the S lock will be taken for each row of inserted data to detect the unique id, and at the same time, an intention lock (insert intension) will be added to the auto-incremented id field of the primary key. ), when the unique key is more complex, the insertion intention lock of the primary key will always be occupied when detecting the unique key, and other insert ignores also want to add an insertion intention lock to the primary key id, resulting in a deadlock.

The above situation was discovered in mysql 8.x. It seems that I have not encountered related problems with lower versions of mysql before, so I don’t know if there are pitfalls in the insert ignore of lower versions of mysql, but 8.x The best version is insert ignore and do not insert multiple rows of data, especially when the unique key is more complex (composed of three or four fields)

mysql insert ignore() function

INSERT IGNORE statement

When you use the INSERT statement to add multiple rows to a table, if an error occurs during processing, MySQL terminates the statement and returns an error. As a result, no rows are inserted into the table.

However, if you use the insert ignore statement, the rows containing invalid data that caused the error will be ignored and the rows with valid data will be inserted into the table.

insert ignore into actor values (3,'ED','CHASE','2006-02-15 12:34:33')

The above is the detailed content of How to solve the performance problem of insert ignore in mysql8.x. For more information, please follow other related articles on the PHP Chinese website!