存儲優化補充篇:Explain索引優化實操
本文內容預覽：
1. 項目背景介紹
1.1 涉及的表結構
1.2 明確查詢訴求
2. 索引問題確認和調優
2.1 問題發現
2.2 問題驗證
2.3 索引優化
3. 總結
Part1項目背景介紹
看過上一篇文章的同學應該還記得在敘述索引原理和實際案例的時候，我們列舉了一個阿里分布式事務中主事務表的例子。
巧了，前段時間因為業務需求，我們開發了一個長事務一致性引擎用來應對廣告體系中的計費時數據上下游一致性問題，其中也涉及了一個類似這樣的表。
然而，最近迭代進行代碼走查時發現，索引用的有問題。
0.1涉及的表結構
如上圖所示，數據庫的字段和索引結構是這個樣子。
- tx_id全局唯一遞增字段為主鍵。
- status字段標識該條記錄的當前狀態，用來區分未執行成功的記錄
- 創建時間和更新字段，用來輔助異步恢復時按時間衰減序列撈取執行。
各字段具體的起作用方式，有興趣可以瀏覽之前寫的《分布式事務從入門到放棄(二)--詳述DT引擎一致性原理及設計》一文。
0.2明確查詢訴求
該表的作用是撈取那些沒有進行到終態的記錄，進行異常恢復。
- 為了避開系統正在處理中的記錄，因此，將時間限定在1分鐘之前。
- 為了盡量高效，將時間范圍限定在前10分鐘，更久的失敗記錄交給更低頻的定時任務處理。
- 為了實現異步處理失敗后的時間衰減，所以使用modify，同時也是為了避免新產生的數據因為老數據處理有問題而導致積壓。
訴求其實也比較簡單：定時撈取·前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;
Part2索引問題確認和調優
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了。
當然，排序這個點，可以再考慮下是否真的需要，如果每次處理的異常數據很少，其實，不進行排序也可以。那樣就又可以省一些索引空間了。
Part3總結
本文從一條sql查詢和數據索引的構建的走查，發現了索引失效問題，并按索引知識一步步排查驗證，直到我們認為OK。
希望通過上述的排查驗證過程，結合上一篇的索引原理，可以讓大家對索引的認識更進一步。
