本篇内容主要讲解“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无法走索引的问题怎么解决”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！