快捷導(dǎo)航

詳細(xì)聊聊MySQL中的LIMIT語(yǔ)句

更新時(shí)間：2021年10月26日 10:43:12 作者：小孩子4919

大家應(yīng)該都知道LIMIT子句可以被用于強(qiáng)制SELECT語(yǔ)句返回指定的記錄數(shù),這篇文章主要給大家介紹了關(guān)于MySQL中LIMIT語(yǔ)句的相關(guān)資料,需要的朋友可以參考下

問題

為了故事的順利發(fā)展，我們得先有個(gè)表：

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個(gè)列，id列是主鍵，key1列是二級(jí)索引列。表中包含1萬條記錄。

當(dāng)我們執(zhí)行下邊這個(gè)語(yǔ)句的時(shí)候，是使用二級(jí)索引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)

這個(gè)很好理解，因?yàn)樵诙?jí)索引idx_key1中，key1列是有序的。而查詢是要取按照key1列排序的第1條記錄，那MySQL只需要從idx_key1中獲取到第一條二級(jí)索引記錄，然后直接回表取得完整的記錄即可。

但是如果我們把上邊語(yǔ)句的LIMIT 1換成LIMIT 5000, 1，則卻需要進(jìn)行全表掃描，并進(jìn)行filesort，執(zhí)行計(jì)劃如下：

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)

有的同學(xué)就很不理解了：LIMIT 5000, 1也可以使用二級(jí)索引idx_key1呀，我們可以先掃描到第5001條二級(jí)索引記錄，對(duì)第5001條二級(jí)索引記錄進(jìn)行回表操作不就好了么，這樣的代價(jià)肯定比全表掃描+filesort強(qiáng)呀。

很遺憾的告訴各位，由于MySQL實(shí)現(xiàn)上的缺陷，不會(huì)出現(xiàn)上述的理想情況，它只會(huì)笨笨的去執(zhí)行全表掃描+filesort，下邊我們嘮叨一下到底是咋回事兒。

server層和存儲(chǔ)引擎層

大家都知道，MySQL內(nèi)部其實(shí)是分為server層和存儲(chǔ)引擎層的：

server層負(fù)責(zé)處理一些通用的事情，諸如連接管理、SQL語(yǔ)法解析、分析執(zhí)行計(jì)劃之類的東西
存儲(chǔ)引擎層負(fù)責(zé)具體的數(shù)據(jù)存儲(chǔ)，諸如數(shù)據(jù)是存儲(chǔ)到文件上還是內(nèi)存里，具體的存儲(chǔ)格式是什么樣的之類的。我們現(xiàn)在基本都使用InnoDB存儲(chǔ)引擎，其他存儲(chǔ)引擎使用的非常少了，所以我們也就不涉及其他存儲(chǔ)引擎了。

MySQL中一條SQL語(yǔ)句的執(zhí)行是通過server層和存儲(chǔ)引擎層的多次交互才能得到最終結(jié)果的。比方說下邊這個(gè)查詢：

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

server層會(huì)分析到上述語(yǔ)句可以使用下邊兩種方案執(zhí)行：

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

server層會(huì)分析上述兩個(gè)方案哪個(gè)成本更低，然后選取成本更低的那個(gè)方案作為執(zhí)行計(jì)劃。然后就調(diào)用存儲(chǔ)引擎提供的接口來真正的執(zhí)行查詢了。

這里假設(shè)采用方案二，也就是使用二級(jí)索引idx_key1執(zhí)行上述查詢。那么server層和存儲(chǔ)引擎層的對(duì)話可以如下所示：

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

InnoDB：“收到，這就去查”，然后InnoDB就通過idx_key1二級(jí)索引對(duì)應(yīng)的B+樹，快速定位到掃描區(qū)間('a', 'b')的第一條二級(jí)索引記錄，然后進(jìn)行回表，得到完整的聚簇索引記錄返回給server層。

server層收到完整的聚簇索引記錄后，繼續(xù)判斷common_field!='a'條件是否成立，如果不成立則舍棄該記錄，否則將該記錄發(fā)送到客戶端。然后對(duì)存儲(chǔ)引擎說：“請(qǐng)把下一條記錄給我哈”

小貼士：

此處將記錄發(fā)送給客戶端其實(shí)是發(fā)送到本地的網(wǎng)絡(luò)緩沖區(qū)，緩沖區(qū)大小由net_buffer_length控制，默認(rèn)是16KB大小。等緩沖區(qū)滿了才真正發(fā)送網(wǎng)絡(luò)包到客戶端。

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

小貼士：
不論是聚簇索引記錄還是二級(jí)索引記錄，都包含一個(gè)稱作next_record的屬性，各個(gè)記錄根據(jù)next_record連成了一個(gè)鏈表，并且鏈表中的記錄是按照鍵值排序的（對(duì)于聚簇索引來說，鍵值指的是主鍵的值，對(duì)于二級(jí)索引記錄來說，鍵值指的是二級(jí)索引列的值）。

server層收到完整的聚簇索引記錄后，繼續(xù)判斷common_field!='a'條件是否成立，如果不成立則舍棄該記錄，否則將該記錄發(fā)送到客戶端。然后對(duì)存儲(chǔ)引擎說：“請(qǐng)把下一條記錄給我哈”

... 然后就不停的重復(fù)上述過程。

直到：

也就是直到InnoDB發(fā)現(xiàn)根據(jù)二級(jí)索引記錄的next_record獲取到的下一條二級(jí)索引記錄不在('a', 'b')區(qū)間中，就跟server層說：“好了，('a', 'b')區(qū)間沒有下一條記錄了”

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

現(xiàn)在大家就知道了server層和存儲(chǔ)引擎層的基本交互過程了。

那LIMIT是什么鬼？

說出來大家可能有點(diǎn)兒驚訝，MySQL是在server層準(zhǔn)備向客戶端發(fā)送記錄的時(shí)候才會(huì)去處理LIMIT子句中的內(nèi)容。拿下邊這個(gè)語(yǔ)句舉例子：

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

如果使用idx_key1執(zhí)行上述查詢，那么MySQL會(huì)這樣處理：

server層向InnoDB要第1條記錄，InnoDB從idx_key1中獲取到第一條二級(jí)索引記錄，然后進(jìn)行回表操作得到完整的聚簇索引記錄，然后返回給server層。server層準(zhǔn)備將其發(fā)送給客戶端，此時(shí)發(fā)現(xiàn)還有個(gè)LIMIT 5000, 1的要求，意味著符合條件的記錄中的第5001條才可以真正發(fā)送給客戶端，所以在這里先做個(gè)統(tǒng)計(jì)，我們假設(shè)server層維護(hù)了一個(gè)稱作limit_count的變量用于統(tǒng)計(jì)已經(jīng)跳過了多少條記錄，此時(shí)就應(yīng)該將limit_count設(shè)置為1。
server層再向InnoDB要下一條記錄，InnoDB再根據(jù)二級(jí)索引記錄的next_record屬性找到下一條二級(jí)索引記錄，再次進(jìn)行回表得到完整的聚簇索引記錄返回給server層。server層在將其發(fā)送給客戶端的時(shí)候發(fā)現(xiàn)limit_count才是1，所以就放棄發(fā)送到客戶端的操作，將limit_count加1，此時(shí)limit_count變?yōu)榱?。
... 重復(fù)上述操作
直到limit_count等于5000的時(shí)候，server層才會(huì)真正的將InnoDB返回的完整聚簇索引記錄發(fā)送給客戶端。

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

怎么辦？

由于MySQL實(shí)現(xiàn)LIMIT子句的局限性，在處理諸如LIMIT 5000, 1這樣的語(yǔ)句時(shí)就無法通過使用二級(jí)索引來加快查詢速度了么？其實(shí)也不是，只要把上述語(yǔ)句改寫成：

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作為一個(gè)子查詢單獨(dú)存在，由于該子查詢的查詢列表只有一個(gè)id列，MySQL可以通過僅掃描二級(jí)索引idx_key1執(zhí)行該子查詢，然后再根據(jù)子查詢中獲得到的主鍵值去表t中進(jìn)行查找。

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