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

如下圖所示：

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

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

1、深分頁(yè)

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

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

1.1. 傳統(tǒng)分頁(yè)

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

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

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

1.2. 問(wèn)題原因

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

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

2、鎖競(jìng)爭(zhēng)問(wèn)題：

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

3、I/O瓶頸：

深分頁(yè)查詢會(huì)對(duì)I/O性能產(chǎn)生壓力，因?yàn)槊看尾樵兌夹枰x取大量的磁盤(pán)數(shù)據(jù)，尤其是在使用MySQL的磁盤(pán)存儲(chǔ)時(shí)，I/O操作會(huì)顯著影響性能。

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

2.1、索引介紹

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

1、B+樹(shù)索引的特點(diǎn)：

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

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

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

如下圖所示：

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

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

1、原理：

通過(guò)記錄上一頁(yè)的最后一個(gè)值（如主鍵或排序字段），作為下一頁(yè)的起點(diǎn)，避免offset。

2、適用場(chǎng)景：

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

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

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

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


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

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

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

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

5、缺點(diǎn)：

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

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

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

1、原理：

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

2、適用場(chǎng)景：

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

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

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

-- 2. 通過(guò)主鍵關(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)點(diǎn)：

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

5、缺點(diǎn)：

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

3. 覆蓋索引

1、原理：

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

2、適用場(chǎng)景：

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

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

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

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

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

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

5、缺點(diǎn)：

索引占用額外存儲(chǔ)空間。

4. 分區(qū)表

1、原理：

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

2、適用場(chǎng)景：

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

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

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

按時(shí)間范圍分區(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年的訂單，分頁(yè)
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 的用戶，分頁(yè)
SELECT * FROM users 
WHERE id > 1000000 
ORDER BY id 
LIMIT 20;

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

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

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

5、缺點(diǎn)：

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

5. 緩存機(jī)制

1、原理：

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

2、適用場(chǎng)景：

數(shù)據(jù)更新頻率低，分頁(yè)請(qǐng)求頻繁。

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

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

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

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

5、缺點(diǎn)：

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

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

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

如限制用戶最多查看前 100 頁(yè)。

2、滑動(dòng)窗口分頁(yè)：

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

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

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

性能對(duì)比

3、總結(jié)

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

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

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

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

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

最新評(píng)論