接下來想介紹的是在一般資料瀏覽會使用到的排序(order by)跟分頁(pagination)的介紹
首先,因為index他本身就是一個有排序的內容,所以在排序的時候如果有用到這個索引,排序就可以直接省一步
但在這邊想多介紹一下order by不是只有asc/desc,他在排序的時候,如果遇到欄位是nullable的,還可以指定null first/null last,如果有需要類似的功能的時候就可以使用
最後最後,想特別說一下pagination的狀況
當要做pagination的時候,最直覺的做法就是,先order by,再用limit加offset來取得指定的頁碼
這個在前幾頁都是沒問題的,但當頁數變多了,就會出現狀況
在資料庫中的offset,他的做法是我先把我要的資料排序,然後依序往下找,接著把需要offset的剃除,最後再回傳
舉個例子
select * from user order by created_at desc offset 100 limit 10;
created_at有index
上面這個的目標是透過創建時間排序user,然後查看第11頁的內容
那這時候,資料庫做的事情是
所以原本會覺得我們limit 10應該會很快,因為只找10筆資料
但最後卻是需要找110筆資料,然後無視前100筆
而在文中,對於這個案例,提出了解法
這個解法有個前提
實際上使用者不是真的需要頁碼,而是只希望可以依序查看資料,然後控制一個要求10筆資料,而且沒有跳頁的需求
當今天我們取得10筆資料
select * from user order by created_at desc limit 10;
這時user的最後一筆的created_at如果是2024-09-18T23:20:20
那下一次的10筆資料就可以用
select * from user where created_at < '2024-09-18T23:20:20' order by created_at desc limit 10;
如此一來,資料庫就可以直接找到指定的時間點,從那個時間點往後抓10筆,大大減少了offset的無效浪費
Ref
https://use-the-index-luke.com/sql/partial-results/fetch-next-page