在數(shù)據(jù)量非常大的情況下，深分頁查詢則變得很常見，深分頁會導致MySQL需要掃描大量前面的數(shù)據(jù)，從而效率低下。例如，使用LIMIT 100000, 10時，MySQL需要掃描前100000條數(shù)據(jù)才能找到第10000頁的數(shù)據(jù)。
在MySQL中解決深分頁問題，可通過以下5種優(yōu)化方案實現(xiàn)：

方案一：延遲關聯(lián) (Deferred Join)

原理：先通過子查詢獲取主鍵，再關聯(lián)原表獲取完整數(shù)據(jù)

通常我們直接查詢分頁較大的數(shù)據(jù)速率較慢，我們可以選擇優(yōu)先查詢主鍵列，因為其可以通過索引查詢且速度最快，然后根據(jù)獲取的主鍵匹配對應的數(shù)據(jù)。

SELECT t.* 
FROM user t
INNER JOIN (
SELECT id 
FROM user 
ORDER BY sort_field 
LIMIT 100000, 10
) AS tmp ON t.id = tmp.id;

方案二：有序唯一鍵分頁 (Cursor-based Pagination)

要求：表中存在有序唯一鍵（如自增ID）

這種方法的原理就是我們在進行范圍查詢后需要記錄頁尾的行號，當查詢以行號開始的范圍數(shù)據(jù)時直接根據(jù)行號匹配，避免了掃描前面的數(shù)據(jù)。

-- 假設已知上一頁最后一條記錄的id為12345
SELECT * 
FROM user 
WHERE id > 12345 
ORDER BY id 
LIMIT 10;

方案三：書簽分頁 (Bookmark Pagination)

原理：記錄上一頁最后一條數(shù)據(jù)的排序字段值

-- 假設按create_time排序，上一頁最后記錄的create_time為'2023-01-01 12:00:00'
SELECT * 
FROM user 
WHERE create_time > '2023-01-01 12:00:00' 
ORDER BY create_time 
LIMIT 10;

方案四：預估分頁 (Approximate Pagination)

適用場景：允許誤差的近似分頁

適用于數(shù)據(jù)量極大的場景，即主鍵也不再進行分頁查詢，而是通過預估得到大致行號的范圍，再通過主鍵匹配數(shù)據(jù)行（此方案可能會有誤差，需要根據(jù)場景選擇）

-- 先獲取預估偏移量
SELECT COUNT(*) 
FROM user 
WHERE sort_field < {target_value};
 
 
-- 再使用延遲關聯(lián)獲取精確數(shù)據(jù)
SELECT t.* 
FROM user t
INNER JOIN (
SELECT id 
FROM user 
WHERE sort_field < {target_value} 
ORDER BY sort_field 
LIMIT 10
) AS tmp ON t.id = tmp.id;

方案五：緩存優(yōu)化 (Caching)

適用場景：高頻訪問的固定排序分頁

• 對常用排序方式預生成分頁結果
• 使用Redis等緩存中間結果
• 查詢時優(yōu)先讀取緩存數(shù)據(jù)

性能對比（100萬數(shù)據(jù)測試）

最佳實踐建議

1.優(yōu)先使用有序唯一鍵分頁（如自增ID），時間復雜度從O(n)降至O(1)

2.對高頻查詢的排序字段建立索引

3.結合業(yè)務場景選擇方案：

• 實時性要求高 → 方案二/三
• 數(shù)據(jù)量極大 → 方案四/五
• 允許誤差 → 方案四

4.對超過10萬條數(shù)據(jù)的分頁需求，建議改用滾動加載（無限下拉）模式

方法補充

下面小編為大家整理了一些Mysql深度分頁優(yōu)化的其他思路和方案，希望對大家有所幫助

1.普通分頁的優(yōu)化方法

一般分頁不是很深的情況下，我們一般可以通過以下方法解決大部分的分頁問題

通過增加主鍵排序，例如：order by id

如果需要根據(jù)時間排序，就給常用的字段增加索引，包括時間字段。例如：order by create_time

以上兩種手段其實可以解決大部分的分頁問題了。但是如果后面的頁數(shù)很深了，比如從100w條開始取20條，我們就會發(fā)現(xiàn)再執(zhí)行sql語句就會非常慢，這是因為mysql的優(yōu)化器在發(fā)現(xiàn)sql查詢的行數(shù)超過一定比例的時候，就會自動轉(zhuǎn)換成全表掃描，可以自己模擬數(shù)據(jù)測試一下。

什么是Mysql的深度分頁？

查詢偏移量過大的分頁的場景我們稱為深度分頁，例如以下sql語句就是一個典型的深度分頁場景

SELECT * FROM t_xxx ORDER BY id LIMIT 1000000, 20

2.深度分頁的優(yōu)化方案

強制索引 force index（不推薦）

一開始想著使用force index強制走索引，但是我的leader跟我說過，不建議添加強制索引來進行sql優(yōu)化，主要有以下幾種缺點：

• 影響選擇性最佳的索引：強制使用索引可能會影響數(shù)據(jù)庫引擎選擇性最佳的索引，導致查詢性能下降
• 增加更新操作的時間：強制使用索引后，數(shù)據(jù)庫更新操作的時間會增加，因為索引文件需要被更新
• 降低查詢的靈活性：如果強制使用索引過于固定，會降低查詢的靈活性，不方便后期維護。

ID范圍查詢

如果那種不需要頁碼的場景下，比如滑動加載（消息列表這種），還有那種只有上下頁按鈕點擊的網(wǎng)站分頁，我們可以通過where id > #{上次查詢的最后一條記錄的id} 進行優(yōu)化

# 查詢指定 ID 范圍的數(shù)據(jù)
SELECT * FROM t_xxx WHERE id > 1000000 AND id <= 1000020 ORDER BY id
# 也可以通過記錄上次查詢結果的最后一條記錄的ID進行下一頁的查詢
SELECT * FROM t_xxx WHERE id > 1000000 LIMIT 20

子查詢+INNER JOIN

可以先根據(jù)時間字段（create_time）或者id排序查詢到id，比如：

SELECT id FROM t_xxx ORDER BY create_time DESC LIMIT 1000000,20

這個子查詢先查出來，作為臨時表，然后再讓主表join這個臨時表去聯(lián)表查詢需要的t_xxx對應的信息字段，這樣也可以達到一個很好的效果，最終sql語句就是這樣：

SELECT * FROM t_xxx INNER JOIN (SELECT id FROM t_xxx WHERE name = 'xxx' ORDER BY id LIMIT 1000000,20) AS t_temp ON t_xxx.id = t_temp.id

子查詢+ID過濾

也可以通過子查詢+ID過濾優(yōu)化的方式進行優(yōu)化，例如：

SELECT * FROM t_xxx WHERE name = 'xxx' AND id >(SELECT id FROM t_xxx WHERE name = 'xxx' ORDER BY id LIMIT 1000000,1) ORDER BY id LIMIT 20

到此這篇關于Mysql中深分頁的五種常用方法整理的文章就介紹到這了,更多相關Mysql深分頁內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論