mysql optimizertrace_[Mysql] 查看 OPTIMIZER_TRACE

The OPTIMIZER_TRACE table provides information produced by the optimizer tracing capability for traced statements. To enable tracking, use the optimizer_trace system variable. For details, see MySQL Internals: Tracing the Optimizer.

我以MySQL 8.0 的样板库为例演示如何开启OPTIMIZER_TRACE.

开启开关,只针对当前线程生效

SET optimizer_trace='enabled=on';

执行sql语句

select id,name,countryCode,district from city where countryCode = 'IND' order by name desc,district asc limit 5 ;

查看执行计划

explain select id,name,countryCode,district from city where countryCode = 'IND' order by name desc,district asc limit 5 ;

image.png

Using filesort 说明本次查询用到了文件排序, 虽然叫文件排序,但是不一定是在文件中执行. 这取决于排序所

需的内存和参数sort_buffer_size.

查看 OPTIMIZER_TRACE

SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`;

结果如下:

{

"steps": [

{

"join_preparation": {

"select#": 1,

"steps": [

{

"expanded_query": "/* select#1 */ select `city`.`ID` AS `id`,`city`.`Name` AS `name`,`city`.`CountryCode` AS `countryCode`,`city`.`District` AS `district` from `city` where (`city`.`CountryCode` = 'IND') order by `city`.`Name` desc,`city`.`District` limit 5"

}

]

}

},

{

"join_optimization": {

"select#": 1,

"steps": [

{

"condition_processing": {

"condition": "WHERE",

"original_condition": "(`city`.`CountryCode` = 'IND')",

"steps": [

{

"transformation": "equality_propagation",

"resulting_condition": "multiple equal('IND', `city`.`CountryCode`)"

},

{

"transformation": "constant_propagation",

"resulting_condition": "multiple equal('IND', `city`.`CountryCode`)"

},

{

"transformation": "trivial_condition_removal",

"resulting_condition": "multiple equal('IND', `city`.`CountryCode`)"

}

]

}

},

{

"substitute_generated_columns": {

}

},

{

"table_dependencies": [

{

"table": "`city`",

"row_may_be_null": false,

"map_bit": 0,

"depends_on_map_bits": [

]

}

]

},

{

"ref_optimizer_key_uses": [

{

"table": "`city`",

"field": "CountryCode",

"equals": "'IND'",

"null_rejecting": false

}

]

},

{

"rows_estimation": [

{

"table": "`city`",

"range_analysis": {

"table_scan": {

"rows": 4188,

"cost": 441.99

},

"potential_range_indexes": [

{

"index": "PRIMARY",

"usable": false,

"cause": "not_applicable"

},

{

"index": "CountryCode",

"usable": true,

"key_parts": [

"CountryCode",

"ID"

]

}

],

"setup_range_conditions": [

],

"group_index_range": {

"chosen": false,

"cause": "not_group_by_or_distinct"

},

"skip_scan_range": {

"potential_skip_scan_indexes": [

{

"index": "CountryCode",

"usable": false,

"cause": "query_references_nonkey_column"

}

]

},

"analyzing_range_alternatives": {

"range_scan_alternatives": [

{

"index": "CountryCode",

"ranges": [

"IND <= CountryCode <= IND"

],

"index_dives_for_eq_ranges": true,

"rowid_ordered": true,

"using_mrr": false,

"index_only": false,

"rows": 341,

"cost": 322.67,

"chosen": true

}

],

"analyzing_roworder_intersect": {

"usable": false,

"cause": "too_few_roworder_scans"

}

},

"chosen_range_access_summary": {

"range_access_plan": {

"type": "range_scan",

"index": "CountryCode",

"rows": 341,

"ranges": [

"IND <= CountryCode <= IND"

]

},

"rows_for_plan": 341,

"cost_for_plan": 322.67,

"chosen": true

}

}

}

]

},

{

"considered_execution_plans": [

{

"plan_prefix": [

],

"table": "`city`",

"best_access_path": {

"considered_access_paths": [

{

"access_type": "ref",

"index": "CountryCode",

"rows": 341,

"cost": 97.381,

"chosen": true

},

{

"access_type": "range",

"range_details": {

"used_index": "CountryCode"

},

"chosen": false,

"cause": "heuristic_index_cheaper"

}

]

},

"condition_filtering_pct": 100,

"rows_for_plan": 341,

"cost_for_plan": 97.381,

"chosen": true

}

]

},

{

"attaching_conditions_to_tables": {

"original_condition": "(`city`.`CountryCode` = 'IND')",

"attached_conditions_computation": [

],

"attached_conditions_summary": [

{

"table": "`city`",

"attached": "(`city`.`CountryCode` = 'IND')"

}

]

}

},

{

"optimizing_distinct_group_by_order_by": {

"simplifying_order_by": {

"original_clause": "`city`.`Name` desc,`city`.`District`",

"items": [

{

"item": "`city`.`Name`"

},

{

"item": "`city`.`District`"

}

],

"resulting_clause_is_simple": true,

"resulting_clause": "`city`.`Name` desc,`city`.`District`"

}

}

},

{

"finalizing_table_conditions": [

{

"table": "`city`",

"original_table_condition": "(`city`.`CountryCode` = 'IND')",

"final_table_condition ": null

}

]

},

{

"refine_plan": [

{

"table": "`city`"

}

]

},

{

"considering_tmp_tables": [

{

"adding_sort_to_table_in_plan_at_position": 0

}

]

}

]

}

},

{

"join_execution": {

"select#": 1,

"steps": [

{

"sorting_table_in_plan_at_position": 0,

"filesort_information": [

{

"direction": "desc",

"table": "`city`",

"field": "Name"

},

{

"direction": "asc",

"table": "`city`",

"field": "District"

}

],

"filesort_priority_queue_optimization": {

"limit": 5,

"chosen": true

},

"filesort_execution": [

],

"filesort_summary": {

"memory_available": 262144,

"key_size": 55,

"row_size": 120,

"number_of_tmp_files":2,

"max_rows_per_buffer": 6,

"num_rows_estimate": 9362,

"num_rows_found": 341,

"num_initial_chunks_spilled_to_disk": 0,

"peak_memory_used": 1188,

"sort_algorithm": "std::sort",

"unpacked_addon_fields": "using_priority_queue",

"sort_mode": ""

}

}

]

}

}

]

}

其中, filesort_summary中的number_of_tmp_files表示的是，排序过程中使用的临时文件数。

select * from performance_schema.session_statu ;

部分结果如下:

image.png