如何優(yōu)化Explain索引

這篇文章主要介紹“如何優(yōu)化Explain索引”，在日常操作中，相信很多人在如何優(yōu)化Explain索引問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”如何優(yōu)化Explain索引”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！

本文內(nèi)容預(yù)覽：

1.  項目背景介紹

      1.1 涉及的表結(jié)構(gòu)

      1.2 明確查詢訴求

 2.  索引問題確認(rèn)和調(diào)優(yōu)

      2.1 問題發(fā)現(xiàn)

      2.2 問題驗證

      2.3 索引優(yōu)化

Part1項目背景介紹

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

巧了，前段時間因為業(yè)務(wù)需求，我們開發(fā)了一個長事務(wù)一致性引擎用來應(yīng)對廣告體系中的計費時數(shù)據(jù)上下游一致性問題，其中也涉及了一個類似這樣的表。

然而，最近迭代進行代碼走查時發(fā)現(xiàn)，索引用的有問題。

0.1涉及的表結(jié)構(gòu)

如上圖所示，數(shù)據(jù)庫的字段和索引結(jié)構(gòu)是這個樣子。

•  tx_id全局唯一遞增字段為主鍵。

•  status字段標(biāo)識該條記錄的當(dāng)前狀態(tài)，用來區(qū)分未執(zhí)行成功的記錄

•  創(chuàng)建時間和更新字段，用來輔助異步恢復(fù)時按時間衰減序列撈取執(zhí)行。

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

0.2明確查詢訴求

該表的作用是撈取那些沒有進行到終態(tài)的記錄，進行異?；謴?fù)。

•  為了避開系統(tǒng)正在處理中的記錄，因此，將時間限定在1分鐘之前。

•  為了盡量高效，將時間范圍限定在前10分鐘，更久的失敗記錄交給更低頻的定時任務(wù)處理。

•  為了實現(xiàn)異步處理失敗后的時間衰減，所以使用modify，同時也是為了避免新產(chǎn)生的數(shù)據(jù)因為老數(shù)據(jù)處理有問題而導(dǎo)致積壓。

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

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索引問題確認(rèn)和調(diào)優(yōu)

0.3問題發(fā)現(xiàn) 

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

當(dāng)前表的索引有兩種：唯一索引tx_id，聯(lián)合索引status_ctime_mtime。

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

根據(jù)我們上一篇文章的索引知識，可以給出結(jié)論，這個索引會有用，但不會全起作用。因為在聯(lián)合索引下，處于后面位置的索引字段起作用的前提，是前置位的字段值相同。

0.4問題驗證

Explain工具上場。

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

key_len=4。key_len標(biāo)識的使用到的索引字段的長度。對于mysql5.7，status是int型占4個,時間字段是datetime型占5個。而這里len=4，說明只使用了status一個索引字段。

type=range。range說明查詢status時已經(jīng)是一個范圍查詢。

rows=167。說明為了找到結(jié)果，遍歷了167。

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

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

另外理論上，有查詢優(yōu)化器的存在，發(fā)現(xiàn)status的區(qū)分度不高，可能直接使用了索引里的時間字段，而不使用status。

畢竟，這份數(shù)據(jù)里，只有兩個值，且數(shù)量級相差也不太多。

那么，按照創(chuàng)建索引的字段需要有足夠的區(qū)分度這個原則，status字段還有必要放在索引里么？

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

0.5索引優(yōu)化

那么，我們應(yīng)該怎么去調(diào)整索引以達到高效查詢呢。

調(diào)整索引字段順序

首先，考慮調(diào)整的是gmt_modified和gmt_create的順序。

因為，聯(lián)合索引下，中間有漏掉索引字段時，后續(xù)字段將不起作用。

調(diào)整兩個時間順序后，再看索引使用情況：

我們看到了變化：

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

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

但是，filesort還存在。

status有必要建在索引里么

我們把status從索引里刪除掉，再來看下explain的結(jié)果：

沒有了status的索引參與，想要在where條件里過濾，要比之前更加耗性能。所以，status是必要的。

filesort怎么優(yōu)化掉

排序字段沒有使用索引，我們能給其單獨創(chuàng)建一個索引么？

答案是不能。

因為sql查詢只會使用一個索引，在查詢條件使用了索引的情況下，排序就不會再使用索引了?？梢詫嶋H看下：

所以，單獨給排序字段創(chuàng)建索引是沒有用的。怎么辦呢？

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

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

修改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中已經(jīng)沒有filesort了。

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

到此，關(guān)于“如何優(yōu)化Explain索引”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注億速云網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>