mysql limit 效能優化

起因

需求：獲取某使用者的所有操作記錄日誌

日誌數量雖然不多，但不可能一股腦的塞給使用者，難看不說，還拖累伺服器效能，因而分頁必不可少

limit

基礎用法

limit 的用法是 limit [offset], [rows] ，其中 offset 表示偏移值， rows 表示需要返回的資料行。

問題

mysql 的 limit 給分頁帶來了極大的方便，但資料偏移量一大，limit 的效能就急劇下降。

以下是兩條查詢語句，都是取10條資料，但效能就相去甚遠。

select * from table_name limit 10000,10 select * from table_name limit 0,10

所以不能簡單的使用 limit 語句實現資料分頁。

探究

為什么 offset 偏大之後 limit 查詢會變慢？這需要了解 limit 操作是如何運作的，以下面這句查詢為例：

select * from table_name limit 10000,10

這句 SQL 的執行邏輯是

1.從資料表中讀取第N條資料新增到資料集中

2.重複第一步直到 N = 10000 + 10

3.根據 offset 拋棄前面 10000 條數

4.返回剩餘的 10 條資料

顯然，導致這句 SQL 速度慢的問題出現在第二步！這前面的 10000 條資料完全對本次查詢沒有意義，但是卻佔據了絕大部分的查詢時間！如何解決？首先我們得了解為什么資料庫為什么會這樣查詢。

首先，資料庫的資料儲存並不是像我們想象中那樣，按表按順序儲存資料，一方面是因為計算機儲存本身就是隨機讀寫，另一方面是因為資料的操作有很大的隨機性，即使一開始資料的儲存是有序的，經過一系列的增刪查改之後也會變得凌亂不堪。所以資料庫的資料儲存是隨機的，使用 B+Tree， Hash 等方式組織索引。所以當你讓資料庫讀取第 10001 條資料的時候，資料庫就只能一條一條的去查去數。

第一次優化

根據資料庫這種查詢的特性，就有了一種想當然的方法，利用自增索引（假設為id）：

select * from table_name where (id >= 10000) limit 10

由於普通搜尋是全表搜尋，適當的新增 WHERE 條件就能把搜尋從全表搜尋轉化為範圍搜尋，大大縮小搜尋的範圍，從而提高搜尋效率。

這個優化思路就是告訴資料庫：「你別數了，我告訴你，第10001條資料是這樣的，你直接去拿吧。」

但是！！！你可能已經注意到了，這個查詢太簡單了，沒有任何的附加查詢條件，如果我需要一些額外的查詢條件，比如我只要某個使用者的資料 ，這種方法就行不通了。

可以見到這種思路是有侷限性的，首先必須要有自增索引列，而且資料在邏輯上必須是連續的，其次，你還必須知道特徵值。

如此苛刻的要求，在實際應用中是不可能滿足的。

第二次優化

說起資料庫查詢優化，第一時間想到的就是索引，所以便有了第二次優化：先查找出需要資料的索引列（假設為 id），再通過索引列查找出需要的資料。

Select * From table_name Where id in (Select id From table_name where ( user = xxx )) limit 10000, 10; select * from table_name where( user = xxx ) limit 10000,10

相比較結果是（500w條資料）：第一條花費平均耗時約為第二條的 1/3 左右。

同樣是較大的 offset，第一條的查詢更為複雜，為什么效能反而得到了提升？

這涉及到 mysql 主索引的資料結構 b+Tree ，這裡不展開，基本原理就是：

子查詢只用到了索引列，沒有取實際的資料，所以不涉及到磁碟IO，所以即使是比較大的 offset 查詢速度也不會太差。

利用子查詢的方式，把原來的基於 user 的搜尋轉化為基於主鍵（id）的搜尋，主查詢因為已經獲得了準確的索引值，所以查詢過程也相對較快。

第三次優化

在資料量大的時候 in 操作的效率就不怎么樣了，我們需要把 in 操作替換掉，使用 join 就是一個不錯的選擇。

select * from table_name inner join ( select id from table_name where (user = xxx) limit 10000,10) b using (id)

至此 limit 在查詢上的優化就告一段落了。如果還有更好的優化方式，歡迎留言告知

最終優化

技術上的優化始終是有天花板的，業務的優化效果往往更為顯著。

比如在本例中，因為資料的時效性，我們最終決定，只提供最近15天內的操作日誌，在這個前提下，偏移值 offset 基本不會超過一萬，這樣一來，即使是沒有經過任何優化的 sql，其執行效率也變得可以接受了，所以優化不能侷限於技術層面，有時候對需求進行一下調整，可能會達到意想不到的效果