欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL深度分頁問題的三種解決方法

 更新時(shí)間:2025年02月28日 09:25:09   作者:Dolphin_Home  
在 MySQL 中解決深度分頁問題的核心思路是減少掃描的數(shù)據(jù)量,尤其是避免通過 LIMIT offset, size 導(dǎo)致的大范圍數(shù)據(jù)掃描,以下是三種優(yōu)化方法及其原理、適用場景和注意事項(xiàng),需要的朋友可以參考下

前言

在 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 JOINRIGHT 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

對(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)

  1. 方向性LEFT JOIN 和 RIGHT JOIN 的方向取決于表的書寫順序。
    • LEFT JOIN 以左表為主,RIGHT JOIN 以右表為主。
  2. 過濾條件
    • 在 LEFT JOIN 中,若在 WHERE 子句中對(duì)右表字段過濾(如 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é)果對(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)文章

  • Mysql中常用的幾種join連接方式總結(jié)

    Mysql中常用的幾種join連接方式總結(jié)

    join語句是面試中經(jīng)常會(huì)讓你現(xiàn)場寫出來的語句,下面這篇文章主要給大家介紹了關(guān)于Mysql中常用的幾種join連接方式,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下
    2022-05-05
  • MySQL?1130錯(cuò)誤原因分析以及解決方案

    MySQL?1130錯(cuò)誤原因分析以及解決方案

    這篇文章主要給大家介紹了關(guān)于MySQL?1130錯(cuò)誤原因分析以及解決方案的相關(guān)資料,MySQL 1130錯(cuò)誤通常是由于連接MySQL時(shí)使用的用戶名或密碼不正確所導(dǎo)致的,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下
    2023-10-10
  • 小型Drupal數(shù)據(jù)庫備份以及大型站點(diǎn)MySQL備份策略分享

    小型Drupal數(shù)據(jù)庫備份以及大型站點(diǎn)MySQL備份策略分享

    為了防止web服務(wù)器出現(xiàn)故障而引起的數(shù)據(jù)丟失,數(shù)據(jù)庫備份顯得非常重要,以免出現(xiàn)重大損失。本文分析研究一下小型的Drupal站的備份策略以及大型站點(diǎn)的mysql備份策略
    2014-11-11
  • MYSQL慢查詢與日志的設(shè)置與測試

    MYSQL慢查詢與日志的設(shè)置與測試

    這篇文章主要給大家介紹了關(guān)于MYSQL慢查詢與日志的設(shè)置與測試,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2021-01-01
  • 怎樣安全地關(guān)閉MySQL實(shí)例

    怎樣安全地關(guān)閉MySQL實(shí)例

    這篇文章主要介紹了怎樣安全地關(guān)閉MySQL實(shí)例,文中講解非常細(xì)致,幫助大家更好的理解和學(xué)習(xí)MySQL,感興趣的朋友可以了解下
    2020-07-07
  • mysql數(shù)據(jù)表按照某個(gè)字段分類輸出

    mysql數(shù)據(jù)表按照某個(gè)字段分類輸出

    這篇文章主要介紹了mysql數(shù)據(jù)表按照某個(gè)字段分類輸出的方法,十分的簡單實(shí)用,需要的朋友可以參考下
    2015-07-07
  • MySQL中SELECT+UPDATE處理并發(fā)更新問題解決方案分享

    MySQL中SELECT+UPDATE處理并發(fā)更新問題解決方案分享

    這篇文章主要介紹了MySQL中SELECT+UPDATE處理并發(fā)更新問題解決方案分享,需要的朋友可以參考下
    2014-05-05
  • MySQL性能優(yōu)化之Open_Table配置參數(shù)的合理配置建議

    MySQL性能優(yōu)化之Open_Table配置參數(shù)的合理配置建議

    這篇文章主要介紹了MySQL性能優(yōu)化之Open_Table配置參數(shù)的合理配置建議,在MySQL數(shù)據(jù)庫中,Opened_tables表示打開過的表數(shù)量,需要的朋友可以參考下
    2014-07-07
  • 淺談mysql可有類似oracle的nvl的函數(shù)

    淺談mysql可有類似oracle的nvl的函數(shù)

    下面小編就為大家?guī)硪黄獪\談mysql可有類似oracle的nvl的函數(shù)。小編覺得挺不錯(cuò)的,現(xiàn)在就分享給大家,也給大家做個(gè)參考。一起跟隨小編過來看看吧
    2017-02-02
  • mysql 查看表大小的方法實(shí)踐

    mysql 查看表大小的方法實(shí)踐

    本文主要介紹了mysql 查看表大小的方法實(shí)踐,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2023-01-01

最新評(píng)論