MySQL深分頁(yè)優(yōu)化

MySQL中的深分頁(yè)問題通常是指當(dāng)我們通過LIMIT語句查詢數(shù)據(jù)，尤其是在翻到較后面的頁(yè)碼時(shí)，性能會(huì)急劇下降。

例如，查詢第1000頁(yè)的數(shù)據(jù)，每頁(yè)10條，系統(tǒng)需要跳過前9990條數(shù)據(jù)，然后才能獲取到所需的記錄，這在大數(shù)據(jù)集上非常低效。

傳統(tǒng)的深分頁(yè)實(shí)現(xiàn)方法通常是使用OFFSET和LIMIT直接做分頁(yè)查詢：

SELECT * FROM table
ORDER BY some_column
LIMIT 9990, 10;

這會(huì)導(dǎo)致數(shù)據(jù)庫(kù)掃描大量不需要的行然后拋棄它們，才能獲取到真正需要的數(shù)據(jù)。

延遲關(guān)聯(lián)的工作方式

延遲關(guān)聯(lián)通過兩步查詢優(yōu)化性能：

1. 快速定位：首先僅在索引上運(yùn)行快速查詢，快速定位到需要的數(shù)據(jù)的位置。這個(gè)步驟不獲取所有字段，只獲取主鍵或者是用于排序的列。
2. 精確獲取：然后根據(jù)第一步查詢獲得的主鍵（或少數(shù)幾個(gè)列），做第二步的查詢以精確獲取所有需要的數(shù)據(jù)字段。

示例：有 posts 表和 comments 表。

-- 查詢有特定標(biāo)簽的文章的ID
SELECT post_id
INTO TEMPORARY TABLE temp_post_ids
FROM posts
WHERE tags LIKE '%特定標(biāo)簽%';

-- 利用臨時(shí)表數(shù)據(jù)進(jìn)行關(guān)聯(lián)查詢
SELECT p.*, c.*
FROM temp_post_ids t
JOIN posts p ON t.post_id = p.id
LEFT JOIN comments c ON p.id = c.post_id;

為什么能提升性能

• 減少數(shù)據(jù)掃描量：第一步查詢只在索引上運(yùn)行，大大減少了數(shù)據(jù)的掃描量。因?yàn)樗饕ǔ１韧暾臄?shù)據(jù)行要小很多，而且數(shù)據(jù)庫(kù)可以更有效地在索引上進(jìn)行排序和分頁(yè)操作。
• 減少IO操作：只有在第二步查詢中才會(huì)獲取完整的數(shù)據(jù)行，這減少了數(shù)據(jù)庫(kù)的IO操作，尤其是當(dāng)表中包含大量大型字段（如TEXT, BLOB類型）時(shí)。
• 充分利用索引：通常，第一步的查詢能夠充分利用索引，使查詢效率最大化。

最大ID查詢法

使用最大ID查詢法，我們利用了數(shù)據(jù)庫(kù)中的ID通常是自增（或至少是有序的）這一性質(zhì)。

通過記錄上一次查詢返回的最后一條記錄的ID，下一次查詢時(shí)，我們只需要選擇ID大于這個(gè)值的記錄，這樣避免了掃描和跳過前面所有的記錄。

優(yōu)點(diǎn)：

• 性能提升：這種方法減少了數(shù)據(jù)庫(kù)的負(fù)載，尤其是對(duì)于大數(shù)據(jù)集。因?yàn)樗徊樵冃枰臄?shù)據(jù)，避免了大量的無用掃描。
• 可擴(kuò)展性：隨著數(shù)據(jù)量的增加，傳統(tǒng)的OFFSET方法性能降低，而最大ID方法的性能下降不明顯，適合大數(shù)據(jù)量的場(chǎng)景。
• 簡(jiǎn)單有效：實(shí)現(xiàn)簡(jiǎn)單，但能顯著提高分頁(yè)查詢的性能。

缺點(diǎn)：

• 依賴有序的ID：這個(gè)方法的有效性依賴于有序的ID（比如自增ID）。如果數(shù)據(jù)庫(kù)表中沒有一個(gè)有序的、單調(diào)遞增的字段，這種方法就不適用。
• 不適合復(fù)雜排序需求：當(dāng)查詢需要基于其他字段進(jìn)行排序時(shí)，這種方法可能就不再適用。比如，如果需要基于時(shí)間或者其他非遞增字段進(jìn)行分頁(yè)，最大ID方法就不能直接使用了。
• 數(shù)據(jù)刪除或更新的處理：如果數(shù)據(jù)表中的記錄會(huì)被刪除，那么這可能會(huì)導(dǎo)致某些ID被跳過，從而影響分頁(yè)的連續(xù)性。同樣，如果ID是可更新的，那么這種方法也會(huì)遇到問題。
• 非等距分頁(yè)：使用最大ID進(jìn)行分頁(yè)時(shí)，如果數(shù)據(jù)表中存在大量的刪除操作，導(dǎo)致ID有較大的間隔，可能會(huì)出現(xiàn)每頁(yè)數(shù)據(jù)量不一致的情況。雖然通常這不是一個(gè)大問題，但在某些應(yīng)用場(chǎng)景中可能會(huì)影響用戶體驗(yàn)。
• 首頁(yè)數(shù)據(jù)動(dòng)態(tài)變化：如果你的應(yīng)用場(chǎng)景需要頻繁展示數(shù)據(jù)的最新狀態(tài)，使用最大ID分頁(yè)法可能會(huì)導(dǎo)致最新添加的記錄不被即時(shí)顯示。例如，當(dāng)用戶在瀏覽第二頁(yè)時(shí)，如果首頁(yè)有新數(shù)據(jù)添加，用戶回到首頁(yè)可能看不到這些新數(shù)據(jù)，因?yàn)椴樵兊钠鹗糏D已經(jīng)改變。
• 不適用于隨機(jī)訪問：對(duì)于需要直接跳轉(zhuǎn)到指定頁(yè)面的場(chǎng)景（例如，用戶直接跳轉(zhuǎn)到第100頁(yè)），最大ID方法實(shí)現(xiàn)起來比較困難，因?yàn)槟銦o法直接知道第100頁(yè)開始的ID是多少，除非你額外維護(hù)一個(gè)每頁(yè)開始ID的映射表。

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論