Trace is a SQL tracing tool provided after MySQL5.6. By using trace, we can understand how the optimizer (optimizer) chooses the execution plan.
Note: Turning on the trace tool will affect the performance of mysql, so it is only suitable for temporary analysis of sql, please close it immediately after use.
Syntax:
-- 1. Open the trace, set the format to JSON, and set the cache size of the trace, so as to avoid the complete trace process not being displayed due to the size of the capacity. set optimizer_trace="enabled=on", end_markers_in_JSON=on; -- 2. Execute the SQL statement that you want to trace select * from t_pay_order_info where biz_code = 'B202305220001' and biz_order_code = 'B202305220002'; -- 3, query information_schema.optimizer_trace, you can see the trace records select * from information_schema. optimizer_trace;
For example: 
Description:
Best Access Path best_access_path
Query results
{
"steps": [
{
"join_preparation": { -- The first stage: SQL preparation stage, format sql
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `t_student`.`id` AS `id`,`t_student`.`std_name` AS `std_name`,`t_student`.`age` AS `age`,` t_student`.`class_id` AS `class_id`,`t_student`.`gmt_create` AS `gmt_create` from `t_student` where (`t_student`.`std_name` > 'a') order by `t_student`.`age`"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": { -- The second stage: SQL optimization stage
"select#": 1,
"steps": [
{
"condition_processing": { -- condition processing
"condition": "WHERE",
"original_condition": "(`t_student`.`std_name` > 'a')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`t_student`.`std_name` > 'a')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`t_student`.`std_name` > 'a')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`t_student`.`std_name` > 'a')"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [ -- table dependency details
{
"table": "`t_student`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [ -- Estimated table access cost
{
"table": "`t_student`",
"range_analysis": {
"table_scan": { -- full table scan
"rows": 100300, -- number of rows
"cost": 20351 -- query cost
} /* table_scan */,
"potential_range_indexes": [ -- The possible indexes for the query
{
"index": "PRIMARY", -- primary key index
"usable": false, -- unused
"cause": "not_applicable" -- reason: not applicable
},
{
"index": "idx_std_age", -- age index
"usable": false, -- unused
"cause": "not_applicable" -- reason: not applicable
},
{
"index": "idx_std_name_age_class", -- combined index of stdname, age, class
"usable": true, -- use
"key_parts": [
"std_name",
"age",
"class_id",
"id"
] /* key_parts */
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": { -- the index used by the group
"chosen": false, -- not used
"cause": "not_group_by_or_distinct" -- reason: group by or distinct is not used
} /* group_index_range */,
"analyzing_range_alternatives": { -- Analyze the cost of using each index
"range_scan_alternatives": [
{
"index": "idx_std_name_age_class",
"ranges": [
"a < std_name" -- index usage range
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false, -- whether the records fetched by this index are sorted by primary key
"using_mrr": false,
"index_only": false, -- whether to use covering index
"rows": 50150, -- number of index scan rows
"cost": 60181, -- Index usage cost
"chosen": false, -- whether to choose the index: no
"cause": "cost" -- reason: cost
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": { -- Analyze the cost of using index merge
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [ -- Analyzed execution plans
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`t_student`",
"best_access_path": { -- best access path
"considered_access_paths": [ --Analyzed final access path
{
"rows_to_scan": 100300,
"access_type": "scan", -- access type: scan, full table scan
"resulting_rows": 100300,
"cost": 20349,
"chosen": true, -- determine the choice
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 100300,
"cost_for_plan": 20349,
"sort_cost": 100300,
"new_cost_for_plan": 120649,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": { -- add conditions for the query table
"original_condition": "(`t_student`.`std_name` > 'a')",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [ -- attached condition results
{
"table": "`t_student`",
"attached": "(`t_student`.`std_name` > 'a')"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"clause_processing": { -- order by processing
"clause": "ORDER BY",
"original_clause": "`t_student`.`age`",
"items": [
{
"item": "`t_student`.`age`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`t_student`.`age`"
} /* clause_processing */
},
{
"reconsidering_access_paths_for_index_ordering": { -- reconsidering the index processing order
"clause": "ORDER BY",
"steps": [
] /* steps */,
"index_order_summary": {
"table": "`t_student`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "unknown",
"plan_changed": false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
"refine_plan": [
{
"table": "`t_student`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": { -- Phase 3: SQL execution phase
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`t_student`",
"field": "age"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": {
"rows": 100000,
"examined_rows": 100000,
"number_of_tmp_files": 14,
"sort_buffer_size": 262016,
"sort_mode": "<sort_key, packed_additional_fields>"
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}