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
Daily Recommendation