Mysql Segmentation Query Usage Analysis _mysql

This article illustrates the usage of MySQL segmentation query. Share to everyone for your reference, specific as follows:

For large queries sometimes need to ' divide and conquer ', the large query is divided into small queries: Each query function exactly the same, but only to complete a small part of the original, each query returns only a small number of result sets.

Deleting old data is a good example. When you regularly clean up old data, a SQL that involves a large amount of data may lock multiple tables or rows at once, consuming a lot of system resources, and blocking many other small but important queries. When you cut a large DELETE statement into smaller queries, you minimize the performance that affects mSQL, while reducing the latency caused by MySQL replication.

For example, a statement that runs once a month to clean up data three months ago:

Copy Code code as follows:

Mysql> DELETE from Messages WHERE dt<date_sub (now (), INTERVAL 3 MONTH);

You can accomplish such a task in the following ways:

rows_affected = 0 do
{
   rows_affected = do_query (' DELETE from Messages WHERE dt<date_sub (now (), INTERVAL 3 MONTH )}while rows_affected>0 LIMIT 10000 ")

Deleting 10,000 rows at a time is a more efficient and less server-impacting practice. At the same time, if you pause for a moment each time you delete data, you can spread the original one-time pressure of the server into a longer period of time, reducing the time it will take to lock the table lock line when you delete it.

For more information about MySQL interested readers can view the site topics: "MySQL Transaction operation skills Summary", "MySQL stored process skills encyclopedia", "MySQL database lock related skills summary" and "MySQL common function large summary"

I hope this article will help you with the MySQL database meter.