自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

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

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

最近，我的一個朋友在面試中遇到了這樣一個問題。在MySQL中，假設(shè)一個表中有數(shù)千萬條記錄，為什么帶有“LIMIT 0, 10”的查詢非?？欤鴰в小癓IMIT 1000000, 10”的查詢卻非常慢？

讓我們一起來分析一下。

首先，假設(shè)我們已經(jīng)創(chuàng)建了一個名為Student的表，并向其中插入了500萬條學(xué)生記錄。以下是創(chuàng)建表和插入部分?jǐn)?shù)據(jù)的示例SQL語句：

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

-- 插入500萬條學(xué)生記錄的存儲過程：
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 ;

-- 調(diào)用存儲過程插入數(shù)據(jù)。
CALL insert_students();

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

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

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

執(zhí)行結(jié)果如下：

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偏移量的增加，查詢會變慢？以及如何優(yōu)化查詢速度？

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

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

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

優(yōu)化方法

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

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

方法1. 起始ID定位法

起始ID定位法意味著在使用LIMIT查詢時指定一個起始ID，而這個起始ID是上一次查詢的最后一個ID。例如，如果上一次查詢的最后一條記錄的ID是990000，那么我們從990001開始掃描表，直接跳過前990000條記錄，因此查詢效率會提高。具體實現(xiàn)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的查詢效率高的原因在于它充分利用了數(shù)據(jù)庫中主鍵索引的B+樹結(jié)構(gòu)。上一次查詢結(jié)束時確定的最后一個ID就像一個準(zhǔn)確的定位標(biāo)記，使得當(dāng)前查詢可以直接從這個標(biāo)記位置開始。

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

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

如下圖所示：

圖片圖片

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

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

優(yōu)缺點分析

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

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

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

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

假設(shè)我們現(xiàn)在有一個需求，要求能夠按創(chuàng)建時間的倒序查詢學(xué)生信息并進(jìn)行分頁。優(yōu)化前的SQL如下：

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

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

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行符合條件的數(shù)據(jù)，然后丟棄前1000000行再返回。

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

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

此時，我們可以進(jìn)行如下優(yōu)化：

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;

讓我們再看看執(zhí)行時間。

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相比，優(yōu)化后的SQL有效避免了頻繁的回表操作。關(guān)鍵在于子查詢只獲取了主鍵ID。通過利用索引覆蓋技術(shù)，它首先準(zhǔn)確定位了少量符合條件的主鍵ID，然后基于這些主鍵ID進(jìn)行后續(xù)查詢，從而顯著提高了查詢效率，降低了查詢成本。

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

責(zé)任編輯：武曉燕 來源： 程序猿技術(shù)充電站
索引查詢優(yōu)化
相關(guān)推薦

2025-02-04 12:17:06

LIMIT數(shù)據(jù)性能

2024-09-05 21:24:02

數(shù)據(jù)庫查詢MySQLlimit

2022-05-06 07:46:48

mysql分頁limit 10

2019-12-12 14:32:26

SQL語句數(shù)據(jù)庫

2024-06-05 09:53:17

2023-04-07 08:17:39

fasthttp場景設(shè)計HTTP

2023-09-20 14:54:17

MySQL

2020-09-18 07:01:38

分頁offsetlimit

2020-03-12 13:58:19

MySQL分頁數(shù)據(jù)庫

2011-03-08 15:27:42

ProftpdLimit

2022-08-17 12:28:14

vite代碼前端

2024-05-27 00:00:01

2024-10-07 10:02:28

2023-04-10 08:07:48

MySQLlimitoffset

2020-07-27 09:55:10

微信架構(gòu)索引

2023-02-26 23:43:43

MySQL數(shù)據(jù)庫分頁查詢

2010-05-25 15:12:22

MySQL分頁

2010-05-17 17:23:27

MySQL limit

2011-03-18 09:26:14

iptableslimit

2021-06-09 06:41:11

OFFSETLIMIT分頁
點贊
收藏

51CTO技術(shù)棧公眾號