建站
咨詢
售后
建站咨詢
新聞中心
前言
之前的大多數(shù)人分頁采用的都是這樣：
站在用戶的角度思考問題，與客戶深入溝通，找到溧陽網(wǎng)站設計與溧陽網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗，讓設計與互聯(lián)網(wǎng)技術結合，創(chuàng)造個性化、用戶體驗好的作品，建站類型包括：成都網(wǎng)站設計、成都網(wǎng)站建設、企業(yè)官網(wǎng)、英文網(wǎng)站、手機端網(wǎng)站、網(wǎng)站推廣、主機域名、網(wǎng)絡空間、企業(yè)郵箱。業(yè)務覆蓋溧陽地區(qū)。
SELECT * FROM table LIMIT 20 OFFSET 50
可能有的小伙伴還是不太清楚LIMIT和OFFSET的具體含義和用法，我介紹一下：
- LIMIT X 表示: 讀取 X 條數(shù)據(jù)
- LIMIT X, Y 表示: 跳過 X 條數(shù)據(jù)，讀取 Y 條數(shù)據(jù)
- LIMIT Y OFFSET X 表示: 跳過 X 條數(shù)據(jù)，讀取 Y 條數(shù)據(jù)
對于簡單的小型應用程序和數(shù)據(jù)量不是很大的場景，這種方式還是沒問題的。
但是你想構建一個可靠且高效的系統(tǒng)，一定要一開始就要把它做好。
今天我們將探討已經(jīng)被廣泛使用的分頁方式存在的問題，以及如何實現(xiàn)高性能分頁。
LIMIT和OFFSET有什么問題
OFFSET 和 LIMIT 對于數(shù)據(jù)量少的項目來說是沒有問題的，但是，當數(shù)據(jù)庫里的數(shù)據(jù)量超過服務器內存能夠存儲的能力，并且需要對所有數(shù)據(jù)進行分頁，問題就會出現(xiàn)，為了實現(xiàn)分頁，每次收到分頁請求時，數(shù)據(jù)庫都需要進行低效的全表遍歷。
全表遍歷就是一個全表掃描的過程，就是根據(jù)雙向鏈表把磁盤上的數(shù)據(jù)頁加載到磁盤的緩存頁里去，然后在緩存頁內部查找那條數(shù)據(jù)。這個過程是非常慢的，所以說當數(shù)據(jù)量大的時候，全表遍歷性能非常低，時間特別長，應該盡量避免全表遍歷。
這意味著，如果你有 1 億個用戶，OFFSET 是 5 千萬，那么它需要獲取所有這些記錄 (包括那么多根本不需要的數(shù)據(jù))，將它們放入內存，然后獲取 LIMIT 指定的 20 條結果。
為了獲取一頁的數(shù)據(jù)：10萬行中的第5萬行到第5萬零20行需要先獲取 5 萬行，這么做非常低效！
初探LIMIT查詢效率
數(shù)據(jù)準備
本文測試使用的環(huán)境：
[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
測試數(shù)據(jù)庫采用的是（存儲引擎采用InnoDB，其它參數(shù)默認）：
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萬條數(shù)據(jù)作為測試：
mysql> SELECT COUNT(*) FROM limit_test;
+----------+
| COUNT(*) |
+----------+
| 3500000 |
+----------+
1 row in set (0.47 sec)
開始測試
首先偏移量設置為0，取20條數(shù)據(jù)(中間輸出省略)：
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。
分析耗時的原因
根據(jù)下面的結果可以看到三條查詢語句都進行了全表掃描：
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行數(shù)據(jù)，然后在返回最后20條數(shù)據(jù)。
前邊查詢的340W數(shù)據(jù)都將被拋棄，這樣的執(zhí)行結果可不是我們想要的。
接下來就是優(yōu)化大偏移量的性能問題
優(yōu)化
你可以這樣做：
SELECT * FROM limit_test WHERE id>10 limit 20
這是一種基于指針的分頁。你要在本地保存上一次接收到的主鍵 (通常是一個 ID) 和 LIMIT，而不是 OFFSET 和 LIMIT，那么每一次的查詢可能都與此類似。
為什么？因為通過顯式告知數(shù)據(jù)庫最新行，數(shù)據(jù)庫就確切地知道從哪里開始搜索（基于有效的索引），而不需要考慮目標范圍之外的記錄。
我們再來一次測試(中間輸出省略)：
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。
注意：如果我們的表沒有主鍵，比如是具有多對多關系的表，那么就使用傳統(tǒng)的 OFFSET/LIMIT 方式，只是這樣做存在潛在的慢查詢問題。所以建議在需要分頁的表中使用自動遞增的主鍵，即使只是為了分頁。
再優(yōu)化
類似于查詢 SELECT * FROM table_name WHERE id > 3400000 LIMIT 20; 這樣的效率非?？?因為主鍵上是有索引的,但是這樣有個缺點,就是ID必須是連續(xù)的,并且查詢不能有WHERE語句,因為WHERE語句會造成過濾數(shù)據(jù)。那使用場景就非常的局限了，于是我們可以這樣：
使用覆蓋索引優(yōu)化
MySQL的查詢完全命中索引的時候，稱為覆蓋索引,是非?？斓?，因為查詢只需要在索引上進行查找，之后可以直接返回，而不用再回數(shù)據(jù)表拿數(shù)據(jù)。因此我們可以先查出索引的 ID，然后根據(jù) Id 拿數(shù)據(jù)。
ELECT * 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);
總結
數(shù)據(jù)量大的時候不能使用OFFSET/LIMIT來進行分頁，因為OFFSET越大，查詢時間越久。
當然不能說所有的分頁都不可以，如果你的數(shù)據(jù)就那么幾千、幾萬條，那就很無所謂，隨便使用。
如果我們的表沒有主鍵，比如是具有多對多關系的表，那么就使用傳統(tǒng)的 OFFSET/LIMIT 方式。
這種方法適用于要求ID為數(shù)值類型，并且查出的數(shù)據(jù)ID連續(xù)的場景且不能有其他字段的排序。
本文名稱：LIMIT和OFFSET分頁性能差！今天來介紹如何高性能分頁
轉載來源：http://www.5511xx.com/article/cdgcsij.html
