快速解決mysql深分頁問題
背景
日常需求開發(fā)過程中，相信大家對于limit一定不會陌生，但是使用limit時，當偏移量（offset）非常大時，會發(fā)現(xiàn)查詢效率越來越慢。一開始limit 2000時，可能200ms,就能查詢出需要的到數(shù)據(jù)，但是當limit 4000 offset 100000時，會發(fā)現(xiàn)它的查詢效率已經(jīng)需要1S左右，那要是更大的時候呢，只會越來越慢。
概括
本文將會討論當mysql表大數(shù)據(jù)量的情況，如何優(yōu)化深分頁問題，并附上最近的優(yōu)化慢sql問題的案例偽代碼。
1、limit深分頁問題描述
先看看表結(jié)構(gòu)（隨便舉了個例子，表結(jié)構(gòu)不全，無用字段就不進行展示了）
CREATE TABLE `p2p_detail_record` ( `id` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '主鍵', `batch_num` int NOT NULL DEFAULT '0' COMMENT '上報數(shù)量', `uptime` bigint NOT NULL DEFAULT '0' COMMENT '上報時間', `uuid` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '會議id', `start_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '開始時間', `answer_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '應答時間', `end_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '結(jié)束時間', `duration` int NOT NULL DEFAULT '0' COMMENT '持續(xù)時間', 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通話記錄詳情表';
假設我們要查詢的深分頁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í)行計劃
也走到了索引，那為什么還是慢呢？我們先來回顧一下mysql 的相關知識點。
聚簇索引和非聚簇索引
聚簇索引： 葉子節(jié)點儲存的是整行的數(shù)據(jù)。
非聚簇索引： 葉子節(jié)點儲存的是整行的數(shù)據(jù)對應的主鍵值。
使用非聚簇索引查詢的流程
- 通過非聚簇索引樹，找到對應的葉子節(jié)點，獲取到主鍵的值。
- 再通過取到主鍵的值，回到聚簇索引樹，找到對應的整行數(shù)據(jù)。（整個過程稱為回表）
回到這條sql為什么慢的問題上，原因如下
1、limit語句會先掃描offset+n行，然后再丟棄掉前offset行，返回后n行數(shù)據(jù)。也就是說limit 100000,10，就會掃描100010行，而limit 0,10，只掃描10行。這里需要回表100010次，大量的時間都在回表這個上面。
方案核心思路： 能不能事先知道要從哪個主鍵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，是不是快了不少。
標簽記錄法
標簽記錄法： 其實標記一下上次查詢到哪一條了，下次再來查的時候，從該條開始往下掃描。類似書簽的作用
select * from p2p_detail_record ppdr where ppdr.id > 'bb9d67ee6eac4cab9909bad7c98f54d4' order by id limit 2000 備注：bb9d67ee6eac4cab9909bad7c98f54d4是上次查詢結(jié)果的最后一條ID
使用標簽記錄法，性能都會不錯的，因為命中了id索引。但是這種方式有幾個缺點。
- 1、只能連續(xù)頁查詢，不能跨頁查詢。
- 2、需要一種類似連續(xù)自增的字段（可以使用orber by id的方式）。
方案對比
- 使用通過子查詢優(yōu)化的方式
優(yōu)點： 可跨頁查詢，想查哪一頁的數(shù)據(jù)就查哪一頁的數(shù)據(jù)。
缺點： 效率不如標簽記錄法。原因： 比如需要查10W條數(shù)據(jù)后，第1000條，也需要先查詢出非聚簇索引對應的10W1000條數(shù)據(jù)，在取第10W開始的ID，進行查詢。
- 使用 標簽記錄法 的方式
優(yōu)點： 查詢效率很穩(wěn)定，非?？?。
缺點：
- 不跨頁查詢，
- 需要一種類似連續(xù)自增的字段
關于第二點的說明： 該點一般都好解決，可使用任意不重復的字段進行排序即可。若使用可能重復的字段進行排序的字段，由于mysql對于相同值的字段排序是無序，導致如果正好在分頁時，上下頁中可能存在相同的數(shù)據(jù)。
實戰(zhàn)案例
需求： 需要查詢查詢某一時間段的數(shù)據(jù)量，假設有幾十萬的數(shù)據(jù)量需要查詢出來，進行某些操作。
需求分析 1、分批查詢（分頁查詢），設計深分頁問題，導致效率較慢。
CREATE TABLE `p2p_detail_record` ( `id` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '主鍵', `batch_num` int NOT NULL DEFAULT '0' COMMENT '上報數(shù)量', `uptime` bigint NOT NULL DEFAULT '0' COMMENT '上報時間', `uuid` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '會議id', `start_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '開始時間', `answer_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '應答時間', `end_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '結(jié)束時間', `duration` int NOT NULL DEFAULT '0' COMMENT '持續(xù)時間', 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通話記錄詳情表';
偽代碼實現(xiàn)：
//最小ID
String lastId = null;
//一頁的條數(shù)
Integer pageSize = 2000;
List<P2pRecordVo> list ;
do{
list = listP2pRecordByPage(lastId,pageSize); //標簽記錄法，記錄上次查詢過的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>這里有個小優(yōu)化點: 可能有的人會先對所有數(shù)據(jù)排序一遍，拿到最小ID，但是這樣對所有數(shù)據(jù)排序，然后去min（id),耗時也蠻長的，其實第一次查詢，可不帶lastId進行查詢，查詢結(jié)果也是一樣。速度更快。
總結(jié)
1、當業(yè)務需要從表中查出大數(shù)據(jù)量時，而又項目架構(gòu)沒上ES時，可考慮使用標簽記錄法的方式，對查詢效率進行優(yōu)化。
2、從需求上也應該盡可能避免，在大數(shù)據(jù)量的情況下，分頁查詢最后一頁的功能。或者限制成只能一頁一頁往后劃的場景。
到此這篇關于快速解決mysql深分頁問題的文章就介紹到這了,更多相關mysql深分頁內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要為大家分享了RPM方式安裝MySQL5.6源碼,具有一定的參考價值，感興趣的小伙伴們可以參考一下2016-10-10
MySQL5.1主從同步出現(xiàn)Relay log read failure錯誤解決方法
這篇文章主要介紹了MySQL5.1主從同步出現(xiàn)Relay log read failure錯誤解決方法,需要的朋友可以參考下2014-07-07
這篇文章主要給大家介紹了關于Mysql悲觀鎖和樂觀鎖使用的相關資料，文中通過示例代碼介紹的非常詳細，對大家學習或者使用Mysql具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧2019-11-11
這篇文章主要介紹了mysql如何判斷同一字段是否有重復數(shù)據(jù)問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-05-05
innodb 庫的備份注意點（由phpmyadmin引起的解決方案）
新版本xampps 1.8.5 X64 集成了phpmyadmin最新版本(4.1.0), 可每次初始化安裝程序后, 進入phpmyadmin管理工具, 就會發(fā)現(xiàn)phpmyadmin高級功能失效, 一直在提示數(shù)據(jù)表不存在, 經(jīng)過再三的分析, 終于找到原因2013-12-12
MySQL5.7 如何通過邏輯備份遷移到GreatSQL及注意事項
在將數(shù)據(jù)庫從MySQL 5.7遷移到GreatSQL8.0.32時,由于數(shù)據(jù)量較小且關注安全性,決定使用mysqldump執(zhí)行邏輯備份,并將數(shù)據(jù)導入GreatSQL,這篇文章主要介紹了MySQL5.7 通過邏輯備份遷移到GreatSQL注意事項,需要的朋友可以參考下2024-06-06
在我們使用查詢語句的時候，經(jīng)常要返回前幾條或者中間某幾行數(shù)據(jù)，這個時候怎么辦呢，mysql已經(jīng)為我們提供了這樣一個功能，盡管語法邏輯很是怪異，這個功能就好比oracle里的rownum，但比mysql提供的這個LIMIT好用、好記和好理解多了，具體內(nèi)容一起通過本文看看吧2017-10-10
這篇文章主要介紹了MySQL 行轉(zhuǎn)列詳情，MySQL 行轉(zhuǎn)列語句不難，具體的詳細資料，感興趣的小伙伴可以參考一下2022-01-01
MySQL Packet for query is too large 問題及解決方法
這篇文章主要介紹了MySQL Packet for query is too large 問題及解決方法,需要的朋友可以參考下2018-05-05
MySQL數(shù)據(jù)庫主從同步實戰(zhàn)過程詳解
這篇文章主要介紹了MySQL數(shù)據(jù)庫主從同步,結(jié)合實例形式詳細分析了MySQL數(shù)據(jù)庫主從同步基本配置方法與操作注意事項,需要的朋友可以參考下2020-05-05