背景
最近系统上了一个大客户 该客户反馈系统使用很慢，部分页面出现需要花到20s
SELECT
id_car idCar,
id_customer idCustomer,
c.brand_id brandId,
c.factory_id factoryId,
c.series_id seriesId,
c.year_id yearId,
main_id mainId,
NAME,
sex,
customer_source customerSource,
customer_type customerType,
company_name companyName,
cell_phone cellPhone,
birthday,
car_no carNo,
car_prefix carPrefix,
vin,
factory_year factoryYear,
car_model carModel,
car_model_short carModelShort,
cm.id_own_org idOwnOrg,
cm.creationtime,
cm.creator,
cm.modifier,
cm.modifiedtime,
m.points,
m. LEVEL,
c.car_type AS carType,
c.car_category_id AS carCategoryId
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 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
INNER JOIN tm_car c ON cm.id_car = c.pk_id
AND c.is_del = 0
WHERE 1=1
and c.car_type = '0'
ORDER BY
m.pk_id
DESC
Limit 0, 10
104045_WZDe_871390.png
104052_Cwfr_871390.png
优化器选择了先走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
}
]
}
]
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`m`.`is_del` = 0) and (`m`.`pk_id` = `cm`.`id_customer`) and (`c`.`is_del` = 0) and (`cm`.`id_car` = `c`.`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))))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`tm_car` `c`",
"attached": "(`c`.`is_del` = 0)"
},
{
"table": "`tm_customer_car` `cm`",
"attached": "(`cm`.`id_customer` is not null)"
},
{
"table": "`tm_customer` `m`",
"attached": "((`m`.`is_del` = 0) 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))))"
}
]
}
},
{
"refine_plan": [
{
"table": "`tm_car` `c`"
},
{
"table": "`tm_customer_car` `cm`"
},
{
"table": "`tm_customer` `m`"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}
从上述的执行计划和trace来看对于Car全表的估算略有错误
明显一开始优化器确实选择了正确的索引
104600_B0pZ_871390.png
使用了car表ref和customer的range进行比较 发现car的ref的cost为比较低 同时对于car全表扫描后可以走到customer的ref
因此考虑尽量还是能走到索引覆盖比较好。
因此查看目前customer表做了索引id_own_org_del的索引 同时也有必然也会有二级索引跟着主键
考虑可以在car表上也建立联合索引如下 idx_id_own_org_type_del 这样大部分的请求至少可以走到index
分析一下表的数据
104623_9ayu_871390.png
在tm_car和tm_customer相对来说差不多的情况下 由于tm_customer表的平均数据size比较大因此在做全表扫描的情况下tm_customer的cost相对较高。
因此会优先选择tm_car做全表扫描【因此考虑尽量给tm_car上增加索引】
在tm_car上建立id_own_org_type_del的索引
同时改写sql如下
SELECT
id_car idCar,
id_customer idCustomer,
c.brand_id brandId,
c.factory_id factoryId,
c.series_id seriesId,
c.year_id yearId,
main_id mainId,
NAME,
sex,
customer_source customerSource,
customer_type customerType,
company_name companyName,
cell_phone cellPhone,
birthday,
car_no carNo,
car_prefix carPrefix,
vin,
factory_year factoryYear,
car_model carModel,
car_model_short carModelShort,
cm.id_own_org idOwnOrg,
cm.creationtime,
cm.creator,
cm.modifier,
cm.modifiedtime,
m.points,
m. LEVEL,
c.car_type AS carType,
c.car_category_id AS carCategoryId
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 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
INNER JOIN tm_car c ON cm.id_car = c.pk_id
AND c.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 c.car_type = 0
AND c.is_del = 0
ORDER BY
c.pk_id DESC
LIMIT 0,
10;
104659_ekP6_871390.png
104735_WCpk_871390.png
炸裂 瞬间变成0.03秒[当然这边排序使用的车辆表 大部分人的车应该是一人一车，不知是否符合业务需求？]
count 也同样的改法
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 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
INNER JOIN tm_car c ON cm.id_car = c.pk_id
and
c.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 c.car_type=0
AND c.is_del = 0
WHERE 1=1
104803_Lbsp_871390.png
104811_QuwY_871390.png
这样从在强制索引之前的20s左右就可以到现在不用强制索引 只要0.37+0.03=0.4s 大约约50倍的性能差距。
回头研究相关cost~
网友评论