分享是最有效的學習方式。
部落格：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，時間整整縮短了十倍，在結合執行計劃觀察一下。
我們綜合上述的執行計劃可以看出，子查詢 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
咱們發現和子查詢的耗時其實差不多，該思路是先透過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的最佳化方法解決了當前的問題。
相信小夥伴們後面遇到這類問題也能搞定了。
我是老貓，資深研發老鳥，讓我們一起聊聊技術，聊聊職場，聊聊人生。