MySQL深度分頁問題的三種解決方法
前言
在 MySQL 中解決深度分頁問題的核心思路是減少掃描的數(shù)據(jù)量,尤其是避免通過 LIMIT offset, size
導(dǎo)致的大范圍數(shù)據(jù)掃描。以下是三種優(yōu)化方法及其原理、適用場景和注意事項(xiàng):
1. 子查詢 + 覆蓋索引(延遲關(guān)聯(lián))
原理
- 先通過覆蓋索引(如二級(jí)索引
(name, id)
)快速定位目標(biāo)頁的起始id
,再通過主鍵索引回表查詢數(shù)據(jù)。 - 子查詢只需掃描二級(jí)索引,體積小且有序,能高效跳過
offset
行,獲取起始id
。 - 主查詢通過
id >= [子查詢結(jié)果]
直接定位數(shù)據(jù),避免全表掃描。
示例 SQL
SELECT * FROM mianshiya WHERE name = 'yupi' AND id >= ( SELECT id FROM mianshiya WHERE name = 'yupi' ORDER BY id LIMIT 99999990, 1 ) ORDER BY id LIMIT 10;
或使用 JOIN 優(yōu)化:
SELECT * FROM mianshiya INNER JOIN ( SELECT id FROM mianshiya WHERE name = 'yupi' ORDER BY id LIMIT 99999990, 10 ) AS tmp ON mianshiya.id = tmp.id;
關(guān)鍵點(diǎn)
- 必須創(chuàng)建聯(lián)合索引
(name, id)
,確保子查詢直接利用索引有序性,避免臨時(shí)排序(filesort)。 - 主查詢的
name
條件可省略(若子查詢結(jié)果id
對(duì)應(yīng)的name
必為'yupi'
),但需權(quán)衡數(shù)據(jù)變更風(fēng)險(xiǎn)。
2. 記錄最大 ID(游標(biāo)分頁)
原理
- 每次分頁返回當(dāng)前頁的最大
id
,下頁查詢時(shí)通過WHERE id > max_id LIMIT size
跳過已讀數(shù)據(jù)。 - 僅掃描目標(biāo)數(shù)據(jù)(
size
行),時(shí)間復(fù)雜度穩(wěn)定為O(size)
,性能極佳。
適用場景
- 連續(xù)分頁(如“下一頁”),不支持隨機(jī)跳頁。
- 數(shù)據(jù)按主鍵或有序字段分頁(如
ORDER BY id
)。
示例 SQL
-- 第一頁 SELECT * FROM mianshiya WHERE name = 'yupi' ORDER BY id LIMIT 10; -- 后續(xù)頁(假設(shè)上一頁最大 id 為 100) SELECT * FROM mianshiya WHERE name = 'yupi' AND id > 100 ORDER BY id LIMIT 10;
注意事項(xiàng)
- 若數(shù)據(jù)刪除或新增可能導(dǎo)致少量重復(fù)或遺漏,需業(yè)務(wù)容忍。
- 需前端配合傳遞
max_id
,不可直接跳頁。
3. Elasticsearch 優(yōu)化
原理
- 將數(shù)據(jù)同步到 Elasticsearch,利用其分布式特性加速搜索。
- 通過
search_after
參數(shù)實(shí)現(xiàn)游標(biāo)分頁,類似記錄max_id
方法,避免深分頁性能問題。
ES 分頁示例
{ "query": { "match": { "name": "yupi" } }, "sort": [{ "id": "asc" }], "size": 10, "search_after": [100] -- 上一頁最后一條記錄的排序值 }
ES 注意事項(xiàng)
- 默認(rèn)限制
from + size <= 10000
,深度分頁需改用search_after
或scroll
。 search_after
需基于唯一排序字段,且僅支持連續(xù)分頁。
其他優(yōu)化思路
- 業(yè)務(wù)限制:禁止深度跳頁(如最多展示前 100 頁)。
- 預(yù)計(jì)算分頁位置:按時(shí)間或分段存儲(chǔ)分頁起始 ID(如每 1000 頁記錄一個(gè)起始 ID)。
- 分區(qū)表:按時(shí)間或范圍分區(qū),減少單次掃描數(shù)據(jù)量。
總結(jié)
方法 | 優(yōu)點(diǎn) | 缺點(diǎn) | 適用場景 |
---|---|---|---|
子查詢 + 覆蓋索引 | 支持跳頁,通用性強(qiáng) | 依賴聯(lián)合索引設(shè)計(jì) | 需頻繁分頁且允許跳頁 |
記錄最大 ID | 性能最優(yōu),復(fù)雜度低 | 僅支持連續(xù)分頁 | 連續(xù)分頁(如瀑布流) |
Elasticsearch | 適合復(fù)雜搜索,分布式性能好 | 維護(hù)成本高,ES 需額外學(xué)習(xí) | 高并發(fā)搜索+分頁場景 |
核心要點(diǎn)
- 索引設(shè)計(jì)是優(yōu)化基礎(chǔ),確保覆蓋索引和排序字段匹配。
- 權(quán)衡業(yè)務(wù)需求,優(yōu)先選擇游標(biāo)分頁(記錄
max_id
)或延遲關(guān)聯(lián)(子查詢)。 - 搜索引擎適用于復(fù)雜查詢,但引入額外組件需評(píng)估成本。
擴(kuò)展:MySQL中 join、inner join、left join、right join區(qū)別
在 MySQL 中,JOIN
操作用于將多個(gè)表中的數(shù)據(jù)關(guān)聯(lián)起來。以下是 INNER JOIN
、LEFT JOIN
、RIGHT JOIN
的核心區(qū)別和用法總結(jié),幫助你快速分清它們:
1. INNER JOIN(內(nèi)連接)
作用:只返回兩個(gè)表中完全匹配的行。
結(jié)果:僅包含兩個(gè)表中都存在關(guān)聯(lián)鍵值的行。
語法:
SELECT * FROM 表A INNER JOIN 表B ON 表A.鍵 = 表B.鍵;
示例:
- 表A(員工表)和表B(部門表)通過
department_id
關(guān)聯(lián)。 - 結(jié)果:只顯示有部門的員工信息,無部門或無員工的數(shù)據(jù)會(huì)被過濾掉。
- 表A(員工表)和表B(部門表)通過
2. LEFT JOIN(左外連接)
作用:返回左表(
LEFT JOIN
左側(cè)的表)的所有行,即使右表沒有匹配。結(jié)果:左表所有數(shù)據(jù) + 右表匹配的數(shù)據(jù)(無匹配時(shí)右表字段為
NULL
)。語法:
SELECT * FROM 表A LEFT JOIN 表B ON 表A.鍵 = 表B.鍵;
示例:
- 表A(員工表)LEFT JOIN 表B(部門表)。
- 結(jié)果:顯示所有員工信息,即使員工沒有部門(部門字段為
NULL
)。
3. RIGHT JOIN(右外連接)
作用:返回右表(
RIGHT JOIN
右側(cè)的表)的所有行,即使左表沒有匹配。結(jié)果:右表所有數(shù)據(jù) + 左表匹配的數(shù)據(jù)(無匹配時(shí)左表字段為
NULL
)。語法:
SELECT * FROM 表A RIGHT JOIN 表B ON 表A.鍵 = 表B.鍵;
示例:
- 表A(員工表)RIGHT JOIN 表B(部門表)。
- 結(jié)果:顯示所有部門信息,即使部門沒有員工(員工字段為
NULL
)。
4. JOIN(默認(rèn)是 INNER JOIN)
說明:在 MySQL 中,直接寫 JOIN
等價(jià)于 INNER JOIN
。
SELECT * FROM 表A JOIN 表B ON 表A.鍵 = 表B.鍵; -- 等同于 INNER JOIN
對(duì)比總結(jié)
類型 | 行為 | 適用場景 |
---|---|---|
INNER JOIN | 僅返回兩個(gè)表匹配的行 | 需要精確匹配的數(shù)據(jù)(如訂單和商品) |
LEFT JOIN | 返回左表全部數(shù)據(jù) + 右表匹配的數(shù)據(jù)(右表無匹配則為 NULL ) | 保留左表全部數(shù)據(jù)(如所有員工信息) |
RIGHT JOIN | 返回右表全部數(shù)據(jù) + 左表匹配的數(shù)據(jù)(左表無匹配則為 NULL ) | 保留右表全部數(shù)據(jù)(如所有部門信息) |
關(guān)鍵注意事項(xiàng)
- 方向性:
LEFT JOIN
和RIGHT JOIN
的方向取決于表的書寫順序。LEFT JOIN
以左表為主,RIGHT JOIN
以右表為主。
- 過濾條件:
- 在
LEFT JOIN
中,若在WHERE
子句中對(duì)右表字段過濾(如WHERE 表B.鍵 IS NULL
),會(huì)篩選出僅存在于左表但右表無匹配的行。
- 在
- 性能:
INNER JOIN
通常效率更高,因?yàn)樗婕暗臄?shù)據(jù)量更小。LEFT/RIGHT JOIN
可能因處理NULL
值而略慢,尤其是在大表中。
示例演示
數(shù)據(jù)準(zhǔn)備
-- 員工表(employees) +-------------+-------+---------------+ | employee_id | name | department_id | +-------------+-------+---------------+ | 1 | 張三 | 101 | | 2 | 李四 | 102 | | 3 | 王五 | NULL | +-------------+-------+---------------+ -- 部門表(departments) +---------------+-----------------+ | department_id | department_name | +---------------+-----------------+ | 101 | 技術(shù)部 | | 102 | 市場部 | | 103 | 財(cái)務(wù)部 | +---------------+-----------------+
查詢結(jié)果對(duì)比
INNER JOIN(匹配數(shù)據(jù)):
SELECT * FROM employees INNER JOIN departments ON employees.department_id = departments.department_id;
結(jié)果:
| 1 | 張三 | 101 | 101 | 技術(shù)部 | | 2 | 李四 | 102 | 102 | 市場部 |
LEFT JOIN(保留所有員工):
SELECT * FROM employees LEFT JOIN departments ON employees.department_id = departments.department_id;
結(jié)果:
| 1 | 張三 | 101 | 101 | 技術(shù)部 | | 2 | 李四 | 102 | 102 | 市場部 | | 3 | 王五 | NULL| NULL| NULL | -- 員工無部門,右表字段為 NULL
RIGHT JOIN(保留所有部門):
SELECT * FROM employees RIGHT JOIN departments ON employees.department_id = departments.department_id;
結(jié)果:
| 1 | 張三 | 101 | 101 | 技術(shù)部 | | 2 | 李四 | 102 | 102 | 市場部 | | NULL| NULL| NULL| 103 | 財(cái)務(wù)部 | -- 部門無員工,左表字段為 NULL
總結(jié)
- INNER JOIN:精確匹配,適合需要嚴(yán)格關(guān)聯(lián)的場景。
- LEFT JOIN:保留左表全部數(shù)據(jù),適合主從表查詢(如“所有員工及其部門”)。
- RIGHT JOIN:保留右表全部數(shù)據(jù),使用較少(通常用
LEFT JOIN
調(diào)換表順序替代)。
以上就是MySQL深度分頁問題的三種解決方法的詳細(xì)內(nèi)容,更多關(guān)于MySQL深度分頁問題的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
小型Drupal數(shù)據(jù)庫備份以及大型站點(diǎn)MySQL備份策略分享
為了防止web服務(wù)器出現(xiàn)故障而引起的數(shù)據(jù)丟失,數(shù)據(jù)庫備份顯得非常重要,以免出現(xiàn)重大損失。本文分析研究一下小型的Drupal站的備份策略以及大型站點(diǎn)的mysql備份策略2014-11-11mysql數(shù)據(jù)表按照某個(gè)字段分類輸出
這篇文章主要介紹了mysql數(shù)據(jù)表按照某個(gè)字段分類輸出的方法,十分的簡單實(shí)用,需要的朋友可以參考下2015-07-07MySQL中SELECT+UPDATE處理并發(fā)更新問題解決方案分享
這篇文章主要介紹了MySQL中SELECT+UPDATE處理并發(fā)更新問題解決方案分享,需要的朋友可以參考下2014-05-05MySQL性能優(yōu)化之Open_Table配置參數(shù)的合理配置建議
這篇文章主要介紹了MySQL性能優(yōu)化之Open_Table配置參數(shù)的合理配置建議,在MySQL數(shù)據(jù)庫中,Opened_tables表示打開過的表數(shù)量,需要的朋友可以參考下2014-07-07