trace分析优化器执行计划

trace分析优化器执行计划

MySQL5.6提供了对SQL的跟踪trace, 通过trace文件能够进一步了解为什么优化器选择A计划, 而不是选择B计划。
打开trace ， 设置格式为 JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。

SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

*************************** 1. row ***************************
QUERY: select * from t_role where id > 5
TRACE: {
“steps”: [
{
“join_preparation”: {
“select#”: 1,
“steps”: [
{
“expanded_query”: "/* select#1 / select t_role.id AS id,t_role.role_name AS role_name,t_role.role_code AS role_code,t_role.description AS description from t_role where (t_role.id > 5)"
}
] / steps /
} / join_preparation /
},
{
“join_optimization”: {
“select#”: 1,
“steps”: [
{
“condition_processing”: {
“condition”: “WHERE”,
“original_condition”: “(t_role.id > 5)”,
“steps”: [
{
“transformation”: “equality_propagation”,
“resulting_condition”: “(t_role.id > 5)”
},
{
“transformation”: “constant_propagation”,
“resulting_condition”: “(t_role.id > 5)”
},
{
“transformation”: “trivial_condition_removal”,
“resulting_condition”: “(t_role.id > 5)”
}
] / steps /
} / condition_processing /
},
{
“substitute_generated_columns”: {
} / substitute_generated_columns /
},
{
“table_dependencies”: [
{
“table”: “t_role”,
“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”: [
{
“table”: “t_role”,
“range_analysis”: {
“table_scan”: {
“rows”: 5,
“cost”: 4.1
} / table_scan /,
“potential_range_indexes”: [
{
“index”: “PRIMARY”,
“usable”: true,
“key_parts”: [
“id”
] / key_parts /
},
{
“index”: “unique_role_name”,
“usable”: false,
“cause”: “not_applicable”
}
] / potential_range_indexes /,
“setup_range_conditions”: [
] / setup_range_conditions /,
“group_index_range”: {
“chosen”: false,
“cause”: “not_group_by_or_distinct”
} / group_index_range /
} / range_analysis /
}
] / rows_estimation /
},
{
“considered_execution_plans”: [
{
“plan_prefix”: [
] / plan_prefix /,
“table”: “t_role”,
“best_access_path”: {
“considered_access_paths”: [
{
“rows_to_scan”: 5,
“access_type”: “scan”,
“resulting_rows”: 5,
“cost”: 2,
“chosen”: true
}
] / considered_access_paths /
} / best_access_path /,
“condition_filtering_pct”: 100,
“rows_for_plan”: 5,
“cost_for_plan”: 2,
“chosen”: true
}
] / considered_execution_plans /
},
{
“attaching_conditions_to_tables”: {
“original_condition”: “(t_role.id > 5)”,
“attached_conditions_computation”: [
] / attached_conditions_computation /,
“attached_conditions_summary”: [
{
“table”: “t_role”,
“attached”: “(t_role.id > 5)”
}
] / attached_conditions_summary /
} / attaching_conditions_to_tables /
},
{
“refine_plan”: [
{
“table”: “t_role”
}
] / 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.00 sec)

ERROR:
No query specified