MySQL带INTERVAL的DELETE查询执行缓慢原因及优化方案咨询

Alright, let's dig into why your DELETE query is running slower than expected and how to fix it. I’ve dealt with this exact scenario countless times in production environments, so here’s a practical breakdown:

The most common culprits here are:

• Index cannot be used due to function wrapping: Your condition uses date_add(created_at, INTERVAL 2 DAY) which wraps the created_at field in a function. Databases can’t leverage indexes on columns that are wrapped in functions—this forces a full table scan, which gets agonizingly slow as your table grows.
• Large table size: If your table has millions (or more) of rows, a full table scan will naturally take a long time, even without other bottlenecks.
• Missing or irrelevant indexes: Even if you have an index on created_at, the function in your WHERE clause renders it useless. If you don’t have an index on created_at at all, that’s an extra strike against performance.
• Lock contention: If other transactions are reading/writing to the same table (especially the rows you’re trying to delete), your DELETE query will have to wait for locks to be released, adding to the total runtime.
• Triggers or foreign key constraints: If your table has triggers that fire on DELETE, or foreign keys referencing other tables, the database has to perform extra checks/operations, which slows things down.

Here are actionable steps to fix this, ordered by impact:

1. Rewrite the WHERE clause to avoid function-wrapped columns
   This is the single most impactful fix. Instead of applying a function to created_at, rearrange the condition to compare the raw created_at value against a computed date:

   DELETE FROM table 
   WHERE created_at < DATE_SUB(NOW(), INTERVAL 2 DAY);
   

   Now created_at is a bare column, so any index on it can be used directly—no more full table scan.

2. Add an index on created_at (if missing)
   If you haven’t already, create an index for the created_at column to let the database quickly locate rows to delete:

   CREATE INDEX idx_table_created_at ON table(created_at);
   

   Combine this with the rewritten WHERE clause, and you’ll see massive speed improvements.

3. Delete in batches instead of all at once
   If you’re deleting tens of thousands (or more) of rows, a single DELETE will lock the table for a long time, blocking other operations. Instead, delete in small chunks with a LIMIT clause:

   DELETE FROM table 
   WHERE created_at < DATE_SUB(NOW(), INTERVAL 2 DAY)
   LIMIT 1000;
   

   Run this query repeatedly until no rows are deleted. Adjust the LIMIT value based on your database’s capacity—start with 1000 and tweak if needed.

4. Resolve lock contention
   Check for long-running transactions or frequent write operations on the table. Try running the DELETE during off-peak hours when there’s less traffic. If necessary, you can temporarily lower the transaction isolation level (e.g., to READ COMMITTED) to reduce lock wait times, but do this cautiously in production.

5. Optimize triggers and foreign keys

   • Disable or remove any unnecessary triggers that fire on DELETE—they add overhead for no reason.
   • If foreign keys are required, ensure the referenced columns in other tables have indexes. This speeds up the referential integrity checks the database has to perform.

6. Use partitioning for extremely large tables
   If your table has hundreds of millions of rows, consider partitioning it by created_at (e.g., daily or monthly partitions). Instead of deleting rows, you can simply drop the entire partition for the old data—this is almost instantaneous compared to a DELETE query.

内容的提问来源于stack exchange，提问作者Yrtymd