In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-03-31 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
The main content of this article is to explain "how to solve the problem that join can not go to the index caused by different MYSQL character sets", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to solve the problem that join can't index caused by different MYSQL character sets.
During the conversion of sql, you can find the following cases where the deptid of the s table is converted to utf8mb4, that is, there are different deptid character sets of the associated fields between the two tables in left join.
On ((`SSS`.`d`.`Deptid` = convert (`ppp`.`s`.`Deptid` using utf8mb4)
Close inspection shows that the deptid field of table d is utf8mb4, but the deptid field of table s is utf8.
As a rule of thumb, different character sets of fields when associated on both sides do cause indexing to be impossible, because there is an implicit conversion. At this point, the index on the s table cannot take effect.
At this point, I have a doubt that when the condition d.DEPTID = '00001111' is replaced by s.DEPTID = '00001111', it can select the index, while different character sets still exist, it is found that the reason for choosing the primary key of the d table above is clustered_pk_chosen_by_heuristics. Because it's the primary key? This is not very understandable.
"considered_access_paths": [
{
"access_type": "eq_ref"
"index": "PRIMARY"
"rows": 1
"cost": 1.2
"chosen": true
"cause": "clustered_pk_chosen_by_heuristics"
}
It will be normal after changing the character set to utf8mb4:
+- +-+-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+ | 1 | SIMPLE | d | NULL | const | PRIMARY INDEX_DEPARTMENT_5 | PRIMARY | 1 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | s | NULL | ref | IND_SHOP_DEPTID | 131c | const | 1 | 100.00 | NULL | + -+-+ "ref_optimizer_key_uses": [ {"table": "`sss`.`department``d`" "field": "Deptid", "equals": "00001111", "null_rejecting": false}, {"table": "`sss`.`department``d`", "field": "Deptid", "equals": "000011'" "null_rejecting": false}, {"table": "`ppp`.`shop``s`", "field": "Deptid", "equals": "`sss`.`d`.`Deptid`" "null_rejecting": false}] / * ref_optimizer_key_uses * /}, {"rows_estimation": [{"table": "`sss`.`department``d`", "rows": 1, "cost": 1 "table_type": "const", "empty": false}, {"table": "`ppp`.`shop``s`", "range_analysis": {"table_scan": {"rows": 998690 "cost": 207849} / * table_scan * /, "potential_range_indexes": [{"index": "PRIMARY", "usable": false "cause": "not_applicable"}, {"index": "IND_SHOP_DEPTID", "usable": true, "key_parts": ["Deptid" "Shopid"] / * key_parts * /}, {"index": "IND_SHOP_DOMAIN", "usable": false "cause": "not_applicable"}] / * potential_range_indexes * /, "setup_range_conditions": [] / * setup_range_conditions * /, "group_index_range": {"chosen": false "cause": "not_single_table"} / * group_index_range * /, "analyzing_range_alternatives": {"range_scan_alternatives": [{"index": "IND_SHOP_DEPTID" "ranges": ["00001111
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.