背景

日常需求開(kāi)發(fā)過(guò)程中，相信大家對(duì)于limit一定不會(huì)陌生，但是使用limit時(shí)，當(dāng)偏移量（offset）非常大時(shí)，會(huì)發(fā)現(xiàn)查詢效率越來(lái)越慢。一開(kāi)始limit 2000時(shí)，可能200ms,就能查詢出需要的到數(shù)據(jù)，但是當(dāng)limit 4000 offset 100000時(shí)，會(huì)發(fā)現(xiàn)它的查詢效率已經(jīng)需要1S左右，那要是更大的時(shí)候呢，只會(huì)越來(lái)越慢。

概括

本文將會(huì)討論當(dāng)mysql表大數(shù)據(jù)量的情況，如何優(yōu)化深分頁(yè)問(wèn)題，并附上最近的優(yōu)化慢sql問(wèn)題的案例偽代碼。

1、limit深分頁(yè)問(wèn)題描述

先看看表結(jié)構(gòu)（隨便舉了個(gè)例子，表結(jié)構(gòu)不全，無(wú)用字段就不進(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 '開(kāi)始時(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è)我們要查詢的深分頁(yè)SQL長(zhǎng)這樣

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慢原因分析

讓我們來(lái)看看這條sql的執(zhí)行計(jì)劃

也走到了索引，那為什么還是慢呢？我們先來(lái)回顧一下mysql 的相關(guān)知識(shí)點(diǎn)。

聚簇索引和非聚簇索引

聚簇索引： 葉子節(jié)點(diǎn)儲(chǔ)存的是整行的數(shù)據(jù)。

非聚簇索引： 葉子節(jié)點(diǎn)儲(chǔ)存的是整行的數(shù)據(jù)對(duì)應(yīng)的主鍵值。

使用非聚簇索引查詢的流程

• 通過(guò)非聚簇索引樹(shù)，找到對(duì)應(yīng)的葉子節(jié)點(diǎn)，獲取到主鍵的值。
• 再通過(guò)取到主鍵的值，回到聚簇索引樹(shù)，找到對(duì)應(yīng)的整行數(shù)據(jù)。（整個(gè)過(guò)程稱為回表）

回到這條sql為什么慢的問(wèn)題上，原因如下

1、limit語(yǔ)句會(huì)先掃描offset+n行，然后再丟棄掉前offset行，返回后n行數(shù)據(jù)。也就是說(shuō)limit 100000,10，就會(huì)掃描100010行，而limit 0,10，只掃描10行。這里需要回表100010次，大量的時(shí)間都在回表這個(gè)上面。

方案核心思路： 能不能事先知道要從哪個(gè)主鍵ID開(kāi)始，減少回表的次數(shù)

常見(jiàn)解決方案

通過(guò)子查詢優(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條開(kāi)始的第2000條，查詢效率為200ms，是不是快了不少。

標(biāo)簽記錄法

標(biāo)簽記錄法： 其實(shí)標(biāo)記一下上次查詢到哪一條了，下次再來(lái)查的時(shí)候，從該條開(kāi)始往下掃描。類似書簽的作用

select * from p2p_detail_record ppdr
where ppdr.id > 'bb9d67ee6eac4cab9909bad7c98f54d4'
order by id 
limit 2000

備注：bb9d67ee6eac4cab9909bad7c98f54d4是上次查詢結(jié)果的最后一條ID

使用標(biāo)簽記錄法，性能都會(huì)不錯(cuò)的，因?yàn)槊辛?code>id索引。但是這種方式有幾個(gè)缺點(diǎn)。

• 1、只能連續(xù)頁(yè)查詢，不能跨頁(yè)查詢。
• 2、需要一種類似連續(xù)自增的字段（可以使用orber by id的方式）。

方案對(duì)比

• 使用通過(guò)子查詢優(yōu)化的方式

優(yōu)點(diǎn)： 可跨頁(yè)查詢，想查哪一頁(yè)的數(shù)據(jù)就查哪一頁(yè)的數(shù)據(jù)。

缺點(diǎn)： 效率不如標(biāo)簽記錄法。原因： 比如需要查10W條數(shù)據(jù)后，第1000條，也需要先查詢出非聚簇索引對(duì)應(yīng)的10W1000條數(shù)據(jù)，在取第10W開(kāi)始的ID，進(jìn)行查詢。

• 使用 標(biāo)簽記錄法 的方式

優(yōu)點(diǎn)： 查詢效率很穩(wěn)定，非常快。

缺點(diǎn)：

• 不跨頁(yè)查詢，
• 需要一種類似連續(xù)自增的字段

關(guān)于第二點(diǎn)的說(shuō)明： 該點(diǎn)一般都好解決，可使用任意不重復(fù)的字段進(jìn)行排序即可。若使用可能重復(fù)的字段進(jìn)行排序的字段，由于mysql對(duì)于相同值的字段排序是無(wú)序，導(dǎo)致如果正好在分頁(yè)時(shí)，上下頁(yè)中可能存在相同的數(shù)據(jù)。

實(shí)戰(zhàn)案例

需求： 需要查詢查詢某一時(shí)間段的數(shù)據(jù)量，假設(shè)有幾十萬(wàn)的數(shù)據(jù)量需要查詢出來(lái)，進(jìn)行某些操作。

需求分析 1、分批查詢（分頁(yè)查詢），設(shè)計(jì)深分頁(yè)問(wèn)題，導(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 '開(kāi)始時(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; 
//一頁(yè)的條數(shù) 
Integer pageSize = 2000; 
List<P2pRecordVo> list ;
do{   
   list = listP2pRecordByPage(lastId,pageSize);    //標(biāo)簽記錄法，記錄上次查詢過(guò)的Id 
   lastId = list.get(list.size()-1).getId();       //獲取上一次查詢數(shù)據(jù)最后的ID，用于記錄
   //對(duì)數(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ì)先對(duì)所有數(shù)據(jù)排序一遍，拿到最小ID，但是這樣對(duì)所有數(shù)據(jù)排序，然后去min（id),耗時(shí)也蠻長(zhǎng)的，其實(shí)第一次查詢，可不帶lastId進(jìn)行查詢，查詢結(jié)果也是一樣。速度更快。

總結(jié)

1、當(dāng)業(yè)務(wù)需要從表中查出大數(shù)據(jù)量時(shí)，而又項(xiàng)目架構(gòu)沒(méi)上ES時(shí)，可考慮使用標(biāo)簽記錄法的方式，對(duì)查詢效率進(jìn)行優(yōu)化。

2、從需求上也應(yīng)該盡可能避免，在大數(shù)據(jù)量的情況下，分頁(yè)查詢最后一頁(yè)的功能?；蛘呦拗瞥芍荒芤豁?yè)一頁(yè)往后劃的場(chǎng)景。

到此這篇關(guān)于快速解決mysql深分頁(yè)問(wèn)題的文章就介紹到這了,更多相關(guān)mysql深分頁(yè)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論