Limit 是一種常用的分頁查詢語句，它可以指定返回記錄行的偏移量和最大數(shù)目。例如，下面的語句表示從 test 表中查詢 val 等于4的記錄，并返回第300001到第300005條記錄：

select * from test where val=4 limit 300000,5;

這樣的語句看起來很簡單，但是在實際使用中，可能會出現(xiàn)性能問題。為什么呢？我們需要從 Mysql 的索引結(jié)構(gòu)和查詢過程來分析。

Mysql 的索引結(jié)構(gòu)

Mysql 支持多種類型的索引，其中最常用的是 B+ 樹索引。B+ 樹索引是一種平衡多路查找樹，它有以下特點：

• 樹中的每個節(jié)點最多包含 m 個子節(jié)點，m 被稱為 B+ 樹的階。
• 樹中的每個節(jié)點最少包含 m/2（向上取整）個子節(jié)點，除了根節(jié)點和葉子節(jié)點。
• 樹中的所有葉子節(jié)點都位于同一層，并且通過指針相連。
• 樹中的所有非葉子節(jié)點只存儲鍵值（索引列）和指向子節(jié)點的指針。
• 樹中的所有葉子節(jié)點存儲鍵值（索引列）和指向數(shù)據(jù)記錄（聚簇索引）或者數(shù)據(jù)記錄地址（非聚簇索引）的指針。

下圖是一個 B+ 樹索引的示例：

在 Mysql 中，有兩種常見的 B+ 樹索引：聚簇索引和非聚簇索引。

聚簇索引是一種特殊的 B+ 樹索引，它將數(shù)據(jù)記錄和索引放在一起存儲，也就是說，葉子節(jié)點就是數(shù)據(jù)記錄。在 Mysql 中，每張表只能有一個聚簇索引，通常是主鍵或者唯一非空鍵。如果沒有定義這樣的鍵，Mysql 會自動生成一個隱藏的聚簇索引。

非聚簇索引是一種普通的 B+ 樹索引，它將數(shù)據(jù)記錄和索引分開存儲，也就是說，葉子節(jié)點只存儲鍵值和指向數(shù)據(jù)記錄地址的指針。在 Mysql 中，每張表可以有多個非聚簇索引，通常是普通鍵或者唯一鍵。

下圖是一個聚簇索引和非聚簇索引的對比：

聚簇索引

非聚簇索引

Mysql 的查詢過程

當(dāng)我們執(zhí)行一個 SQL 查詢語句時，Mysql 會根據(jù)優(yōu)化器的選擇，使用不同的執(zhí)行計劃來執(zhí)行。其中，最常見的執(zhí)行計劃有以下幾種：

• 全表掃描：顧名思義，就是掃描整張表的所有數(shù)據(jù)記錄，逐條檢查是否滿足條件。這種執(zhí)行計劃通常在沒有合適的索引或者條件過于復(fù)雜時使用。
• 索引掃描：也稱為范圍掃描，就是根據(jù)條件在索引上進(jìn)行查找，并返回滿足條件的記錄。這種執(zhí)行計劃通常在有合適的索引且條件較為簡單時使用。
• 索引覆蓋掃描：也稱為索引只掃描，就是根據(jù)條件在索引上進(jìn)行查找，并返回滿足條件的記錄，但是不需要再訪問數(shù)據(jù)記錄，因為查詢所需的所有字段都在索引中。這種執(zhí)行計劃通常在有合適的索引且查詢字段較少時使用。
• 回表查詢：也稱為索引查找，就是根據(jù)條件在索引上進(jìn)行查找，并返回滿足條件的記錄，然后再根據(jù)索引指針去訪問數(shù)據(jù)記錄，獲取查詢所需的其他字段。這種執(zhí)行計劃通常在有合適的索引但查詢字段較多時使用。

下圖是一個回表查詢的示例：

Mysql 的 Limit 性能問題

回到我們最開始的問題，Mysql 的 Limit 會影響性能嗎？為什么？

答案是：會影響性能，因為 Limit 會導(dǎo)致 Mysql 掃描過多的數(shù)據(jù)記錄或者索引記錄，而且大部分掃描到的記錄都是無用的。

我們以一個非聚簇索引為例，來分析一下 Limit 的影響。假設(shè)我們有一張表 test ，它有兩個字段 id 和 val ，其中 id 是主鍵，val 是非唯一非聚簇索引。表中有 500 萬條數(shù)據(jù)，val 的值從 1 到 10 隨機(jī)分布。我們執(zhí)行以下語句：

select * from test where val=4 limit 300000,5;

這條語句的意思是查詢 val 等于 4 的記錄，并返回第 300001 到第 300005 條記錄。Mysql 會怎么執(zhí)行呢？

首先，Mysql 會選擇 val 索引作為執(zhí)行計劃，因為它可以縮小查詢范圍。然后，Mysql 會從 val 索引的根節(jié)點開始查找，沿著 B+ 樹向下搜索，直到找到第一個 val 等于 4 的葉子節(jié)點。接著，Mysql 會沿著葉子節(jié)點的指針向右移動，掃描所有 val 等于 4 的葉子節(jié)點，并記錄它們對應(yīng)的 id 值和數(shù)據(jù)記錄地址。

由于我們要返回第 300001 到第 300005 條記錄，所以 Mysql 必須掃描至少 300005 個葉子節(jié)點，才能確定哪些是我們需要的。這就導(dǎo)致了大量的隨機(jī) I/O 操作，在磁盤上讀取索引頁。

接下來，Mysql 還要根據(jù)葉子節(jié)點指向的數(shù)據(jù)記錄地址，去訪問數(shù)據(jù)頁，獲取查詢所需的所有字段。由于我們要返回所有字段（select *），所以 Mysql 必須訪問至少 300005 次數(shù)據(jù)頁，才能獲取到完整的數(shù)據(jù)記錄。這又導(dǎo)致了大量的隨機(jī) I/O 操作，在磁盤上讀取數(shù)據(jù)頁。

最后，Mysql 還要對掃描到的數(shù)據(jù)記錄進(jìn)行排序和過濾，拋棄前面 300000 條無用的記錄，只保留后面 5 條有用的記錄。這就導(dǎo)致了大量的 CPU 和內(nèi)存消耗，在內(nèi)存中進(jìn)行排序和過濾。

綜上所述，Mysql 在執(zhí)行這條語句時，需要做以下操作：

• 掃描至少 300005 個索引頁
• 訪問至少 300005 次數(shù)據(jù)頁
• 排序和過濾至少 300005 條數(shù)據(jù)記錄

這些操作都是非常耗時和耗資源和時間的浪費。為了返回 5 條有用的記錄，Mysql 不得不掃描和訪問大量的無用的記錄。這就是 Limit 會影響性能的原因。

那么，有沒有辦法優(yōu)化這個問題呢？

答案是：有，但是需要根據(jù)具體的情況來選擇合適的方法。下面，我們介紹幾種常見的優(yōu)化方法：

• 使用索引覆蓋掃描。如果我們只需要查詢部分字段，而不是所有字段，我們可以嘗試使用索引覆蓋掃描，也就是讓查詢所需的所有字段都在索引中，這樣就不需要再訪問數(shù)據(jù)頁，減少了隨機(jī) I/O 操作。例如，如果我們只需要查詢 id 和 val 字段，我們可以執(zhí)行以下語句：

select id,val from test where val=4 limit 300000,5;

這樣，Mysql 只需要掃描索引頁，而不需要訪問數(shù)據(jù)頁，提高了查詢效率。

• 使用子查詢。如果我們不能使用索引覆蓋掃描，或者查詢字段較多，我們可以嘗試使用子查詢，也就是先用一個子查詢找出我們需要的記錄的 id 值，然后再用一個主查詢根據(jù) id 值獲取其他字段。例如，我們可以執(zhí)行以下語句：

select * from test where id in (select id from test where val=4 limit 300000,5);

這樣，Mysql 先執(zhí)行子查詢，在 val 索引上進(jìn)行范圍掃描，并返回 5 個 id 值。然后，Mysql 再執(zhí)行主查詢，在 id 索引上進(jìn)行點查找，并返回所有字段。這樣，Mysql 只需要掃描 5 個數(shù)據(jù)頁，而不是 300005 個數(shù)據(jù)頁，提高了查詢效率。

• 使用分區(qū)表。如果我們的表非常大，或者數(shù)據(jù)分布不均勻，我們可以嘗試使用分區(qū)表，也就是將一張大表分成多個小表，并按照某個字段或者范圍進(jìn)行劃分。這樣，Mysql 可以根據(jù)條件只訪問部分分區(qū)表，而不是整張表，減少了掃描和訪問的數(shù)據(jù)量。例如，如果我們按照 val 字段將 test 表分成 10 個分區(qū)表（test_1 到 test_10），每個分區(qū)表只存儲 val 等于某個值的記錄，我們可以執(zhí)行以下語句：

select * from test_4 limit 300000,5;

這樣，Mysql 只需要訪問 test_4 這個分區(qū)表，而不需要訪問其他分區(qū)表，提高了查詢效率。