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

Posted on by

索性失效前提

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秒

MySQL--SQL優化案例--隱式字符編碼轉換_聯合索引

看看執行計劃:

MySQL--SQL優化案例--隱式字符編碼轉換_mysql_02

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

優化初步處理

MySQL--SQL優化案例--隱式字符編碼轉換_mysql_03

查看預新建聯合索引的字段選擇性:

MySQL--SQL優化案例--隱式字符編碼轉換_mysql_04

結合選擇性來看；

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

初步優化無效分析

創建後，再次查看執行計劃依然無效;

MySQL--SQL優化案例--隱式字符編碼轉換_字段_05

查看表結構:

MySQL--SQL優化案例--隱式字符編碼轉換_mysql_06

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

MySQL--SQL優化案例--隱式字符編碼轉換_mysql_07

MySQL--SQL優化案例--隱式字符編碼轉換_聯合索引_08

MySQL--SQL優化案例--隱式字符編碼轉換_字段_09

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

因此:

MySQL--SQL優化案例--隱式字符編碼轉換_mysql_10

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

第二次優化處理

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

MySQL--SQL優化案例--隱式字符編碼轉換_聯合索引_11

再看查詢隻需要0.02秒瞭

MySQL--SQL優化案例--隱式字符編碼轉換_聯合索引_12

第三次優化

但是還有個問題，如上執行計劃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隱式字符編碼轉換內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：