主要是分析《》中案例的原因，以此記錄下：

再仔細往上回顧。

在以上關聯對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無法走索引

相關文章