文章詳情頁

MySQL百萬數據深度分頁優化思路解析

瀏覽：168日期：2023-05-08 10:17:36

業務場景

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

創建了一張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是相對較慢的操作。

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

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

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

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

我們看下執行效果：

優化前：1.4s

優化后：0.2s

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

以上就是MySQL百萬數據深度分頁優化思路分析的詳細內容，更多關于MySQL數據分頁優化的資料請關注其它相關文章！

MySQL