Heim > Datenbank > MySQL-Tutorial > mysql5.6.16下跟踪SQL查询转换的结果_MySQL

mysql5.6.16下跟踪SQL查询转换的结果_MySQL

WBOY
Freigeben: 2016-06-01 13:02:03
Original
937 Leute haben es durchsucht

当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": [
]
}
}
]
}

Verwandte Etiketten:
Quelle:php.cn
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Beliebte Tutorials
Mehr>
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage