In MySQL we know that there are:
- If you perform a function operation on an index field, you may break the order of the index values, so the optimizer decides to abandon the tree search function.
- Implicit type conversion also causes tree searches to be abandoned.
Because type conversion is equivalent to using functions on conditional fields such as:
假设tradeid字段有索引，且为varchar类型：
mysql> select * from tradelog where tradeid=110717;
等价于：
mysql> select * from tradelog where CAST(tradid AS signed int) = 110717; Let's take a look at a slow SQL resulting from implicit character encoding conversion:
：
A SQL execution on a business takes 1.31 seconds:
Take a look at the execution plan:
From the execution plan analysis, the problem is that the r table is h_merge_result_new_indicator the table is scanned fully, and the table node of the table has a federated index. However, the federated index range will be invalidated, so it is planned to create a new federated index:
To view the field selectivity for a pre-created federated index:
Combined with selectivity:
create index idx_hmrni on h_merge_result_new_indicator(keyName,module,BATCH_NO); After creating, reviewing the execution plan again is still invalid:
To view the table structure:
There are 3 other table structures of which there are 2 utf8mb4 and 1 utf8:
The character set utf8mb4 is a superset of utf8, so when these two types of strings are being compared, the internal operation of MySQL is to convert the utf8 string to the utf8mb4 character set and then compare.
therefore:
This section is converted before being associated with the h_merge_result_new_indicator.
Optimization only needs to convert the character set encoding to utf8 and then associate it with h_merge_result_new_indicator to use the index:
It only takes 0.02 seconds to look at the query:
But there is still a problem, as in the above implementation plan key_len is 606 = (100 *3+3)+(100*3+3)
That is to say, without using the index on the BATCH_NO field, we know that the index is one less field, the occupation will be reduced, and it will not be 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//
Performing a function operation on an index field can break the orderliness of the index value, so the optimizer decided to abandon the tree search function. This example is implicit character encoding conversion, which uses functions on other conditional indexes, because function operations on index fields are required to result in a full index scan.
The MySQL optimizer is indeed suspected of being "lazy", even if you simply rewrite where id +1=1000 to where id=1000-1, you can use the index to quickly find, and you will not actively rewrite this statement.
Ensuring that the orderliness of index values is not destroyed on conditional indexes is a weapon for optimizing indexes.
Mo Tianlun original link: https://www.modb.pro/db/153885?sjhy (copy the link to the browser or click on the end of the article to read the original article to view)
About the author
Jiarui Chen, MySQL technical consultant of Yunhe Enmo, has MySQL OCP, PGCE, OBCA, SCDP certificates, and has long served in the telecommunications industry. He is currently responsible for the technical work of the company's MySQL database and distributed database operation and maintenance, and is passionate about learning and sharing O&M fault handling, backup and recovery, upgrade and migration, and performance optimization.