当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
<MEMO1> <MEMO2>
<MEMO2>：
{ "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": [ ] } } ] }
查看更多关于mysql5.6.16下跟踪SQL查询转换的结果 - mysql数据库栏的详细内容...