MySQL大批量更新或者删除数据

1. DELETE operation is an expensive operation due to the following reasons:

1. it causes the records to be locked as part of the transaction executing the DELETE statement,
2. it can generate a large amount of UNDO logs,
3. it can generate a large amount of binary logs,
4. the records to be deleted are brought into the InnoDB buffer pool.

2. reduces the rate of growth of tables reduces the overhead of large tables and can help performance as follows:

1. Improves response times of queries which perform index scans and/or table scans on such large tables.
2. Improves overall performance by reducing storage I/O by reducing the the size of working set of data.

3. UPDATE or DELETE sets exclusive next-key locks. Some tips to reduce the locking and impact:

1. When deleting based on a key, use FORCE INDEX to make sure the index is used (to avoid locking all rows in table).
2. If number of rows being deleted is most of the table, rather SELECT the rows you want to keep into a new table, then drop the old table and rename new one.
3. Consider using READ COMMITTED transaction isolation level.
4. Delete rows in smaller batches, such as a few thousand per transaction. Optionally sleep a few seconds between batches.
5. Design table so that it can be partitioned. An entire partition could be dropped, which is quicker than a bulk delete. (e.g. partition by date ranges).
6. Monitor and tune InnoDB purge so that it does not fall too far behind.
7. Delete rows in Primary Key order.