数据库性能调优技术系列文章(3)--深入理解嵌套循环执行计划(摘自老杨)

一、概述

        这篇文章是数据库性能调优技术的第三篇。上一篇文章讲解了深入了解单表执行计划，单表执行计划是理解多表执行计划的基础。

       两张表的连接有三种执行方式： 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 ））发送给客户端。