一、深度分頁

MySQL 深度分頁是指在分頁查詢數(shù)據(jù)量比較大的表時，需要訪問表中的某一段數(shù)據(jù)，而這段數(shù)據(jù)的位置非?？亢螅枰ㄟ^較大的 offset 來獲取目標(biāo)數(shù)據(jù)。

二、測試數(shù)據(jù)

-- 測試表
drop table if exists wide_table;
create table wide_table
(
    id          bigint auto_increment primary key,
    a           varchar(255),
    b           varchar(255),
    c           varchar(255),
    d           varchar(255),
    e           varchar(255),
    f           varchar(255),
    g           varchar(255),
    h           varchar(255),
    i           varchar(255),
    create_time datetime default current_timestamp
);

-- 插入十萬條記錄
delimiter //

drop procedure if exists insert_data;
create procedure insert_data()
begin
    declare i int default 1;
    while i <= 100000
        do
            insert into wide_table (a, b, c, d, e, f, g, h, i)
            values (i, i, i, i, i, i, i, i, i);
            set i = i + 1;
            if i % 10000 = 0 then
                select i;
            end if;
        end while;
end //

delimiter ;

call insert_data();

三、分頁策略

3.1 默認(rèn)分頁

默認(rèn)分頁即通過 limit #{offset}, #{pageSize} 或 limit #{pageSize} offset #{offset} 來進(jìn)行分頁。二者本質(zhì)上都是全表掃描，MySQL 會依次取出 pageSize 條記錄，然后判斷其是否在 offset 后，如果不在則舍棄，繼續(xù)過濾，所以效率低下。

test> select *
      from wide_table
      limit 80000, 100
[2024-01-18 14:55:42] 在 335 ms (execution: 298 ms, fetching: 37 ms) 內(nèi)檢索到從 1 開始的 100 行
test> select *
      from wide_table
      limit 100 offset 80000
[2024-01-18 14:55:43] 在 282 ms (execution: 233 ms, fetching: 49 ms) 內(nèi)檢索到從 1 開始的 100 行

explain
select *
from wide_table
limit 80000, 100;
# +--+-----------+----------+----------+----+-------------+----+-------+----+-----+--------+-----+
# |id|select_type|table     |partitions|type|possible_keys|key |key_len|ref |rows |filtered|Extra|
# +--+-----------+----------+----------+----+-------------+----+-------+----+-----+--------+-----+
# |1 |SIMPLE     |wide_table|null      |ALL |null         |null|null   |null|99551|100     |null |
# +--+-----------+----------+----------+----+-------------+----+-------+----+-----+--------+-----+

explain
select *
from wide_table
limit 100 offset 80000;
# +--+-----------+----------+----------+----+-------------+----+-------+----+-----+--------+-----+
# |id|select_type|table     |partitions|type|possible_keys|key |key_len|ref |rows |filtered|Extra|
# +--+-----------+----------+----------+----+-------------+----+-------+----+-----+--------+-----+
# |1 |SIMPLE     |wide_table|null      |ALL |null         |null|null   |null|99551|100     |null |
# +--+-----------+----------+----------+----+-------------+----+-------+----+-----+--------+-----+

3.2 索引分頁

索引分頁即通過索引字段（一般選取主鍵）的范圍查詢以及比較操作進(jìn)行分頁，通過應(yīng)用索引能夠大幅縮短查詢時間。

test> select *
      from wide_table
      where id between 80000 and 80100
[2024-01-18 15:02:27] 在 224 ms (execution: 184 ms, fetching: 40 ms) 內(nèi)檢索到從 1 開始的 101 行
test> select *
      from wide_table
      where id > 80000
      limit 100
[2024-01-18 14:58:34] 在 218 ms (execution: 185 ms, fetching: 33 ms) 內(nèi)檢索到從 1 開始的 100 行

explain
select *
from wide_table
where id between 80000 and 80100;
# +--+-----------+----------+----------+-----+-------------+-------+-------+----+----+--------+-----------+
# |id|select_type|table     |partitions|type |possible_keys|key    |key_len|ref |rows|filtered|Extra      |
# +--+-----------+----------+----------+-----+-------------+-------+-------+----+----+--------+-----------+
# |1 |SIMPLE     |wide_table|null      |range|PRIMARY      |PRIMARY|8      |null|101 |100     |Using where|
# +--+-----------+----------+----------+-----+-------------+-------+-------+----+----+--------+-----------+

explain
select *
from wide_table
where id > 80000
limit 100;
# +--+-----------+----------+----------+-----+-------------+-------+-------+----+-----+--------+-----------+
# |id|select_type|table     |partitions|type |possible_keys|key    |key_len|ref |rows |filtered|Extra      |
# +--+-----------+----------+----------+-----+-------------+-------+-------+----+-----+--------+-----------+
# |1 |SIMPLE     |wide_table|null      |range|PRIMARY      |PRIMARY|8      |null|39420|100     |Using where|
# +--+-----------+----------+----------+-----+-------------+-------+-------+----+-----+--------+-----------+

3.3 子查詢

索引分頁的局限性在于待查詢的表必須有一個自增長的主鍵，如果主鍵是 UUID 就無法進(jìn)行索引分頁了，此外在編碼時還需要考慮索引斷裂導(dǎo)致的重復(fù)查詢。

我們也可以通過子查詢來優(yōu)化分頁，即先通過默認(rèn)分頁操作找到限定的主鍵，這個操作能夠利用主鍵索引而且只考慮主鍵這一個字段，因此效率比較高。然后在根據(jù)這個主鍵進(jìn)行條件查詢，從而得到分頁數(shù)據(jù)。

test> select *
      from wide_table
      where id >= (select id from wide_table limit 80000, 1)
      limit 100
[2024-01-18 15:03:34] 在 236 ms (execution: 202 ms, fetching: 34 ms) 內(nèi)檢索到從 1 開始的 100 行

explain
select *
from wide_table
where id >= (select id from wide_table limit 80000, 1)
limit 100;
# +--+-----------+----------+----------+-----+-------------+-------+-------+----+-----+--------+-----------+
# |id|select_type|table     |partitions|type |possible_keys|key    |key_len|ref |rows |filtered|Extra      |
# +--+-----------+----------+----------+-----+-------------+-------+-------+----+-----+--------+-----------+
# |1 |PRIMARY    |wide_table|null      |range|PRIMARY      |PRIMARY|8      |null|39420|100     |Using where|
# |2 |SUBQUERY   |wide_table|null      |index|null         |PRIMARY|8      |null|99551|100     |Using index|
# +--+-----------+----------+----------+-----+-------------+-------+-------+----+-----+--------+-----------+

3.4 延遲關(guān)聯(lián)

延遲關(guān)聯(lián)主要通過減少回表次數(shù)來提高查詢效率。

分析下面的執(zhí)行計劃：

• 第三行執(zhí)行計劃 id 最大最先執(zhí)行，這是一個對 wide_table 表的索引掃描，只遍歷索引而無需回表（可以理解為索引覆蓋）。最終查詢到了從第 80001 條記錄開始的 100 條記錄的主鍵，并將這些主鍵保存在臨時表 wt 中。
• 第一行執(zhí)行計劃對 wt 進(jìn)行了全表掃描，獲取了主鍵的查詢結(jié)果集。
• 第二行執(zhí)行計劃在 wt 中這 100 條主鍵記錄的基礎(chǔ)上，通過等值連接（延遲關(guān)聯(lián)）回表獲取到了最終的分頁結(jié)果。

整個查詢過程中只有最終的 100 條記錄發(fā)生了回表，其余記錄都只查詢了 id 并被過濾掉了，提高了查詢的效率。文章來源地址http://www.zghlxwxcb.cn/news/detail-807462.html

test> select *
      from wide_table
      inner join (select id from wide_table limit 80000, 100) as wt
      on wide_table.id = wt.id
[2024-01-18 15:03:36] 在 241 ms (execution: 207 ms, fetching: 34 ms) 內(nèi)檢索到從 1 開始的 100 行

explain
select *
from wide_table
inner join (select id from wide_table limit 80000, 100) as wt
on wide_table.id = wt.id;
# +--+-----------+----------+----------+------+-------------+-------+-------+-----+-----+--------+-----------+
# |id|select_type|table     |partitions|type  |possible_keys|key    |key_len|ref  |rows |filtered|Extra      |
# +--+-----------+----------+----------+------+-------------+-------+-------+-----+-----+--------+-----------+
# |1 |PRIMARY    |<derived2>|null      |ALL   |null         |null   |null   |null |80100|100     |null       |
# |1 |PRIMARY    |wide_table|null      |eq_ref|PRIMARY      |PRIMARY|8      |wt.id|1    |100     |null       |
# |2 |DERIVED    |wide_table|null      |index |null         |PRIMARY|8      |null |99551|100     |Using index|
# +--+-----------+----------+----------+------+-------------+-------+-------+-----+-----+--------+-----------+

到了這里，關(guān)于MySQL 深度分頁的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！