上次在MySQL8上導入一個1000萬數據之后，今天就想驗證看看mysql對于單表大數據的分頁的表現情況，并探討一下單表大數據分頁的優化思路。
測試環境
簡單說明下測試環境。
- mysql版本：8.0.17
- 在test測試數據庫中新增一個表t_user,字段為ID和NAME。其中在ID字段設置索引，且name值不同。
- 導入1千萬條數據，之前有部分測試數據，最后數據量為10000000，見如下圖：
傳統的分頁查詢
一般開發人員對于web分頁的解決方案是使用limit子句來完成，語法為：
limit n,m
對于小規模數據應用來講，可能并不會有大問題，在一定程度上還是比較有效率的，但是如果在大數據應用來講，它的效率就可能很低了，因為它是全表掃描，數據越往后，即ID越大，數據越慢，例如下面的例子
select * from t_user LIMIT 90000 ,100 ##耗時 1秒 select * from t_user LIMIT 900000 ,100 ##耗時 2秒 select * from t_user LIMIT 9000000 ,100 ##耗時 6秒
耗時依次增加。
因為我們字段比較少，所以可能執行起來沒想象中的慢，我們可以用執行計劃來驗證一下
explain select * from t_user LIMIT 1000000 ,100
從結果看出執行全表掃描，所以如果只取前面幾條，則還是比較容易，如果是取相對靠后的數據，則全表掃描對數據庫壓力時非常大，特別web應用并發查詢，則很容易造成數據庫響應慢。
優化思路1
很明顯，既然做全表掃描引起效率低下，那么我們第一印象肯定想辦法是不是不走全表掃描。可以這么做，先在索引ID上完成分頁操作，然后再根據ID關聯回去。查詢代碼更新如下：
1.索引ID完成分頁
select id from t_user order by id limit 9000000,100
2.最后完成自關聯，最終SQL如下：
select * from t_user a inner join (select id from t_user order by id limit 9000000,100) b on a.id = b.id
3.看看執行計劃，可見已經沒有再全表掃描，這種方式是減少掃描來提高分頁效率。
最終執行時間為3秒，效率提高一倍。
優化思路2
使用子查詢進行優化，原理是通過子句先定位查詢起始ID值，然后再通過主查詢語句的ID往后查詢，此方法適用ID排序的情況。舉例如下：
select * from t_user where id>=(select id from t_user limit 9000000,1) limit 100;
通過執行計劃查看，它也屬于非全表掃描，所以效率同第一種優化方案
優化思路3
把LIMIT 查詢轉換成某個位置的查詢，此思路需要和應用開發進行結合，不是單純的數據庫優化，因為我們實現的是web的分頁查詢，所以是可以通過頁面的現實記錄數和當前頁，計算出該頁的ID范圍，我們拼出以下SQL
select * from t_user where id between 9000000 and 9000100 limit 100;
耗時只需0.4豪秒，是非常快速的查詢，也是我們在生產中最常見的一種實現方法。
總結
優化方案2和優化3其實都是把LIMIT m,n轉換成 limit n的查詢，適合排序字段唯一的情況，當然大部分場景我們的排序字段都會選主鍵ID，這樣操作的確能減輕分頁的壓力。理論上，幾千萬數據在mysql8上還是可以支撐的。
如果在數據量更大的情況下，這幾種方案都無法滿足了，一般我們得在數據庫層和應用層之間設置緩存數據庫，再執行分頁查詢。
