Usually, the application needs to page the data in the table , If the amount of data is large , Performance issues often arise :
root@sns 07:16:25>select count(*) from  reply_0004 where thread_id = 5616385
and deleted = 0; +———-+ | count(*) | +———-+ |  1236795 | +———-+ 1 row in set
(0.44 sec) root@sns 07:16:30>select id from reply_0004 where thread_id =
5616385 and deleted = 0 order by id asc limit 1236785, 10 ; +———–+ | id        |
+———–+ | 162436798 | | 162438180 | | 162440102 | | 162442044 | | 162479222 | |
162479598 | | 162514705 | | 162832588 | | 162863394 | | 162899685 | +———–+ 10
rows in set (1.32 sec) Indexes :threa_id+deleted+id(gmt_Create) 10 rows in set (1.32
sec)
These two sql Is to query the page turning of the last page sql For inquiry . Because only a small amount of data needs to be queried at a time , as 10 strip , But it needs to scan back a lot of data , That is to say, the later you turn the page to query , More data to scan , The speed of query is getting slower and slower .
Because the data size of the query is fixed , If the query speed is not affected by the number of pages turned , Or the lowest impact , So that's the best result ( The speed of the last few pages of the query is the same as that of the first few pages ).
When turning the page , You often need to sort one of the fields ( This field is in the index ), Ascending sort . Can we use the order of index to solve the above problems , The answer is yes . For example 10000 Data need to be paged , So before 5000 Strip work asc sort , after 5000 strip desc sort , stay limit
startnum,pagesize Adjust the parameters accordingly .
But it certainly complicates the application , This one sql It's for forum reply sql, When users are reading posts , It's usually looking at the first and last pages , Then, when turning the page, the last few pages of turning query adopt desc How to turn pages , In this way, the performance can be improved :
root@snsgroup 07:16:49>select * from (select id

->            from group_thread_reply_0004 where thread_id = 5616385 and
deleted = 0

->        order by id desc limit 0, 10)t order by t.id asc;

+———–+

| id        |

+———–+

| 162436798 |

| 162438180 |

| 162440102 |

| 162442044 |

| 162479222 |

| 162479598 |

| 162514705 |

| 162832588 |

| 162863394 |

| 162899685 |

+———–+

10 rows in set (0.87 sec)

You can see the performance improvement 50% above .

Technology
©2019-2020 Toolsou All rights reserved,
use PyMC3 Bayesian statistical analysis was performed ( code + example )Vue Get the text and option value of the drop-down box about Bellman-Ford Personal understanding of algorithms element-ui Of el-date-picker Component get value use VS2019 “Windows Desktop applications ” Module creation Win32 window vue use THREE.js Create a cube that you can control shiro-oauth Enable third party authentication login SpringMVC Frame in controller Layer gets the property value of the custom configuration file latex Custom commands in ———\newcommandKeras Summary of training data loading