MYSQL字符集不同引起的join无法走索引的问题怎么解决

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