LIMIT和OFFSET分頁性能差!今天來介紹如何高性能分頁
* G r e a t S Q L 社 區 原 創 內 容 未 經 授 權 不 得 隨 意 使 用 , 轉 載 請 聯 系 小 編 並 注 明 來 源 。
-
前言
-
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連續的場景且不能有其他字段的排序。
En j o y G r e a t S Q L : )
《零基礎學習MySQL 》 視 頻 課 程
戳 此 小 程 序 即 可 直 達 B 站
文 章 推 薦 :
G r e a t S Q L 是 由 萬 裏 數 據 庫 維 護 的 M y S Q L 分 支 , 專 注 於 提 升 M G R 可 靠 性 及 性 能 , 支 持 I n n o D B 並 行 查 詢 特 性 , 是 適 用 於 金 融 級 應 用 的 M y S Q L 分 支 版 本 。
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 LG 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 LB i l i b i l i :
h t t p s : / / s p a c e . b i l i b i l i . c o m / 1 3 6 3 8 5 0 0 8 2 / v i d e o
http://greatsql.cn/
(對文章有疑問或者有獨到見解都可以去社區官網提出或分享哦~)
微 信 & Q Q 羣 :
可 掃 碼 添 加 G r e a t S Q L 社 區 助 手 微 信 好 友 , 發 送 驗 證 信 息 “ 加 羣 ” 加 入 G r e a t S Q L / M G R 交 流 微 信 羣 , 亦 可 直 接 掃 碼 加 入 G r e a t S Q L / M G R 交 流 Q Q 羣 。
微 信
Q Q
想 看 更 多 技 術 好 文 , 點 個 “ 在 看 ” 吧 !
- LIMIT和OFFSET分頁性能差!今天來介紹如何高性能分頁
- MySQL8.0修改lower_case_table_names參數導致重啟失敗
- Linux利用Cgroup資源控制
- 包拯斷案 | 別再讓慢sql背鍋@還故障一個真相
- 淺析TIMESTAMP類型
- 同樣是刪用户,為啥還有差別?
- MySQL主鍵自增值為什麼有“空洞”?
- MySQL 存儲過程運行的內存管理
- 數據中間件如何與MySQL數據同步?
- MySQL存儲過程中包含HINT導致升級失敗紀實
- MySQL報障之coredump收集處理流程
- MySQL 8.0有趣的新特性:CHECK約束
- 技術分享 | 微服務架構的數據庫為什麼喜歡分庫分表?
- 技術分析 | 淺析MySQL與ElasticSearch的組合使用
- 構建MySQL智能化高可用架構
- 面向開發的內存調試神器,如何使用ASAN檢測內存泄漏、堆棧溢出等問題
- 有事務衝突時節點怎麼加入MGR集羣
- 重磅丨生態共榮——萬里數據庫推動GreatSQL攜手openEuler 共建數據庫產業生態
- 為MySQL MGR實現簡單的負載均衡代理
- 4.直方圖介紹和使用|MySQL索引學習