<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 原理类似: