MySQL -- SQL optimization case -- implicit character encoding conversion

Simply fail premise

MySQL We know there are

1、 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 .

2、 Implicit type conversion will also lead to the same abandonment of tree walking search .

Because type conversion is equivalent to using functions on condition fields, such as ：

      
      

       
        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;
      
      
      
      

       
       
1.
       
       
2.
       
       
3.
       
       
4.
      
      

A real case

Let's take a look at a slow problem caused by implicit character encoding conversion sql

Original before optimization sql analysis

There is a business sql Execution requires 1.31 second

Look at the implementation 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 structure of this table has a joint index . However, the union index range will be invalid , So I plan to create a new joint index .

Optimize preliminary treatment

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);

Preliminary optimization invalidation analysis

After creation , Viewing the execution plan again 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 , The first utf8 String to utf8mb4 Character set , To compare .

therefore

This part will be converted and then connected with h_merge_result_new_indicator relation

The second optimization

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

Third optimization

But there's a problem , The above implementation plan key_len yes 606 =（100*3+3）+（100*3+3）

in other words , 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 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）;

Conclusion

Function operation on index field , May break the order of index values , So the optimizer decided to give up the tree search function . This example is an implicit character encoding conversion , They are the same as functions used on other conditional indexes , Full index scanning is caused by requiring function operation on index field .

MySQL Our optimizer does have “ lazy ” The suspicion of , Even if you simply put where id+1=1000 Rewrite into where id=1000-1 You can use the index to quickly find , I won't take the initiative to rewrite this statement .

Ensure that the order of index values is not destroyed in the conditional index , Is a powerful tool for optimizing indexes .