Mysql之trace分析SQL优化器（trace分析SQL优化器介绍、使⽤trace进⾏。。
。
建表
CREATE TABLE`test_trace`(
`id`INT(11)NOT NULL AUTO_INCREMENT,
`a`INT(11)DEFAULT NULL,
`b`INT(11)DEFAULT NULL,
`create_time`DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'记录创建时间',
`update_time`DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT'记录更新时间', PRIMARY KEY(`id`),
KEY`idx_a`(`a`),
KEY`idx_b`(`b`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
trace分析SQL优化器介绍
从前⾯学到了 explain 可以查看 SQL 执⾏计划，但是⽆法知道它为什么做这个决策，如果想确定多种索引⽅案之间是如何选择的或者排序时选择的是哪种排序模式，有什么好的办法吗？
从 MySQL 5.6 开始，可以使⽤ trace 查看优化器如何选择执⾏计划。
通过trace，能够进⼀步了解为什么优化器选择A执⾏计划⽽不是选择B执⾏计划，或者知道某个排序使⽤的排序模式，帮助我们更好地理解优化器⾏为。
如果需要使⽤，先开启 trace，设置格式为 JSON，再执⾏需要分析的 SQL，最后查看 trace 分析结果（在
information_schema.OPTIMIZER_TRACE 中）。
开启该功能，会对 MySQL 性能有所影响，因此只建议分析问题时临时开启。
下⾯⼀起来看下 trace 的使⽤⽅法。使⽤讲解 explain 时创建的表test_trace做实验。
⾸先构造如下 SQL (表⽰取出表 t1 中 a 的值⼤于 900 并且 b 的值⼤于 910 的数据，然后按照 a 字段排序)：
select*from test_trace where a >900and b >910order by a;
使⽤ trace 进⾏分析
set session optimizer_trace="enabled=on",end_markers_in_json=on;
optimizer_trace=“enabled=on” 表⽰开启trace
end_markers_in_json=on 表⽰ JSON 输出开启结束标记
查看trace分析结果
SELECT*FROM information_schema.OPTIMIZER_TRACE\G
QUERY: select*from t1 where a >900and b >910order by a    --SQL语句
TRACE: {
"steps": [
{
"join_preparation": {      --SQL准备阶段
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `t1`.`id` AS `id`,`t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`create_time` AS `create_time`,`t1`.`update_time` AS `upd ate_time` from `t1` where ((`t1`.`a` > 900) and (`t1`.`b` > 910)) order by `t1`.`a`"
}
]/* steps */
} /* join_preparation */
},
{
"join_optimization": {      --SQL优化阶段
"select#": 1,
"steps": [
{
"condition_processing": {    --条件处理
"condition": "WHERE",
"original_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))",--原始条件
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"--等值传递转换
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"--常量传递转换
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"--去除没有的条件后的结构                }
]/* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */--替换虚拟⽣成列
},
{
"table_dependencies": [--表依赖详情
{
"table": "`t1`",
"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": "`t1`",
"range_analysis": {
"table_scan": {
"rows": 1000,--扫描⾏数
"cost": 207.1--成本
} /* table_scan */,
"potential_range_indexes": [--分析可能使⽤的索引
{
"index": "PRIMARY",
"usable": false,--为false，说明主键索引不可⽤
"cause": "not_applicable"
},
{
"index": "idx_a",--可能使⽤索引idx_a
"usable": true,
"key_parts": [
"a",
"id"
]/* key_parts */
},
{
"index": "idx_b",--可能使⽤索引idx_b
"usable": true,
"key_parts": [
"b",
"id"
]/* key_parts */
}
]/* potential_range_indexes */,
"setup_range_conditions": [
]/* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"analyzing_range_alternatives": { --分析各索引的成本
"range_scan_alternatives": [
{
"index": "idx_a",--使⽤索引idx_a的成本
"ranges": [
"900 < a"--使⽤索引idx_a的范围
]/* ranges */,
"index_dives_for_eq_ranges": true,--是否使⽤index dive（详细描述请看下⽅的知识扩展）
"rowid_ordered": false,--使⽤该索引获取的记录是否按照主键排序
"using_mrr": false,--是否使⽤mrr
"index_only": false,--是否使⽤覆盖索引
"rows": 100,--使⽤该索引获取的记录数
"cost": 121.01,--使⽤该索引的成本
"chosen": true--可能选择该索引
},
{
"index": "idx_b",--使⽤索引idx_b的成本
"ranges": [
"910 < b"
]
/* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 90,
"cost": 109.01,
"chosen": true--也可能选择该索引
}
]/* range_scan_alternatives */,
"analyzing_roworder_intersect": { --分析使⽤索引合并的成本
数据库优化sql语句
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": {  --确认最优⽅法
"range_access_plan": {
"type": "range_scan",
"index": "idx_b",
"rows": 90,
"ranges": [
"910 < b"
]
/* ranges */
} /* range_access_plan */,
"rows_for_plan": 90,
"cost_for_plan": 109.01,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
]/* rows_estimation */
},
},
{
"considered_execution_plans": [--考虑的执⾏计划
{
"plan_prefix": [
]/* plan_prefix */,
"table": "`t1`",
"best_access_path": {          --最优的访问路径
"considered_access_paths": [--决定的访问路径
{
"rows_to_scan": 90,--扫描的⾏数
"access_type": "range",--访问类型：为range
"range_details": {
"used_index": "idx_b"--使⽤的索引为：idx_b
} /* range_details */,
"resulting_rows": 90,--结果⾏数
"cost": 127.01,--成本
"chosen": true,--确定选择
"use_tmp_table": true
}
]/* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 90,
"cost_for_plan": 127.01,
"sort_cost": 90,
"new_cost_for_plan": 217.01,
"chosen": true
}
]/* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {  --尝试添加⼀些其他的查询条件
"original_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))",
"attached_conditions_computation": [
]/* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`t1`",
"attached": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"
}
]/* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`t1`.`a`",
"items": [
{
"item": "`t1`.`a`"
}
]/* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`t1`.`a`"
} /* clause_processing */
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"index_order_summary": {
"table": "`t1`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "idx_b",
"plan_changed": false
"plan_changed": false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
"refine_plan": [--改进的执⾏计划
{
"table": "`t1`",
"pushed_index_condition": "(`t1`.`b` > 910)",
"table_condition_attached": "(`t1`.`a` > 900)"
}
]/* refine_plan */
}
]/* steps */
} /* join_optimization */
},
{
"join_execution": {            --SQL执⾏阶段
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`t1`",
"field": "a"
}
]/* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,--未使⽤优先队列优化排序
"cause": "not applicable (no LIMIT)"--未使⽤优先队列排序的原因是没有limit
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
]/* filesort_execution */,
"filesort_summary": {          --排序详情
"rows": 90,
"examined_rows": 90,--参与排序的⾏数
"number_of_tmp_files": 0,--排序过程中使⽤的临时⽂件数
"sort_buffer_size": 115056,
"sort_mode": "<sort_key, additional_fields>"--排序模式（详解请看下⽅知识扩展）
} /* filesort_summary */
}
]/* steps */
} /* join_execution */
}
]
/* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0--该字段表⽰分析过程丢弃的⽂本字节⼤⼩，本例为0，说明没丢弃任何⽂本          INSUFFICIENT_PRIVILEGES: 0--查看trace的权限是否不⾜，0表⽰有权限查看trace详情
1row in set(0.00 sec)
关闭trace
set session optimizer_trace="enabled=off";
TRACE 字段中整个⽂本⼤致分为三个过程。
准备阶段：对应⽂本中的 join_preparation
优化阶段：对应⽂本中的 join_optimization
执⾏阶段：对应⽂本中的 join_execution
使⽤时，重点关注优化阶段和执⾏阶段。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。