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