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

分享是最有效的學習方式。

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

故事

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

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

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

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

limit分頁為什么會變慢？

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

做個小實驗

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

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='商品表'

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

當為淺分頁的時候，如下：

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

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

當調(diào)整分頁查詢?yōu)樯疃确猪撝�后，如下�?

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

此時深度分頁的查詢時間為：
"executeTimeMillis":27499

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

剖析一下原因

簡單回顧一下普通索引和聚簇索引

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

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

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

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

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

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

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

有了以上的知識基礎我們再來回過頭看一下上述深度分頁SQL的執(zhí)行過程。
上述的查詢語句中idx_timeCreated顯然是普通索引，咱們結(jié)合上述的知識儲備點，其深度分頁的執(zhí)行就可以拆分為如下步驟：

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

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

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

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

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

再深入一點從底層存儲來看，數(shù)據(jù)庫表中行數(shù)據(jù)、索引都是以文件的形式存儲到磁盤(硬盤)上的，而硬盤的速度相對來說要慢很多，存儲引擎運行sql語句時，需要訪問硬盤查詢文件，然后返回數(shù)據(jù)給服務層。當返回的數(shù)據(jù)越多時，訪問磁盤的次數(shù)就越多，就會越耗時。

替換limit分頁的一些方案。

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

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

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

子查詢法

思路：如果把查詢條件，轉(zhuǎn)移回到主鍵索引樹，那就不就可以減少回表次數(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;

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

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

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

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

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

這種優(yōu)化的方式其實和子查詢優(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

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

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

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

標簽記錄法（錨點記錄法）

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

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

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

顯然，這種方式非�？�，耗時如下：
"executeTimeMillis":1

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

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

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

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

存入到es中

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

寫到最后

那么半夜三更爬起來優(yōu)化慢查詢的小貓究竟有沒有解決問題呢？電腦前，小貓長吁了一口氣，解決了！
我們看下小貓的優(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)化方法解決了當前的問題。

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

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