MySQL splitting query Usage Analysis and MySQL splitting Usage Analysis

MySQL splitting query Usage Analysis and MySQL splitting Usage Analysis

This example describes the usage of MySQL splitting query. We will share this with you for your reference. The details are as follows:

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:
Copy codeThe Code is as follows: 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 = 0do {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.