從根上理解MySQL的Limit底層原理

造數(shù)據(jù)

老樣子，先建個(gè)表。

還是這張表，表里我創(chuàng)建了近 10W 條數(shù)據(jù)。

CREATE?TABLE?demo_info(
????id?INT?NOT?NULL?auto_increment,
????key1?VARCHAR(100),
????key2?INT,
????key3?VARCHAR(100),
????key_part1?VARCHAR(100),
????key_part2?VARCHAR(100),
????key_part3?VARCHAR(100),
????common_field?VARCHAR(100),
????PRIMARY?KEY?(id),
????KEY?idx_key1?(key1),
????UNIQUE?KEY?uk_key2?(key2),
????KEY??idx_key3?(key3),
????KEY?idx_key_part(key_part1,?key_part2,?key_part3)
)ENGINE?=?INNODB?CHARSET=utf8mb4;

id 列是主鍵，key1 列是二級(jí)索引列。

查看執(zhí)行計(jì)劃

從 sql 執(zhí)行計(jì)劃看 Limit 的影響，分析一下 sql 執(zhí)行計(jì)劃。

在二級(jí)索引 idx_key1 中，key1 列是有序的，查找按 key1 列排序的第 1 條記錄，MySQL 只需要從 idx_key1 中獲取到第一條二級(jí)索引記錄，然后直接回表取得完整的記錄即可，這個(gè)很容易理解。

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

explain?select?*?from?demo_info?order?by?key1?limit?10000,?1;

有的同學(xué)就很不理解了：limit 10000, 1也可以使用二級(jí)索引 idx_key1 呀，我們可以先掃描到第 10001 條二級(jí)索引記錄，對(duì)第 10001 條二級(jí)索引記錄進(jìn)行回表操作就好了啊。

由于 MySQL 實(shí)現(xiàn)缺陷，不會(huì)出現(xiàn)上述的理想情況，它只會(huì)全表掃描 + filesort，下邊我們分析一下。

Limit 執(zhí)行過程

下面我們從 server 層和存儲(chǔ)引擎層分析 Limit 執(zhí)行過程，MySQL 其實(shí)是分為 server 層和存儲(chǔ)引擎層的：

• server 層負(fù)責(zé)處理一些通用的事情，諸如連接管理、SQL 語法解析、分析執(zhí)行計(jì)劃之類的東西

• 存儲(chǔ)引擎層負(fù)責(zé)具體的數(shù)據(jù)存儲(chǔ)，諸如數(shù)據(jù)是存儲(chǔ)到文件上還是內(nèi)存里，具體的存儲(chǔ)格式是什么樣的之類的。我們現(xiàn)在基本都使用 InnoDB 存儲(chǔ)引擎，其他存儲(chǔ)引擎使用的非常少了，所以我們也就不討論其他存儲(chǔ)引擎了。

MySQL 中一條 SQL 語句的執(zhí)行是通過 server 層和存儲(chǔ)引擎層的多次交互才能得到最終結(jié)果的。先不用 Limit 子句舉一個(gè)簡(jiǎn)單例子分析：

SELECT?*?FROM?demo_info?WHERE?key1?>?'a'?AND?key1?'b'?AND?common_field?!=?'a';

server層會(huì)分析到上述語句可以使用下邊兩種方案執(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ǔ)引擎層的執(zhí)行過程如下：

• server 層：“去查查 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 找到 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 在哪里起作用呢？

MySQL 是在 server 層準(zhǔn)備向客戶端發(fā)送記錄的時(shí)候才會(huì)去處理 limit 子句中的內(nèi)容。舉個(gè)例子：

select?*?from?demo_info?order?by?key1?limit?10000,?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 10000, 1 的要求，意味著符合條件的記錄中的第 10001 條才可以真正發(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)榱?2。

… 重復(fù)上述操作

直到 limit_count 等于 10000 的時(shí)候，server 層才會(huì)真正的將 InnoDB 返回的完整聚集索引記錄發(fā)送給客戶端。

從上述過程中我們可以看到，MySQL 中是在實(shí)際向客戶端發(fā)送記錄前才會(huì)去判斷 limit 子句是否符合要求，所以如果使用二級(jí)索引執(zhí)行上述查詢的話，意味著要進(jìn)行 10001 次回表操作。server 層在進(jìn)行執(zhí)行計(jì)劃分析的時(shí)候會(huì)覺得執(zhí)行這么多次回表的成本太大了，還不如直接全表掃描 + filesort 快呢，全表掃描 + filesort 就是把聚集索引中的記錄都依次與給定的搜索條件進(jìn)行比較，把符合搜索條件的記錄再進(jìn)行排序，MySQL 認(rèn)為這樣操作的成本比多次回表成本低，所以就選擇了后者執(zhí)行查詢。

注意

有一個(gè)點(diǎn)很容易混淆，走 PRIMARY 索引和全表掃描有什么區(qū)別呢？他們其實(shí)都是在聚集索引上操作的（聚集索引 B+ 樹的葉子結(jié)點(diǎn)是根據(jù)主鍵排好序的完整的用戶記錄，包含表里的所有字段），區(qū)別就在于

全表掃描將聚集索引 B+ 樹的葉子結(jié)點(diǎn)依次順序掃描并判斷條件，在以下幾種情況會(huì)走全表掃描：

• select * from demo_info 這種無條件的查詢語句
• select * from demo_info where common_field != 'a'這種條件字段 common_field沒有建索引的情況
• select * from demo_info order by key1 limit 10000, 1 條件字段 key1 建了索引但是 MySQL 認(rèn)為走二級(jí)索引的成本比全表掃描成本高的情況。

PRIMARY 索引是利用二分思想將聚集索引 B+ 樹到指定范圍區(qū)間進(jìn)行掃描，比如select * from demo_info where id in (1, 2)這種條件字段是主鍵 id，可以很好的利用 PRIMARY 索引進(jìn)行二分的快速查詢。

怎么解決這個(gè)問題？

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

select?*?from?demo_info?d,?
(select?id?from?demo_info?order?by?key1?limit?10000,?1)?t?
WHERE?d.id?=?t.id;
--?或者這么寫
select?*?from?demo_info?d
join?
(select?id?from?demo_info?order?by?key1?limit?10000,?1)?t
on?d.id?=?t.id

這樣，select id from demo_info order by key1 limit 10000, 1作為一個(gè)子查詢單獨(dú)存在，由于該子查詢的查詢列表只有一個(gè) id 列，MySQL 可以通過僅掃描二級(jí)索引 idx_key1 的葉子結(jié)點(diǎn)不用回表，然后再根據(jù)子查詢中獲得到的主鍵值去表 demo_info 中進(jìn)行查找。這樣就省去了前 10000 條記錄的回表操作，從而大大提升了查詢效率！