存儲優化之 Explain 索引優化

本文內容預覽：

  1. 項目背景介紹
    1.1 涉及的表結構
    1.2 明確查詢訴求

  2. 索引問題確認和調優
    2.1 問題發現
    2.2 問題驗證
    2.3 索引優化

  3. 總結

看過上一篇文章的同學應該還記得在敘述索引原理和實際案例的時候，我們列舉了一個阿里分佈式事務中主事務表的例子。

巧了，前段時間因爲業務需求，我們開發了一個長事務一致性引擎用來應對廣告體系中的計費時數據上下游一致性問題，其中也涉及了一個類似這樣的表。

然而，最近迭代進行代碼走查時發現，索引用的有問題。

0.1 涉及的表結構

如上圖所示，數據庫的字段和索引結構是這個樣子。

各字段具體的起作用方式，有興趣可以瀏覽之前寫的《分佈式事務從入門到放棄 (二)-- 詳述 DT 引擎一致性原理及設計》一文。

0.2 明確查詢訴求

該表的作用是撈取那些沒有進行到終態的記錄，進行異常恢復。

訴求其實也比較簡單：定時撈取 · 前 1 分鐘 · 到 · 前 10 分鐘 ·，且，狀態屬於某些狀態的記錄，即：

select * from activity_t 
where 
status in (1,2) 
and gmt_modified>='2021-01-01 xx:xx:10' 
and gmt_modified<'2021-01-01 xx:xx:01'
order by gmt_create;

0.3 問題發現

-- 唯一索引和聯合索引
PRIMARY KEY (`tx_id`),
KEY `idx_status_time` (`status`,`gmt_create`,`gmt_modified`)

當前表的索引有兩種：唯一索引 tx_id，聯合索引 status_ctime_mtime。

我們當然希望的是有此索引的存在讓之前的查詢語句效率變高，乍一看，好像查詢條件，排序條件都被聯合索引包含了，那實際上，上述的查詢語句，配合當前索引，能達到想要的效果嗎？

根據我們上一篇文章的索引知識，可以給出結論，這個索引會有用，但不會全起作用。因爲在聯合索引下，處於後面位置的索引字段起作用的前提，是前置位的字段值相同。

0.4 問題驗證

Explain 工具上場。

key=idx_status_time。key 標識的是本次查詢實際使用的索引。所以，說明我們的聯合索引是起了一定作用的。

key_len=4。key_len 標識的使用到的索引字段的長度。對於 mysql5.7，status 是 int 型佔 4 個, 時間字段是 datetime 型佔 5 個。而這裏 len=4，說明只使用了 status 一個索引字段。

type=range。range 說明查詢 status 時已經是一個範圍查詢。

rows=167。說明爲了找到結果，遍歷了 167。

Extra='Using index condition; Using filesort'。很糟糕的是，排序語句觸發了文件排序。

上述結果，可以知道之前的索引設置是不合適的，時間索引沒有被使用，而且，在排序的時候，使用了額外文件排序。效率和性能相對而言被影響較大，是需要消除的。

另外理論上，有查詢優化器的存在，發現 status 的區分度不高，可能直接使用了索引裏的時間字段，而不使用 status。

畢竟，這份數據裏，只有兩個值，且數量級相差也不太多。

那麼，按照創建索引的字段需要有足夠的區分度這個原則，status 字段還有必要放在索引裏麼？ 

帶着問題我們來一起實際看下。

0.5 索引優化

那麼，我們應該怎麼去調整索引以達到高效查詢呢。

調整索引字段順序

首先，考慮調整的是 gmt_modified 和 gmt_create 的順序。

因爲，聯合索引下，中間有漏掉索引字段時，後續字段將不起作用。

調整兩個時間順序後，再看索引使用情況：

我們看到了變化：

key_len=9。說明使用了 gmt_modified 索引字段。

rows=2。這個變化說明我們的調整是有效的，查詢到數據只進行了 2 個遍歷。相比之前的 167 要高效很多。

但是，filesort 還存在。

status 有必要建在索引裏麼

我們把 status 從索引裏刪除掉，再來看下 explain 的結果：沒有了 status 的索引參與，想要在 where 條件裏過濾，要比之前更加耗性能。所以，status 是必要的。

filesort 怎麼優化掉

排序字段沒有使用索引，我們能給其單獨創建一個索引麼？

答案是不能。

因爲 sql 查詢只會使用一個索引，在查詢條件使用了索引的情況下，排序就不會再使用索引了。可以實際看下：所以，單獨給排序字段創建索引是沒有用的。怎麼辦呢？

考慮修改 sql，讓排序字段使用到索引。

首先我們需要知道，mysql 在執行 order by 的時候，會先查看參與排序的字段在執行計劃裏是否使用了索引：如果使用了索引，則說明結果是排好序的，否則，進行排序操作。

修改 sql 如下：

select * from activity_t 
where 
status in (1,2) 
and gmt_modified>='2021-01-01 xx:xx:10' 
and gmt_modified<'2021-01-01 xx:xx:01'
order by status,gmt_modified,gmt_create;

將查詢條件字段也加到排序字段中，

可以看到，此時的 Extra 中已經沒有 filesort 了。

當然，排序這個點，可以再考慮下是否真的需要，如果每次處理的異常數據很少，其實，不進行排序也可以。那樣就又可以省一些索引空間了。

本文從一條 sql 查詢和數據索引的構建的走查，發現了索引失效問題，並按索引知識一步步排查驗證，直到我們認爲 OK。

希望通過上述的排查驗證過程，結合上一篇的索引原理，可以讓大家對索引的認識更進一步。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/5ToAWbN0HE3iLNVoyG36Hg

猜你喜歡