关闭

数据库性能调优技术 --深入理解嵌套循环执行计划

305人阅读 评论(0) 收藏 举报

一、概述

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

两张表的连接有三种执行方式:1)嵌套循环连接;2)散列连接;3)归并连接。两张表连接时选择这三种中的哪一种呢?这取决于索引、以及连接的代价。在该系列的第三篇(本文)文章中讲解嵌套循环连接,第四篇文章中讲解散列连接,第五篇文章中讲解归并连接。在第六篇以后会分析 IN 子查询以及 EXISTS子查询。

达梦数据库、oracle 数据库、sql server 数据库在数据库执行计划方面并无本质区别,因此上篇文章使用达梦数据库作为实例数据库进行分析,这篇文章我们选择 oracle 10g作为实例数据库。

读完本文后,应该能够读懂这三个数据库的嵌套循环连接执行计划。

另外需要申明一点的是:因为 oracle 的源代码是不公开的,我这里描写的是根据执行计划、成本代价以及 10053 文件进行反推的结果,尽管这样,从大的方向上讲,不会出现问题,仅做抛砖引玉。

二、深入理解嵌套循环执行计划

Oracle 数据库常用的显示执行计划的方式有两种:

1)setautotrace on  命令;

2)explain planfor  命令;

 

举例说明使用 setautotrace 命令:

SQL>create table t1(c1 int,c2 int);

Tablecreated.

SQL>create index it1c1 on t1(c1);

Indexcreated.

SQL>insert into t1 values(1,1);

1row created.

SQL>insert into t1 values(2,2);

1row created.

SQL>commit;

Commit complete.

SQL>set autotrace on explain;

SQL>select c1 from t1 where c1=1;

C1

----------

                  1

 

 

ExecutionPlan

----------------------------------------------------------

 0     SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=13)

 1      0INDEX (RANGE SCAN) OF 'IT1C1'(INDEX) (Cost=1 Card=1 Bytes=13)

SQL>set autotrace off;

SQL>

我们可以看到,执行了“setautotrace on explain;”语句之后,接下来的查询、插入、更新、删除语句就会显示执行计划,直到执行“setautotrace off;”语句。如果是设置了“setautotrace on;”,除了会显示执行计划之外,还会显示一些有用的统计信息。本系列文章不涉及查询代价的评估分析。

我们从上一段代码中,我们发现在显示“selectc1 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

--------------------------------------------------------------------------------

Planhash value: 2624316456

--------------------------------------------------------------------------

Id        Operation        Name Rows  Bytes  Cost (%CPU) Time

----------------------------------------------------------------------------------------------------------------

0    SELECT STATEMENT           1    13      1 (0)     00:00:01   

*1   INDEX RANGESCAN  T1C1    1    13      1 (0)     00:00:01

----------------------------------------------------------------------------------------------------------------

PredicateInformation (identified by operation id):

-------------------------------------------------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

1- access("C1"=1)

Note

-----

-dynamic sampling used for this statement

17rows selected.

SQL>

使用“explainplan for   查询语句;”生成执行计划,然后使用“select * from table(DBMS_XPLAN.display);”语句显示执行计划。

下面的内容,将通过一些例子来理解嵌套理解执行计划:

1.不带索引的嵌套连接的执行计划该如何理解?

构造处测试场景:

createtable t1(c1 int,c2 int);

insertinto t1 values(1,1);

insert intot1 values(2,2);

createtable t2(d1 int,d2 int);

createindex it2d1 on t2(d1);

insertinto t2 values(1,1);

insertinto t2 values(2,2);

insertinto t2 values(3,3);

insertinto 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 为内部表。此查询语句的执行计划为:

ExecutionPlan

----------------------------------------------------------

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)

“ExecutionPlan”显示优化程序用来执行查询的步骤。每一步都被赋予一个ID 值(以0 开始)。第二个数字显示当前操作符的父结点。

在这个执行计划中:“NESTEDLOOPS”的父结点是“SELECT STATEMENT”,“TABLEACCESS (FULL) OF 'T1' (TABLE)”与“TABLEACCESS (FULL) OF 'T2' (TABLE)”的父结点都是“NESTEDLOOPS”。也可能称为,操作符“SELECT STATEMENT”的孩子结点是“NESTEDLOOPS”,操作符“NESTED LOOPS”的第一个孩子结点是“TABLEACCESS (FULL)OF 'T1' (TABLE)”,操作符“NESTEDLOOPS”的第二个孩子结点是“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 c1=d2;

Explained.

SQL>select * from table(DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------

Planhash value: 4033694122

--------------------------------------------------------------------------------------------------------------

 Id         Operation        Name  Rows   Bytes  Cost (%CPU)   Time  

---------------------------------------------------------------------------------------------------------------

 0   SELECT STATEMENT           2      78       4 (0)       00:00:01

  1    NESTEDLOOPS               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

---------------------------------------------------------------------------------------------------------------

PredicateInformation (identified by operation id):

---------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement
19 rows selected.
SQL>

这里显示的步骤0、1、2、3与前面通过setautotrace on命令显示的执行计划在意义上是一样的。红颜色表明t2只能扫描到符合过滤条件c1=d1的记录才会将控制权传给父节点“NESTEDLOOPS”。

  对于该查询语句的执行,如果用代码可以描述成这样:

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)

  使用explainplan对应的执行计划:

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(“NESTEDLOOPS”的简写),将控制权传递给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(“NESTEDLOOPS”的简写),将控制权传递给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))发送给客户端。

 

 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    最新评论