MySQL High watermark problem

Everyone should know the concept of the database water level. In the past, I personally think that this is basically pure theory, and it does not seem to have anything to do with the actual development and writing of SQL. But in the process of solving a slow sql encountered a watermark problem.

Problem phenomenon:
The function appears slow. Slow check sql is DELETE FROM tb_cust_search_task_detail WHERE task_id = 2024;

 problem analysis:
 Everyone looked at this sql and estimated that the first reaction was that the amount of data was too large, which led to a slow check, and there was indeed a problem with a large amount of data in member retrieval. So the first solution that came up was to delete in batches. Then I looked at the approximate amount of data that can cause such a slow check. Only more than 30,000 details in a task can cause deletion for more than 5 minutes? ? I was a bit skeptical at the time, but I still didn't go into details, and still deleted the code in batches. After knocking the code, of course, it is necessary to test the speed. In sqladmin, I found that SELECT * FROM tb_cust_search_task_detail WHERE task_id = 2024 LIMIT 10000 I could not find it out, more than 30 seconds. . . . . Under EXPLAIN, the number of scanned rows is nearly 100 million, and the total number is not only 30,000. The index is also used. Why is the rows value so large? ? ? Then I looked at the execution plan of other task_ids with more than this number. . Then I went over all kinds of Baidu, and the more I looked at it, the more I thought I might have a high water mark. After reading the following background, everyone should know why (here is the background of this task_id, this task_id is a periodic task, it will be retrieved again every half hour, and then delete all the previous ones, and then insert. This is the Do frequent deletes and inserts. Then the next step is to solve the problem of how to lower the water level. If it can't be solved, it will be changed to batch deletion or slow check. I found a DBA to discuss this issue. The suggestion given by dba is to let me add a separate task_id index. The reason is that a separate index is better to query and faster (unfortunately mysql did not choose this index later). By the way, he did the following table reorganization to reduce the high Water level. The execution plan after the reorganization is normal

  Solution to the problem:
   1. Submit the script to dba and let dba reorganize the table to solve the current high-water mark problem. 2. For periodic retrieval tasks, no longer delete all and then insert. There is still a problem of high water mark. The new way is to compare the re-retrieved data with the old one, the insert of the insert, the delete of the delete 3. For the page function re-retrieval, edit the place, you need to delete the retrieval details (the number of such deletions is not much) . Change to batch deletion If you do delete and insert frequently, you may want to consider whether this hidden problem also exists.
mysql high water mark problem