104052_Cwfr_871390.png
[聚合文章] MySql优化器的bug？
优化器选择了先走C表的全表扫描【而m表上有id_own_org的索引】
假设我们使用强制索引
104205_xHsI_871390.png
基本耗时在0.23s左右效率相差数十倍
104238_CUIm_871390.png
执行计划明显走了m的id_own_org索引。那么很明显
其实该表的索引区分度并不高
104254_LDaO_871390.png
由于我们是SAAS系统 那么id_own_org作为每一家门店的标志想必也是应该作为高频查询条件。
但是为何这边没有走到索引呢？
分析
首先了解一下业务 由于系统中同一个公司不同门店下面的车辆是通用的，所以存在如下问题 一开始做数据系统迁移时系统中大量导入 比如某个公司大约有20家门店【所有的车辆投在同一个门店内导入】那么假设存在8w两车辆那么这8w辆车基本全部存在某个对应的id_own_org下。
因此考虑是否可能是id_own_org分布不均导致出现走的全表扫描呢？
首先找到某个主门店 同时去除对应门店使用如下查询
104402_NLZs_871390.png
果然此时就已经走到了正确的索引
为了证明猜测的正确性 继续如下执行如下Sql
select count(*) from tm_customer_car cm INNER JOIN tm_customer m -- force index(IDX_ID_OWN_ORG_DEL) ON cm.id_customer = m.pk_id AND m.id_own_org =10545511425563128134 AND m.is_del = 0 INNER JOIN tm_car c ON cm.id_car = c.pk_id AND c.is_del = 0 WHERE 1=1我们考虑数据分布很不均衡 比如
select count(*),id_own_org from tm_customer group by id_own_org order by count(*) desc
104513_2ycj_871390.png
在数据严重分布不均匀的情况下 可能导致使用索引时
考虑开启trace工具得到结果如下
{ "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "IN_uses_bisection": true }, { "expanded_query": "/* select#1 */ select count(0) AS `count(*)` from ((`tm_customer_car` `cm` join `tm_customer` `m` on(((`cm`.`id_customer` = `m`.`pk_id`) and (`m`.`id_own_org` in (cast('10545511425563138989' as unsigned),cast('10545511425563140093' as unsigned),cast('10545511425563140621' as unsigned),cast('10545511425563141565' as unsigned),cast('10545511425563139362' as unsigned),cast('10545511425563136430' as unsigned),cast('10545511425563138387' as unsigned),cast('10545511425563137867' as unsigned),cast('10545511425563139144' as unsigned),cast('10545511425563139602' as unsigned),cast('10545511425565292623' as unsigned),cast('10545511425563128134' as unsigned),cast('10545511425563140397' as unsigned),cast('10545511425563137692' as unsigned),cast('10545511425563140988' as unsigned),cast('10545511425563138546' as unsigned),cast('10545511425563139830' as unsigned),cast('10545511425563136673' as unsigned),cast('10545511425563138073' as unsigned),cast('10545511425563137152' as unsigned),cast('10545511425563138802' as unsigned),cast('10545511425563137450' as unsigned),cast('10545511425563135631' as unsigned),cast('10545511425563136936' as unsigned))) and (`m`.`is_del` = 0)))) join `tm_car` `c` on(((`cm`.`id_car` = `c`.`pk_id`) and (`c`.`is_del` = 0)))) where (1 = 1)" }, { "transformations_to_nested_joins": { "transformations": [ "JOIN_condition_to_WHERE", "parenthesis_removal" ], "expanded_query": "/* select#1 */ select count(0) AS `count(*)` from `tm_customer_car` `cm` join `tm_customer` `m` join `tm_car` `c` where ((1 = 1) and (`cm`.`id_car` = `c`.`pk_id`) and (`c`.`is_del` = 0) and (`cm`.`id_customer` = `m`.`pk_id`) and (`m`.`id_own_org` in (cast('10545511425563138989' as unsigned),cast('10545511425563140093' as unsigned),cast('10545511425563140621' as unsigned),cast('10545511425563141565' as unsigned),cast('10545511425563139362' as unsigned),cast('10545511425563136430' as unsigned),cast('10545511425563138387' as unsigned),cast('10545511425563137867' as unsigned),cast('10545511425563139144' as unsigned),cast('10545511425563139602' as unsigned),cast('10545511425565292623' as unsigned),cast('10545511425563128134' as unsigned),cast('10545511425563140397' as unsigned),cast('10545511425563137692' as unsigned),cast('10545511425563140988' as unsigned),cast('10545511425563138546' as unsigned),cast('10545511425563139830' as unsigned),cast('10545511425563136673' as unsigned),cast('10545511425563138073' as unsigned),cast('10545511425563137152' as unsigned),cast('10545511425563138802' as unsigned),cast('10545511425563137450' as unsigned),cast('10545511425563135631' as unsigned),cast('10545511425563136936' as unsigned))) and (`m`.`is_del` = 0))" } } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((1 = 1) and (`cm`.`id_car` = `c`.`pk_id`) and (`c`.`is_del` = 0) and (`cm`.`id_customer` = `m`.`pk_id`) and (`m`.`id_own_org` in (cast('10545511425563138989' as unsigned),cast('10545511425563140093' as unsigned),cast('10545511425563140621' as unsigned),cast('10545511425563141565' as unsigned),cast('10545511425563139362' as unsigned),cast('10545511425563136430' as unsigned),cast('10545511425563138387' as unsigned),cast('10545511425563137867' as unsigned),cast('10545511425563139144' as unsigned),cast('10545511425563139602' as unsigned),cast('10545511425565292623' as unsigned),cast('10545511425563128134' as unsigned),cast('10545511425563140397' as unsigned),cast('10545511425563137692' as unsigned),cast('10545511425563140988' as unsigned),cast('10545511425563138546' as unsigned),cast('10545511425563139830' as unsigned),cast('10545511425563136673' as unsigned),cast('10545511425563138073' as unsigned),cast('10545511425563137152' as unsigned),cast('10545511425563138802' as unsigned),cast('10545511425563137450' as unsigned),cast('10545511425563135631' as unsigned),cast('10545511425563136936' as unsigned))) and (`m`.`is_del` = 0))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((1 = 1) and (`m`.`id_own_org` in (cast('10545511425563138989' as unsigned),cast('10545511425563140093' as unsigned),cast('10545511425563140621' as unsigned),cast('10545511425563141565' as unsigned),cast('10545511425563139362' as unsigned),cast('10545511425563136430' as unsigned),cast('10545511425563138387' as unsigned),cast('10545511425563137867' as unsigned),cast('10545511425563139144' as unsigned),cast('10545511425563139602' as unsigned),cast('10545511425565292623' as unsigned),cast('10545511425563128134' as unsigned),cast('10545511425563140397' as unsigned),cast('10545511425563137692' as unsigned),cast('10545511425563140988' as unsigned),cast('10545511425563138546' as unsigned),cast('10545511425563139830' as unsigned),cast('10545511425563136673' as unsigned),cast('10545511425563138073' as unsigned),cast('10545511425563137152' as unsigned),cast('10545511425563138802' as unsigned),cast('10545511425563137450' as unsigned),cast('10545511425563135631' as unsigned),cast('10545511425563136936' as unsigned))) and multiple equal(`cm`.`id_car`, `c`.`pk_id`) and multiple equal(0, `c`.`is_del`) and multiple equal(`cm`.`id_customer`, `m`.`pk_id`) and multiple equal(0, `m`.`is_del`))" }, { "transformation": "constant_propagation", "resulting_condition": "((1 = 1) and (`m`.`id_own_org` in (cast('10545511425563138989' as unsigned),cast('10545511425563140093' as unsigned),cast('10545511425563140621' as unsigned),cast('10545511425563141565' as unsigned),cast('10545511425563139362' as unsigned),cast('10545511425563136430' as unsigned),cast('10545511425563138387' as unsigned),cast('10545511425563137867' as unsigned),cast('10545511425563139144' as unsigned),cast('10545511425563139602' as unsigned),cast('10545511425565292623' as unsigned),cast('10545511425563128134' as unsigned),cast('10545511425563140397' as unsigned),cast('10545511425563137692' as unsigned),cast('10545511425563140988' as unsigned),cast('10545511425563138546' as unsigned),cast('10545511425563139830' as unsigned),cast('10545511425563136673' as unsigned),cast('10545511425563138073' as unsigned),cast('10545511425563137152' as unsigned),cast('10545511425563138802' as unsigned),cast('10545511425563137450' as unsigned),cast('10545511425563135631' as unsigned),cast('10545511425563136936' as unsigned))) and multiple equal(`cm`.`id_car`, `c`.`pk_id`) and multiple equal(0, `c`.`is_del`) and multiple equal(`cm`.`id_customer`, `m`.`pk_id`) and multiple equal(0, `m`.`is_del`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`m`.`id_own_org` in (cast('10545511425563138989' as unsigned),cast('10545511425563140093' as unsigned),cast('10545511425563140621' as unsigned),cast('10545511425563141565' as unsigned),cast('10545511425563139362' as unsigned),cast('10545511425563136430' as unsigned),cast('10545511425563138387' as unsigned),cast('10545511425563137867' as unsigned),cast('10545511425563139144' as unsigned),cast('10545511425563139602' as unsigned),cast('10545511425565292623' as unsigned),cast('10545511425563128134' as unsigned),cast('10545511425563140397' as unsigned),cast('10545511425563137692' as unsigned),cast('10545511425563140988' as unsigned),cast('10545511425563138546' as unsigned),cast('10545511425563139830' as unsigned),cast('10545511425563136673' as unsigned),cast('10545511425563138073' as unsigned),cast('10545511425563137152' as unsigned),cast('10545511425563138802' as unsigned),cast('10545511425563137450' as unsigned),cast('10545511425563135631' as unsigned),cast('10545511425563136936' as unsigned))) and multiple equal(`cm`.`id_car`, `c`.`pk_id`) and multiple equal(0, `c`.`is_del`) and multiple equal(`cm`.`id_customer`, `m`.`pk_id`) and multiple equal(0, `m`.`is_del`))" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`tm_customer_car` `cm`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] }, { "table": "`tm_customer` `m`", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [ ] }, { "table": "`tm_car` `c`", "row_may_be_null": false, "map_bit": 2, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`tm_customer_car` `cm`", "field": "id_customer", "equals": "`m`.`pk_id`", "null_rejecting": false }, { "table": "`tm_customer_car` `cm`", "field": "id_car", "equals": "`c`.`pk_id`", "null_rejecting": false }, { "table": "`tm_customer` `m`", "field": "pk_id", "equals": "`cm`.`id_customer`", "null_rejecting": true }, { "table": "`tm_car` `c`", "field": "pk_id", "equals": "`cm`.`id_car`", "null_rejecting": true } ] }, { "rows_estimation": [ { "table": "`tm_customer_car` `cm`", "table_scan": { "rows": 530994, "cost": 2329 } }, { "table": "`tm_customer` `m`", "range_analysis": { "table_scan": { "rows": 596337, "cost": 128535 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "IDX_LEVEL", "usable": false, "cause": "not_applicable" }, { "index": "IDX_ID_OWN_ORG_DEL", "usable": true, "key_parts": [ "id_own_org", "is_del", "pk_id" ] } ], "best_covering_index_scan": { "index": "IDX_ID_OWN_ORG_DEL", "cost": 120649, "chosen": true }, "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_single_table" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "IDX_ID_OWN_ORG_DEL", "ranges": [ "10545511425563128134 <= id_own_org <= 10545511425563128134 AND 0 <= is_del <= 0", "10545511425563135631 <= id_own_org <= 10545511425563135631 AND 0 <= is_del <= 0", "10545511425563136430 <= id_own_org <= 10545511425563136430 AND 0 <= is_del <= 0", "10545511425563136673 <= id_own_org <= 10545511425563136673 AND 0 <= is_del <= 0", "10545511425563136936 <= id_own_org <= 10545511425563136936 AND 0 <= is_del <= 0", "10545511425563137152 <= id_own_org <= 10545511425563137152 AND 0 <= is_del <= 0", "10545511425563137450 <= id_own_org <= 10545511425563137450 AND 0 <= is_del <= 0", "10545511425563137692 <= id_own_org <= 10545511425563137692 AND 0 <= is_del <= 0", "10545511425563137867 <= id_own_org <= 10545511425563137867 AND 0 <= is_del <= 0", "10545511425563138073 <= id_own_org <= 10545511425563138073 AND 0 <= is_del <= 0", "10545511425563138387 <= id_own_org <= 10545511425563138387 AND 0 <= is_del <= 0", "10545511425563138546 <= id_own_org <= 10545511425563138546 AND 0 <= is_del <= 0", "10545511425563138802 <= id_own_org <= 10545511425563138802 AND 0 <= is_del <= 0", "10545511425563138989 <= id_own_org <= 10545511425563138989 AND 0 <= is_del <= 0", "10545511425563139144 <= id_own_org <= 10545511425563139144 AND 0 <= is_del <= 0", "10545511425563139362 <= id_own_org <= 10545511425563139362 AND 0 <= is_del <= 0", "10545511425563139602 <= id_own_org <= 10545511425563139602 AND 0 <= is_del <= 0", "10545511425563139830 <= id_own_org <= 10545511425563139830 AND 0 <= is_del <= 0", "10545511425563140093 <= id_own_org <= 10545511425563140093 AND 0 <= is_del <= 0", "10545511425563140397 <= id_own_org <= 10545511425563140397 AND 0 <= is_del <= 0", "10545511425563140621 <= id_own_org <= 10545511425563140621 AND 0 <= is_del <= 0", "10545511425563140988 <= id_own_org <= 10545511425563140988 AND 0 <= is_del <= 0", "10545511425563141565 <= id_own_org <= 10545511425563141565 AND 0 <= is_del <= 0", "10545511425565292623 <= id_own_org <= 10545511425565292623 AND 0 <= is_del <= 0" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 135777, "cost": 27471, "chosen": true } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "IDX_ID_OWN_ORG_DEL", "rows": 135777, "ranges": [ "10545511425563128134 <= id_own_org <= 10545511425563128134 AND 0 <= is_del <= 0", "10545511425563135631 <= id_own_org <= 10545511425563135631 AND 0 <= is_del <= 0", "10545511425563136430 <= id_own_org <= 10545511425563136430 AND 0 <= is_del <= 0", "10545511425563136673 <= id_own_org <= 10545511425563136673 AND 0 <= is_del <= 0", "10545511425563136936 <= id_own_org <= 10545511425563136936 AND 0 <= is_del <= 0", "10545511425563137152 <= id_own_org <= 10545511425563137152 AND 0 <= is_del <= 0", "10545511425563137450 <= id_own_org <= 10545511425563137450 AND 0 <= is_del <= 0", "10545511425563137692 <= id_own_org <= 10545511425563137692 AND 0 <= is_del <= 0", "10545511425563137867 <= id_own_org <= 10545511425563137867 AND 0 <= is_del <= 0", "10545511425563138073 <= id_own_org <= 10545511425563138073 AND 0 <= is_del <= 0", "10545511425563138387 <= id_own_org <= 10545511425563138387 AND 0 <= is_del <= 0", "10545511425563138546 <= id_own_org <= 10545511425563138546 AND 0 <= is_del <= 0", "10545511425563138802 <= id_own_org <= 10545511425563138802 AND 0 <= is_del <= 0", "10545511425563138989 <= id_own_org <= 10545511425563138989 AND 0 <= is_del <= 0", "10545511425563139144 <= id_own_org <= 10545511425563139144 AND 0 <= is_del <= 0", "10545511425563139362 <= id_own_org <= 10545511425563139362 AND 0 <= is_del <= 0", "10545511425563139602 <= id_own_org <= 10545511425563139602 AND 0 <= is_del <= 0", "10545511425563139830 <= id_own_org <= 10545511425563139830 AND 0 <= is_del <= 0", "10545511425563140093 <= id_own_org <= 10545511425563140093 AND 0 <= is_del <= 0", "10545511425563140397 <= id_own_org <= 10545511425563140397 AND 0 <= is_del <= 0", "10545511425563140621 <= id_own_org <= 10545511425563140621 AND 0 <= is_del <= 0", "10545511425563140988 <= id_own_org <= 10545511425563140988 AND 0 <= is_del <= 0", "10545511425563141565 <= id_own_org <= 10545511425563141565 AND 0 <= is_del <= 0", "10545511425565292623 <= id_own_org <= 10545511425565292623 AND 0 <= is_del <= 0" ] }, "rows_for_plan": 135777, "cost_for_plan": 27471, "chosen": true } } }, { "table": "`tm_car` `c`", "table_scan": { "rows": 694802, "cost": 7659 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`tm_customer` `m`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "rows_to_scan": 135777, "access_type": "range", "range_details": { "used_index": "IDX_ID_OWN_ORG_DEL" }, "resulting_rows": 135777, "cost": 54626, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 135777, "cost_for_plan": 54626, "rest_of_plan": [ { "plan_prefix": [ "`tm_customer` `m`" ], "table": "`tm_customer_car` `cm`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "INDEX_ID_CUSTOMER", "rows": 1.2508, "cost": 203789, "chosen": true }, { "access_type": "ref", "index": "INDEX_ID_CAR", "usable": false, "chosen": false }, { "rows_to_scan": 530994, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 530994, "cost": 1.4e10, "chosen": false } ] }, "condition_filtering_pct": 100, "rows_for_plan": 169825, "cost_for_plan": 258416, "rest_of_plan": [ { "plan_prefix": [ "`tm_customer` `m`", "`tm_customer_car` `cm`" ], "table": "`tm_car` `c`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 203789, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "condition_filtering_pct": 100, "rows_for_plan": 169825, "cost_for_plan": 462205, "chosen": true } ] }, { "plan_prefix": [ "`tm_customer` `m`" ], "table": "`tm_car` `c`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "rows_to_scan": 694802, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 69480, "cost": 1.89e9, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 9.43e9, "cost_for_plan": 1.89e9, "pruned_by_cost": true } ] }, { "plan_prefix": [ ], "table": "`tm_customer_car` `cm`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "INDEX_ID_CUSTOMER", "usable": false, "chosen": false }, { "access_type": "ref", "index": "INDEX_ID_CAR", "usable": false, "chosen": false }, { "rows_to_scan": 530994, "access_type": "scan", "resulting_rows": 530994, "cost": 108528, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 530994, "cost_for_plan": 108528, "rest_of_plan": [ { "plan_prefix": [ "`tm_customer_car` `cm`" ], "table": "`tm_customer` `m`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 637193, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "rows_to_scan": 135777, "access_type": "range", "range_details": { "used_index": "IDX_ID_OWN_ORG_DEL" }, "resulting_rows": 135777, "cost": 2.9e10, "chosen": false } ] }, "condition_filtering_pct": 22.769, "rows_for_plan": 120899, "cost_for_plan": 745721, "pruned_by_cost": true }, { "plan_prefix": [ "`tm_customer_car` `cm`" ], "table": "`tm_car` `c`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 637193, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "access_type": "scan", "chosen": false, "cause": "covering_index_better_than_full_scan" } ] }, "condition_filtering_pct": 10, "rows_for_plan": 53099, "cost_for_plan": 745721, "pruned_by_cost": true } ] }, { "plan_prefix": [ ], "table": "`tm_car` `c`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "rows_to_scan": 694802, "access_type": "scan", "resulting_rows": 69480, "cost": 146619, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 69480, "cost_for_plan": 146619, "rest_of_plan": [ { "plan_prefix": [ "`tm_car` `c`" ], "table": "`tm_customer` `m`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "PRIMARY", "usable": false, "chosen": false }, { "rows_to_scan": 135777, "access_type": "range", "range_details": { "used_index": "IDX_ID_OWN_ORG_DEL" }, "resulting_rows": 135777, "cost": 3.8e9, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 9.43e9, "cost_for_plan": 3.8e9, "pruned_by_cost": true }, { "plan_prefix": [ "`tm_car` `c`" ], "table": "`tm_customer_car` `cm`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "INDEX_ID_CUSTOMER", "usable": false, "chosen": false }, { "access_type": "ref", "index": "INDEX_ID_CAR", "rows": 1, "cost": 83376, "chosen": true }, { "rows_to_scan": 530994, "access_type": "scan", "using_join_cache": true, "buffers_needed": 1, "resulting_rows": 530994, "cost": 7.38e9, "chosen": false } ] }, "condition_filtering_pct": 100, "rows_for_plan": 69480, "cost_for_plan": 229996, "rest_of_plan": [ { "plan_prefix": [ "`tm_car` `c`", "`tm_customer_car` `cm`" ], "table": "`tm_customer` `m`", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 83376, "chosen": true, "cause": "clustered_pk_chosen_by_heuristics" }, { "rows_to_scan": 135777, "access_type": "range", "range_details": { "used_index": "IDX_ID_OWN_ORG_DEL" }, "resulting_rows": 135777, "cost": 3.8e9, "chosen": false } ] }, "added_to_eq_ref_extension": true, "condition_filtering_pct": 100, "rows_for_plan": 69480, "cost_for_plan": 313372, "chosen": true
注：本文内容来自互联网，旨在为开发者提供分享、交流的平台。如有涉及文章版权等事宜，请你联系站长进行处理。