数据库性能调优技术系列文章(3)--深入理解嵌套循环执行计划(摘自老杨)
来源：互联网 发布：php 函数缺省参数 编辑：程序博客网 时间：2024/06/14 16:37
一、概述
这篇文章是数据库性能调优技术的第三篇。上一篇文章讲解了深入了解单表执行计划，单表执行计划是理解多表执行计划的基础。
两张表的连接有三种执行方式：1）嵌套循环连接；2）散列连接；3）归并连接。两张表连接时选择这三种中的哪一种呢？这取决于索引、以及连接的代价。在该系列的第三篇（本文）文章中讲解嵌套循环连接，第四篇文章中讲解散列连接，第五篇文章中讲解归并连接。在第六篇以后会分析IN子查询以及EXISTS子查询。
达梦数据库、oracle数据库、sql server数据库在数据库执行计划方面并无本质区别，因此上篇文章使用达梦数据库作为实例数据库进行分析，这篇文章我们选择oracle 10g作为实例数据库。
读完本文后，应该能够读懂这三个数据库的嵌套循环连接执行计划。
另外需要申明一点的是：因为oracle的源代码是不公开的，我这里描写的是根据执行计划、成本代价以及10053文件进行反推的结果，尽管这样，从大的方向上讲，不会出现问题，仅做抛砖引玉。
二、深入理解嵌套循环执行计划
Oracle数据库常用的显示执行计划的方式有两种：
1）set autotrace on 命令；
2）explain plan for 命令；
举例说明使用set autotrace命令：
SQL> create table t1(c1 int,c2 int);
Table created.
SQL> create index it1c1 on t1(c1);
Index created.
SQL> insert into t1 values(1,1);
1 row created.
SQL> insert into t1 values(2,2);
1 row created.
SQL> commit;
Commit complete.
SQL> set autotrace on explain;
SQL> select c1 from t1 where c1=1;
C1
----------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=13)
1 0 INDEX (RANGE SCAN) OF 'IT1C1' (INDEX) (Cost=1 Card=1 Bytes
=13)
SQL> set autotrace off;
SQL>
我们可以看到，执行了“set autotrace on explain;”语句之后，接下来的查询、插入、更新、删除语句就会显示执行计划，直到执行“set autotrace off;”语句。如果是设置了“set autotrace on;”，除了会显示执行计划之外，还会显示一些有用的统计信息。本系列文章不涉及查询代价的评估分析。
我们从上一段代码中，我们发现在显示“select c1 from t1 where c1=1;”执行计划之前显示了该执行语句的查询结果。这说明：显示执行计划之前就真正地将该查询语句执行了一遍。这样会带来一个不好后果，假设我们现在有一条语句，执行的时间需要半个小时，即使我们仅仅需要知道该语句的执行计划，此种情况下，我们必须等待半个小时。因此，如果查询的性能很慢，我们可以选择选择使用explain plan for命令。
举例说明explain plan for命令：
SQL> explain plan for select c1 from t1 where c1=1;
Explained.
SQL> select * from table(DBMS_XPLAN.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2624316456
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IT1C1 | 1 | 13 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - access("C1"=1)
Note
-----
- dynamic sampling used for this statement
17 rows selected.
SQL>
使用“explain plan for 查询语句;”生成执行计划，然后使用“select * from table(DBMS_XPLAN.display);”语句显示执行计划。
下面的内容，将通过一些例子来理解嵌套理解执行计划：
1.不带索引的嵌套连接的执行计划该如何理解？
构造处测试场景：
create table t1(c1 int,c2 int);
insert into t1 values(1,1);
insert into t1 values(2,2);
create table t2(d1 int,d2 int);
create index it2d1 on t2(d1);
insert into t2 values(1,1);
insert into t2 values(2,2);
insert into t2 values(3,3);
insert into t2 values(4,4);
查询语句为：
select /*+ USE_NL(t2) */ c1,c2 from t1 inner join t2 on c1=d2;
该语句中“/*+ USE_NL(t2) */”是我们常说的hint提示，这里的USE_NL告诉优化程序使用嵌套连接对表进行连接，t2为内部表。此查询语句的执行计划为：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=2 Bytes=78)
1 0 NESTED LOOPS (Cost=4 Card=2 Bytes=78)
2 1 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=2 Card=2 Bytes
=52)
3 1 TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=1 Card=1 Bytes
=13)
“Execution Plan”显示优化程序用来执行查询的步骤。每一步都被赋予一个ID值（以0开始）。第二个数字显示当前操作符的父结点。在这个执行计划中，“NESTED LOOPS”的父结点是“SELECT STATEMENT”，“TABLE ACCESS (FULL) OF 'T1' (TABLE)”与“TABLE ACCESS (FULL) OF 'T2' (TABLE)”的父结点都是“NESTED LOOPS”。也可能称为，操作符“SELECT STATEMENT”的孩子结点是“NESTED LOOPS”，操作符“NESTED LOOPS”的第一个孩子结点是“TABLE ACCESS (FULL) OF 'T1' (TABLE)”，操作符“NESTED LOOPS”的第二个孩子结点是“TABLE ACCESS (FULL) OF 'T2' (TABLE)”。
第二行表示，对表T1进行全表扫描，括号中的三个值是该步骤的成本代价，这里不作阐述。第三行表示，对T2进行全表扫描，这里还隐藏了一个细节：此处进行了c1=d1的判断。参考explain plan for生成的执行计划：
SQL> explain plan for select /*+ USE_NL(t2) */ c1,c2 from t1 inner join t2 on c
1=d2;
Explained.
SQL> select * from table(DBMS_XPLAN.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4033694122
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 78 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 78 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 2 | 52 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 13 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("C1"="D2")
Note
-----
- dynamic sampling used for this statement
19 rows selected.
SQL>
这里显示的步骤0、1、2、3与前面通过set autotrace on命令显示的执行计划在意义上是一样的。红颜色表明t2只能扫描到符合过滤条件c1=d1的记录才会将控制权传给父节点“NESTED LOOPS”。
对于该查询语句的执行，如果用代码可以描述成这样：
for (rec1 is t1’s first record; rec1!=NULL; rec1=rec1->next)
for(rec2 is t2’s first record; rec2!=NULL; rec2=rec->next)
{
if(rec1.c1==rec2.d1)
put result(rec1.c1,rec1.c2) into result set;
}
也就是说，t1与t2先生成笛卡尔集，然后过过滤条件c1=d1过滤该笛卡尔集。
其实，数据库执行该语句的步骤也是类似的，下面是执行该语句的步骤：
1）TAF(T1)(“TABLE ACCESS (FULL) OF 'T1'”的简写)取得T1的第一条记录（1,1）传递给NL（“NESTED LOOPS”的简写），将控制权传递给操作符NL。
2）操作符NL将控制权传给第二个孩子TAF(T2)（“TABLE ACCESS (FULL) OF 'T2'”的简写）。
3）TAF(T2)取得T2的第一条记录(1,1)，符合过滤条件c1=d1，将控制权传给操作符NL。
4）NL将记录（1,1）传给SS(“SELECT STATEMENT”的简写)，将控制权传给SS。
5）SS将记录(1,1)放入结果集合，将控制权限传给NL。
6）NL将控制权限传给TAF(T2)。
7）TAF(T2)取得T2表的下一条记录（2，2），不符合条件c1=d1；取得下一条记录（3,3），不符合条件（4，4）。取得下一条记录，取不到记录。T2表扫描结束。将控制权限传递给NL。
8）NL将控制权限传给第一个孩子TAF(T1)。
9）TAF（T1）取得T1表的下一条记录（2，2）传递给NL,将控制权传给NL。
10） NL将控制权传给第二个孩子TAF(T2)。
11） TAF(T2)取得T2的第一条（1，1），不符合过滤条件c1=d1；取得下一条记录（2，2），满足条件c1=d1，将控制权传给操作符NL。
12） NL将记录（2，2）传给SS，将控制权传给SS。
13） SS将记录（2，2）放入结果集，将控制权传给NL。
14） NL将控制权限传给TAF(T2)。
15） TAF(T2)取得T2的下一条记录（3,3），不符合过滤条件c1=d1；取得下一条记录（4，4），不符合过滤条件c1=d1；取得下一条记录，取不到记录。T2表扫描结束。将控制权限传递给NL。
16） NL将控制权限传给第一个孩子TAF(T1)。
17） TAF(T1)取得T1表的下一条记录，取不到记录，T1表扫描结束。将控制权传给NL，通知NL扫描结束。
18） NL将控制权限传给SS，通知SS操作结束。
19） SS将结果集（包含记录（1，1）、（2，2））发送给客户端。
在上面的例子中，只查询显示t1的列，如果要显示t2的列，情况是一样，只是TAF(T2)需要将符合条件的T2记录传递给NL，然后NL组合成符合条件的(c1,c2,d1,d2)传递给SS。
select /*+ USE_NL(t2) */ c1,c2,d1,d2 from t1 inner join t2 on c1=d2;
对应的执行计划：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=2 Bytes=104
)
1 0 NESTED LOOPS (Cost=4 Card=2 Bytes=104)
2 1 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=2 Card=2 Bytes
=52)
3 1 TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=1 Card=1 Bytes
=26)
2.使用非唯一索引的嵌套连接的执行计划该如何理解？
测试数据与1中描述的一样。
查询语句：
select /*+ index(t2) */ c1,c2,d1 from t1 inner join t2 on c1=d1;
对应的执行计划：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=2 Bytes=78)
1 0 NESTED LOOPS (Cost=4 Card=2 Bytes=78)
2 1 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=2 Card=2 Bytes
=52)
3 1 INDEX (RANGE SCAN) OF 'IT2D1' (INDEX) (Cost=1 Card=1 Byt
es=13)
使用explain plan对应的执行计划：
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2841753667
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 78 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 78 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 2 | 52 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IT2D1 | 1 | 13 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C1"="D1") //此处的access表示使用值3去命中索引IT2D1对应的B树。
Note
-----
- dynamic sampling used for this statement
19 rows selected.
SQL>
对于该查询语句的执行，如果用代码可以描述成这样：
for (rec1 is t1’s first record; rec1!=NULL; rec1=rec1->next)
for(rec2 in t2’s first record that match c1=d1; d1=c1; rec2=rec->next)
{
put result(rec1.c1,rec1.c2，rec2.d1) into result set;
}
据库执行该执行语句的步骤也是类似的，下面是执行该执行语句的步骤：
1) TAF(T1)(“TABLE ACCESS (FULL) OF 'T1'”的简写)取得表T1的第一条记录(1,1)传递给NL（“NESTED LOOPS”的简写），将控制权传递给NL。
2) 操作符NL将控制权传递给第二个孩子IRS(IT2D1)(“INDEX (RANGE SCAN) OF 'IT2D1'”的简写)。
3) IRS(IT2D1)使用键值(1)去命中索引IT2D1对应的B树，得到索引记录(1,rowid1)。将d1对应的数据(1)传递给NL，将控制权传递给NL。注意，在本例中，将d1的数据上传是因为select中出现了d1，也就是说要将d1的值传给客户端，如果select中没有d1，此处就和上例中是一样的，不需要传递d1给上层。
4) 操作NL组合生成记录(1,1,1)（对应select项(c1,c2,d1)）传给SS，将控制权传给SS。
5) 操作符SS将记录(1,1,1)放入结果集，将控制权传给NL。
6) NL将控制权传给IRS(IT2D1)。此处传给IRS(IT2D1)的原因是，it2d1是非唯一索引，可能有两条以上的记录符合d1=1。
7) IRS(IT2D1)取得下一条记录(2,rowid2)，因为2!=1，所以对应d1=1的索引查找已经结束，通知NL,将控制权限传递给NL。
8) NL控制权传给TAF(T1)。
9) TAF(T1)取得下一条记录(2,2)传递给NL，将控制权传给NL。
10) NL将控制权传给IRS(IT2D1)。
11) IRS(IT2D1)使用键值(2)去命中索引IT2D1对应的B树，得到索引记录(2,rowid2)。将d1对应的数据(2)传递给NL，将控制权传递给NL。
12) 操作NL组合生成记录(2,2,2)传给SS，将控制权传给SS。
13) 操作符SS将记录(2,2,2)放入结果集，将控制权传给NL。
14) NL将控制权传给IRS(IT2D1)。
15) IRS(IT2D1)取得下一条记录(3,rowid3)，因为3!=2，所以对应d1=2的索引查找已经结束，通知NL查找结束,将控制权限传递给NL。
16) NL控制权传给TAF(T1)。
17) TAF(T1)取得下一条记录，发现已经扫描结束，通知NL扫描结束，将控制权传给NL。
18) NL通知SS扫描结束，将控制权传给SS。
19) SS将结果集（包含记录（1，1，1）、（2，2，2））发送给客户端。
3.使用唯一索引的嵌套连接的执行计划该如何理解？
测试数据与1中描述的一样。删除原来的非唯一索引，建立唯一索引：
drop index it2d1;
create unique index iut2d1 on t2(d1);
查询语句：
select /*+ index(t2) */ c1,c2,d1 from t1 inner join t2 on c1=d1;
对应的执行计划：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=2 Bytes=78)
1 0 NESTED LOOPS (Cost=2 Card=2 Bytes=78)
2 1 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=2 Card=2 Bytes
=52)
3 1 INDEX (UNIQUE SCAN) OF 'IUT2D1' (INDEX (UNIQUE)) (Cost=0
Card=1 Bytes=13)
该执行计划与2中描述的执行过程类似：
1) TAF(T1)(“TABLE ACCESS (FULL) OF 'T1'”的简写)取得表T1的第一条记录(1,1)传递给NL（“NESTED LOOPS”的简写），将控制权传递给NL。
2) 操作符NL将控制权传递给第二个孩子IUS(IUT2D1)(“INDEX (UNIQUE SCAN) OF 'IUT2D1''”的简写)。
3) IUS(IUT2D1)使用键值(1)去命中索引IUT2D1对应的B树，得到索引记录(1,rowid1)。将d1对应的数据(1)传递给NL，将控制权传递给NL。
4) 操作NL组合生成记录(1,1,1)（对应select项(c1,c2,d1)）传给SS，将控制权传给SS。
5) 操作符SS将记录(1,1,1)放入结果集，将控制权传给NL。
6) NL控制权传给TAF(T1)。因为iut2d1是唯一索引，所以只可能有一条记录满足d1=1，所以此时不需要将控制权限再传给IUS(IUT2D1)。
7) TAF(T1)取得下一条记录(2,2)传递给NL，将控制权传给NL。
8) NL将控制权传给IRS(IUT2D1)。
9) IUS(IUT2D1)使用键值(2)去命中索引IUT2D1对应的B树，得到索引记录(2,rowid2)。将d1对应的数据(2)传递给NL，将控制权传递给NL。
10) 操作NL组合生成记录(2,2,2)传给SS，将控制权传给SS。
11) 操作符SS将记录(2,2,2)放入结果集，将控制权传给NL。
12) NL控制权传给TAF(T1)。
13) TAF(T1)取得下一条记录，发现已经扫描结束，通知NL扫描结束，将控制权传给NL。
14) NL通知SS扫描结束，将控制权传给SS。
15) SS将结果集（包含记录（1，1，1）、（2，2，2））发送给客户端。
- 数据库性能调优技术系列文章(3)--深入理解嵌套循环执行计划(摘自老杨)
- 数据库性能调优技术系列文章(3)--深入理解嵌套循环执行计划
- 数据库性能调优技术系列文章(2)--深入理解单表执行计划 (摘自老杨)
- 数据库性能调优技术系列文章(4)--深入理解散列连接执行计划(摘自老杨)
- 数据库性能调优技术 --深入理解嵌套循环执行计划
- 数据库性能调优技术系列文章(2)--深入理解单表执行计划
- 数据库性能调优技术系列文章(4)--深入理解散列连接执行计划
- 数据库性能调优技术系列文章(1)--索引调优(摘自老杨)
- 数据库性能调优技术─嵌套循环执行计划
- 深入理解嵌套循环执行计划
- 数据库性能调优技术 --深入理解单表执行计划
- 数据库性能调优技术 --深入理解散列连接执行计划
- 深入理解数据库原理系列（1）---日志系统原理(摘自老杨)
- 深入理解数据库原理系列（2）---并发控制原理(摘自老杨)
- 深入理解mysql之BDB系列（3）---数据页结构(摘自老杨)
- 数据库性能调优技术系列文章
- 深入理解mysql之BDB系列（1）---BDB相关基础知识(摘自老杨)
- 深入理解mysql之BDB系列（2）---数据元页结构(摘自老杨)
- 电视连续剧《海灯法师》主题歌 当黑夜降临的时候 一千次的攀登
- 冯仑：做生意的“ 6-3-1”办法
- 数据库性能调优技术系列文章(2)--深入理解单表执行计划 (摘自老杨)
- 我还要一千次的攀登！
- 免费培训喽！教你玩转3G手机
- 数据库性能调优技术系列文章(3)--深入理解嵌套循环执行计划(摘自老杨)
- 使用COleDataSource.DoDropDrag进行往外Drag时要注意的一点
- 隻去掉小數點後，末位的0
- ARM芯片的地址重映射
- [转]从0到1亿美元 ---- PopCap创始人John Vechey自述
- Dojo在Firefox出现Could not load 'dojo.parser'问题的解决方法
- Thread
- 可以錄入負數--設定
- 舞动的灵魂、这一季的雪、让我为自己感伤时间
