MySQL優化案例之隱式字元編碼轉換

2022-07-20 18:00:41

索性失效前提

MySQL中我們知道有：

1、如果對索引欄位做函數操作，可能會破壞索引值的有序性，因此優化器就決定放棄走樹搜尋功能。
2、隱式型別轉換也會導致同樣的放棄走樹搜尋。

因為型別轉換等價於在條件欄位上使用了函數比如：

/*假設tradeid欄位有索引，且為varchar型別*/
mysql> select * from tradelog where tradeid=110717;
/*等價於*/
mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;

一個真實的案例

下面來看看隱式字元編碼轉換導致的一個慢sql

優化前原始sql分析

業務上有個sql執行需要1.31秒

看看執行計劃:

從執行計劃分析看出問題出在r表也就是 h_merge_result_new_indicator 表全表掃描，檢視該表的表結構有聯合索引。但是聯合索引範圍後會失效，於是打算新建一個聯合索引。

優化初步處理

檢視預新建聯合索引的欄位選擇性:

結合選擇性來看；

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

初步優化無效分析

建立後，再次檢視執行計劃依然無效;

檢視表結構:

另外3個表結構其中有2個utf8mb4，1個utf8

字元集 utf8mb4 是 utf8 的超集，所以當這兩個型別的字串在做比較的時候，MySQL 內部的操作是，先把 utf8 字串轉成 utf8mb4 字元集，再做比較。

因此:

這部分會轉換後再與h_merge_result_new_indicator關聯

第二次優化處理

優化就只需要將字元集編碼轉為utf8再和h_merge_result_new_indicator關聯就能用上索引

再看查詢只需要0.02秒了

第三次優化

但是還有個問題，如上執行計劃key_len是606 =（100*3+3）+（100*3+3）

也就是說，沒有用上BATCH_NO欄位上的索引，我們知道索引少一個欄位，佔用會減少，不會太臃腫，因此，聯合索引只需要包含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）;

結論

對索引欄位做函數操作，可能會破壞索引值的有序性，因此優化器就決定放棄走樹搜尋功能。該例子是隱式字元編碼轉換，它們都跟其他條件索引上使用函數一樣，因為要求在索引欄位上做函數操作而導致了全索引掃描。

MySQL 的優化器確實有“偷懶”的嫌疑，即使簡單地把 where id+1=1000 改寫成 where id=1000-1 就能夠用上索引快速查詢，也不會主動做這個語句重寫。

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

到此這篇關於MySQL優化案例之隱式字元編碼轉換的文章就介紹到這了,更多相關MySQL隱式字元編碼轉換內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！