關(guān)于sql在mysql中的執(zhí)行過程：Mysql中select查詢語句的執(zhí)行過程

如下圖所示：

在 MySQL 中，深分頁（Deep Pagination）是指當(dāng)使用limit和offset進行分頁查詢時，隨著offset值的增大，查詢性能顯著下降的現(xiàn)象。

例如，查詢第 10000 頁（每頁 10 條數(shù)據(jù)）時，offset為 99990，MySQL 需要掃描前面 99990 行才能找到目標(biāo)數(shù)據(jù)，導(dǎo)致性能瓶頸。

1、深分頁

是對大型數(shù)據(jù)集進行分頁查詢時，尤其是當(dāng)需要獲取較后頁的數(shù)據(jù)時，性能可能會受到影響。

傳統(tǒng)的分頁方法在數(shù)據(jù)量較大時，隨著頁數(shù)的增加，性能會迅速下降。

1.1. 傳統(tǒng)分頁

當(dāng)數(shù)據(jù)進行查詢的時候，需要進行以下過程：

SELECT * FROM table_name ORDER BY id LIMIT offset, size;

• limit：控制每頁返回的記錄數(shù)（size）。
• offset：跳過前多少條記錄（offset）。

1.2. 問題原因

1、掃描大量數(shù)據(jù)：

MySQL需要跳過大量的數(shù)據(jù)行才能返回請求的數(shù)據(jù)。在數(shù)據(jù)量較大的表中，掃描的成本是巨大的，導(dǎo)致查詢延遲增加。

2、鎖競爭問題：

在使用OFFSET進行分頁時，數(shù)據(jù)表的鎖可能被頻繁地獲取和釋放，尤其是在高并發(fā)的情況下，會導(dǎo)致鎖競爭問題，進一步影響數(shù)據(jù)庫的響應(yīng)速度。

3、I/O瓶頸：

深分頁查詢會對I/O性能產(chǎn)生壓力，因為每次查詢都需要讀取大量的磁盤數(shù)據(jù)，尤其是在使用MySQL的磁盤存儲時，I/O操作會顯著影響性能。

2、深分頁的優(yōu)化方案

2.1、索引介紹

在mysql中索引分為聚簇索引和非聚簇索引。

1、B+樹索引的特點：

• 節(jié)點存儲：B+樹是一種自平衡的樹結(jié)構(gòu)，其中每個節(jié)點可以有多個子節(jié)點。
• 非葉子節(jié)點存儲的是指向子節(jié)點的指針和分隔值，而葉子節(jié)點存儲的是實際的數(shù)據(jù)記錄或記錄的指針。
• 順序訪問：葉子節(jié)點中的數(shù)據(jù)是按照索引列的順序存儲的，這使得范圍查詢非常高效。

2、聚簇索引和非聚簇索引：

聚簇索引（主鍵索引）的葉子節(jié)點直接存儲行數(shù)據(jù)，而非聚簇索引（二級索引）的葉子節(jié)點存儲的是主鍵值。

如下圖所示：

2.2、優(yōu)化方案分類

1. 基于主鍵游標(biāo)的分頁

1、原理：

通過記錄上一頁的最后一個值（如主鍵或排序字段），作為下一頁的起點，避免offset。

2、適用場景：

數(shù)據(jù)有序且可唯一標(biāo)識（如id或時間戳）。

3、實現(xiàn)步驟：

假設(shè)我們有一個users表，并且希望查詢某一頁的數(shù)據(jù)，傳統(tǒng)的分頁查詢?nèi)缦拢?

SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 1000;


使用游標(biāo)分頁的查詢?nèi)缦拢?

SELECT * FROM users WHERE id > ? ORDER BY id LIMIT 10;

4、優(yōu)點：

避免offset，直接定位到起始位置。查詢效率穩(wěn)定，不受頁數(shù)影響。

5、缺點：

無法直接跳轉(zhuǎn)到任意頁。

需要業(yè)務(wù)層維護“游標(biāo)”（如上一頁最后一個記錄的id）。

2. 延遲關(guān)聯(lián)

1、原理：

先通過子查詢獲取主鍵，再通過主鍵關(guān)聯(lián)原表獲取完整數(shù)據(jù)。

2、適用場景：

需要關(guān)聯(lián)多表或查詢非主鍵字段的場景。

3、實現(xiàn)步驟：

-- 1. 先查詢主鍵（使用覆蓋索引）
SELECT id FROM table_name ORDER BY id LIMIT 99990, 10;

-- 2. 通過主鍵關(guān)聯(lián)原表獲取完整數(shù)據(jù)
SELECT t.* 
FROM table_name t 
JOIN (
    SELECT id FROM table_name ORDER BY id LIMIT 99990, 10
) AS tmp ON t.id = tmp.id;

4、優(yōu)點：

減少掃描數(shù)據(jù)量，尤其是當(dāng)主鍵字段有索引時。

5、缺點：

需要額外的子查詢和 JOIN 操作。

3. 覆蓋索引

1、原理：

創(chuàng)建包含查詢所需字段的復(fù)合索引，避免回表操作。

2、適用場景：

查詢字段較少且可被索引覆蓋。

3、實現(xiàn)步驟：

-- 創(chuàng)建覆蓋索引（假設(shè)按 id 排序）
CREATE INDEX idx_cover ON table_name (id, name, age);

-- 使用覆蓋索引查詢（無需回表）
SELECT id, name, age FROM table_name ORDER BY id LIMIT 100000, 10;

4、優(yōu)點：

索引本身包含所需數(shù)據(jù)，減少 I/O。

5、缺點：

索引占用額外存儲空間。

4. 分區(qū)表

1、原理：

將大表按規(guī)則（如按時間或范圍）拆分為多個分區(qū)，查詢時只掃描相關(guān)分區(qū)。

2、適用場景：

數(shù)據(jù)可按某種規(guī)則分區(qū)（如按時間）。

3、實現(xiàn)步驟：

1、按時間范圍分區(qū)

按時間范圍分區(qū)
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id INT,
    create_time DATETIME
)
PARTITION BY RANGE (YEAR(create_time)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);


-- 查詢2023年的訂單，分頁
SELECT * FROM orders 
WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY create_time DESC 
LIMIT 1000000, 20;


優(yōu)化效果：僅掃描 p2023 分區(qū)，避免全表掃描。

2、按 ID 范圍分區(qū)

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100)
)
PARTITION BY RANGE (id) (
    PARTITION p1 VALUES LESS THAN (1000000),
    PARTITION p2 VALUES LESS THAN (2000000),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);


-- 查詢 ID > 1000000 的用戶，分頁
SELECT * FROM users 
WHERE id > 1000000 
ORDER BY id 
LIMIT 20;

僅掃描 p2 和 p3 分區(qū)，跳過 p1。

4、優(yōu)點：

顯著減少掃描數(shù)據(jù)量。

5、缺點：

分區(qū)管理復(fù)雜，不適合頻繁修改分區(qū)規(guī)則的場景。

5. 緩存機制

1、原理：

對頻繁訪問的分頁結(jié)果進行緩存（如 Redis），減少數(shù)據(jù)庫查詢。

2、適用場景：

數(shù)據(jù)更新頻率低，分頁請求頻繁。

3、實現(xiàn)步驟：

• 使用緩存中間件（如 Redis）存儲分頁結(jié)果。
• 對于冷數(shù)據(jù)或過深分頁，直接返回緩存或提示用戶跳轉(zhuǎn)限制。

4、優(yōu)點：

顯著降低數(shù)據(jù)庫壓力。

5、缺點：

數(shù)據(jù)實時性要求高的場景不適用。

6. 業(yè)務(wù)層優(yōu)化

1、限制最大頁數(shù)：

如限制用戶最多查看前 100 頁。

2、滑動窗口分頁：

允許用戶通過“上一頁/下一頁”滑動訪問，而非跳轉(zhuǎn)到任意頁。

3、預(yù)加載數(shù)據(jù)：

在用戶瀏覽當(dāng)前頁時，預(yù)加載下一頁數(shù)據(jù)。

性能對比

3、總結(jié)

深分頁是 MySQL 處理大數(shù)據(jù)量時的常見性能瓶頸。優(yōu)化的核心在于減少掃描數(shù)據(jù)量和避免 OFFSET 的全表掃描。

根據(jù)業(yè)務(wù)需求選擇合適的方案：

• 優(yōu)先推薦：游標(biāo)分頁或延遲關(guān)聯(lián)（適合大多數(shù)場景）。
• 補充方案：覆蓋索引、分區(qū)表或緩存機制（針對特定需求）。
• 業(yè)務(wù)層配合：限制分頁深度或改用滑動窗口。

通過合理設(shè)計索引、查詢語句和分頁邏輯，可以顯著提升深分頁的性能，避免 MySQL 在大數(shù)據(jù)量下的性能退化。

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

最新評論