For large queries, you sometimes need to divide them into small queries: each query has the same function, but only a small part of the original is completed, only a small number of result sets are returned for each query.
Deleting old data is a good example. When regular cleaning of old data, if an SQL statement involves a large amount of data, it may lock Multiple tables or rows, consuming a lot of system resources, but it blocks many other small but important queries. When dividing a large Delete statement into smaller queries, You can minimize the impact on msql performance and reduce the latency caused by MySQL replication.
For example, a statement is run once a month to clear data three months ago:
Mysql> Delete from messages where dt <date_sub (now (), interval 3 month );
You can use the following methods to complete such a task:
Rows_affected = 0
Do {
Rows_affected = do_query ("delete from messages where dt <date_sub (now (), interval 3 month) limit 10000 ")
} While rows_affected> 0
Deleting 10 thousand rows of data at a time is more efficient and has little impact on the server. At the same time, if the data is paused for a while each time the server is deleted, the original one-time pressure on the server can be distributed to a longer period of time, thus reducing the time for locking the table lock row during deletion.
(Refer to "High Performance MySQL")
