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

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

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

通常我們直接查詢(xún)分頁(yè)較大的數(shù)據(jù)速率較慢，我們可以選擇優(yōu)先查詢(xún)主鍵列，因?yàn)槠淇梢酝ㄟ^(guò)索引查詢(xún)且速度最快，然后根據(jù)獲取的主鍵匹配對(duì)應(yīng)的數(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;

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

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

這種方法的原理就是我們?cè)谶M(jìn)行范圍查詢(xún)后需要記錄頁(yè)尾的行號(hào)，當(dāng)查詢(xún)以行號(hào)開(kāi)始的范圍數(shù)據(jù)時(shí)直接根據(jù)行號(hào)匹配，避免了掃描前面的數(shù)據(jù)。

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

方案三：書(shū)簽分頁(yè) (Bookmark Pagination)

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

-- 假設(shè)按create_time排序，上一頁(yè)最后記錄的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;

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

適用場(chǎng)景：允許誤差的近似分頁(yè)

適用于數(shù)據(jù)量極大的場(chǎng)景，即主鍵也不再進(jìn)行分頁(yè)查詢(xún)，而是通過(guò)預(yù)估得到大致行號(hào)的范圍，再通過(guò)主鍵匹配數(shù)據(jù)行（此方案可能會(huì)有誤差，需要根據(jù)場(chǎng)景選擇）

-- 先獲取預(yù)估偏移量
SELECT COUNT(*) 
FROM user 
WHERE sort_field < {target_value};
 
 
-- 再使用延遲關(guān)聯(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)

適用場(chǎng)景：高頻訪問(wèn)的固定排序分頁(yè)

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

性能對(duì)比（100萬(wàn)數(shù)據(jù)測(cè)試）

最佳實(shí)踐建議

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

2.對(duì)高頻查詢(xún)的排序字段建立索引

3.結(jié)合業(yè)務(wù)場(chǎng)景選擇方案：

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

4.對(duì)超過(guò)10萬(wàn)條數(shù)據(jù)的分頁(yè)需求，建議改用滾動(dòng)加載（無(wú)限下拉）模式

方法補(bǔ)充

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

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

一般分頁(yè)不是很深的情況下，我們一般可以通過(guò)以下方法解決大部分的分頁(yè)問(wèn)題

通過(guò)增加主鍵排序，例如：order by id

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

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

什么是Mysql的深度分頁(yè)？

查詢(xún)偏移量過(guò)大的分頁(yè)的場(chǎng)景我們稱(chēng)為深度分頁(yè)，例如以下sql語(yǔ)句就是一個(gè)典型的深度分頁(yè)場(chǎng)景

SELECT * FROM t_xxx ORDER BY id LIMIT 1000000, 20

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

強(qiáng)制索引 force index（不推薦）

一開(kāi)始想著使用force index強(qiáng)制走索引，但是我的leader跟我說(shuō)過(guò)，不建議添加強(qiáng)制索引來(lái)進(jìn)行sql優(yōu)化，主要有以下幾種缺點(diǎn)：

• 影響選擇性最佳的索引：強(qiáng)制使用索引可能會(huì)影響數(shù)據(jù)庫(kù)引擎選擇性最佳的索引，導(dǎo)致查詢(xún)性能下降
• 增加更新操作的時(shí)間：強(qiáng)制使用索引后，數(shù)據(jù)庫(kù)更新操作的時(shí)間會(huì)增加，因?yàn)樗饕募枰桓?/li>
• 降低查詢(xún)的靈活性：如果強(qiáng)制使用索引過(guò)于固定，會(huì)降低查詢(xún)的靈活性，不方便后期維護(hù)。

ID范圍查詢(xún)

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

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

子查詢(xún)+INNER JOIN

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

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

這個(gè)子查詢(xún)先查出來(lái)，作為臨時(shí)表，然后再讓主表join這個(gè)臨時(shí)表去聯(lián)表查詢(xún)需要的t_xxx對(duì)應(yīng)的信息字段，這樣也可以達(dá)到一個(gè)很好的效果，最終sql語(yǔ)句就是這樣：

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

子查詢(xún)+ID過(guò)濾

也可以通過(guò)子查詢(xún)+ID過(guò)濾優(yōu)化的方式進(jìn)行優(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

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

最新評(píng)論