MySQL深分頁問題解決思路

2022-12-23 14:01:19

一、MySQL深分頁問題

我們在日常開發中，查詢資料量比較大的時候，後端基本都會通過前端，行動端傳過來的頁碼，每頁資料行數，通過SQL中的 limit 進行分頁，如果查詢頁數比較小的時候，不會出現太大問題，但是如果查詢頁碼比較大的時候，效能就會出現急劇下降瓶頸

如：

假設有一個千萬量級的表，取1到10條資料

select column_name1，column_name2... from table limit 0,10;

select column_name1，column_name2... from table limit 1000,10;

這兩條語句查詢時間應該在毫秒級完成

select column_name1，column_name2... from table limit 1000000,10;

這條語句執行之間在秒級完成，查詢效率低下，還可能導致介面超時

使用select column_name1,column_name2... from table_name表名 limit offset, rows 的情況下直接⽤limit 1000000，10 掃描的是約100萬條資料，並且是需要回表100W次，也就是說⼤部分效能都耗在隨機存取上，到頭來只⽤到10條資料（總共取1000010條資料只留10條記錄）

這種查詢的慢，其實是因為 limit 後面的偏移量太大導致的

1、limit 語法解讀

limit用於資料的分頁查詢，也會用於資料的擷取，limit的用法：

SELECT column_name1,column_name2... FROM table_name表名 LIMIT offset,rows
或
SELECT column_name1,column_name2... FROM table_name表名 LIMIT rows OFFSET offset

注：

table_name ：表名

column_name：欄位名

第一種：SELECT * FROM table LIMIT offset, rows # 常用形式

-- 從0開始，擷取5條記錄，即檢索行為1到5
SELECT column_name1,column_name2... FROM table_name表名 limit 0,5
-- 注意: 關鍵字limit後面的兩個參與用逗號分割

第二種：SELECT * FROM table LIMIT rows OFFSET offset

-- 從0開始，擷取5條記錄，即檢索行為1到5
SELECT column_name1,column_name2... FROM table_name表名 limit 5 offset 0
-- 注意: 使用limit和offset兩個關鍵字，並且各帶一個引數，中間沒有逗號分割

第三種：SELECT * FROM table LIMIT rows

-- 擷取記錄的前五行資料，可以理解為offset的預設值為0
SELECT column_name1,column_name2... FROM table_name表名 limit 5

2、回表

回表，顧名思義就是回到表中，也就是先通過普通索引掃描出資料所在的行，再通過行主鍵ID取出索引中未包含的資料。所以回表的產生也是需要一定條件的，如果一次索引查詢就能獲得所有的select記錄就不需要回表，如果select所需獲得列中有其他的非索引列，就會發生回表動作。即基於非主鍵索引的查詢需要多掃描一棵索引樹

主鍵索引樹的葉子節點直接就是我們要查詢的整行資料，而非主鍵索引的葉子節點是主鍵的值，查到主鍵的值以後，還需要再通過主鍵的值再進行一次查詢

回表，簡單說就是mysql內部需要經過兩次查詢

第一次先索引掃描，然後再通過主鍵去取索引中未能提供的資料

create `table` tb_name(
    `id` int(11) not null auto_increment , 
    `k` int(11) default '0' ,
    `name` varchar(16),
    primary key(id)
    index (k)
)engine=InnoDB;

我們提取id=500這一行的全部資料，這裡通過主鍵id定位到這一行，然後返回資料

select * from T where ID=500;
+-----+---+-------+
| id  | k | name  |
+-----+---+-------+
| 500 | 5 | name5 |
+-----+---+-------+

這裡我們先通過普通索引，搜尋 k 索引樹，得到 ID 的值為 500，再到 ID 索引樹搜尋一次。這個過程即為回表

select * from T where k=5;
+-----+---+-------+
| id  | k | name  |
+-----+---+-------+
| 500 | 5 | name5 |
+-----+---+-------+

二、優化方案

（一）模仿百度、谷歌方案（前端業務控制）

類似於分段。我們給每次只能翻100頁、超過一百頁的需要重新載入後面的100頁。這樣就解決了每次載入數量資料大速度慢的問題了

這種方式比較簡單粗暴，就是不允許檢視這麼靠後的資料

（二）索引覆蓋 + 子查詢

根據主鍵 id，在上面建了索引，先在索引樹中找到開始位置的 id 值，再根據找到的 id 值查詢行資料

SELECT 
    id,name,age 
FROM
    t_user user
WHERE
    user.id = (select MIN(id) from t_user where age = #{age})

SELECT 
    id,name,age 
FROM 
    t_user 
WHERE 
    id >= (SELECT id FROM t_user order by id LIMIT 80000,1) 
LIMIT 10

（三）起始位置重定義（記錄每次取出的最大id，然後where id > 最大id）

這種方法適用於：除了主鍵ID等離散型欄位外，也適用連續型欄位datetime等

最大id由前端分頁 pageNum 和 pageIndex 計算出來

select * from table_name Where id > 最大id limit 10000, 10;

到此這篇關於MySQL深分頁問題解決思路的文章就介紹到這了,更多相關MySQL深分頁內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

MySQL深分頁問題解決思路

目錄

一、MySQL深分頁問題

1、limit 語法解讀

2、回表

二、優化方案

熱門文章