場景之分頁查詢設計
一、場景
後端開發中，無論是管理後台還是面向普通用戶，必不可少的涉及到批量數據的查詢，這種查詢的結果通產在前端以列表分頁的形式展示出來，包含的參數包括，頁碼pageNum，每頁記錄數pageSize或者說是頁面大小，總共多少條記錄total等。
其中前端傳入參數的時候可能還包含，日期範圍startTime，endTime，以及涉及到根據商品名的模糊匹配等，還有包括按照篩選欄位正序倒序排序等。
二、實現
由於篩選條件通常是多種多樣的，並且有些數據更新頻繁，很難指定有效的快取策略，即使有命中率以及使用率也很低，所以放棄快取策略，壓力直接給到了資料庫這邊。
1、後台sql
操作映射到後台，可能就是查詢語句比如
select * from table_name where create_time >= startTime and create_tiem <= endTime and product_name like %product% order by id desc limit pageNum offset pageSize * (pageNum - 1)
這裡默認前端傳參pageNum頁數是0，所以在參數校驗的時候，可以對頁碼重置，保證偏移量的正確。
if request.PageNum == 0:
pageNum = 1
根據頁碼和頁面大小可以計算出正確的偏移量
offset = pageSize * (pageNum - 1)
2、limit和offset
執行sql的時候可以用offset，limit或者單獨limit做分頁。
比如
1. select* from table LIMIT 1,3
2.select * from table LIMIT 3 OFFSET 1
上面兩種寫法都表示取2,3,4三條條數據。
- 1中，limit後邊跟兩個參數，第一個數表示要跳過的數量，後一位表示要取的數量。 LIMIT 1,3 就是跳過1條數據,從第2條數據開始取，取3條數據，也就是取2,3,4三條數據
- 2中，limit和offset組合使用的時候，limit後面只能有一個參數，表示要取的的數量,offset表示要跳過的數量。LIMIT 3 OFFSET 1 表示跳過1條數據,從第2條數據開始取，取3條數據，也就是取2,3,4三條數據。
3、問題
以上採用limit和offset組合的方式區分偏移量和頁面大小。
實例中只是簡單的多參數查詢，如果涉及到的表記錄數很多，需要逐行遍歷，特別是在用戶端顯示的時候，如果用戶從第一頁直接請求最後一頁，先忽略查詢條件，假設每頁20條，到後端的表現可能就是
select * from table_name order by product_id offset 1000000 limit 20
採用這種SQL查詢分頁的話，從100萬數據中取出這20行數據的代價是非常大的，需要先查出前1000020條記錄，根據product_id排序，然後拋棄前面1000000條。效率很低。
三、優化
所以想到對於資料庫的只是查詢操作，對錶建立索引進行優化。
1、覆蓋索引
如果對於sql查詢的時候只要查找表中的部分欄位，可以通過索引可以直接獲取查詢的結果，不再需要回表查詢，也就是建立覆蓋索引。
select product_id, product_name from table order by product_id offset 1000000 limit 20
對product_id 和product_name兩個欄位建立聯合索引，查找的時候直接命中不會再去主鍵索引樹找真實的記錄。
2、子查詢優化
因為實際開發中，只展示查詢一兩列欄位操作是非常少的，因此上述的覆蓋索引的適用範圍就比較有限。
而子查詢避免了大量查詢偏移量中的記錄，而是只查詢目標記錄的Id，進而再去查詢所有欄位。
select * from table where id>=(select id from table order by proudct_id limit 1000000, 1) limit 20;
這種子查詢也有自己的限制，
- 要求主鍵ID必須是連續的，但是一般的業務中，可以定義默認主鍵或者自定義字自增主鍵，在刪除的時候，採用邏輯刪除（is_del=1）而非物理刪除，對於新增的記錄的可以實現主鍵連續。
- where子句不允許再添加其他條件，但是可以在前端入參的時候做校驗，對於默認參數的分頁查詢可以使用子查詢優化。
3、記錄查詢位置
用戶可能觸發相同查詢條件的多次分頁查詢，比如第一次
select * from table where product_id > 2222 offset 500000 limit 10;
經過此次查詢之後後端記錄下來商品id>2222的第500000條記錄的id，如果在有類似查詢如下
select * from table where product_id > 2222 offset 1000000 limit 10;
分頁時直接從記錄的這個500000條記錄的id的位置開始掃描，從而避免MySQL掃描大量的數據再拋棄的操作。
