MYSQL 字符集不同引起的join无法走索引

主要是分析《 MYSQL sql执行过程的一些跟踪分析(二.mysql优化器追踪分析) 》中案例的原因，以此记录下：

再仔细往上回顾。

在以上关联对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 */
          },

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29863023/viewspace-2565308/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29863023/viewspace-2565308/