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