在 MySQL 中，limit X,Y 的查詢中，X 值越大，那么查詢速度也就越慢，例如以下示例：

• limit 0,10：查詢時(shí)間大概在 20 毫秒左右。
• limit 1000000,10：查詢時(shí)間可能是 15 秒左右（1秒等于 1000 毫秒），甚至更長(zhǎng)時(shí)間。

所以，可以看出，limit 中 X 值越大，那么查詢速度都越慢。

這個(gè)問(wèn)題呢其實(shí)就是 MySQL 中典型的深度分頁(yè)問(wèn)題。那問(wèn)題來(lái)了，為什么 limit 越往后查詢?cè)铰?？如何?yōu)化查詢速度呢？

為什么limit越來(lái)越慢？

在數(shù)據(jù)庫(kù)查詢中，當(dāng)使用 LIMIT x, y 分頁(yè)查詢時(shí)，如果 x 值越大，查詢速度可能會(huì)變慢。這主要是因?yàn)閿?shù)據(jù)庫(kù)需要掃描和跳過(guò) x 條記錄才能返回 y 條結(jié)果。隨著 x 的增加，需要掃描和跳過(guò)的記錄數(shù)也增加，從而導(dǎo)致性能下降。

例如 limit 1000000,10 需要掃描 1000010 行數(shù)據(jù)，然后丟掉前面的 1000000 行記錄，所以查詢速度就會(huì)很慢。

優(yōu)化手段

對(duì)于 MySQL 深度分頁(yè)比較典型的優(yōu)化手段有以下兩種：

• 起始 ID 定位法：使用最后查詢的 ID 作為起始查詢的 ID。
• 索引覆蓋+子查詢。

1.起始ID定位法

起始 ID 定位法指的是 limit 查詢時(shí)，指定起始 ID。而這個(gè)起始 ID 是上一次查詢的最后一條 ID。例如上一次查詢的最后一條數(shù)據(jù)的 ID 為 6800000，那我們就從 6800001 開(kāi)始掃描表，直接跳過(guò)前面的 6800000 條數(shù)據(jù)，這樣查詢的效率就高了，具體實(shí)現(xiàn) SQL 如下：

select name, age, gender
from person
where id > 6800000 -- 核心實(shí)現(xiàn) SQL
order by id limit 10;

其中 id 字段為表的主鍵字段。

為什么起始ID查詢效率高呢？

因此這種查詢是以上一次查詢的最后 ID 作為起始 ID 進(jìn)行查詢的，而上次的 ID 已經(jīng)定位到具體的位置了，所以只需要遍歷 B+ 樹(shù)葉子節(jié)點(diǎn)的雙向鏈表（主鍵索引的底層數(shù)據(jù)結(jié)構(gòu)）就可以查詢到后面的數(shù)據(jù)了，所以查詢效率就比較高，如下圖所示：

如果上次查詢結(jié)果為 9，之后再查詢時(shí)，只需要從 9 之后再遍歷 N 條數(shù)據(jù)就能查詢出結(jié)果了，所以效率就很高。

優(yōu)缺點(diǎn)分析

這種查詢方式，只適合一頁(yè)一頁(yè)的數(shù)據(jù)查詢，例如手機(jī) APP 中刷新聞時(shí)那種瀑布流方式。

但如果用戶是跳著分頁(yè)的，例如查詢完第 1 頁(yè)之后，直接查詢第 250 頁(yè)，那么這種實(shí)現(xiàn)方式就不行了。

2.索引覆蓋+子查詢

此時(shí)我們?yōu)榱瞬樵冃剩梢允褂盟饕采w加子查詢的方式，具體實(shí)現(xiàn)如下。

假設(shè)，我們未優(yōu)化前的 SQL 如下：

select name, age, gender
from person
order by createtime desc 
limit 1000000,10;

在以上 SQL 中，createtime 字段創(chuàng)建了索引，但查詢效率依然很慢，因?yàn)樗〕?100w 完整的數(shù)據(jù)，并需要讀取大量的索引頁(yè)，和進(jìn)行頻繁的回表查詢，所以執(zhí)行效率會(huì)很低。

此時(shí)，我們可以做以下優(yōu)化：

SELECT p1.name, p1.age, p1.gender
FROM person p1
JOIN (
    SELECT id FROM person ORDER BY createtime desc LIMIT 1000000, 10
) AS p2 ON p1.id = p2.id;

相比于優(yōu)化前的 SQL，優(yōu)化后的 SQL 將不需要頻繁回表查詢了，因?yàn)樽硬樵冎兄徊樵冎麈I ID，這時(shí)可以使用索引覆蓋來(lái)實(shí)現(xiàn)。那么子查詢就可以先查詢出一小部分主鍵 ID，再進(jìn)行查詢，這樣就可以大大提升查詢的效率了。

索引覆蓋（Index Coverage）是一種數(shù)據(jù)庫(kù)查詢優(yōu)化技術(shù)，它指的是在執(zhí)行查詢時(shí)，數(shù)據(jù)庫(kù)引擎可以直接從索引中獲取所有需要的數(shù)據(jù)，而不需要再回表（訪問(wèn)主鍵索引或者表中的實(shí)際數(shù)據(jù)行）來(lái)獲取額外的信息。這種方式可以減少磁盤 I/O 操作，從而提高查詢性能。