一.背景

二.MySQL是如何評(píng)估成本的？

三.即使加了索引，也沒(méi)有起作用

四.內(nèi)存碎片也是一個(gè)值得關(guān)注的問(wèn)題

五.前綴索引的坑

六.索引合并

七.有時(shí)候SQL沒(méi)啥問(wèn)題，但還是報(bào)了慢查詢(xún)？

八.總結(jié)

一.背景

今年初團(tuán)隊(duì)開(kāi)始推行“服務(wù)穩(wěn)定性問(wèn)題治理專(zhuān)項(xiàng)”。通過(guò)錯(cuò)誤日志、慢SQL、接口性能等各項(xiàng)指標(biāo)的優(yōu)化，進(jìn)一步提升系統(tǒng)穩(wěn)定性與可靠性。在此契機(jī)之下，本文將從“慢SQL治理”的角度，通過(guò)部分實(shí)際案例，分析其原理，做一些階段性總結(jié)和思考。

二.MySQL是如何評(píng)估成本的？

某日線(xiàn)上突發(fā)告警，發(fā)現(xiàn)有一條慢SQL

select * from xxxx_supplier_extra 
where column_key = 'xxxx'

作為有“臨床經(jīng)驗(yàn)”的老司機(jī)，第一直覺(jué)是先用explain分析。explain用了毫秒級(jí)的時(shí)間就輸出了這樣一份執(zhí)行計(jì)劃，果然它很快，兩分鐘都不到。

圖片

通過(guò)“key=null”，我們不難發(fā)現(xiàn)，這條SQL走了全表掃描。這是一條簡(jiǎn)單的SQL案例，我們借用來(lái)分析MYSQL背后的成本評(píng)估原理。

執(zhí)行SQL前，優(yōu)化器會(huì)先選擇它認(rèn)為成本最低的方案。正如同有電梯坐電梯，沒(méi)電梯爬樓梯。

圖片

由于沒(méi)有可供選擇的索引，執(zhí)行器選了全表掃描。查詢(xún)成本一般由IO成本和CPU成本組成。眾所周知，表數(shù)據(jù)是存儲(chǔ)在磁盤(pán)的，每次讀磁盤(pán)上的數(shù)據(jù)都 產(chǎn)生IO，受制于磁盤(pán)的物理屬性，IO往往占查詢(xún)成本的大頭。舉個(gè)例子，key_name="input_time"這行記錄存儲(chǔ)在磁盤(pán)頁(yè)A中，讀完這行后，又讀了頁(yè)B中的數(shù)據(jù)key_name="input_state"。此時(shí)多了一次IO，成本就有多一些。

因?yàn)槿頀呙枋前l(fā)生在聚簇索引上，首先會(huì)估算整個(gè)聚簇索引占用的頁(yè)面數(shù)，以及表的記錄數(shù)，再計(jì)算IO成本和掃描成本（可以理解為CPU成本）。

計(jì)算口徑如下：

IO成本：頁(yè)面數(shù) x io_block_read_cost（IO成本數(shù) 默認(rèn)0.25） + 1.0
CPU成本：記錄數(shù) x cpu_tuple_cost （掃描成本數(shù) 默認(rèn)0.1）
總成本 = IO成本 + CPU成本

MySQL的IO成本默認(rèn)基于隨機(jī)IO計(jì)算（`io_block_read_cost=1.0`），而非順序IO。這里因?yàn)槿頀呙枋琼樞騃O，io_block_read_cost的默認(rèn)值則為0.25。表記錄越多，占用的頁(yè)數(shù)則隨著增長(zhǎng)，其查詢(xún)成本也就不斷累加。

三.即使加了索引，也沒(méi)有起作用

看到慢 SQL 直接加索引就好嗎？基于前面全表掃描的原理，是否看到慢 SQL 直接加索引就完事了？

以前面SQL為例，當(dāng)我們?yōu)椤甤olumn_key’字段加索引后，測(cè)試環(huán)境 explain 分析能命中索引，但上線(xiàn)后還是咔咔咔出現(xiàn)慢查詢(xún)。

這是因?yàn)槿绻饕侄蔚膮^(qū)分度不夠，優(yōu)化器會(huì)認(rèn)為查找成本過(guò)大，此時(shí)還是選擇走全表掃描。而測(cè)試環(huán)境表記錄較少的情況下，優(yōu)化器覺(jué)得回表開(kāi)銷(xiāo)不大，就能命中索引，這也解釋了為什么兩者的執(zhí)行計(jì)劃不同。

索引能否命中往往與查詢(xún)條件以及數(shù)據(jù)分布有關(guān)。

如何在索引設(shè)計(jì)之初就規(guī)避此類(lèi)問(wèn)題？

網(wǎng)上一些文章會(huì)提到基于該列的業(yè)務(wù)屬性來(lái)區(qū)分，例如性別字段只有兩個(gè)值：“sex=男，sex=女”。由于大部分記錄都擁有相同值，數(shù)據(jù)區(qū)分度不大，所以容易成為低效索引。

除此之外，可以使用該語(yǔ)句計(jì)算區(qū)分度：

SELECT COUNT(DISTINCT column_name ) 
/ COUNT(*)

區(qū)分度低于10%的字段避免單獨(dú)建索引。對(duì)于聯(lián)合索引而言，也應(yīng)盡量將區(qū)分度高的字段放在前面。

值得注意的是，即使該字段的區(qū)分度能夠建立索引。也要根據(jù)已有索引和查詢(xún)場(chǎng)景做綜合取舍，要避免在同一個(gè)表上堆砌過(guò)多索引。

四.內(nèi)存碎片也是一個(gè)值得關(guān)注的問(wèn)題

內(nèi)存碎片對(duì)優(yōu)化器的影響

上文提到了成本估算是基于頁(yè)數(shù)以及記錄數(shù)計(jì)算的，這些數(shù)據(jù)來(lái)源于庫(kù)中的統(tǒng)計(jì)信息。當(dāng)內(nèi)存碎片過(guò)大時(shí)，如果出現(xiàn)庫(kù)表的統(tǒng)計(jì)信息未及時(shí)更新，也會(huì)因?yàn)閮?yōu)化器評(píng)估的結(jié)果與實(shí)際差距太大，從而影響實(shí)際執(zhí)行效果。

內(nèi)存碎片導(dǎo)致慢查詢(xún)

舉個(gè)例子：某日xxxx_price表產(chǎn)生慢查詢(xún)告警，該表作為統(tǒng)計(jì)數(shù)據(jù)表，其查詢(xún)SQL較簡(jiǎn)單，單純從SQL上分析并沒(méi)有太多問(wèn)題。

聯(lián)想到前陣子，該表由于歷史原因，積壓了2億+無(wú)效數(shù)據(jù)。后面做了批量刪除清理，由此推斷可能是內(nèi)存碎片導(dǎo)致的。

通過(guò)查看表的TABLE STATUS

SHOW TABLE STATUS LIKE 'xxx_price'

圖片

輸出結(jié)果顯示：Data_free=54835281920。碎片占了大量空間。

當(dāng)內(nèi)存碎片過(guò)多時(shí)，首當(dāng)其沖會(huì)讓物理IO被放大。原本“id=1，id=10，id=15”這三條記錄讀一次數(shù)據(jù)頁(yè)就能夠拿到，現(xiàn)在由于這幾條數(shù)據(jù)被分散在多個(gè)數(shù)據(jù)頁(yè)中，從而引發(fā)IO次數(shù)增多。同時(shí)，數(shù)據(jù)頁(yè)是加載到緩沖池（Buffer Pool）里面的，這也會(huì)導(dǎo)致緩存命中率下降。

一般情況下，有一定的內(nèi)存碎片是正常情況。但當(dāng)內(nèi)存碎片的占比過(guò)高時(shí)，則需要關(guān)注。

為什么頻繁刪除會(huì)出現(xiàn)內(nèi)存碎片問(wèn)題？

圖片

圖片

五.前綴索引的坑

某日發(fā)現(xiàn)線(xiàn)上出現(xiàn)一些重復(fù)異常，顯示查詢(xún)某參考價(jià)表的數(shù)據(jù)重復(fù)了，通過(guò)排查insert的兩條數(shù)據(jù)，發(fā)現(xiàn)其實(shí)并沒(méi)有重復(fù)?？戳吮斫Y(jié)構(gòu)才發(fā)現(xiàn)，原來(lái)表某個(gè)字段加了唯一索引，且唯一索引鍵使用了前綴索引。

unique (cate_id, brand_id, 
model_id, key_props(10))

由于 key_props字段使用了前綴索引，因此索引樹(shù)的葉子節(jié)點(diǎn)，并沒(méi)有完整地存儲(chǔ)整個(gè)字符串，而是截取字符串前面N個(gè)字符。這可以有效地節(jié)省索引空間。但這里的問(wèn)題是使用了唯一索引，導(dǎo)致兩個(gè)不同的字符串，只是前綴相同就觸發(fā)重復(fù)沖突。

一般對(duì)于長(zhǎng)度過(guò)長(zhǎng)的字段，加前綴索引是一種選擇，但像案例中在唯一約束中使用前綴索引，則需要保證前綴唯一性

六.索引合并

除了在單個(gè)索引下檢索數(shù)據(jù)，其實(shí)還有可能在多個(gè)索引上檢索。在符合特定條件下，通過(guò)索引合并，能夠減少回表帶來(lái)的消耗。如：

select * from xxx_supplier_order 
where k1 = '123' and k2 = '345'

假設(shè)xxx_supplier_order的主鍵是id字段。k1和k2分別是兩個(gè)獨(dú)立的索引字段。當(dāng)滿(mǎn)足一定條件時(shí)（k1的葉子結(jié)點(diǎn)上id是有序的，k2也是id有序）。此時(shí)MYSQL可以根據(jù) k1 = '123'在索引上檢索出id，再根據(jù) k2 = '345'在索引上檢索id。并將兩次檢索的id取交集，就可以篩選出符合條件的id并回表執(zhí)行。

這樣做的好處在于

1.要同時(shí)滿(mǎn)足 k1 = '123' and k2 = '345'的記錄，其id必然存在于兩個(gè)索引樹(shù)上，通過(guò)交集，篩選出少量符合條件的id才去回表，理論上能夠有效減少回表的次數(shù)。

2.id有序性有利于取交集操作，如某次檢索。從k1上讀到id=1，再?gòu)膋2讀到id=2，此時(shí)就可以判定id=1不滿(mǎn)足k2的條件。另外，通過(guò)有序id，也能夠確保每次回表能夠有序，避免隨機(jī)IO。

七.有時(shí)候SQL沒(méi)啥問(wèn)題，但還是報(bào)了慢查詢(xún)？

如果SQL沒(méi)有問(wèn)題，那么關(guān)注點(diǎn)可以放在mysql實(shí)例的資源開(kāi)銷(xiāo)上了。因?yàn)樵斐陕樵?xún)的原因不單只是SQL本身，有可能是磁盤(pán)負(fù)載，CPU以及網(wǎng)絡(luò) 等方面的資源不足引起了。舉個(gè)例子：

某統(tǒng)計(jì)服務(wù)數(shù)據(jù)庫(kù)，其庫(kù)表大部分?jǐn)?shù)據(jù)源自大數(shù)據(jù)平臺(tái)的異步交換任務(wù)。某個(gè)時(shí)間段有多個(gè)交換任務(wù)往庫(kù)表里面導(dǎo)入大批量數(shù)據(jù)，從而引發(fā)了磁盤(pán)等資源的負(fù)載增加，帶來(lái)慢查詢(xún)。

另外有時(shí)候事務(wù)的問(wèn)題也需要關(guān)注。比如當(dāng)長(zhǎng)事務(wù)導(dǎo)致Undo Log膨脹時(shí)，容易使得掃描效率降低。同時(shí)Buffer Pool中緩存頁(yè)因舊版本數(shù)據(jù)過(guò)多，其緩存命中率也會(huì)下降。我們可以通過(guò) `SHOW ENGINE INNODB STATUS`中`History list length`值是否飆升，加以判斷。

八.總結(jié)

本文試圖通過(guò)一些案例，分析其背后的原理。至于覆蓋索引，聯(lián)合索引等其它內(nèi)容，相信網(wǎng)上有很多類(lèi)似的內(nèi)容，這里不多贅述。慢SQL治理是一個(gè)值得關(guān)注的問(wèn)題。重要的是理解MySQL索引，事務(wù)等方面執(zhí)行原理，然后現(xiàn)實(shí)使用場(chǎng)景中靈活分析和運(yùn)用。