業務場景

一般在項目開發中會有很多的統計數據需要進行上報分析，一般在分析過後會在後臺展示出來給運營和產品進行分頁查看，最常見的一種就是根據日期進行篩選。這種統計數據隨著時間的推移數據量會慢慢的變大，達到百萬、千萬條數據只是時間問題。

瓶頸再現

創建了一張user表，給create_time欄位添加了索引。併在該表中添加了100w條數據。

我們這裡使用limit分頁的方式查詢下前5條數據和後5條數據在查詢時間上有什麼區別。

查詢前10條基本上不消耗什麼時間

我們從第50w+開始取數據的時候，查詢耗時1秒。

SQL_NO_CACHE

這個關鍵詞是為了不讓SQL查詢走緩存。

同樣的SQL語句，不同的分頁條件，兩者的性能差距如此之大，那麼隨著數據量的增長，往後頁的查詢所耗時間按理會越來越大。

問題分析

回表

我們一般對於查詢頻率比較高的欄位會建立索引。索引會提高我們的查詢效率。我們上面的語句使用了SELECT * FROM user，但是我們並不是所有的欄位都建立了索引。當從索引文件中查詢到符合條件的數據後，還需要從數據文件中查詢到沒有建立索引的欄位。那麼這個過程稱之為回表。

覆蓋索引

如果查詢的欄位正好創建了索引了，比如 SELECT create_time FROM user，我們查詢的欄位是我們創建的索引，那麼這個時候就不需要再去數據文件裡面查詢，也就不需要回表。這種情況我們稱之為覆蓋索引。

IO

回表操作通常是IO操作，因為需要根據索引查找到數據行後，再根據數據行的主鍵或唯一索引去聚簇索引中查找具體的數據行。聚簇索引一般是存儲在磁碟上的數據文件，因此在執行回表操作時需要從磁碟讀取數據，而磁碟IO是相對較慢的操作。

LIMTI 2000,10 ？

你有木有想過LIMIT 2000,10會不會掃描1-2000行，你之前有沒有跟我一樣，覺得數據是直接從2000行開始取的，前面的根本沒掃描或者不回表。其實這樣的寫法，一個完整的流程是查詢數據，如果不能覆蓋索引，那麼也是要回表查詢數據的。

現在你知道為什麼越到後面查詢越慢了吧！

問題總結

我們現在知道了LIMIT 遇到後面查詢的性能越差，性能差的原因是因為要回表，既然已經找到了問題那麼我們只需要減少回表的次數就可以提升查詢性能了。

解決方案

既然覆蓋索引可以防止數據回表，那麼我們可以先查出來主鍵id（主鍵索引），然後將查出來的數據作為臨時表然後 JOIN 原表就可以了，這樣只需要對查詢出來的5條結果進行數據回表，大幅減少了IO操作。

優化前後性能對比

我們看下執行效果：

• 優化前：1.4s
  

• 優化後：0.2s
  

查詢耗時性能大幅提升。這樣如果分頁數據很大的話，也不會像普通的limit查詢那樣慢。

本文來自博客園，作者：一個程式員的成長，轉載請註明原文鏈接：https://www.cnblogs.com/bingfengdada/p/17384958.html