How to Efficiently Delete Millions of MySQL Rows Without Disrupting Site Operations?

Efficiently Deleting Millions of MySQL Rows

Deleting substantial amounts of data from a MySQL table can pose challenges due to potential table locking issues. This article examines a common scenario where a large number of duplicate data rows must be removed without disrupting site operations.

Challenges

The primary obstacle in this situation is the lengthy table lock imposed when executing a single query to delete millions of rows. This can halt all other operations involving the table, leading to site instability.

Potential Solutions

To mitigate the locking issue, two main approaches are available:

1. Iterative Deletion: This involves creating a script that executes numerous smaller delete queries in a loop, limiting the number of rows affected per query. While this avoids the single-query lock, it can still spike database load and extend the execution time.
2. Table Recreation: An alternative method is to rename the problematic table, create an empty replacement table, perform cleanup on the renamed table, and finally rename the tables back and merge the data. This approach minimises interruptions but introduces additional steps, particularly when dealing with reporting tables or complex data types.

Optimized Solution

For optimal performance, a more efficient solution is to utilise an incremental deletion approach with a loop and a limit clause. This involves the following steps:

DELETE FROM `table`
WHERE (whatever criteria)
ORDER BY `id`
LIMIT 1000

The query is iterated until no more rows are affected, with a brief pause between iterations to prevent overloading the database. This approach effectively deletes data in manageable batches while allowing other queries to execute concurrently, reducing both the table lock time and overall execution time.

The above is the detailed content of How to Efficiently Delete Millions of MySQL Rows Without Disrupting Site Operations?. For more information, please follow other related articles on the PHP Chinese website!