為什么 LIMIT 0, 10 快，而 LIMIT 1000000, 10 慢？

作者：寫bug的高哈哈 2025-01-22 08:29:18

索引覆蓋是一種數據庫查詢優化技術。它意味著在執行查詢時，數據庫引擎可以直接從索引中獲取所有需要的數據，而不必回表（訪問主鍵索引或表中的實際數據行）來獲取額外的信息。這樣可以減少磁盤I/O操作，從而提高查詢性能。?

最近，我的一個朋友在面試中遇到了這樣一個問題。在MySQL中，假設一個表中有數千萬條記錄，為什么帶有“LIMIT 0, 10”的查詢非常快，而帶有“LIMIT 1000000, 10”的查詢卻非常慢？

讓我們一起來分析一下。

首先，假設我們已經創建了一個名為Student的表，并向其中插入了500萬條學生記錄。以下是創建表和插入部分數據的示例SQL語句：

-- 創建一個名為'Student'的表來存儲學生信息。
CREATETABLE Student (
    idINT AUTO_INCREMENT PRIMARY KEY,
    nameVARCHAR(100),
    age INT,
    gender ENUM('Male', 'Female'),
    create_time TIMESTAMPDEFAULTCURRENT_TIMESTAMP
);

-- 插入500萬條學生記錄的存儲過程：
DELIMITER //
CREATEPROCEDURE insert_students()
BEGIN
    DECLARE i INTDEFAULT1;
    WHILE i <= 5000000 DO
        INSERTINTO Student (name, age, gender)
        VALUES (CONCAT('Student', i), FLOOR(RAND() * 100), ELT(FLOOR(RAND() * 2 + 1), 'Male', 'Female'));
        SET i = i + 1;
    ENDWHILE;
END //
DELIMITER ;

-- 調用存儲過程插入數據。
CALL insert_students();

請注意，插入500萬條記錄可能需要相對較長的時間，具體時間取決于你的數據庫性能。

接下來，我們將分別使用“LIMIT 0, 10”和“LIMIT 1000000, 10”來查詢數據。

-- 查詢前10個學生。
SELECT * FROM Student LIMIT 0, 10;

執行結果如下：

mysql> SELECT * FROM Student LIMIT0, 10;
+---+-----------+-----+--------+---------------------+
| id | name      | age | gender | create_time         |
+---+-----------+-----+--------+---------------------+
| 1  | Student1  | 71  | Male   | 2025-01-01 14:41:15 |
| 2  | Student2  | 9   | Male   | 2025-01-01 14:41:15 |
| 3  | Student3  | 33  | Female | 2025-01-01 14:41:15 |
| 4  | Student4  | 56  | Female | 2025-01-01 14:41:15 |
| 5  | Student5  | 73  | Female | 2025-01-01 14:41:15 |
| 6  | Student6  | 84  | Male   | 2025-01-01 14:41:15 |
| 7  | Student7  | 50  | Male   | 2025-01-01 14:41:15 |
| 8  | Student8  | 4   | Male   | 2025-01-01 14:41:15 |
| 9  | Student9  | 18  | Male   | 2025-01-01 14:41:15 |
| 10 | Student10 | 8   | Male   | 2025-01-01 14:41:15 |
+---+-----------+-----+--------+---------------------+
10 rows in set (0.01 sec)

接下來，我們查詢從第1000000條記錄開始的10條記錄：

mysql> SELECT * FROM Student LIMIT 1000000, 10;
Empty set (1.75 sec)

可以看到，兩者的耗時差異非常大，查詢從毫秒級變成了秒級！！！

因此，可以看出，LIMIT中的X值越大，查詢速度越慢。

這個問題實際上是MySQL中典型的分頁深度問題。那么問題來了：為什么隨著LIMIT偏移量的增加，查詢會變慢？以及如何優化查詢速度？

為什么隨著LIMIT中X值的增大，查詢速度會變慢？

實際上，這主要是因為數據庫需要掃描并跳過X條記錄，然后才能返回Y條結果。隨著X的增加，需要掃描和跳過的記錄數量也會增加，從而導致性能下降。

LIMIT 1000000, 10需要掃描1000010行數據，然后丟棄前1000000行，因此查詢速度會非常慢。

優化方法

在MySQL中，分頁深度問題有兩種典型的優化方法：

起始ID定位法：使用上一次查詢的最后一個ID作為本次查詢的起始ID。
覆蓋索引 + 子查詢。

方法1. 起始ID定位法

起始ID定位法意味著在使用LIMIT查詢時指定一個起始ID，而這個起始ID是上一次查詢的最后一個ID。例如，如果上一次查詢的最后一條記錄的ID是990000，那么我們從990001開始掃描表，直接跳過前990000條記錄，因此查詢效率會提高。具體實現SQL如下：

select name, age, gender from student where id > 990000 order by id limit 10;

可以看到，查詢只用了0.01秒。

mysql> select name, age, gender from student whereid > 990000orderbyidlimit10;
+--------------+-----+--------+
| name         | age | gender |
+--------------+-----+--------+
| Student990001 | 12  | Male   |
| Student990002 | 84  | Female |
| Student990003 | 98  | Male   |
| Student990004 | 14  | Male   |
| Student990005 | 93  | Male   |
| Student990006 | 36  | Male   |
| Student990007 | 47  | Male   |
| Student990008 | 82  | Female |
| Student990009 | 40  | Male   |
| Student990010 | 31  | Male   |
+--------------+-----+--------+
10 rows in set (0.01 sec)

為什么帶有起始ID的查詢效率高？

帶有起始ID的查詢效率高的原因在于它充分利用了數據庫中主鍵索引的B+樹結構。上一次查詢結束時確定的最后一個ID就像一個準確的定位標記，使得當前查詢可以直接從這個標記位置開始。

B+樹的葉子節點通過雙向鏈表連接。當有一個確定的起始ID時，數據庫不需要像普通的LIMIT查詢那樣盲目掃描和跳過大量記錄，而是可以沿著這個雙向鏈表有針對性地向后遍歷，快速找到符合條件的后續記錄。

這種方法避免了處理大量無關數據，大大減少了數據庫的操作開銷，從而顯著提高了查詢的速度和效率。

如下圖所示：

圖片

如果上一次查詢的結果是9，那么后續再次查詢時，只需要從9開始向后遍歷N條數據即可得到結果，因此效率非常高。

這種方式避免了從頭開始掃描大量數據，而是直接從已知的定位點（即上一次查詢的最后一個ID）開始，沿著B+樹的雙向鏈表向后遍歷，快速找到符合條件的記錄。由于不需要掃描和跳過大量無關數據，查詢的開銷大大減少，從而顯著提高了查詢效率。

優缺點分析

這種查詢方法更適合逐頁查詢數據，比如在移動應用中瀏覽新聞時的瀑布流模式。

然而，如果用戶在頁面之間跳轉，例如在查詢第1頁后直接查詢第100頁，那么這種實現方法就不太合適了。

方法2. 覆蓋索引 + 子查詢

如果你仔細觀察，可能會注意到我們之前的查詢語句沒有任何條件，這實際上并不太符合實際應用場景。

假設我們現在有一個需求，要求能夠按創建時間的倒序查詢學生信息并進行分頁。優化前的SQL如下：

select name, age, gender, create_time from student order by create_time desc limit 1000000,10;

執行一下，看看在有500萬條數據的情況下，查詢第100萬條記錄之后的10條數據需要多長時間。

mysql> select name, age, gender, create_time from student orderby create_time desclimit1000000,10;
+----------------+-----+--------+---------------------+
| name           | age | gender | create_time         |
+----------------+-----+--------+---------------------+
| Student#000012 | 15  | Female | 2025-01-01 20:10:19 |
| Student#000013 | 88  | Female | 2025-01-01 20:10:19 |
| Student#000014 | 31  | Male   | 2025-01-01 20:10:19 |
| Student#000015 | 96  | Male   | 2025-01-01 20:10:19 |
| Student#000016 | 61  | Male   | 2025-01-01 20:10:19 |
| Student#000017 | 90  | Male   | 2025-01-01 20:10:19 |
| Student#000018 | 45  | Female | 2025-01-01 20:10:19 |
| Student#000019 | 14  | Male   | 2025-01-01 20:10:19 |
| Student#000020 | 70  | Female | 2025-01-01 20:10:19 |
| Student#000021 | 2   | Female | 2025-01-01 20:10:19 |
+----------------+-----+--------+---------------------+
10 rows in set (3.36 sec)

可以看到，實際上用了3秒！！！顯然，用戶會明顯感覺到卡頓，用戶體驗非常差。

讓我們再看看如果改成limit 0, 10會花多少時間。

mysql> select name, age, gender, create_time from student orderby create_time desclimit0,10;
+----------------+-----+--------+---------------------+
| name           | age | gender | create_time         |
+----------------+-----+--------+---------------------+
| Student5000000 | 29  | Male   | 2025-01-01 21:39:19 |
| Student4999999 | 1   | Female | 2025-01-01 21:39:19 |
| Student4999998 | 0   | Female | 2025-01-01 21:39:19 |
| Student4999997 | 44  | Male   | 2025-01-01 21:39:19 |
| Student4999996 | 83  | Male   | 2025-01-01 21:39:19 |
| Student4999995 | 93  | Female | 2025-01-01 21:39:19 |
| Student4999994 | 32  | Female | 2025-01-01 21:39:19 |
| Student4999993 | 90  | Male   | 2025-01-01 21:39:19 |
| Student4999992 | 21  | Female | 2025-01-01 21:39:19 |
| Student4999991 | 68  | Female | 2025-01-01 21:39:19 |
+----------------+-----+--------+---------------------+
10 rows in set (0.00 sec)

幾乎感覺不到時間。

那么為什么深度分頁會變慢呢？

我相信你肯定會說，因為它會掃描1000010行符合條件的數據，然后丟棄前1000000行再返回。

然而，這并不是唯一的原因。

在上面的SQL中，雖然已經為create_time字段創建了索引，但查詢效率仍然很慢。這是因為它需要獲取1000000條完整的數據，包括沒有索引的字段，如name、age和gender。因此，它需要頻繁地回表查找，導致執行效率非常低。

此時，我們可以進行如下優化：

select t1.name, t1.age, t1.gender, t1.create_time from student as t1
inner join
(select id from student order by create_time desc limit 1000000,10) as t2 on t1.id = t2.id;

讓我們再看看執行時間。

mysql> select t1.name, t1.age, t1.gender, t1.create_time from student as t1
innerjoin
(selectidfrom student orderby create_time desclimit1000000,10) as t2 on t1.id = t2.id;
+----------------+-----+--------+---------------------+
| name           | age | gender | create_time         |
+----------------+-----+--------+---------------------+
| Student#000012 | 15  | Female | 2025-01-01 20:10:19 |
| Student#000013 | 88  | Female | 2025-01-01 20:10:19 |
| Student#000014 | 31  | Male   | 2025-01-01 20:10:19 |
| Student#000015 | 96  | Male   | 2025-01-01 20:10:19 |
| Student#000016 | 61  | Male   | 2025-01-01 20:10:19 |
| Student#000017 | 90  | Male   | 2025-01-01 20:10:19 |
| Student#000018 | 45  | Female | 2025-01-01 20:10:19 |
| Student#000019 | 14  | Male   | 2025-01-01 20:10:19 |
| Student#000020 | 70  | Female | 2025-01-01 20:10:19 |
| Student#000021 | 2   | Female | 2025-01-01 20:10:19 |
+----------------+-----+--------+---------------------+
10 rows in set (0.23 sec)

只用了0.23秒。

與原始SQL相比，優化后的SQL有效避免了頻繁的回表操作。關鍵在于子查詢只獲取了主鍵ID。通過利用索引覆蓋技術，它首先準確定位了少量符合條件的主鍵ID，然后基于這些主鍵ID進行后續查詢，從而顯著提高了查詢效率，降低了查詢成本。

索引覆蓋是一種數據庫查詢優化技術。它意味著在執行查詢時，數據庫引擎可以直接從索引中獲取所有需要的數據，而不必回表（訪問主鍵索引或表中的實際數據行）來獲取額外的信息。這樣可以減少磁盤I/O操作，從而提高查詢性能。

責任編輯：武曉燕來源：程序猿技術充電站

索引查詢優化