這篇文章主要介紹“如何優(yōu)化Explain索引”，在日常操作中，相信很多人在如何優(yōu)化Explain索引問題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”如何優(yōu)化Explain索引”的疑惑有所幫助！接下來，請(qǐng)跟著小編一起來學(xué)習(xí)吧！
創(chuàng)新互聯(lián)是專業(yè)的海州網(wǎng)站建設(shè)公司，海州接單;提供網(wǎng)站設(shè)計(jì)制作、網(wǎng)站建設(shè),網(wǎng)頁設(shè)計(jì),網(wǎng)站設(shè)計(jì),建網(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)隊(duì),希望更多企業(yè)前來合作!
1. 項(xiàng)目背景介紹
1.1 涉及的表結(jié)構(gòu)
1.2 明確查詢?cè)V求
2. 索引問題確認(rèn)和調(diào)優(yōu)
2.1 問題發(fā)現(xiàn)
2.2 問題驗(yàn)證
2.3 索引優(yōu)化
看過上一篇文章的同學(xué)應(yīng)該還記得在敘述索引原理和實(shí)際案例的時(shí)候，我們列舉了一個(gè)阿里分布式事務(wù)中主事務(wù)表的例子。
巧了，前段時(shí)間因?yàn)闃I(yè)務(wù)需求，我們開發(fā)了一個(gè)長事務(wù)一致性引擎用來應(yīng)對(duì)廣告體系中的計(jì)費(fèi)時(shí)數(shù)據(jù)上下游一致性問題，其中也涉及了一個(gè)類似這樣的表。
然而，最近迭代進(jìn)行代碼走查時(shí)發(fā)現(xiàn)，索引用的有問題。
如上圖所示，數(shù)據(jù)庫的字段和索引結(jié)構(gòu)是這個(gè)樣子。
tx_id全局唯一遞增字段為主鍵。
status字段標(biāo)識(shí)該條記錄的當(dāng)前狀態(tài)，用來區(qū)分未執(zhí)行成功的記錄
創(chuàng)建時(shí)間和更新字段，用來輔助異步恢復(fù)時(shí)按時(shí)間衰減序列撈取執(zhí)行。
各字段具體的起作用方式，有興趣可以瀏覽之前寫的《分布式事務(wù)從入門到放棄(二)--詳述DT引擎一致性原理及設(shè)計(jì)》一文。
該表的作用是撈取那些沒有進(jìn)行到終態(tài)的記錄，進(jìn)行異?；謴?fù)。
為了避開系統(tǒng)正在處理中的記錄，因此，將時(shí)間限定在1分鐘之前。
為了盡量高效，將時(shí)間范圍限定在前10分鐘，更久的失敗記錄交給更低頻的定時(shí)任務(wù)處理。
為了實(shí)現(xiàn)異步處理失敗后的時(shí)間衰減，所以使用modify，同時(shí)也是為了避免新產(chǎn)生的數(shù)據(jù)因?yàn)槔蠑?shù)據(jù)處理有問題而導(dǎo)致積壓。
訴求其實(shí)也比較簡(jiǎn)單：定時(shí)撈取·前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;
-- 唯一索引和聯(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)合索引包含了，那實(shí)際上，上述的查詢語句，配合當(dāng)前索引，能達(dá)到想要的效果嗎？
根據(jù)我們上一篇文章的索引知識(shí)，可以給出結(jié)論，這個(gè)索引會(huì)有用，但不會(huì)全起作用。因?yàn)樵诼?lián)合索引下，處于后面位置的索引字段起作用的前提，是前置位的字段值相同。
Explain工具上場(chǎng)。
key=idx_status_time。key標(biāo)識(shí)的是本次查詢實(shí)際使用的索引。所以，說明我們的聯(lián)合索引是起了一定作用的。
key_len=4。key_len標(biāo)識(shí)的使用到的索引字段的長度。對(duì)于MySQL5.7，status是int型占4個(gè),時(shí)間字段是datetime型占5個(gè)。而這里len=4，說明只使用了status一個(gè)索引字段。
type=range。range說明查詢status時(shí)已經(jīng)是一個(gè)范圍查詢。
rows=167。說明為了找到結(jié)果，遍歷了167。
Extra='Using index condition; Using filesort'。很糟糕的是，排序語句觸發(fā)了文件排序。
上述結(jié)果，可以知道之前的索引設(shè)置是不合適的，時(shí)間索引沒有被使用，而且，在排序的時(shí)候，使用了額外文件排序。效率和性能相對(duì)而言被影響較大，是需要消除的。
另外理論上，有查詢優(yōu)化器的存在，發(fā)現(xiàn)status的區(qū)分度不高，可能直接使用了索引里的時(shí)間字段，而不使用status。
畢竟，這份數(shù)據(jù)里，只有兩個(gè)值，且數(shù)量級(jí)相差也不太多。
那么，按照創(chuàng)建索引的字段需要有足夠的區(qū)分度這個(gè)原則，status字段還有必要放在索引里么？
帶著問題我們來一起實(shí)際看下。
那么，我們應(yīng)該怎么去調(diào)整索引以達(dá)到高效查詢呢。
調(diào)整索引字段順序
首先，考慮調(diào)整的是gmt_modified和gmt_create的順序。
因?yàn)?，?lián)合索引下，中間有漏掉索引字段時(shí)，后續(xù)字段將不起作用。
調(diào)整兩個(gè)時(shí)間順序后，再看索引使用情況：
我們看到了變化：
key_len=9。說明使用了gmt_modified索引字段。
rows=2。這個(gè)變化說明我們的調(diào)整是有效的，查詢到數(shù)據(jù)只進(jìn)行了2個(gè)遍歷。相比之前的167要高效很多。
但是，filesort還存在。
status有必要建在索引里么
我們把status從索引里刪除掉，再來看下explain的結(jié)果：
沒有了status的索引參與，想要在where條件里過濾，要比之前更加耗性能。所以，status是必要的。
filesort怎么優(yōu)化掉
排序字段沒有使用索引，我們能給其單獨(dú)創(chuàng)建一個(gè)索引么？
答案是不能。
因?yàn)閟ql查詢只會(huì)使用一個(gè)索引，在查詢條件使用了索引的情況下，排序就不會(huì)再使用索引了?？梢詫?shí)際看下：
所以，單獨(dú)給排序字段創(chuàng)建索引是沒有用的。怎么辦呢？
考慮修改sql，讓排序字段使用到索引。
首先我們需要知道，mysql在執(zhí)行order by的時(shí)候，會(huì)先查看參與排序的字段在執(zhí)行計(jì)劃里是否使用了索引：如果使用了索引，則說明結(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;
將查詢條件字段也加到排序字段中，
可以看到，此時(shí)的Extra中已經(jīng)沒有filesort了。
當(dāng)然，排序這個(gè)點(diǎn)，可以再考慮下是否真的需要，如果每次處理的異常數(shù)據(jù)很少，其實(shí)，不進(jìn)行排序也可以。那樣就又可以省一些索引空間了。
到此，關(guān)于“如何優(yōu)化Explain索引”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！
重慶分公司
重慶分公司
