LIMIT和OFFSET分頁性能差！今天來介紹如何高性能分頁

• 前言

• LIMIT和OFFSET有什麼問題

• 初探LIMIT查詢效率

• 數據準備

• 開始測試

• 分析耗時的原因

• 優化

• 再優化

• 總結

前言

之前的大多數人分頁採用的都是這樣：

SELECT * FROM table LIMIT 20 OFFSET 50

可能有的小夥伴還是不太清楚LIMIT和OFFSET的具體含義和用法，我介紹一下：

• LIMIT X 表示 : 讀取 X 條數據
• LIMIT X, Y 表示 : 跳過 X 條數據，讀取 Y 條數據
• LIMIT Y OFFSET X 表示 : 跳過 X 條數據，讀取 Y 條數據

對於簡單的 小型應用程序 和 數據量不是很大 的場景，這種方式還是沒問題的。

但是你想構建一個 可靠且高效 的系統，一定要一開始就要把它做好。

今天我們將探討已經被廣泛使用的分頁方式存在的問題，以及如何實現 高性能分頁 。

LIMIT和OFFSET有什麼問題

OFFSET 和 LIMIT 對於數據量少的項目來説是沒有問題的，但是，當數據庫裏的 數據量超過服務器內存能夠存儲的能力 ，並且需要對所有數據進行分頁，問題就會出現，為了實現分頁，每次收到分頁請求時，數據庫都需要進行低效的 全表遍歷 。

全表遍歷就是一個全表掃描的過程，就是根據雙向鏈表把磁盤上的數據頁加載到磁盤的緩存頁裏去，然後在緩存頁內部查找那條數據。這個過程是非常慢的，所以説當數據量大的時候，全表遍歷性能非常低，時間特別長，應該儘量避免全表遍歷。

這意味着，如果你有 1 億個用户，OFFSET 是 5 千萬，那麼它需要獲取所有這些記錄 (包括那麼多根本不需要的數據)，將它們放入內存，然後獲取 LIMIT 指定的 20 條結果。

為了獲取一頁的數據：10萬行中的第5萬行到第5萬零20行需要先獲取 5 萬行，這麼做非常低效！

初探LIMIT查詢效率

數據準備

• 本文測試使用的環境：

[[email protected] ~]# cat /etc/system-release
CentOS Linux release 7.9.2009 (Core)

[[email protected] ~]# uname -a
Linux zhyno1 3.10.0-1160.62.1.el7.x86_64 #1 SMP Tue Apr 5 16:57:59 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux

• 測試數據庫採用的是（存儲引擎採用InnoDB，其它參數默認）：

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.25-16 |
+-----------+
1 row in set (0.00 sec)

表結構如下：

CREATE TABLE `limit_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `column1` decimal(11,2) NOT NULL DEFAULT '0.00',
  `column2` decimal(11,2) NOT NULL DEFAULT '0.00',
  `column3` decimal(11,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`)
)ENGINE=InnoDB

mysql> DESC limit_test;
+---------+---------------+------+-----+---------+----------------+
| Field   | Type          | Null | Key | Default | Extra          |
+---------+---------------+------+-----+---------+----------------+
| id      | int           | NO   | PRI | NULL    | auto_increment |
| column1 | decimal(11,2) | NO   |     | 0.00    |                |
| column2 | decimal(11,2) | NO   |     | 0.00    |                |
| column3 | decimal(11,2) | NO   |     | 0.00    |                |
+---------+---------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

插入350萬條數據作為測試：

mysql> SELECT COUNT(*) FROM limit_test;
+----------+
| COUNT(*) |
+----------+
|  3500000 |
+----------+
1 row in set (0.47 sec)

開始測試

首先偏移量設置為0，取20條數據(中間輸出省略)：

mysql> SELECT * FROM limit_test LIMIT 0,20;
+----+----------+----------+----------+
| id | column1  | column2  | column3  |
+----+----------+----------+----------+
|  1 | 50766.34 | 43459.36 | 56186.44 |
 #...中間輸出省略
| 20 | 66969.53 |  8144.93 | 77600.55 |
+----+----------+----------+----------+
20 rows in set (0.00 sec)

可以看到查詢時間基本忽略不計，於是我們要一步一步的加大這個偏移量然後進行測試，先將偏移量改為10000(中間輸出省略)：

mysql> SELECT * FROM limit_test LIMIT 10000,20;
+-------+----------+----------+----------+
| id    | column1  | column2  | column3  |
+-------+----------+----------+----------+
| 10001 | 96945.17 | 33579.72 | 58460.97 |
 #...中間輸出省略
| 10020 |  1129.85 | 27087.06 | 97340.04 |
+-------+----------+----------+----------+
20 rows in set (0.00 sec)

可以看到查詢時間還是非常短的，幾乎可以忽略不計，於是我們將偏移量直接上到340W(中間輸出省略)：

mysql> SELECT * FROM limit_test LIMIT 3400000,20;
+---------+----------+----------+----------+
| id      | column1  | column2  | column3  |
+---------+----------+----------+----------+
| 3400001 |  5184.99 | 67179.02 | 56424.95 |
 #...中間輸出省略
| 3400020 |  8732.38 | 71035.71 | 52750.14 |
+---------+----------+----------+----------+
20 rows in set (0.73 sec)

這個時候就可以看到非常明顯的變化了，查詢時間猛增到了0.73s。

分析耗時的原因

根據下面的結果可以看到三條查詢語句都進行了全表掃描：

mysql> EXPLAIN SELECT * FROM limit_test LIMIT 0,20;
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | limit_test | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3491695 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM limit_test LIMIT 10000,20;
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | limit_test | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3491695 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM limit_test LIMIT 3400000,20;
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | limit_test | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3491695 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)

此時就可以知道的是，在偏移量非常大的時候，就像案例中的LIMIT 3400000,20這樣的查詢。

此時MySQL就需要查詢3400020行數據，然後在返回最後20條數據。

前邊查詢的340W數據都將被拋棄，這樣的執行結果可不是我們想要的。

接下來就是優化大偏移量的性能問題

優化

你可以這樣做：

SELECT * FROM limit_test WHERE id>10 limit 20

這是一種 基於指針 的分頁。你要在本地保存上一次接收到的主鍵 (通常是一個 ID) 和 LIMIT，而不是 OFFSET 和 LIMIT，那麼每一次的查詢可能都與此類似。

為什麼？因為通過顯式吿知數據庫最新行，數據庫就確切地知道從哪裏開始搜索（基於有效的索引），而不需要考慮目標範圍之外的記錄。

我們再來一次測試(中間輸出省略)：

mysql> SELECT * FROM limit_test WHERE id>3400000 LIMIT 20;
+---------+----------+----------+----------+
| id      | column1  | column2  | column3  |
+---------+----------+----------+----------+
| 3400001 |  5184.99 | 67179.02 | 56424.95 |
 #...中間輸出省略
| 3400020 |  8732.38 | 71035.71 | 52750.14 |
+---------+----------+----------+----------+
20 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM limit_test WHERE id>3400000 LIMIT 20;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | limit_test | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 185828 |   100.00 | Using where |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

返回同樣的結果，第一個查詢使用了 0.73 sec ，而第二個僅用了 0.00 sec 。

注意：如果我們的表沒有主鍵，比如是具有多對多關係的表，那麼就使用傳統的 OFFSET/LIMIT 方式，只是這樣做存在潛在的慢查詢問題。所以建議在需要分頁的表中使用自動遞增的主鍵，即使只是為了分頁。

再優化

類似於查詢 SELECT * FROM table_name WHERE id > 3400000 LIMIT 20; 這樣的效率非常快,因為主鍵上是有索引的,但是這樣有個缺點,就是 ID必須是連續的 ,並且查詢不能有WHERE語句,因為WHERE語句會造成過濾數據。那使用場景就非常的侷限了，於是我們可以這樣：

使用覆蓋索引優化

MySQL的查詢完全命中索引的時候，稱為覆蓋索引,是非常快的，因為查詢只需要在索引上進行查找，之後可以直接返回，而不用再回數據表拿數據。因此我們可以先查出索引的 ID，然後根據 Id 拿數據。

SELECT * FROM (SELECT id FROM table_name LIMIT 3400000,20) a LEFT JOIN table_name b ON a.id = b.id;

#或者是

SELECT * FROM table_name a INNER JOIN (SELECT id FROM table_name LIMIT 3400000,20) b USING (id);

總結

• 數據量大的時候不能使用OFFSET/LIMIT來進行分頁，因為OFFSET越大，查詢時間越久。

• 當然不能説所有的分頁都不可以，如果你的數據就那麼幾千、幾萬條，那就很無所謂，隨便使用。

• 如果我們的表沒有主鍵，比如是具有多對多關係的表，那麼就使用傳統的 OFFSET/LIMIT 方式。

• 這種方法適用於要求ID為數值類型，並且查出的數據ID連續的場景且不能有其他字段的排序。

文 章 推 薦 ：

• 什麼？MySQL 8.0 會同時修改兩個ib_logfilesN 文件？

• 簡析XDP的重定向機制

• 網卡限速工具之WonderShaper

• Java Web中日誌跟蹤的簡單實現

• MySQL8.0修改lower_case_table_names參數導致重啟失敗

GreatSQL社區官網:  

http://greatsql.cn/

G i t e e :  

h t t p s : / / g i t e e . c o m / G r e a t S Q L / G r e a t S Q L

G i t H u b :  

h t t p s : / / g i t h u b . c o m / G r e a t S Q L / G r e a t S Q L

Q Q