mysql join on 索引_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 */

},