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

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

創(chuàng)新互聯(lián)是專業(yè)的海州網(wǎng)站建設(shè)公司，海州接單;提供網(wǎng)站設(shè)計制作、網(wǎng)站建設(shè),網(wǎng)頁設(shè)計,網(wǎng)站設(shè)計,建網(wǎng)站,PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進(jìn)行海州網(wǎng)站開發(fā)網(wǎng)頁制作和功能擴(kuò)展;專業(yè)做搜索引擎喜愛的網(wǎng)站,專業(yè)的做網(wǎng)站團(tuán)隊,希望更多企業(yè)前來合作!

本文內(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)對廣告體系中的計費(fèi)時數(shù)據(jù)上下游一致性問題，其中也涉及了一個類似這樣的表。

然而，最近迭代進(jìn)行代碼走查時發(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明確查詢訴求

該表的作用是撈取那些沒有進(jìn)行到終態(tài)的記錄，進(jìn)行異常恢復(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)前索引，能達(dá)到想要的效果嗎？

根據(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)整索引以達(dá)到高效查詢呢。

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

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

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

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

我們看到了變化：

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

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

但是，filesort還存在。

status有必要建在索引里么

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

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

filesort怎么優(yōu)化掉

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

答案是不能。

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

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

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

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

修改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ù)很少，其實，不進(jìn)行排序也可以。那樣就又可以省一些索引空間了。

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

當(dāng)前名稱：如何優(yōu)化Explain索引
路徑分享：http://muchs.cn/article42/jpgghc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供定制開發(fā)、做網(wǎng)站、網(wǎng)頁設(shè)計公司、動態(tài)網(wǎng)站、靜態(tài)網(wǎng)站、搜索引擎優(yōu)化

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)