溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容主要講解“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無法走索引的問題怎么解決”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
