mysql optimizer mrr_用MySQL的optimizer_trace进行sql调优

在我们调优MySQL的SQL时候，通常使用三种工具进行查看sql执行的效率，explain、profile、optimizer_trace。前两个经常被人使用，由于第三个难度较大，大家使用的较少，下面简单说下如何使用。

opitimizer_trace的使用：

# 开启跟踪,默认是关闭的

SET optimizer_trace="enabled=on";

#执行你的sql语句

select ....

#查看trace信息

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

# 执行完你需要的sql语句就可以关闭trace了

SET optimizer_trace="enabled=off";

支持的trace语句有：

1.SELECT;

2.INSERT or REPLACE (with VALUES or SELECT);

3.UPDATE/DELETE and their multi-table variants;

4.all the previous ones prefixed by EXPLAIN;

5.SET (unless it manipulates the optimizer_trace system variable);

6.DO;

7.DECLARE/CASE/IF/RETURN (stored routines language elements);

8.CALL.

如下这个语句可以把结果插入到一个文件里：

SELECT TRACE INTO DUMPFILE FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

在mysqld里进行设置相关参数：

A brief overview from "mysqld --verbose --help":

--optimizer-trace=name

Controls tracing of the Optimizer:

--optimizer-trace-features=name

Enables/disables tracing of selected features of the

Optimizer:

optimizer_trace_features=option=val[,option=val...],

where option is one of {greedy_search, range_optimizer,

dynamic_range, repeated_subselect} and val is one of {on,

off, default}

--optimizer-trace-limit=#

Maximum number of shown optimizer traces

--optimizer-trace-max-mem-size=#

Maximum allowed cumulated size of stored optimizer traces

--optimizer-trace-offset=#

Offset of first optimizer trace to show; see manual

--end-markers-in-json=#

In JSON output ("EXPLAIN FORMAT=JSON" and optimizer

trace), if set to 1, repeats the structure‘s key (if it

has one) near the closing bracket

具体跟踪的json格式的信息：

select * from test1,test2 where test1.id=test2.id and test1.id>4999900 | {

"steps": [

{

"join_preparation": {           --连接准备

"select#": 1,                 --join准备的第一步

"steps": [                    --解析成编号，解析数据库和表

{

"expanded_query": "/* select#1 */ select `test1`.`id` AS `id`,`test1`.`k` AS `k`,`test1`.`c` AS `c`,`test1`.`pad` AS `pad`,`test2`.`id` AS `id`,`test2`.`k` AS `k`,`test2`.`c` AS `c`,`test2`.`pad` AS `pad` from `test1` join `test2` where ((`test1`.`id` = `test2`.`id`) and (`test1`.`id` > 4999900))"

}

]

}

},

{

"join_optimization": {         --join优化

"select#": 1,

"steps": [

{

"condition_processing": {       --where条件

"condition": "WHERE",

"original_condition": "((`test1`.`id` = `test2`.`id`) and (`test1`.`id` > 4999900))",

"steps": [       --优化的步骤

{

"transformation": "equality_propagation",         --等值优化

"resulting_condition": "((`test1`.`id` > 4999900) and multiple equal(`test1`.`id`, `test2`.`id`))"   --把test.id>4999900放到前面，test1.id=test2.id使用多等值连接

},

{

"transformation": "constant_propagation",        --常量优化

"resulting_condition": "((`test1`.`id` > 4999900) and multiple equal(`test1`.`id`, `test2`.`id`))"

},

{

"transformation": "trivial_condition_removal",   --琐碎的条件排除

"resulting_condition": "((`test1`.`id` > 4999900) and multiple equal(`test1`.`id`, `test2`.`id`))"

}

]

}

},

{

"table_dependencies": [     --表依赖

{

"table": "`test1`",        --表名

"row_may_be_null": false,  --是否有null值，flase是没有

"map_bit": 0,

"depends_on_map_bits": [

]

},

{

"table": "`test2`",

"row_may_be_null": false,

"map_bit": 1,

"depends_on_map_bits": [

]

}

]

},

{

"ref_optimizer_key_uses": [    --相关优化索引使用

{

"table": "`test1`",

"field": "id",             --索引字段

"equals": "`test2`.`id`",  --连接的等值字段

"null_rejecting": false

},

{

"table": "`test2`",

"field": "id",

"equals": "`test1`.`id`",

"null_rejecting": false

}

]

},

{

"rows_estimation": [          --行评估

{

"table": "`test1`",

"range_analysis": {          --范围分析

"table_scan": {

"rows": 4804854,          --4804854行数据

"cost": 1.03e6            --花费1.03e6

},

"potential_range_indices": [   --可能的范围指数

{

"index": "PRIMARY",

"usable": true,           --可使用的索引

"key_parts": [

"id"

]                         --可使用的索引字段

},

{

"index": "k_1",

"usable": false,         --不能使用的索引

"cause": "not_applicable"  --不被应用

}

],

"setup_range_conditions": [     --设置范围条件

],

"group_index_range": {          --组范围索引

"chosen": false,              --不选择的

"cause": "not_single_table"   --goup by不是一个表的，所以不选择

},

"analyzing_range_alternatives": {     --分析每个索引做范围扫描的花费

"range_scan_alternatives": [       --范围扫描花费

{

"index": "PRIMARY",

"ranges": [

"4999900 < id"

],

"index_dives_for_eq_ranges": true,    --索引驱动等值范围扫描

"rowid_ordered": true,                --rowid是顺序的

"using_mrr": false,                   --不能使用mrr，因为是主键

"index_only": false,

"rows": 99,                           --过滤出来99行

"cost": 21.434,                       --花费21.434

"chosen": true                        --这个索引被选择选择

}

],

"analyzing_roworder_intersect": {         --分析执行顺序阶段

"usable": false,                        --不可使用

"cause": "too_few_roworder_scans"       --少数的执行顺序扫描

}

},

"chosen_range_access_summary": {           --选择范围访问概述

"range_access_plan": {

"type": "range_scan",

"index": "PRIMARY",

"rows": 99,

"ranges": [

"4999900 < id"

]

},

"rows_for_plan": 99,

"cost_for_plan": 21.434,

"chosen": true

}

}

},

{

"table": "`test2`",

"range_analysis": {

"table_scan": {

"rows": 4804854,

"cost": 1.03e6

},

"potential_range_indices": [

{

"index": "PRIMARY",

"usable": true,

"key_parts": [

"id"

]

},

{

"index": "k_2",

"usable": false,

"cause": "not_applicable"

}

],

"setup_range_conditions": [

],

"group_index_range": {

"chosen": false,

"cause": "not_single_table"

},

"analyzing_range_alternatives": {

"range_scan_alternatives": [

{

"index": "PRIMARY",

"ranges": [

"4999900 < id"

],

"index_dives_for_eq_ranges": true,

"rowid_ordered": true,

"using_mrr": false,

"index_only": false,

"rows": 99,

"cost": 21.433,

"chosen": true

}

],

"analyzing_roworder_intersect": {

"usable": false,

"cause": "too_few_roworder_scans"

}

},

"chosen_range_access_summary": {

"range_access_plan": {

"type": "range_scan",

"index": "PRIMARY",

"rows": 99,

"ranges": [

"4999900 < id"

]

},

"rows_for_plan": 99,

"cost_for_plan": 21.433,

"chosen": true

}

}

}

]

},

{

"considered_execution_plans": [   --决定执行计划

{

"plan_prefix": [             --计划前

],

"table": "`test1`",          --test1表的执行计划

"best_access_path": {        --最好的访问路径

"considered_access_paths": [  --决定的访问路径

{

"access_type": "ref",     --访问类型是ref

"index": "PRIMARY",       --使用的索引是主键

"usable": false,

"chosen": false

},

{

"access_type": "range",

"rows": 99,

"cost": 41.234,

"chosen": true

}

]

},

"cost_for_plan": 41.234,

"rows_for_plan": 99,

"rest_of_plan": [

{

"plan_prefix": [

"`test1`"

],

"table": "`test2`",

"best_access_path": {

"considered_access_paths": [

{

"access_type": "ref",

"index": "PRIMARY",

"rows": 1,

"cost": 99.2,

"chosen": true

},

{

"access_type": "range",

"cause": "heuristic_index_cheaper",

"chosen": false

}

]

},

"cost_for_plan": 160.03,

"rows_for_plan": 99,

"chosen": true

}

]

},

{

"plan_prefix": [

],

"table": "`test2`",

"best_access_path": {

"considered_access_paths": [

{

"access_type": "ref",

"index": "PRIMARY",

"usable": false,

"chosen": false

},

{

"access_type": "range",

"rows": 99,

"cost": 41.233,

"chosen": true

}

]

},

"cost_for_plan": 41.233,

"rows_for_plan": 99,

"rest_of_plan": [

{

"plan_prefix": [

"`test2`"

],

"table": "`test1`",

"best_access_path": {

"considered_access_paths": [

{

"access_type": "ref",

"index": "PRIMARY",

"rows": 1,

"cost": 99.2,

"chosen": true

},

{

"access_type": "range",

"cause": "heuristic_index_cheaper",

"chosen": false

}

]

},

"cost_for_plan": 160.03,

"rows_for_plan": 99,

"pruned_by_cost": true

}

]

}

]

},

{

"attaching_conditions_to_tables": {

"original_condition": "((`test2`.`id` = `test1`.`id`) and (`test1`.`id` > 4999900))",

"attached_conditions_computation": [

],

"attached_conditions_summary": [

{

"table": "`test1`",

"attached": "(`test1`.`id` > 4999900)"

},

{

"table": "`test2`",

"attached": null

}

]

}

},

{

"refine_plan": [

{

"table": "`test1`",

"access_type": "range"

},

{

"table": "`test2`"

}

]

}

]

}

},

{

"join_execution": {

"select#": 1,

"steps": [

]

}

}

]

}