索性失效前提

MySQL中我們知道有：

• 1、如果對索引字段做函數(shù)操作，可能會(huì)破壞索引值的有序性，因此優(yōu)化器就決定放棄走樹搜索功能。
• 2、隱式類型轉(zhuǎn)換也會(huì)導(dǎo)致同樣的放棄走樹搜索。

因?yàn)轭愋娃D(zhuǎn)換等價(jià)于在條件字段上使用了函數(shù)比如：

/*假設(shè)tradeid字段有索引，且為varchar類型*/
mysql> select * from tradelog where tradeid=110717;
/*等價(jià)于*/
mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;

一個(gè)真實(shí)的案例

下面來看看隱式字符編碼轉(zhuǎn)換導(dǎo)致的一個(gè)慢sql

優(yōu)化前原始sql分析

業(yè)務(wù)上有個(gè)sql執(zhí)行需要1.31秒

看看執(zhí)行計(jì)劃:

從執(zhí)行計(jì)劃分析看出問題出在r表也就是 h_merge_result_new_indicator 表全表掃描，查看該表的表結(jié)構(gòu)有聯(lián)合索引。但是聯(lián)合索引范圍后會(huì)失效，于是打算新建一個(gè)聯(lián)合索引。

優(yōu)化初步處理

查看預(yù)新建聯(lián)合索引的字段選擇性:

結(jié)合選擇性來看；

create index idx_hmrni on h_merge_result_new_indicator(keyName,module,BATCH_NO);

初步優(yōu)化無效分析

創(chuàng)建后，再次查看執(zhí)行計(jì)劃依然無效;

查看表結(jié)構(gòu):

另外3個(gè)表結(jié)構(gòu)其中有2個(gè)utf8mb4，1個(gè)utf8

字符集 utf8mb4 是 utf8 的超集，所以當(dāng)這兩個(gè)類型的字符串在做比較的時(shí)候，MySQL 內(nèi)部的操作是，先把 utf8 字符串轉(zhuǎn)成 utf8mb4 字符集，再做比較。

因此:

這部分會(huì)轉(zhuǎn)換后再與h_merge_result_new_indicator關(guān)聯(lián)

第二次優(yōu)化處理

優(yōu)化就只需要將字符集編碼轉(zhuǎn)為utf8再和h_merge_result_new_indicator關(guān)聯(lián)就能用上索引

再看查詢只需要0.02秒了

第三次優(yōu)化

但是還有個(gè)問題，如上執(zhí)行計(jì)劃key_len是606 =（100*3+3）+（100*3+3）

也就是說，沒有用上BATCH_NO字段上的索引，我們知道索引少一個(gè)字段，占用會(huì)減少，不會(huì)太臃腫，因此，聯(lián)合索引只需要包含r(keyName,module）

• drop index idx_hmrni on h_merge_result_new_indicator;
• create index idx_hmrni on h_merge_result_new_indicator(keyName,module）;

結(jié)論

對索引字段做函數(shù)操作，可能會(huì)破壞索引值的有序性，因此優(yōu)化器就決定放棄走樹搜索功能。該例子是隱式字符編碼轉(zhuǎn)換，它們都跟其他條件索引上使用函數(shù)一樣，因?yàn)橐笤谒饕侄紊献龊瘮?shù)操作而導(dǎo)致了全索引掃描。

MySQL 的優(yōu)化器確實(shí)有“偷懶”的嫌疑，即使簡單地把 where id+1=1000 改寫成 where id=1000-1 就能夠用上索引快速查找，也不會(huì)主動(dòng)做這個(gè)語句重寫。

保證在條件索引上不做破壞索引值的有序性，是優(yōu)化索引的利器。

到此這篇關(guān)于MySQL優(yōu)化案例之隱式字符編碼轉(zhuǎn)換的文章就介紹到這了,更多相關(guān)MySQL隱式字符編碼轉(zhuǎn)換內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論