詳細聊聊MySQL中的LIMIT語句

Posted on by

最近有多個小夥伴在答疑群裡問瞭小孩子關於LIMIT的一個問題，下邊我來大致描述一下這個問題。

問題

為瞭故事的順利發展，我們得先有個表：

CREATE TABLE t (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1)
) Engine=InnoDB CHARSET=utf8;

表t包含3個列，id列是主鍵，key1列是二級索引列。表中包含1萬條記錄。

當我們執行下邊這個語句的時候，是使用二級索引idx_key1的：

mysql>  EXPLAIN SELECT * FROM t ORDER BY key1 LIMIT 1;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | index | NULL          | idx_key1 | 303     | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

這個很好理解，因為在二級索引idx_key1中，key1列是有序的。而查詢是要取按照key1列排序的第1條記錄，那MySQL隻需要從idx_key1中獲取到第一條二級索引記錄，然後直接回表取得完整的記錄即可。

但是如果我們把上邊語句的LIMIT 1換成LIMIT 5000, 1，則卻需要進行全表掃描，並進行filesort，執行計劃如下：

mysql>  EXPLAIN SELECT * FROM t ORDER BY key1 LIMIT 5000, 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9966 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

有的同學就很不理解瞭：LIMIT 5000, 1也可以使用二級索引idx_key1呀，我們可以先掃描到第5001條二級索引記錄，對第5001條二級索引記錄進行回表操作不就好瞭麼，這樣的代價肯定比全表掃描+filesort強呀。

很遺憾的告訴各位，由於MySQL實現上的缺陷，不會出現上述的理想情況，它隻會笨笨的去執行全表掃描+filesort，下邊我們嘮叨一下到底是咋回事兒。

server層和存儲引擎層

大傢都知道，MySQL內部其實是分為server層和存儲引擎層的：

  • server層負責處理一些通用的事情，諸如連接管理、SQL語法解析、分析執行計劃之類的東西
  • 存儲引擎層負責具體的數據存儲，諸如數據是存儲到文件上還是內存裡，具體的存儲格式是什麼樣的之類的。我們現在基本都使用InnoDB存儲引擎，其他存儲引擎使用的非常少瞭，所以我們也就不涉及其他存儲引擎瞭。

MySQL中一條SQL語句的執行是通過server層和存儲引擎層的多次交互才能得到最終結果的。比方說下邊這個查詢：

SELECT * FROM t WHERE key1 > 'a' AND key1 < 'b' AND common_field != 'a';

server層會分析到上述語句可以使用下邊兩種方案執行：

  • 方案一：使用全表掃描
  • 方案二：使用二級索引idx_key1，此時需要掃描key1列值在(‘a’, ‘b’)之間的全部二級索引記錄，並且每條二級索引記錄都需要進行回表操作。

server層會分析上述兩個方案哪個成本更低，然後選取成本更低的那個方案作為執行計劃。然後就調用存儲引擎提供的接口來真正的執行查詢瞭。

這裡假設采用方案二，也就是使用二級索引idx_key1執行上述查詢。那麼server層和存儲引擎層的對話可以如下所示：

server層：“hey，麻煩去查查idx_key1二級索引的(‘a’, ‘b’)區間的第一條記錄，然後把回表後把完整的記錄返給我哈”

InnoDB：“收到，這就去查”，然後InnoDB就通過idx_key1二級索引對應的B+樹，快速定位到掃描區間(‘a’, ‘b’)的第一條二級索引記錄，然後進行回表，得到完整的聚簇索引記錄返回給server層。

server層收到完整的聚簇索引記錄後，繼續判斷common_field!=’a’條件是否成立，如果不成立則舍棄該記錄，否則將該記錄發送到客戶端。然後對存儲引擎說：“請把下一條記錄給我哈”

小貼士：

此處將記錄發送給客戶端其實是發送到本地的網絡緩沖區，緩沖區大小由net_buffer_length控制，默認是16KB大小。等緩沖區滿瞭才真正發送網絡包到客戶端。

InnoDB：“收到，這就去查”。InnoDB根據記錄的next_record屬性找到idx_key1的(‘a’, ‘b’)區間的下一條二級索引記錄，然後進行回表操作，將得到的完整的聚簇索引記錄返回給server層。

小貼士：
不論是聚簇索引記錄還是二級索引記錄，都包含一個稱作next_record的屬性，各個記錄根據next_record連成瞭一個鏈表，並且鏈表中的記錄是按照鍵值排序的（對於聚簇索引來說，鍵值指的是主鍵的值，對於二級索引記錄來說，鍵值指的是二級索引列的值）。

server層收到完整的聚簇索引記錄後，繼續判斷common_field!=’a’條件是否成立，如果不成立則舍棄該記錄，否則將該記錄發送到客戶端。然後對存儲引擎說：“請把下一條記錄給我哈”

… 然後就不停的重復上述過程。

直到：

也就是直到InnoDB發現根據二級索引記錄的next_record獲取到的下一條二級索引記錄不在(‘a’, ‘b’)區間中，就跟server層說：“好瞭，(‘a’, ‘b’)區間沒有下一條記錄瞭”

server層收到InnoDB說的沒有下一條記錄的消息，就結束查詢。

現在大傢就知道瞭server層和存儲引擎層的基本交互過程瞭。

那LIMIT是什麼鬼？

說出來大傢可能有點兒驚訝，MySQL是在server層準備向客戶端發送記錄的時候才會去處理LIMIT子句中的內容。拿下邊這個語句舉例子：

SELECT * FROM t ORDER BY key1 LIMIT 5000, 1;

如果使用idx_key1執行上述查詢，那麼MySQL會這樣處理：

  • server層向InnoDB要第1條記錄，InnoDB從idx_key1中獲取到第一條二級索引記錄，然後進行回表操作得到完整的聚簇索引記錄，然後返回給server層。server層準備將其發送給客戶端，此時發現還有個LIMIT 5000, 1的要求，意味著符合條件的記錄中的第5001條才可以真正發送給客戶端，所以在這裡先做個統計，我們假設server層維護瞭一個稱作limit_count的變量用於統計已經跳過瞭多少條記錄，此時就應該將limit_count設置為1。
  • server層再向InnoDB要下一條記錄，InnoDB再根據二級索引記錄的next_record屬性找到下一條二級索引記錄，再次進行回表得到完整的聚簇索引記錄返回給server層。server層在將其發送給客戶端的時候發現limit_count才是1，所以就放棄發送到客戶端的操作，將limit_count加1，此時limit_count變為瞭2。
  • … 重復上述操作
  • 直到limit_count等於5000的時候，server層才會真正的將InnoDB返回的完整聚簇索引記錄發送給客戶端。

從上述過程中我們可以看到，由於MySQL中是在實際向客戶端發送記錄前才會去判斷LIMIT子句是否符合要求，所以如果使用二級索引執行上述查詢的話，意味著要進行5001次回表操作。server層在進行執行計劃分析的時候會覺得執行這麼多次回表的成本太大瞭，還不如直接全表掃描+filesort快呢，所以就選擇瞭後者執行查詢。

怎麼辦？

由於MySQL實現LIMIT子句的局限性，在處理諸如LIMIT 5000, 1這樣的語句時就無法通過使用二級索引來加快查詢速度瞭麼？其實也不是，隻要把上述語句改寫成：

SELECT * FROM t, (SELECT id FROM t ORDER BY key1 LIMIT 5000, 1) AS d
    WHERE t.id = d.id;

這樣，SELECT id FROM t ORDER BY key1 LIMIT 5000, 1作為一個子查詢單獨存在，由於該子查詢的查詢列表隻有一個id列，MySQL可以通過僅掃描二級索引idx_key1執行該子查詢，然後再根據子查詢中獲得到的主鍵值去表t中進行查找。

這樣就省去瞭前5000條記錄的回表操作，從而大大提升瞭查詢效率！

吐個槽

設計MySQL的大叔啥時候能改改LIMIT子句的這種超笨的實現呢？還得用戶手動想欺騙優化器的方案才能提升查詢效率~

到此這篇關於MySQL中LIMIT語句的文章就介紹到這瞭,更多相關MySQL的LIMIT語句內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：