讓我們從壹個性能很差的常用查詢開始。
選擇*
來自城市
按id DESC排序
限額0，15
這個查詢需要0.00秒，那麽，這個查詢有什麽問題呢？其實這個查詢語句和參數沒有任何問題，因為它使用了下表的主鍵，只讀取15條記錄。
創建表格城市(
id int(10)無符號NOT NULL AUTO_INCREMENT，
city varchar(128)不為空，
主鍵(id)
)ENGINE = InnoDB
真正的問題是當偏移量(分頁偏移量)很大時，就像這樣:
選擇*
來自城市
按id DESC排序
限價100000，15；
當有2M記錄行時，上面的查詢需要0.22秒。通過EXPLAIN查看SQL的執行計劃，可以發現SQL檢索了100015行，但是最後只需要15行。大的分頁偏移量會增加使用的數據，MySQL會將大量最終不會用到的數據加載到內存中。即使假設大部分網站的用戶只訪問前幾頁數據，少量分頁偏移量大的請求也會對整個系統造成危害。臉書意識到了這壹點，但是臉書並沒有優化數據庫以每秒處理更多的請求，而是專註於減少請求響應時間的差異。
對於分頁請求，另壹個信息也很重要，那就是記錄總數。通過下面的查詢，我們可以很容易地得到記錄的總數。
選擇計數(*)
來自城市；
然而，當使用InnoDB作為存儲引擎時，上面的SQL需要9.28秒。壹個不正確的優化是使用SQL _ calc _ found _ rows，可以在分頁查詢時提前準備好符合條件的記錄數，然後只執行壹個select FOUND _ ROWS()；妳可以得到記錄的總數。但大多數情況下，查詢語句的簡短並不意味著性能的提升。不幸的是，這種分頁查詢方法在很多主流框架中都有使用。我們來看看這條語句的查詢性能。
SELECT SQL _ CALC _發現_行*
來自城市
按id DESC排序
限價100000，15；
這條語句耗時20.02秒，是上壹條語句的兩倍。事實證明，使用SQL_CALC_FOUND_ROWS進行分頁並不是壹個好主意。
我們來看看如何優化。文章分為兩部分。第壹部分是如何獲得記錄總數，第二部分是如何獲得真實記錄。
高效地計算行數
如果使用的引擎是MyISAM，可以直接執行COUNT(*)來獲得行數。類似地，在堆表中，行數也存儲在表的元信息中。但是如果引擎是InnoDB，情況會更復雜，因為InnoDB並不在表中保存具體的行數。
我們可以緩存行數，然後我們可以通過守護進程定期更新它們，或者在緩存由於用戶的某些操作而無效時執行以下語句:
選擇計數(*)
來自城市
使用索引(初級)；
獲取記錄
讓我們進入本文最重要的部分，通過分頁來獲得要顯示的記錄。如上所述，大的偏移量會影響性能，所以我們必須重寫查詢語句。為了進行演示，我們創建了壹個新表“news ”,它根據當前情況進行排序(最新發布的排在最前面),以實現壹個高性能的分頁。為簡單起見，我們假設最新新聞稿的Id也是最大的。
創建表格新聞(
id INT無符號主鍵AUTO_INCREMENT，
title VARCHAR(128)不為空
)ENGINE = InnoDB
更有效的方法是基於用戶顯示的最後壹個新聞Id。查詢下壹頁的語句如下，需要傳入當前頁面顯示的最後壹個Id。
選擇*
來自新聞，id & lt$last_id
按id DESC排序
每頁限制$美元
查詢上壹頁的語句類似，只是需要傳入當前頁的第壹個Id，而且要反過來。
選擇*
從新聞中id & gt$last_id
按id ASC排序
每頁限制$美元
上述查詢方法適用於簡單的分頁，即不顯示具體的頁面導航，只顯示“上壹頁”和“下壹頁”，例如在博客的頁腳顯示“上壹頁”和“下壹頁”按鈕。但如果還是難以實現真正的頁面導航，那我們就來看看另壹種方式。
選擇id
來自(
選擇id，((@ CNT:= @ CNT+1)+$ per page-1)% $ per page CNT
來自新聞
JOIN (SELECT @cnt:= 0)T
其中id & lt$last_id
按id DESC排序
限制$ perpage * $按鈕
)C
其中CNT = 0；
通過上面的語句，可以為每個分頁按鈕計算對應於偏移量的id。這種方法還有壹個優點。假設網站上正在發布壹篇新文章，那麽所有文章的位置都會向後移動壹個位置，所以如果用戶在發布文章時換了頁面，他會看到壹篇文章兩次。如果每個按鈕的偏移Id是固定的，這個問題就解決了。Mark Callaghan發表了壹個類似的博客，使用了壹個組合索引和兩個位置變量，但基本思想是相同的。
如果表中的記錄很少被刪除或修改，還可以存儲與表中記錄對應的頁碼，並在該列上創建適當的索引。這樣，在添加記錄時，需要執行下面的查詢來重新生成相應的頁碼。
集合p:= 0；
按id DESC更新新聞集page = CEIL((p:= p+1)/$ per page)ORDER；
當然也可以添加壹個分頁專用的表，可以用後臺程序維護。
更新分頁測試
加入(
SELECT id，cell((p:= p+1)/$ per page)頁面
來自新聞
按id排序
)C
在C.id = T.id上
設置t . page = c . page；
現在很容易獲得任何頁面的元素:
選擇*
來自新聞A
在A.id=B.ID上聯接分頁B
其中page = $ offset
還有另壹種分頁方法，類似於前面的方法。當數據集相對較小並且沒有可用的索引時——例如，在處理搜索結果時——嘗試使用這種方法。當有2M記錄時，在普通服務器上執行下面的查詢大約需要2秒。這個方法比較簡單，只需要創建壹個臨時表來存儲所有的id(這也是性價比最高的地方)。
創建臨時表_tmp(鍵排序(隨機))
選擇id，FLOOR(RAND() * 0x8000000) random
來自城市；
ALTER TABLE _ tmp ADD OFFSET INT UNSIGNED PRIMARY KEY AUTO _ INCREMENT，DROP INDEX SORT，ORDER BY random
接下來，您可以按如下方式執行分頁查詢。
選擇*
來自_tmp
其中偏移量& gt= $offset
按偏移量排序
限制每頁$ 100；
簡單來說，分頁的優化就是。。。當數據量很大時，避免掃描太多記錄。