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

来源:https://www.cnblogs.com/greatsql/archive/2022/09/07/16667445.html
-Advertisement-
Play Games

GreatSQL社區原創內容未經授權不得隨意使用,轉載請聯繫小編並註明來源。 GreatSQL是MySQL的國產分支版本,使用上與MySQL一致。 前言 之前的大多數人分頁採用的都是這樣: SELECT * FROM table LIMIT 20 OFFSET 50 可能有的小伙伴還是不太清楚LIM ...


  • GreatSQL社區原創內容未經授權不得隨意使用,轉載請聯繫小編並註明來源。
  • GreatSQL是MySQL的國產分支版本,使用上與MySQL一致。

前言

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

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查詢效率

數據準備

  • 本文測試使用的環境:
[root@zhyno1 ~]# cat /etc/system-release
CentOS Linux release 7.9.2009 (Core)

[root@zhyno1 ~]# 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連續的場景且不能有其他欄位的排序。

Enjoy GreatSQL

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 首先打開 Visual Studio Installer 可以看到vs2022 只支持安裝4.6及以上的版本,如圖所示。那麼該如何安裝4.6以下的版本,下麵將詳細介紹。 安裝4.0~4.5系列 首先在微軟 Visual Studio SDK 庫里去下載相應版本的開發者工具包,然後安裝即可,這種方法簡 ...
  • zabbix自定義監控mysql主從狀態和延遲 zabbix自定義監控mysql主從狀態 | 主機IP|角色 | 主機名| | : | : :| : | |192.168.222.250 | zabbix_server|localhost| |192.168.222.251 |zabbix_agen ...
  • 摘要:本文主要介紹基於君正X2000開發板的OpenHarmony環境搭建以及簡單介紹網路配置情況 本文分享自華為雲社區《君正X2000開發板的OpenHarmony環境搭建》,作者: 星辰27。 本文主要介紹基於君正X2000開發板的OpenHarmony環境搭建以及簡單介紹網路配置情況。 1 概 ...
  • zabbix自定義監控 需要先配置mysql主從,從庫上需要安裝zabbix_agentd zabbix_server配置 zabbix_agentd配置 mysql主從配置 監控mysql主從狀態 主從同步完成 [root@localhost ~]# mysql -uroot -p123456 - ...
  • 關於EMC整改問題,其實能用三要素概括:干擾源、耦合電路、敏感器件;而EMC整改的常用方法也能用四要素概括:屏蔽、接地、濾波、去耦。 以下STS先淺談3種常見的EMC測試具體整改措施:傳導測試干擾整改、輻射測試整改、浪涌測試整改元器件的方法。 傳導測試干擾整改 傳導測試常見的干擾有共模干擾和差模干擾 ...
  • 1. 安裝Mysql-5.6.40 軟體包存放目錄: /usr/local/src/ 1.切換到軟體包目錄操作 cd /usr/local/src/ 使用wget命令下載所需的編譯包 2.解壓操作 tar zxvf mysql-5.6.43-linux-glibc2.12-x86_64.tar.gz ...
  • zabbix自定義監控 自定義監控進程 測試監控httpd,需要在監控端部署httpd,以方便監控 配置監控腳本 #在監控端 [root@localhost ~]# dnf -y install httpd [root@localhost ~]# systemctl start httpd [roo ...
  • Java開源博客系統 AngelBlog 是一個簡潔美觀、功能強大並且自適應的Java博客。使用springboot開發,前端使用Bootstrap。支持移動端自適應,配有完備的前臺和後臺管理功能。 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...