本篇內容主要講解“MySQL字符集不同引起的join無法走索引的問題怎么解決”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“MYSQL字符集不同引起的join無法走索引的問題怎么解決”吧!
公司主營業(yè)務：成都網(wǎng)站制作、成都網(wǎng)站建設、移動網(wǎng)站開發(fā)等業(yè)務。幫助企業(yè)客戶真正實現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競爭能力。成都創(chuàng)新互聯(lián)公司是一支青春激揚、勤奮敬業(yè)、活力青春激揚、勤奮敬業(yè)、活力澎湃、和諧高效的團隊。公司秉承以“開放、自由、嚴謹、自律”為核心的企業(yè)文化，感謝他們對我們的高要求，感謝他們從不同領域給我們帶來的挑戰(zhàn)，讓我們激情的團隊有機會用頭腦與智慧不斷的給客戶帶來驚喜。成都創(chuàng)新互聯(lián)公司推出凌源免費做網(wǎng)站回饋大家。
在對sql進行轉換時，可以發(fā)現(xiàn)了以下將s表的deptid 轉換成utf8mb4 的情況，即是說，兩個表在left join時存在關聯(lián)字段deptid 字符集不同的情況。
on((`SSS`.`d`.`Deptid` = convert(`ppp`.`s`.`Deptid` using utf8mb4))))
細查發(fā)現(xiàn)d表的deptid字段是utf8mb4，但是s表的deptid字段是utf8。
而根據(jù)經(jīng)驗，兩邊關聯(lián)時字段字符集不一確實會導致無法走索引，因為這里是發(fā)生了隱式轉換了。此時s表上的索引便無法生效。
此時我有個疑問是當以上將條件d.DEPTID = '00001111' 換成s.DEPTID = '00001111',其可以選擇了索引，當此時字符集不同的情況仍然存在，發(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無法走索引的問題怎么解決”有了更深的了解，不妨來實際操作一番吧！這里是創(chuàng)新互聯(lián)網(wǎng)站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續(xù)學習！
