MYSQL字符集不同引起的join无法走索引的问题怎么解决
发布时间：2022-01-18 14:03:43 所属栏目：MySql教程 来源：互联网
导读：本篇内容主要讲解MYSQL字符集不同引起的join无法走索引的问题怎么解决，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习MYSQL字符集不同引起的join无法走索引的问题怎么解决吧! 在对sql进行转换时，可以发现了以下
|
本篇内容主要讲解“MYSQL字符集不同引起的join无法走索引的问题怎么解决”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MYSQL字符集不同引起的join无法走索引的问题怎么解决”吧! 在对sql进行转换时，可以发现了以下将s表的deptid 转换成utf8mb4 的情况，即是说，两个表在left join时存在关联字段deptid 字符集不同的情况。 on((`SSS`.`d`.`Deptid` = convert(`ppp`.`s`.`Deptid` using utf8mb4)))) 细查发现d表的deptid字段是utf8mb4，但是s表的deptid字段是utf8。 而根据经验，两边关联时字段字符集不一确实会导致无法走索引，因为这里是发生了隐式转换了。此时s表上的索引便无法生效。 此时我有个疑问是当以上将条件d.DEPTID = '00001111' 换成s.DEPTID = '00001111',其可以选择了索引，当此时字符集不同的情况仍然存在，发现以上选择d表的主键的原因是clustered_pk_chosen_by_heuristics。因为是主键而选择？这个便不是很理解了 "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 1.2, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, 在修改字符集统一为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 | 130 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | s | NULL | ref | IND_SHOP_DEPTID | IND_SHOP_DEPTID | 131 | 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": "'00001111'", "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 <= Deptid <= 00001111" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 1, "cost": 2.21, "chosen": true } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "IND_SHOP_DEPTID", "rows": 1, "ranges": [ "00001111 <= Deptid <= 00001111" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 1, "cost_for_plan": 2.21, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ "`sss`.`department` `d`" ] /* plan_prefix */, "table": "`ppp`.`shop` `s`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "IND_SHOP_DEPTID", "rows": 1, "cost": 1.2, "chosen": true }, { "access_type": "range", "range_details": { "used_index": "IND_SHOP_DEPTID" } /* range_details */, "chosen": false, "cause": "heuristic_index_cheaper" } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 1, "cost_for_plan": 1.2, "chosen": true } ] /* considered_execution_plans */ }, 到此，相信大家对“MYSQL字符集不同引起的join无法走索引的问题怎么解决”有了更深的了解，不妨来实际操作一番吧！ （编辑：站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
