mysql sql执行顺序

<pre name="code" class="html">mysql>  explain select * from (select * from ( select * from test where id=1) a) b;+----+-------------+------------+------+---------------+------+---------+------+------+-------------+| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |+----+-------------+------------+------+---------------+------+---------+------+------+-------------+|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL        ||  2 | DERIVED     | <derived3> | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL        ||  3 | DERIVED     | test       | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |+----+-------------+------------+------+---------------+------+---------+------+------+-------------+3 rows in set (0.00 sec)很明显 这里ID=3 先执行, ID=2 select_type=DERIVED是一个派生表指向<derived3> 表示3产生的派生表ID=1 PRIMARY:最外面的SELECT   最外面执行的SELECT <derived2> 表示ID=2产生的派生表mysql> explain select t1.* from t2 ,t1 where t2.id=t1.id;+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL                                               ||  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using join buffer (Block Nested Loop) |+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+2 rows in set (0.00 sec)如果id相同，则执行顺序从上至下。SIMPLE简单SELECT(不使用UNION或子查询等)id=1 t2返回的记录 t2为驱动表,传递给t1对比Oracle的嵌套循环:SQL> explain plan for select t1.* from t1,t2 where t1.id=t2.id;已解释。SQL> select * from table(dbms.exlain.display());select * from table(dbms.exlain.display())                    *第 1 行出现错误:ORA-00904: "DBMS"."EXLAIN"."DISPLAY": 标识符无效SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 2959412835---------------------------------------------------------------------------| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |---------------------------------------------------------------------------|   0 | SELECT STATEMENT   |  |7 |   266 |6   (0)| 00:00:01 ||*  1 |  HASH JOIN   |  |7 |   266 |6   (0)| 00:00:01 ||   2 |   TABLE ACCESS FULL| T2   |6 |    78 |3   (0)| 00:00:01 ||   3 |   TABLE ACCESS FULL| T1   |7 |   175 |3   (0)| 00:00:01 |---------------------------------------------------------------------------PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("T1"."ID"="T2"."ID")Note-----   - dynamic sampling used for this statement (level=2)已选择19行采集统计信息:BEGIN  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'QUERY',                                tabname          => 'T1',                                estimate_percent => 30,                                method_opt       => 'for all columns size repeat',                                no_invalidate    => FALSE,                                degree           => 8,                                cascade          => TRUE);END;BEGIN  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'QUERY',                                tabname          => 'T2',                                estimate_percent => 30,                                method_opt       => 'for all columns size repeat',                                no_invalidate    => FALSE,                                degree           => 8,                                cascade          => TRUE);END;SQL> explain plan for select t1.* from t1,t2 where t1.id=t2.id;已解释。SQL>  select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 469473159------------------------------------------------------------------------------| Id  | Operation   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |     |   7 | 119 |   3   (0)| 00:00:01 ||   1 |  NESTED LOOPS   |     |   7 | 119 |   3   (0)| 00:00:01 ||   2 |   TABLE ACCESS FULL| T1      |   7 |  98 |   3   (0)| 00:00:01 ||*  3 |   INDEX RANGE SCAN | T2_IDX1 |   1 |   3 |   0   (0)| 00:00:01 |------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - access("T1"."ID"="T2"."ID")已选择15行。mysql> explain select t1.* from t2 ,t1 where t2.id=t1.id;+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL                                               ||  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using join buffer (Block Nested Loop) |+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+2 rows in set (0.00 sec)如果id相同，则执行顺序从上至下。SIMPLE简单SELECT(不使用UNION或子查询等)id=1 t2返回的记录 t2为驱动表,传递给t1原理类似: