半夜被慢查詢告警吵醒，limit深度分頁(yè)的坑

分享是最有效的學(xué)習(xí)方式。

博客： https://blog.ktdaddy.com/

故事

梅雨季，悶熱的夜，令人窒息，窗外一道道閃電劃破漆黑的夜幕，小貓塞著耳機(jī)聽著恐怖小說，輾轉(zhuǎn)反側(cè)，終于睡意來了，然而挨千刀的手機(jī)早不振晚不振，偏偏這個(gè)時(shí)候振動(dòng)了一下，一個(gè)激靈，沒有按捺住對(duì)內(nèi)容的好奇，點(diǎn)開了短信，臥槽？告警信息，原來是負(fù)責(zé)的服務(wù)出現(xiàn)慢查詢了。小貓想起來，今天在下班之前上線了一個(gè)版本，由于新增了一個(gè)業(yè)務(wù)字段，所以小貓寫了相關(guān)的刷數(shù)據(jù)的接口，在下班之前調(diào)用開始刷歷史數(shù)據(jù)。

考慮到表的數(shù)據(jù)量比較大，一次性把數(shù)據(jù)全部讀取出來然后在內(nèi)存里面去刷新數(shù)據(jù)肯定是不現(xiàn)實(shí)的，所以小貓采用了分頁(yè)查詢的方式依次根據(jù)條件查詢出結(jié)果，然后進(jìn)行表數(shù)據(jù)的重置。沒想到的是，數(shù)據(jù)量太大，分頁(yè)的深度越來越深，漸漸地，慢查詢也就暴露出來了。

強(qiáng)迫癥小貓瞬間睡意全無，翻起來打開電腦開始解決問題。

那么為什么用使用limit之后會(huì)出現(xiàn)慢查詢呢？接下來老貓和大家一起來剖析一下吧。

limit分頁(yè)為什么會(huì)變慢？

在解釋為什么慢之前，咱們來重現(xiàn)一下小貓的慢查詢場(chǎng)景。咱們從實(shí)際的例子推進(jìn)。

做個(gè)小實(shí)驗(yàn)

假設(shè)我們有一張這樣的業(yè)務(wù)表，商品Product表。具體的建表語(yǔ)句如下：

CREATE TABLE `Product` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `type` tinyint(3) unsigned NOT NULL DEFAULT '1' ,
  `spuCode` varchar(50) NOT NULL DEFAULT '' ,
  `spuName` varchar(100) NOT NULL DEFAULT '' ,
  `spuTitle` varchar(300) NOT NULL DEFAULT '' ,
  `channelId` bigint(20) unsigned NOT NULL DEFAULT '0',
  `sellerId` bigint(20) unsigned NOT NULL DEFAULT '0'
  `mallSpuCode` varchar(32) NOT NULL DEFAULT '',
  `originCategoryId` bigint(20) unsigned NOT NULL DEFAULT '0' ,
  `originCategoryName` varchar(50) NOT NULL DEFAULT '' ,
  `marketPrice` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  `status` tinyint(3) unsigned NOT NULL DEFAULT '1' ,
  `isDeleted` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `timeCreated` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `timeModified` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) ,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_spuCode` (`spuCode`,`channelId`,`sellerId`),
  KEY `idx_timeCreated` (`timeCreated`),
  KEY `idx_spuName` (`spuName`),
  KEY `idx_channelId_originCategory` (`channelId`,`originCategoryId`,`originCategoryName`) USING BTREE,
  KEY `idx_sellerId` (`sellerId`)
) ENGINE=InnoDB AUTO_INCREMENT=12553120 DEFAULT CHARSET=utf8mb4 COMMENT='商品表'

從上述建表語(yǔ)句中我們發(fā)現(xiàn)timeCreated走普通索引。
接下來我們根據(jù)創(chuàng)建時(shí)間來執(zhí)行一下分頁(yè)查詢：

當(dāng)為淺分頁(yè)的時(shí)候，如下：

select * from Product where timeCreated > "2020-09-12 13:34:20" limit 0,10

此時(shí)執(zhí)行的時(shí)間為：
"executeTimeMillis":1

當(dāng)調(diào)整分頁(yè)查詢?yōu)樯疃确猪?yè)之后，如下：

select * from Product where timeCreated > "2020-09-12 13:34:20" limit 10000000,10

此時(shí)深度分頁(yè)的查詢時(shí)間為：
"executeTimeMillis":27499

此時(shí)看到這里，小貓的場(chǎng)景已經(jīng)重現(xiàn)了，此時(shí)深度分頁(yè)的查詢已經(jīng)非常耗時(shí)。

剖析一下原因

簡(jiǎn)單回顧一下普通索引和聚簇索引

我們來回顧一下普通索引和聚簇索引（也有人叫做聚集索引）的關(guān)系。

大家可能都知道Mysql底層用的數(shù)據(jù)結(jié)構(gòu)是B+tree（如果有不知道的伙伴可以自己了解一下為什么mysql底層是B+tree）,B+tree索引其實(shí)可以分為兩大類，一類是聚簇索引，另外一類是非聚集索引（即普通索引）。

（1）聚簇索引：InnoDB存儲(chǔ)表是索引組織表，聚簇索引就是一種索引組織形式，聚簇索引葉子節(jié)點(diǎn)存放表中所有行數(shù)據(jù)記錄的信息，所以經(jīng)常會(huì)說索引即數(shù)據(jù)，數(shù)據(jù)即索引。當(dāng)然這個(gè)是針對(duì)聚簇索引。

由圖可知在執(zhí)行查詢的時(shí)候，從根節(jié)點(diǎn)開始共經(jīng)歷了3次查詢即可找到真實(shí)數(shù)據(jù)。倘若沒有聚簇索引的話，就需要在磁盤上進(jìn)行逐個(gè)掃描，直至找到數(shù)據(jù)為止。顯然，索引會(huì)加快查詢速度，但是在寫入數(shù)據(jù)的時(shí)候，由于需要維護(hù)這顆B+樹，因此在寫入過程中性能也會(huì)下降。

（2）普通索引：普通索引在葉子節(jié)點(diǎn)并不包含所有行的數(shù)據(jù)記錄，只是會(huì)在葉子節(jié)點(diǎn)存本身的鍵值和主鍵的值，在檢索數(shù)據(jù)的時(shí)候，通過普通索引子節(jié)點(diǎn)上的主鍵來獲取想要找到的行數(shù)據(jù)記錄。

由圖可知流程，首先從非聚簇索引開始尋找聚簇索引，找到非聚簇索引上的聚簇索引后，就會(huì)到聚簇索引的B+樹上進(jìn)行查詢，通過聚簇索引B+樹找到完整的數(shù)據(jù)。該過程比較專業(yè)的叫法也被稱為“回表”。

看一下實(shí)際深度分頁(yè)執(zhí)行過程

有了以上的知識(shí)基礎(chǔ)我們?cè)賮砘剡^頭看一下上述深度分頁(yè)SQL的執(zhí)行過程。
上述的查詢語(yǔ)句中idx_timeCreated顯然是普通索引，咱們結(jié)合上述的知識(shí)儲(chǔ)備點(diǎn)，其深度分頁(yè)的執(zhí)行就可以拆分為如下步驟：

1、通過普通索引idx_timeCreated，過濾timeCreated，找到滿足條件的記錄ID；

2、通過ID，回到主鍵索引樹，找到滿足記錄的行，然后取出展示的列（回表）；

3、掃描滿足條件的10000010行，然后扔掉前10000000行，返回。

結(jié)合看一下執(zhí)行計(jì)劃：

原因其實(shí)很清晰了：
顯然，導(dǎo)致這句SQL速度慢的問題出現(xiàn)在第2步。其中發(fā)生了10000010次回表，這前面的10000000條數(shù)據(jù)完全對(duì)本次查詢沒有意義，但是卻占據(jù)了絕大部分的查詢時(shí)間。

再深入一點(diǎn)從底層存儲(chǔ)來看，數(shù)據(jù)庫(kù)表中行數(shù)據(jù)、索引都是以文件的形式存儲(chǔ)到磁盤(硬盤)上的，而硬盤的速度相對(duì)來說要慢很多，存儲(chǔ)引擎運(yùn)行sql語(yǔ)句時(shí)，需要訪問硬盤查詢文件，然后返回?cái)?shù)據(jù)給服務(wù)層。當(dāng)返回的數(shù)據(jù)越多時(shí)，訪問磁盤的次數(shù)就越多，就會(huì)越耗時(shí)。

替換limit分頁(yè)的一些方案。

上述我們其實(shí)已經(jīng)搞清楚深度分頁(yè)慢的原因了，總結(jié)為“無用回表次數(shù)過多”。

那怎么優(yōu)化呢？相信大家應(yīng)該都已經(jīng)知道了，其核心當(dāng)然是減少無用回表次數(shù)了。

有哪些方式可以幫助我們減少無用回表次數(shù)呢？

子查詢法

思路：如果把查詢條件，轉(zhuǎn)移回到主鍵索引樹，那就不就可以減少回表次數(shù)了。
所以，咱們將實(shí)際的SQL改成下面這種形式：

select * FROM Product where id >= (select p.id from Product p where p.timeCreated > "2020-09-12 13:34:20" limit 10000000, 1) LIMIT 10;

測(cè)試一下執(zhí)行時(shí)間：
"executeTimeMillis":2534

我們可以明顯地看到相比之前的27499，時(shí)間整整縮短了十倍，在結(jié)合執(zhí)行計(jì)劃觀察一下。

我們綜合上述的執(zhí)行計(jì)劃可以看出，子查詢 table p查詢是用到了idx_timeCreated索引。首先在索引上拿到了聚集索引的主鍵ID,省去了回表操作，然后第二查詢直接根據(jù)第一個(gè)查詢的 ID往后再去查10個(gè)就可以了!

顯然這種優(yōu)化方式是有效的。

使用inner join方式進(jìn)行優(yōu)化

這種優(yōu)化的方式其實(shí)和子查詢優(yōu)化方法如出一轍，其本質(zhì)優(yōu)化思路和子查詢法一樣。
我們直接來看一下優(yōu)化之后的SQL：

select * from Product p1 inner join (select p.id from Product p where p.timeCreated > "2020-09-12 13:34:20" limit 10000000,10) as p2 on p1.id = p2.id

測(cè)試一下執(zhí)行的時(shí)間：
"executeTimeMillis":2495

咱們發(fā)現(xiàn)和子查詢的耗時(shí)其實(shí)差不多，該思路是先通過idx_timeCreated二級(jí)索引樹查詢到滿足條件的主鍵ID，再與原表通過主鍵ID內(nèi)連接，這樣后面直接走了主鍵索引了，同時(shí)也減少了回表。

上面兩種方式其核心優(yōu)化思想都是減少回表次數(shù)進(jìn)行優(yōu)化處理。

標(biāo)簽記錄法（錨點(diǎn)記錄法）

我們?cè)賮砜聪乱环N優(yōu)化思路，上述深度分頁(yè)慢原因我們也清楚了，一次性查詢的數(shù)據(jù)太多也是問題，所以我們從這個(gè)點(diǎn)出發(fā)去優(yōu)化，每次查詢少量的數(shù)據(jù)。那么我們可以采用下面那種錨點(diǎn)記錄的方式。類似船開到一個(gè)地方短暫停泊之后繼續(xù)行駛，那么那個(gè)停泊的地方就是拋錨的地方，老貓喜歡用錨點(diǎn)標(biāo)記來做比方，當(dāng)然看到網(wǎng)上有其他的小伙伴稱這種方式為標(biāo)簽記錄法。其實(shí)意思也都差不多。

這種方式就是標(biāo)記一下上次查詢到哪一條了，下次再來查的時(shí)候，從該條開始往下掃描。我們直接看一下SQL：

select * from Product p where p.timeCreated > "2020-09-12 13:34:20" and id>10000000 limit 10

顯然，這種方式非常快，耗時(shí)如下：
"executeTimeMillis":1

但是這種方式顯然是有缺陷的，大家想想如果我們的id不是連續(xù)的，或者說不是自增形式的，那么我們得到的數(shù)據(jù)就一定是不準(zhǔn)確的。與此同時(shí)咱們也不能跳頁(yè)查看，只能前后翻頁(yè)。

當(dāng)然存在相同的缺陷，我們還可以換一種寫法。

select * from Product p where p.timeCreated > "2020-09-12 13:34:20" and id between 10000000 and 10000010  

這種方式也是一樣存在上述缺陷，另外的話更要注意的是between ...and語(yǔ)法是兩頭都是閉區(qū)域間。上述語(yǔ)句如果ID連續(xù)不斷地情況下，咱們最終得到的其實(shí)是11條數(shù)據(jù)，并不是10條數(shù)據(jù)，所以這個(gè)地方還是需要注意的。

存入到es中

上述羅列的幾種分頁(yè)優(yōu)化的方法其實(shí)已經(jīng)夠用了，那么如果數(shù)據(jù)量再大點(diǎn)的話咋整，那么我們可能就要選擇其他中間件進(jìn)行查詢了，當(dāng)然我們可以選擇es。那么es真的就是萬能藥嗎？顯然不是。ES中同樣存在深度分頁(yè)的問題，那么針對(duì)es的深度分頁(yè)，那么又是另外一個(gè)故事了，這里咱們就不展開了。

寫到最后

那么半夜三更爬起來優(yōu)化慢查詢的小貓究竟有沒有解決問題呢？電腦前，小貓長(zhǎng)吁了一口氣，解決了！
我們看下小貓的優(yōu)化方式：

select * from InventorySku isk inner join (select id from InventorySku where inventoryId = 6058 limit 109500,500 ) as d on isk.id = d.id

顯然小貓采用了inner join的優(yōu)化方法解決了當(dāng)前的問題。

相信小伙伴們后面遇到這類問題也能搞定了。

我是老貓，資深研發(fā)老鳥，讓我們一起聊聊技術(shù)，聊聊職場(chǎng)，聊聊人生。