快捷導(dǎo)航

MySQL深分頁優(yōu)化方式

更新時間：2024年12月24日 08:54:18 作者：知知之之

本文討論了MySQL中深分頁問題及其解決方法,包括延遲關(guān)聯(lián)和最大ID查詢法,延遲關(guān)聯(lián)通過兩步查詢優(yōu)化性能,減少數(shù)據(jù)掃描量和IO操作,充分利用索引,最大ID查詢法利用數(shù)據(jù)表中ID的有序性,減少掃描量和IO操作,性能提升明顯,但依賴有序ID、不適合復(fù)雜排序需求

MySQL深分頁優(yōu)化

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

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

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

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

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

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

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

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

示例：有 posts 表和 comments 表。

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

-- 利用臨時表數(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ù)庫可以更有效地在索引上進(jìn)行排序和分頁操作。
減少IO操作：只有在第二步查詢中才會獲取完整的數(shù)據(jù)行，這減少了數(shù)據(jù)庫的IO操作，尤其是當(dāng)表中包含大量大型字段（如TEXT, BLOB類型）時。
充分利用索引：通常，第一步的查詢能夠充分利用索引，使查詢效率最大化。

最大ID查詢法

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

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

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

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

缺點(diǎn)：

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