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

分享是最有效的學習方式。博客：https://blog.ktdaddy.com/ 故事梅雨季，悶熱的夜，令人窒息，窗外一道道閃電劃破漆黑的夜幕，小貓塞著耳機聽著恐怖小說，輾轉反側，終於睡意來了，然而挨千刀的手機早不振晚不振，偏偏這個時候振動了一下，一個激靈，沒有按捺住對內容的好奇，點開了簡訊，卧 ...

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

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

故事

梅雨季，悶熱的夜，令人窒息，窗外一道道閃電劃破漆黑的夜幕，小貓塞著耳機聽著恐怖小說，輾轉反側，終於睡意來了，然而挨千刀的手機早不振晚不振，偏偏這個時候振動了一下，一個激靈，沒有按捺住對內容的好奇，點開了簡訊，卧槽？告警信息，原來是負責的服務出現慢查詢了。小貓想起來，今天在下班之前上線了一個版本，由於新增了一個業務欄位，所以小貓寫了相關的刷數據的介面，在下班之前調用開始刷歷史數據。

考慮到表的數據量比較大，一次性把數據全部讀取出來然後在記憶體裡面去刷新數據肯定是不現實的，所以小貓採用了分頁查詢的方式依次根據條件查詢出結果，然後進行表數據的重置。沒想到的是，數據量太大，分頁的深度越來越深，漸漸地，慢查詢也就暴露出來了。

慢查詢告警

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

那麼為什麼用使用limit之後會出現慢查詢呢？接下來老貓和大家一起來剖析一下吧。

剖析流程

limit分頁為什麼會變慢？

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

做個小實驗

假設我們有一張這樣的業務表，商品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='商品表'

從上述建表語句中我們發現timeCreated走普通索引。
接下來我們根據創建時間來執行一下分頁查詢：

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

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

此時執行的時間為：
"executeTimeMillis":1

當調整分頁查詢為深度分頁之後，如下：

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

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

此時看到這裡，小貓的場景已經重現了，此時深度分頁的查詢已經非常耗時。

剖析一下原因

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

我們來回顧一下普通索引和聚簇索引（也有人叫做聚集索引）的關係。

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

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

聚簇索引

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

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

普通索引

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

看一下實際深度分頁執行過程

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

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

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

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

結合看一下執行計劃：

執行計劃

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

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

替換limit分頁的一些方案。

上述我們其實已經搞清楚深度分頁慢的原因了，總結為“無用回表次數過多”。

那怎麼優化呢？相信大家應該都已經知道了，其核心當然是減少無用回表次數了。

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

子查詢法

思路：如果把查詢條件，轉移回到主鍵索引樹，那就不就可以減少回表次數了。
所以，咱們將實際的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;

測試一下執行時間：
"executeTimeMillis":2534

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

執行計劃2

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

顯然這種優化方式是有效的。

使用inner join方式進行優化

這種優化的方式其實和子查詢優化方法如出一轍，其本質優化思路和子查詢法一樣。
我們直接來看一下優化之後的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

測試一下執行的時間：
"executeTimeMillis":2495

執行計劃3

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

上面兩種方式其核心優化思想都是減少回表次數進行優化處理。

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

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

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

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

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

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

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

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

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

存入到es中

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

寫到最後

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

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的優化方法解決了當前的問題。

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

我是老貓，資深研發老鳥，讓我們一起聊聊技術，聊聊職場，聊聊人生。

我是老貓，10year+資深研發，讓我們一起聊聊技術，聊聊職場，聊聊人生～更多精彩，歡迎關註公眾號“程式員老貓”。個人博客：https://blog.ktdaddy.com/