mysql batch delete large amounts of data

mysql batch delete large amounts of data

Assume that there is a table (syslogs) with 10 million records. You need to delete all the records with statusid=1 without stopping the business. There are about 6 million records. If you directly execute DELETE FROM syslogs WHERE statusid=1, you will find that the deletion fails because of the lock wait timeout exceed error.

Because this statement involves too many records, we delete them in batches using the LIMIT parameter, for example, deleting every 10,000 records.

MySQL can be completed with the following statement:

 DELETE FROM syslogs WHERE status=1 ORDER BY statusid LIMIT 10000;

Then you can successfully delete these records by executing it multiple times.

Note:

When performing large-scale deletions, be sure to use limit. Because if you don't use limit, deleting a large amount of data is likely to cause deadlock.

If the where clause of delete is not on the index, you can first find the primary key and then delete the database based on the primary key.

It is best to add limit 1 when updating and deleting to prevent accidental operations.

Thank you for reading, I hope it can help you, thank you for your support of this site!