mysql查询出来结果转换_mysql5.6.16下跟踪SQL查询转换的结果

当SQL执行时，会做一些查询转换，你看到的SQL很可能被转换为其他的形式的SQL执行(有视图重写，查询转换，谓词推进等)。在Oracle下，可以通过10053跟踪SQL语句。在mysql下，可以通过设置optimizer_trace来实现。

mysql> select version();

+-----------+

| version() |

+-----------+

| 5.6.16    |

+-----------+

1 row in set (0.00 sec)

mysql> create view v_test5 as select * from test1 order by index_length;

mysql> set session optimizer_trace=‘enabled=on‘;

mysql> select count(1) from v_test5 where index_length>1024 ;

mysql> select * from information_schema.optimizer_trace;

QUERY   TRACE   MISSING_BYTES_BEYOND_MAX_MEM_SIZ

：select count(1) from v_test5 where index_length>1024

：

{

"steps": [

{

"view": {

"database": "test",

"view": "v_test5",

"in_select#": 1,

"select#": 2,

"merged": true

}

},

{

"join_preparation": {

"select#": 1,

"steps": [

{

"expanded_query": "/* select#1 */ select count(1) AS `count(1)` from (`test1`) where (`test1`.`INDEX_LENGTH` > 1024) order by `test1`.`INDEX_LENGTH`"

}

]

}

},

{

"join_optimization": {

"select#": 1,

"steps": [

{

"transformations_to_nested_joins": {

"transformations": [

"parenthesis_removal"

],

"expanded_query": "/* select#1 */ select count(1) AS `count(1)` from `test1` where (`test1`.`INDEX_LENGTH` > 1024) order by `test1`.`INDEX_LENGTH`"

}

},

{

"condition_processing": {

"condition": "WHERE",

"original_condition": "(`test1`.`INDEX_LENGTH` > 1024)",

"steps": [

{

"transformation": "equality_propagation",

"resulting_condition": "(`test1`.`INDEX_LENGTH` > 1024)"

},

{

"transformation": "constant_propagation",

"resulting_condition": "(`test1`.`INDEX_LENGTH` > 1024)"

},

{

"transformation": "trivial_condition_removal",

"resulting_condition": "(`test1`.`INDEX_LENGTH` > 1024)"

}

]

}

},

{

"table_dependencies": [

{

"table": "`test1`",

"row_may_be_null": false,

"map_bit": 0,

"depends_on_map_bits": [

]

}

]

},

{

"ref_optimizer_key_uses": [

]

},

{

"rows_estimation": [

{

"table": "`test1`",

"table_scan": {

"rows": 166,

"cost": 4

}

}

]

},

{

"considered_execution_plans": [

{

"plan_prefix": [

],

"table": "`test1`",

"best_access_path": {

"considered_access_paths": [

{

"access_type": "scan",

"rows": 166,

"cost": 37.2,

"chosen": true

}

]

},

"cost_for_plan": 37.2,

"rows_for_plan": 166,

"chosen": true

}

]

},

{

"attaching_conditions_to_tables": {

"original_condition": "(`test1`.`INDEX_LENGTH` > 1024)",

"attached_conditions_computation": [

],

"attached_conditions_summary": [

{

"table": "`test1`",

"attached": "(`test1`.`INDEX_LENGTH` > 1024)"

}

]

}

},

{

"refine_plan": [

{

"table": "`test1`",

"access_type": "table_scan"

}

]

}

]

}

},

{

"join_execution": {

"select#": 1,

"steps": [

]

}

}

]

}

原文：http://blog.csdn.net/stevendbaguo/article/details/38438593