前言

在 MySQL 中解決深度分頁問題的核心思路是減少掃描的數(shù)據(jù)量，尤其是避免通過 LIMIT offset, size 導(dǎo)致的大范圍數(shù)據(jù)掃描。以下是三種優(yōu)化方法及其原理、適用場景和注意事項(xiàng)：

1. 子查詢 + 覆蓋索引（延遲關(guān)聯(lián)）

原理

• 先通過覆蓋索引（如二級索引 (name, id)）快速定位目標(biāo)頁的起始 id，再通過主鍵索引回表查詢數(shù)據(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 對應(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é)

核心要點(diǎn)

• 索引設(shè)計(jì)是優(yōu)化基礎(chǔ)，確保覆蓋索引和排序字段匹配。
• 權(quán)衡業(yè)務(wù)需求，優(yōu)先選擇游標(biāo)分頁（記錄 max_id）或延遲關(guān)聯(lián)（子查詢）。
• 搜索引擎適用于復(fù)雜查詢，但引入額外組件需評估成本。

擴(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ì)被過濾掉。

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

對比總結(jié)

關(guān)鍵注意事項(xiàng)

1. 方向性：LEFT JOIN 和 RIGHT JOIN 的方向取決于表的書寫順序。
   • LEFT JOIN 以左表為主，RIGHT JOIN 以右表為主。
2. 過濾條件：
   • 在 LEFT JOIN 中，若在 WHERE 子句中對右表字段過濾（如 WHERE 表B.鍵 IS NULL），會(huì)篩選出僅存在于左表但右表無匹配的行。
3. 性能：
   • 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é)果對比

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深度分頁問題的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論