一.背景

二.MySQL是如何評估成本的？

三.即使加了索引，也沒有起作用

四.內存碎片也是一個值得關注的問題

五.前綴索引的坑

六.索引合并

七.有時候SQL沒啥問題，但還是報了慢查詢？

八.總結

一.背景

今年初團隊開始推行“服務穩定性問題治理專項”。通過錯誤日志、慢SQL、接口性能等各項指標的優化，進一步提升系統穩定性與可靠性。在此契機之下，本文將從“慢SQL治理”的角度，通過部分實際案例，分析其原理，做一些階段性總結和思考。

二.MySQL是如何評估成本的？

某日線上突發告警，發現有一條慢SQL

select * from xxxx_supplier_extra 
where column_key = 'xxxx'

作為有“臨床經驗”的老司機，第一直覺是先用explain分析。explain用了毫秒級的時間就輸出了這樣一份執行計劃，果然它很快，兩分鐘都不到。

圖片

通過“key=null”，我們不難發現，這條SQL走了全表掃描。這是一條簡單的SQL案例，我們借用來分析MYSQL背后的成本評估原理。

執行SQL前，優化器會先選擇它認為成本最低的方案。正如同有電梯坐電梯，沒電梯爬樓梯。

圖片

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

因為全表掃描是發生在聚簇索引上，首先會估算整個聚簇索引占用的頁面數，以及表的記錄數，再計算IO成本和掃描成本（可以理解為CPU成本）。

計算口徑如下：

IO成本：頁面數 x io_block_read_cost（IO成本數 默認0.25） + 1.0
CPU成本：記錄數 x cpu_tuple_cost （掃描成本數 默認0.1）
總成本 = IO成本 + CPU成本

MySQL的IO成本默認基于隨機IO計算（`io_block_read_cost=1.0`），而非順序IO。這里因為全表掃描是順序IO，io_block_read_cost的默認值則為0.25。表記錄越多，占用的頁數則隨著增長，其查詢成本也就不斷累加。

三.即使加了索引，也沒有起作用

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

以前面SQL為例，當我們為‘column_key’字段加索引后，測試環境 explain 分析能命中索引，但上線后還是咔咔咔出現慢查詢。

這是因為如果索引字段的區分度不夠，優化器會認為查找成本過大，此時還是選擇走全表掃描。而測試環境表記錄較少的情況下，優化器覺得回表開銷不大，就能命中索引，這也解釋了為什么兩者的執行計劃不同。

索引能否命中往往與查詢條件以及數據分布有關。

如何在索引設計之初就規避此類問題？

網上一些文章會提到基于該列的業務屬性來區分，例如性別字段只有兩個值：“sex=男，sex=女”。由于大部分記錄都擁有相同值，數據區分度不大，所以容易成為低效索引。

除此之外，可以使用該語句計算區分度：

SELECT COUNT(DISTINCT column_name ) 
/ COUNT(*)

區分度低于10%的字段避免單獨建索引。對于聯合索引而言，也應盡量將區分度高的字段放在前面。

值得注意的是，即使該字段的區分度能夠建立索引。也要根據已有索引和查詢場景做綜合取舍，要避免在同一個表上堆砌過多索引。

四.內存碎片也是一個值得關注的問題

內存碎片對優化器的影響

上文提到了成本估算是基于頁數以及記錄數計算的，這些數據來源于庫中的統計信息。當內存碎片過大時，如果出現庫表的統計信息未及時更新，也會因為優化器評估的結果與實際差距太大，從而影響實際執行效果。

內存碎片導致慢查詢

舉個例子：某日xxxx_price表產生慢查詢告警，該表作為統計數據表，其查詢SQL較簡單，單純從SQL上分析并沒有太多問題。

聯想到前陣子，該表由于歷史原因，積壓了2億+無效數據。后面做了批量刪除清理，由此推斷可能是內存碎片導致的。

通過查看表的TABLE STATUS

SHOW TABLE STATUS LIKE 'xxx_price'

圖片

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

當內存碎片過多時，首當其沖會讓物理IO被放大。原本“id=1，id=10，id=15”這三條記錄讀一次數據頁就能夠拿到，現在由于這幾條數據被分散在多個數據頁中，從而引發IO次數增多。同時，數據頁是加載到緩沖池（Buffer Pool）里面的，這也會導致緩存命中率下降。

一般情況下，有一定的內存碎片是正常情況。但當內存碎片的占比過高時，則需要關注。

為什么頻繁刪除會出現內存碎片問題？

圖片

圖片

五.前綴索引的坑

某日發現線上出現一些重復異常，顯示查詢某參考價表的數據重復了，通過排查insert的兩條數據，發現其實并沒有重復。看了表結構才發現，原來表某個字段加了唯一索引，且唯一索引鍵使用了前綴索引。

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

由于 key_props字段使用了前綴索引，因此索引樹的葉子節點，并沒有完整地存儲整個字符串，而是截取字符串前面N個字符。這可以有效地節省索引空間。但這里的問題是使用了唯一索引，導致兩個不同的字符串，只是前綴相同就觸發重復沖突。

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

六.索引合并

除了在單個索引下檢索數據，其實還有可能在多個索引上檢索。在符合特定條件下，通過索引合并，能夠減少回表帶來的消耗。如：

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

假設xxx_supplier_order的主鍵是id字段。k1和k2分別是兩個獨立的索引字段。當滿足一定條件時（k1的葉子結點上id是有序的，k2也是id有序）。此時MYSQL可以根據 k1 = '123'在索引上檢索出id，再根據 k2 = '345'在索引上檢索id。并將兩次檢索的id取交集，就可以篩選出符合條件的id并回表執行。

這樣做的好處在于

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

2.id有序性有利于取交集操作，如某次檢索。從k1上讀到id=1，再從k2讀到id=2，此時就可以判定id=1不滿足k2的條件。另外，通過有序id，也能夠確保每次回表能夠有序，避免隨機IO。

七.有時候SQL沒啥問題，但還是報了慢查詢？

如果SQL沒有問題，那么關注點可以放在mysql實例的資源開銷上了。因為造成慢查詢的原因不單只是SQL本身，有可能是磁盤負載，CPU以及網絡 等方面的資源不足引起了。舉個例子：

某統計服務數據庫，其庫表大部分數據源自大數據平臺的異步交換任務。某個時間段有多個交換任務往庫表里面導入大批量數據，從而引發了磁盤等資源的負載增加，帶來慢查詢。

另外有時候事務的問題也需要關注。比如當長事務導致Undo Log膨脹時，容易使得掃描效率降低。同時Buffer Pool中緩存頁因舊版本數據過多，其緩存命中率也會下降。我們可以通過 `SHOW ENGINE INNODB STATUS`中`History list length`值是否飆升，加以判斷。

八.總結

本文試圖通過一些案例，分析其背后的原理。至于覆蓋索引，聯合索引等其它內容，相信網上有很多類似的內容，這里不多贅述。慢SQL治理是一個值得關注的問題。重要的是理解MySQL索引，事務等方面執行原理，然后現實使用場景中靈活分析和運用。