共計 4601 個字符，預計需要花費 12 分鐘才能閱讀完成。

丸趣 TV 小編給大家分享一下 MySQL 中的 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 語句有什么用”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注丸趣 TV 行業資訊頻道！