A case of MySQL implicit conversion

Implicit conversion , It can be said to be a relational database SQL A very hidden problem in optimization , I have encountered many cases related to him before ,

《 Alternatives to implicit conversion are not omnipotent 》

《Oracle、SQL Server and MySQL Similarities and differences of implicit transformation of 》

《 Let's see SQL Server Different implicit conversion processing 》

《 How to find the of implicit conversion SQL？》

《 Convert cases implicitly , To tap the skills of developers 》

《 On explicit conversion and implicit conversion 》

《 Implicitly converted case scenarios 》

《 An interesting misselection of execution plan problem location 》

This article on data and cloud 《SQL Optimize —— Implicit character encoding conversion 》 It introduces MySQL The problem of implicit conversion caused by different character sets in .

MySQL We know that ,

• If you do a function operation on the index field , May break the order of index values , So the optimizer decided to give up the tree search function .

• Implicit type conversion can also lead to abandoning tree search .

Because type conversion is equivalent to using a function on a condition field , for example ,

 hypothesis tradeid The field has an index , And for varchar type ,
mysql> select * from tradelog where tradeid=110717;
 Equivalent to ,
mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;

Let's take a look at a slow problem caused by implicit character encoding conversion SQL, There is a business SQL Execution requires 1.31 second ,

Look at the execution plan ,

From the analysis of the implementation plan, we can see that the problem lies in r Watch is h_merge_result_new_indicator Table full table scan , Check that the table of this table has a joint index . However, the union index range will be invalid , So I plan to create a new joint index ,

View the fields of the pre created union index ,

Combined with selectivity ,

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

After creation , Again, the implementation plan is still invalid ,

View table structure ,

in addition 3 A table structure with 2 individual utf8mb4,1 individual utf8,

Character set utf8mb4 yes utf8 Superset , So when these two types of strings are compared ,MySQL The internal operation is , First the utf8 String to utf8mb4 Character set , To compare . therefore ,

This part will be converted and then connected with h_merge_result_new_indicator relation . Optimization only needs to convert the character set encoding to utf8 And again h_merge_result_new_indicator The association can use the index ,

If you look at the query again, you only need 0.02 The second ,

But there's a problem , The above implementation plan key_len yes 606=(100*3+3)+(100*3+3). That is to say , It doesn't work BATCH_NO Index on field , We know that the index is missing one field , Occupancy will be reduced , Not too bloated . therefore , The federated index only needs to contain (keyName,module),

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

Function operation on index field , Whether it's explicit , Or implicit , Will destroy the order of index values , And then affect the judgment of the optimizer , This is right Oracle and MySQL It's all interlinked .

therefore , In the development stage , And the audit stage , Avoiding and excavating these problems , It's worth noticing , Input output ratio , Still considerable .

Recently updated articles ：

《Linux Comparison of several data transmission tools 》

《 Xiaobai studies MySQL - There are some differences between users created by different versions 》

《 Recent problems 》

《Linux Retrieval of process information 》

《OCR Analyze the picture of health treasure 》

Article classification and indexing ：

《 official account 900 Article classification and index 》