深入解析MySQL中的LIMIT語句-mysql教程-PHP中文網

這篇文章帶大家了解MySQL中的LIMIT語句，聊聊一個問題--MySQL的LIMIT這麼差勁的嗎？希望對大家有幫助！

深入解析MySQL中的LIMIT語句

最近有多個小夥伴在答疑群組裡問了小孩關於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萬筆記錄。【相關推薦：mysql影片教學】

當我們執行下邊這個語句的時候，是使用二級索引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 > &#39;a&#39; AND key1 < &#39;b&#39; AND common_field != &#39;a&#39;;

登入後複製

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

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

server層會分析上述兩個方案哪個成本較低，然後選取成本較低的該方案作為執行計劃。然後就呼叫儲存引擎提供的介面來真正的執行查詢了。

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

深入解析MySQL中的LIMIT語句

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

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

深入解析MySQL中的LIMIT語句

server層收到完整的叢集索引記錄後，繼續判斷common_field!='a'條件是否成立，如果不成立則捨棄該記錄，否則將該記錄傳送到客戶端。然後對儲存引擎說：「請把下一筆記錄給我哈」

小貼士：

這裡將記錄傳送給客戶端其實是傳送到本地的網絡緩衝區，緩衝區大小由net_buffer_length控制，預設是16KB大小。等緩衝區滿了才真正發送網路包到客戶端。

InnoDB：「收到，這就去查」。 InnoDB根據記錄的next_record屬性找到idx_key1的('a', 'b')區間的下一筆二級索引記錄，然後進行回表操作，將得到的完整的聚集索引記錄傳回server層。

小贴士：

不论是聚簇索引记录还是二级索引记录，都包含一个称作next_record的属性，各个记录根据next_record连成了一个链表，并且链表中的记录是按照键值排序的（对于聚簇索引来说，键值指的是主键的值，对于二级索引记录来说，键值指的是二级索引列的值）。

深入解析MySQL中的LIMIT語句

server层收到完整的聚簇索引记录后，继续判断common_field!='a'条件是否成立，如果不成立则舍弃该记录，否则将该记录发送到客户端。然后对存储引擎说：“请把下一条记录给我哈”

... 然后就不停的重复上述过程。

直到：

深入解析MySQL中的LIMIT語句

也就是直到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語句的詳細內容。更多資訊請關注PHP中文網其他相關文章！