您的位置:首頁(yè) > 軟件教程 > 教程 > 半夜被慢查詢(xún)告警吵醒,limit深度分頁(yè)的坑

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

來(lái)源:好特整理 | 時(shí)間:2024-06-27 09:34:41 | 閱讀:145 |  標簽: T MIT   | 分享到:

分享是最有效的學(xué)習方式。 博客:https://blog.ktdaddy.com/ 故事 梅雨季,悶熱的夜,令人窒息,窗外一道道閃電劃破漆黑的夜幕,小貓塞著(zhù)耳機聽(tīng)著(zhù)恐怖小說(shuō),輾轉反側,終于睡意來(lái)了,然而挨千刀的手機早不振晚不振,偏偏這個(gè)時(shí)候振動(dòng)了一下,一個(gè)激靈,沒(méi)有按捺住對內容的好奇,點(diǎn)開(kāi)了短信,臥

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

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

故事

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

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

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

強迫癥小貓瞬間睡意全無(wú),翻起來(lái)打開(kāi)電腦開(kāi)始解決問(wèn)題。

那么為什么用使用limit之后會(huì )出現慢查詢(xún)呢?接下來(lái)老貓和大家一起來(lái)剖析一下吧。

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

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

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

做個(gè)小實(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ā)現timeCreated走普通索引。
接下來(lái)我們根據創(chuàng )建時(shí)間來(lái)執行一下分頁(yè)查詢(xún):

當為淺分頁(yè)的時(shí)候,如下:

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

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

當調整分頁(yè)查詢(xún)?yōu)樯疃确猪?yè)之后,如下:

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

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

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

剖析一下原因

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

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

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

(1)聚簇索引:InnoDB存儲表是索引組織表,聚簇索引就是一種索引組織形式,聚簇索引葉子節點(diǎn)存放表中所有行數據記錄的信息,所以經(jīng)常會(huì )說(shuō)索引即數據,數據即索引。當然這個(gè)是針對聚簇索引。

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

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

(2)普通索引:普通索引在葉子節點(diǎn)并不包含所有行的數據記錄,只是會(huì )在葉子節點(diǎn)存本身的鍵值和主鍵的值,在檢索數據的時(shí)候,通過(guò)普通索引子節點(diǎn)上的主鍵來(lái)獲取想要找到的行數據記錄。

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

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

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

有了以上的知識基礎我們再來(lái)回過(guò)頭看一下上述深度分頁(yè)SQL的執行過(guò)程。
上述的查詢(xún)語(yǔ)句中idx_timeCreated顯然是普通索引,咱們結合上述的知識儲備點(diǎn),其深度分頁(yè)的執行就可以拆分為如下步驟:

1、通過(guò)普通索引idx_timeCreated,過(guò)濾timeCreated,找到滿(mǎn)足條件的記錄ID;

2、通過(guò)ID,回到主鍵索引樹(shù),找到滿(mǎn)足記錄的行,然后取出展示的列(回表);

3、掃描滿(mǎn)足條件的10000010行,然后扔掉前10000000行,返回。

結合看一下執行計劃:

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

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

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

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

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

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

有哪些方式可以幫助我們減少無(wú)用回表次數呢?

子查詢(xún)法

思路:如果把查詢(xú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;

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

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

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

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

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

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

這種優(yōu)化的方式其實(shí)和子查詢(xún)優(yōu)化方法如出一轍,其本質(zhì)優(yōu)化思路和子查詢(xún)法一樣。
我們直接來(lái)看一下優(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

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

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

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

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

標簽記錄法(錨點(diǎn)記錄法)

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

這種方式就是標記一下上次查詢(xún)到哪一條了,下次再來(lái)查的時(shí)候,從該條開(kāi)始往下掃描。我們直接看一下SQL:

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

顯然,這種方式非?,耗時(shí)如下:
"executeTimeMillis":1

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

當然存在相同的缺陷,我們還可以換一種寫(xiě)法。

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

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

存入到es中

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

寫(xiě)到最后

那么半夜三更爬起來(lái)優(yōu)化慢查詢(xún)的小貓究竟有沒(méi)有解決問(wèn)題呢?電腦前,小貓長(chá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)化方法解決了當前的問(wèn)題。

相信小伙伴們后面遇到這類(lèi)問(wèn)題也能搞定了。

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

小編推薦閱讀

好特網(wǎng)發(fā)布此文僅為傳遞信息,不代表好特網(wǎng)認同期限觀(guān)點(diǎn)或證實(shí)其描述。

相關(guān)視頻攻略

更多

掃二維碼進(jìn)入好特網(wǎng)手機版本!

掃二維碼進(jìn)入好特網(wǎng)微信公眾號!

本站所有軟件,都由網(wǎng)友上傳,如有侵犯你的版權,請發(fā)郵件admin@haote.com

湘ICP備2022002427號-10 湘公網(wǎng)安備:43070202000427號© 2013~2024 haote.com 好特網(wǎng)