MYSQL字符集不同引起的join無法走索引的問題怎么解決

本篇內(nèi)容主要講解“MYSQL字符集不同引起的join無法走索引的問題怎么解決”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學(xué)習(xí)“MYSQL字符集不同引起的join無法走索引的問題怎么解決”吧!

在對sql進(jìn)行轉(zhuǎn)換時，可以發(fā)現(xiàn)了以下將s表的deptid 轉(zhuǎn)換成utf8mb4 的情況，即是說，兩個表在left join時存在關(guān)聯(lián)字段deptid 字符集不同的情況。

on((`SSS`.`d`.`Deptid` = convert(`ppp`.`s`.`Deptid` using utf8mb4))))

細(xì)查發(fā)現(xiàn)d表的deptid字段是utf8mb4，但是s表的deptid字段是utf8。

而根據(jù)經(jīng)驗，兩邊關(guān)聯(lián)時字段字符集不一確實會導(dǎo)致無法走索引，因為這里是發(fā)生了隱式轉(zhuǎn)換了。此時s表上的索引便無法生效。

此時我有個疑問是當(dāng)以上將條件d.DEPTID = '00001111' 換成s.DEPTID = '00001111',其可以選擇了索引，當(dāng)此時字符集不同的情況仍然存在，發(fā)現(xiàn)以上選擇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"

                        },

在修改字符集統(tǒng)一為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無法走索引的問題怎么解決”有了更深的了解，不妨來實際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！