MySQL Large Table Data Deletion Optimization Strategy

This article discusses the optimization strategy for deleting data from large MySQL tables. The scenario involves a table storing user operation logs that grows over time and requires periodic cleanup. The business requirements specify keeping only the last 3 months of data while excluding certain log types (type = 'c') from deletion.

The article presents three approaches: an initial basic method using direct SQL queries with both date and type conditions, a failed optimization attempt that removed the type condition from SQL but introduced pagination issues, and a successful final solution. The successful approach uses primary key-based pagination with startId tracking, filters data in memory, and implements intelligent startId calculation to minimize scanning overhead for subsequent deletions.

Key technical insights include avoiding MyBatis PageHelper for large-scale deletions, using primary key-based pagination instead of offset-based pagination, and optimizing the startId calculation to reduce scanning scope after the first deletion run. The solution also recommends adding sleep intervals during initial large-scale deletions to prevent database overload.