共計(jì) 3758 個(gè)字符，預(yù)計(jì)需要花費(fèi) 10 分鐘才能閱讀完成。
今天丸趣 TV 小編給大家分享一下如何解決 mysql 深分頁問題的相關(guān)知識點(diǎn)，內(nèi)容詳細(xì)，邏輯清晰，相信大部分人都還太了解這方面的知識，所以分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后有所收獲，下面我們一起來了解一下吧。
日常需求開發(fā)過程中，相信大家對于 limit 一定不會(huì)陌生，但是使用 limit 時(shí)，當(dāng)偏移量（offset）非常大時(shí)，會(huì)發(fā)現(xiàn)查詢效率越來越慢。一開始 limit 2000 時(shí)，可能 200ms, 就能查詢出需要的到數(shù)據(jù)，但是當(dāng) limit 4000 offset 100000 時(shí)，會(huì)發(fā)現(xiàn)它的查詢效率已經(jīng)需要 1S 左右，那要是更大的時(shí)候呢，只會(huì)越來越慢。
概括
本文將會(huì)討論當(dāng) mysql 表大數(shù)據(jù)量的情況，如何優(yōu)化深分頁問題，并附上最近的優(yōu)化慢 sql 問題的案例偽代碼。
1、limit 深分頁問題描述
先看看表結(jié)構(gòu)（隨便舉了個(gè)例子，表結(jié)構(gòu)不全，無用字段就不進(jìn)行展示了）
CREATE TABLE `p2p_detail_record` ( `id` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT COMMENT 主鍵 ,
`batch_num` int NOT NULL DEFAULT 0 COMMENT 上報(bào)數(shù)量 ,
`uptime` bigint NOT NULL DEFAULT 0 COMMENT 上報(bào)時(shí)間 ,
`uuid` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT COMMENT 會(huì)議 id ,
`start_time_stamp` bigint NOT NULL DEFAULT 0 COMMENT 開始時(shí)間 ,
`answer_time_stamp` bigint NOT NULL DEFAULT 0 COMMENT 應(yīng)答時(shí)間 ,
`end_time_stamp` bigint NOT NULL DEFAULT 0 COMMENT 結(jié)束時(shí)間 ,
`duration` int NOT NULL DEFAULT 0 COMMENT 持續(xù)時(shí)間 ,
PRIMARY KEY (`id`),
KEY `idx_uuid` (`uuid`),
KEY `idx_start_time_stamp` (`start_time_stamp`) // 索引,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT= p2p 通話記錄詳情表 假設(shè)我們要查詢的深分頁 SQL 長這樣
select *
from p2p_detail_record ppdr
where ppdr .start_time_stamp 1656666798000
limit 0,2000查詢效率是 94ms，是不是很快？那如果我們 limit 100000，2000 呢，查詢效率是 1.5S，已經(jīng)非常慢，那如果更多呢？
2、sql 慢原因分析
讓我們來看看這條 sql 的執(zhí)行計(jì)劃
也走到了索引，那為什么還是慢呢？我們先來回顧一下 mysql 的相關(guān)知識點(diǎn)。
聚簇索引和非聚簇索引
聚簇索引：葉子節(jié)點(diǎn)儲存的是整行的數(shù)據(jù)。
非聚簇索引：葉子節(jié)點(diǎn)儲存的是整行的數(shù)據(jù)對應(yīng)的主鍵值。
使用非聚簇索引查詢的流程
通過非聚簇索引樹，找到對應(yīng)的葉子節(jié)點(diǎn)，獲取到主鍵的值。
再通過取到主鍵的值，回到聚簇索引樹，找到對應(yīng)的整行數(shù)據(jù)。（整個(gè)過程稱為回表）
回到這條 sql 為什么慢的問題上，原因如下
1、limit 語句會(huì)先掃描 offset+ n 行，然后再丟棄掉前 offset 行，返回后 n 行數(shù)據(jù)。也就是說 limit 100000,10，就會(huì)掃描 100010 行，而 limit 0,10，只掃描 10 行。這里需要回表 100010 次，大量的時(shí)間都在回表這個(gè)上面。
方案核心思路：能不能事先知道要從哪個(gè)主鍵 ID 開始，減少回表的次數(shù)
常見解決方案通過子查詢優(yōu)化
select *
from p2p_detail_record ppdr
where id = (select id from p2p_detail_record ppdr2 where ppdr2 .start_time_stamp 1656666798000 limit 100000,1)
limit 2000相同的查詢結(jié)果，也是 10W 條開始的第 2000 條，查詢效率為 200ms，是不是快了不少。
標(biāo)簽記錄法
標(biāo)簽記錄法：其實(shí)標(biāo)記一下上次查詢到哪一條了，下次再來查的時(shí)候，從該條開始往下掃描。類似書簽的作用
select * from p2p_detail_record ppdr
where ppdr.id bb9d67ee6eac4cab9909bad7c98f54d4
order by id
limit 2000
備注：bb9d67ee6eac4cab9909bad7c98f54d4 是上次查詢結(jié)果的最后一條 ID使用標(biāo)簽記錄法，性能都會(huì)不錯(cuò)的，因?yàn)槊辛?id 索引。但是這種方式有幾個(gè)缺點(diǎn)。
1、只能連續(xù)頁查詢，不能跨頁查詢。
2、需要一種類似連續(xù)自增的字段（可以使用 orber by id 的方式）。
方案對比
使用通過子查詢優(yōu)化的方式
優(yōu)點(diǎn)：可跨頁查詢，想查哪一頁的數(shù)據(jù)就查哪一頁的數(shù)據(jù)。
缺點(diǎn)：效率不如標(biāo)簽記錄法。原因：比如需要查 10W 條數(shù)據(jù)后，第 1000 條，也需要先查詢出非聚簇索引對應(yīng)的 10W1000 條數(shù)據(jù)，在取第 10W 開始的 ID，進(jìn)行查詢。
使用 標(biāo)簽記錄法 的方式
優(yōu)點(diǎn)：查詢效率很穩(wěn)定，非常快。
缺點(diǎn)：
不跨頁查詢，
需要一種類似連續(xù)自增的字段
關(guān)于第二點(diǎn)的說明：該點(diǎn)一般都好解決，可使用任意不重復(fù)的字段進(jìn)行排序即可。若使用可能重復(fù)的字段進(jìn)行排序的字段，由于 mysql 對于相同值的字段排序是無序，導(dǎo)致如果正好在分頁時(shí)，上下頁中可能存在相同的數(shù)據(jù)。
實(shí)戰(zhàn)案例
需求：需要查詢查詢某一時(shí)間段的數(shù)據(jù)量，假設(shè)有幾十萬的數(shù)據(jù)量需要查詢出來，進(jìn)行某些操作。
需求分析 1、分批查詢（分頁查詢），設(shè)計(jì)深分頁問題，導(dǎo)致效率較慢。
CREATE TABLE `p2p_detail_record` ( `id` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT COMMENT 主鍵 ,
`batch_num` int NOT NULL DEFAULT 0 COMMENT 上報(bào)數(shù)量 ,
`uptime` bigint NOT NULL DEFAULT 0 COMMENT 上報(bào)時(shí)間 ,
`uuid` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT COMMENT 會(huì)議 id ,
`start_time_stamp` bigint NOT NULL DEFAULT 0 COMMENT 開始時(shí)間 ,
`answer_time_stamp` bigint NOT NULL DEFAULT 0 COMMENT 應(yīng)答時(shí)間 ,
`end_time_stamp` bigint NOT NULL DEFAULT 0 COMMENT 結(jié)束時(shí)間 ,
`duration` int NOT NULL DEFAULT 0 COMMENT 持續(xù)時(shí)間 ,
PRIMARY KEY (`id`),
KEY `idx_uuid` (`uuid`),
KEY `idx_start_time_stamp` (`start_time_stamp`) // 索引,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT= p2p 通話記錄詳情表 偽代碼實(shí)現(xiàn)：
// 最小 ID
String lastId = null;
// 一頁的條數(shù)
Integer pageSize = 2000;
List P2pRecordVo list ;
do{
list = listP2pRecordByPage(lastId,pageSize); // 標(biāo)簽記錄法，記錄上次查詢過的 Id
lastId = list.get(list.size()-1).getId(); // 獲取上一次查詢數(shù)據(jù)最后的 ID，用于記錄
// 對數(shù)據(jù)的操作邏輯
XXXXX();
}while(isNotEmpty(list));
select id = listP2pRecordByPage
select *
from p2p_detail_record ppdr where 1=1
if test = lastId != null
and ppdr.id #{lastId}
/if
order by id asc
limit #{pageSize}
/select這里有個(gè)小優(yōu)化點(diǎn): 可能有的人會(huì)先對所有數(shù)據(jù)排序一遍，拿到最小 ID，但是這樣對所有數(shù)據(jù)排序，然后去 min（id), 耗時(shí)也蠻長的，其實(shí)第一次查詢，可不帶 lastId 進(jìn)行查詢，查詢結(jié)果也是一樣。速度更快。
以上就是“如何解決 mysql 深分頁問題”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，丸趣 TV 小編每天都會(huì)為大家更新不同的知識，如果還想學(xué)習(xí)更多的知識，請關(guān)注丸趣 TV 行業(yè)資訊頻道。
