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