mysql执行计划中性能最差的是_MySQL选择的执行计划性能底下原因分析--实战案例分析...

| SELECT `id`,`t_name` FROM user.`test_tab` WHERE `token` = 'xx_8cilc73a22hm' AND `t_is_check` = 1 AND `is_delete` = 0 ORDER BY id desc LIMIT 5 |{

"steps":[{

"join_preparation": {

"select#": 1,

"steps": [

{

"expanded_query": "/* select#1 */ select `user`.`test_tab`.`id` AS `id`,`user`.`test_tab`.`t_name` AS `t_name` from `user`.`test_tab` where ((`user`.`test_tab`.`token` = 'xx_8cilc73a22hm') and (`user`.`test_tab`.`t_is_check` = 1) and (`user`.`test_tab`.`is_delete` = 0)) order by `user`.`test_tab`.`id` desc limit 5"

}]}

},

{

"join_optimization": {

"select#": 1,

"steps":[{

"condition_processing": {

"condition": "WHERE",

"original_condition": "((`user`.`test_tab`.`token` = 'xx_8cilc73a22hm') and (`user`.`test_tab`.`t_is_check` = 1) and (`user`.`test_tab`.`is_delete` = 0))",

"steps": [

{

"transformation": "equality_propagation",

"resulting_condition": "((`user`.`test_tab`.`token` = 'xx_8cilc73a22hm') and (`user`.`test_tab`.`t_is_check` = 1) and multiple equal(0, `user`.`test_tab`.`is_delete`))"

},

{

"transformation": "constant_propagation",

"resulting_condition": "((`user`.`test_tab`.`token` = 'xx_8cilc73a22hm') and (`user`.`test_tab`.`t_is_check` = 1) and multiple equal(0, `user`.`test_tab`.`is_delete`))"

},

{

"transformation": "trivial_condition_removal",

"resulting_condition": "((`user`.`test_tab`.`token` = 'xx_8cilc73a22hm') and (`user`.`test_tab`.`t_is_check` = 1) and multiple equal(0, `user`.`test_tab`.`is_delete`))"

}]}

},

{

"table_dependencies":[{

"table": "`user`.`test_tab`",

"row_may_be_null": false,

"map_bit": 0,

"depends_on_map_bits": []}

]

},

{

"ref_optimizer_key_uses":[{

"table": "`user`.`test_tab`",

"field": "token",

"equals": "'xx_8cilc73a22hm'",

"null_rejecting": false

},

{

"table": "`user`.`test_tab`",

"field": "token",

"equals": "'xx_8cilc73a22hm'",

"null_rejecting": false

}]},

{

"rows_estimation":[{

"table": "`user`.`test_tab`",

"range_analysis": {

"table_scan": {

"rows": 2597778,

"cost": 572230

},

"potential_range_indices": [

{

"index": "PRIMARY",

"usable": false, --可以看到根据范围条件选择，主键索引是false的，也就是范围条件选择主键是不好，那为啥实际为啥还使用主键呢？往下看。。。

"cause": "not_applicable"

},

{

"index": "z",

"usable": false,

"cause": "not_applicable"

},

{

"index": "t_name",

"usable": false,

"cause": "not_applicable"

},

{

"index": "token",

"usable": true, --范围查询意向选择token索引。这没有问题啊！！！再往下看

"key_parts": [

"token",

"sort",

"id"]},

{

"index": "idx_0",

"usable": true,

"key_parts":["token",

"user_id",

"is_delete",

"id"]},

{

"index": "idx_doc_time",

"usable": false,

"cause": "not_applicable"

}

],

"setup_range_conditions":[

],

"group_index_range": {

"chosen": false,

"cause": "not_group_by_or_distinct"

},

"analyzing_range_alternatives": {

"range_scan_alternatives":[{

"index": "token",

"ranges": [

"xx_8cilc73a22hm <= token <= xx_8cilc73a22hm"],

"index_dives_for_eq_ranges": true,

"rowid_ordered": false,

"using_mrr": false,

"index_only": false,

"rows":11506,

"cost":13808,

"chosen": true

},

{

"index": "idx_0",

"ranges":["xx_8cilc73a22hm <= token <= xx_8cilc73a22hm"],

"index_dives_for_eq_ranges": true,

"rowid_ordered": false,

"using_mrr": false,

"index_only": false,

"rows":10960,

"cost":13153,

"chosen": true

}

],

"analyzing_roworder_intersect": {

"usable": false,

"cause": "too_few_roworder_scans"

}

},

"chosen_range_access_summary": {

"range_access_plan": {

"type": "range_scan",

"index": "idx_0",

"rows":10960,

"ranges":["xx_8cilc73a22hm <= token <= xx_8cilc73a22hm"]},

"rows_for_plan":10960,

"cost_for_plan":13153,

"chosen": true

}

}

}

]

},

{

"considered_execution_plans":[{

"plan_prefix": [],

"table": "`user`.`test_tab`",

"best_access_path": {

"considered_access_paths":[{

"access_type": "ref",

"index": "token",

"rows": 11506,

"cost": 13807,

"chosen": true

},

{

"access_type": "ref",

"index": "idx_0",

"rows": 10960,

"cost": 13152,

"chosen": true

},

{

"access_type": "range",

"cause": "heuristic_index_cheaper",

"chosen": false

}]},

"cost_for_plan":13152,

"rows_for_plan":10960,

"chosen": true

}

]

},

{

"attaching_conditions_to_tables": {

"original_condition": "((`user`.`test_tab`.`is_delete` = 0) and (`user`.`test_tab`.`token` = 'xx_8cilc73a22hm') and (`user`.`test_tab`.`t_is_check` = 1))",

"attached_conditions_computation":[

],

"attached_conditions_summary":[{

"table": "`user`.`test_tab`",

"attached": "((`user`.`test_tab`.`is_delete` = 0) and (`user`.`test_tab`.`token` = 'xx_8cilc73a22hm') and (`user`.`test_tab`.`t_is_check` = 1))"

}]}

},

{

"clause_processing": {

"clause": "ORDER BY",

"original_clause": "`user`.`test_tab`.`id` desc",

"items":[{

"item": "`user`.`test_tab`.`id`"

}],

"resulting_clause_is_simple": true,

"resulting_clause": "`user`.`test_tab`.`id` desc"

}

},

{

"refine_plan":[{

"table": "`user`.`test_tab`",

"pushed_index_condition": "((`user`.`test_tab`.`is_delete` = 0) and (`user`.`test_tab`.`token` = 'xx_8cilc73a22hm'))",

"table_condition_attached": "(`user`.`test_tab`.`t_is_check` = 1)"

}]},

{

"added_back_ref_condition": "((`user`.`test_tab`.`token` <=> 'xx_8cilc73a22hm') and (`user`.`test_tab`.`t_is_check` = 1))"

},

{

"reconsidering_access_paths_for_index_ordering": {

"clause": "ORDER BY",

"index_order_summary": {

"table": "`user`.`test_tab`",

"index_provides_order": true, --关键来了，在判断排序的时候发现主键字段id能够排序，所以MySQL认为排序是花费很大的操作，使用这个主键字段是有序的，不用排序了，就使用它吧！！！！！我靠这不就是错了吗。虽然排序消耗时间，但是你为啥不判断下排序结果集大小啊，扫描所有数据(2865011)和排序5行(这里是排序1000多行还是5行不确定，就当mysql最优5行吧)数据哪个消耗更低?

"order_direction": "desc",

"disabled_pushed_condition_on_old_index": true,

"index": "PRIMARY",

"plan_changed": true,

"access_type": "index_scan"

}

}

}

]

}

},

{

"join_execution": {

"select#": 1,

"steps":[

]}

}

]

}