Optimize the execution plan through Trace Analysis

Mysql5.6 provides a trace for SQL. the trace file can be used to better understand why Execution Plan A is selected rather than execution plan B, which helps us better understand and optimize its behavior.

Usage: first, enable trace, set the format to JSON, and set the maximum memory size that can be used by trace, so as to avoid incomplete display because the default memory size is too small during the connection process.

Set optimizer_trace = "enabled = on", end_markers_in_json = on;

Set optimizer_trace_max_mem_size = 1000000;

Execute SQL statements

Select * From xuehao;

Check infomation_schema.optimizer_trace to know how MySQL executes SQL.

Mysql> select * From information_schema.optimizer_trace \ G
* *************************** 1. row ***************************
Query: Select * From xuehao
Trace :{
"Steps ":[
{
"Join_preparation ":{
"Select #": 1,
"Steps ":[
{
"Expanded_query": "/* select #1 */select 'xuehao'. 'id' as 'id' from 'xuehao '"
}
]/* Steps */
}/* Join_preparation */
},
{
"Join_optimization ":{
"Select #": 1,
"Steps ":[
{
"Table_dependencies ":[
{
"Table": "'xuehao '",
"Row_may_be_null": false,
"Map_bit": 0,
"Depends_on_map_bits ":[
]/* Depends_on_map_bits */
}
]/* Table_dependencies */
},
{
"Rows_estimation ":[
{
"Table": "'xuehao '",
"Table_scan ":{
"Rows": 8,
"Cost": 2
}/* Table_scan */
}
]/* Rows_estimation */
},
{
"Considered_execution_plans ":[
{
"Plan_prefix ":[
]/* Plan_prefix */,
"Table": "'xuehao '",
"Best_access_path ":{
"Considered_access_paths ":[
{
"Access_type": "scan ",
"Rows": 8,
"Cost": 3.6137,
"Chosen": True
}
]/* Considered_access_paths */
}/* Best_access_path */,
"Cost_for_plan": 3.6137,
"Rows_for_plan": 8,
"Chosen": True
}
]/* Considered_execution_plans */
},
{
"Attaching_conditions_to_tables ":{
"Original_condition": NULL,
"Attached_conditions_computation ":[
]/* Attached_conditions_computation */,
"Attached_conditions_summary ":[
{
"Table": "'xuehao '",
"Attached": NULL
}
]/* Attached_conditions_summary */
}/* Attaching_conditions_to_tables */
},
{
"Refine_plan ":[
{
"Table": "'xuehao '",
"Access_type": "table_scan"
}
]/* Refine_plan */
}
]/* Steps */
}/* Join_optimization */
},
{
"Join_execution ":{
"Select #": 1,
"Steps ":[
]/* Steps */
}/* Join_execution */
}
]/* Steps */
}
Missing_bytes_beyond_max_mem_size: 0
Insufficient_privileges: 0
1 row in SET (0.01 Sec)

Optimize the execution plan through Trace Analysis