数据库性能调优技术

一、概述

　　随着数据库在各个领域的使用不断增长，越来越多的应用提出了高性能的要求。数据库性能调优是知识密集型的学科，需要综合考虑各种复杂的因素：数据库缓冲区的大小、索引的创建、语句改写等等。总之，数据库性能调优的目的在于使系统运行得更快。

　　调优需要有广泛的知识，这使得它既简单又复杂。

　　说调优简单，是因为调优者不必纠缠于复杂的公式和规则。许多学术界和业界的研究者都在尝试将调优和查询处理建立在数学基础之上。

　　称调优复杂，是因为如果要完全理解常识所依赖的原理，还需要对应用、数据库管理系统、操作系统以及硬件有广泛而深刻的理解。

　　数据库调优技术可以在不同的数据库系统中使用。如果需要调优数据库系统，最好掌握如下知识：1)查询处理、并发控制以及数据库恢复的知识;2)一些调优的基本原则。

　　这里主要描述索引调优。

　　二、索引调优

　　索引是建立在表上的一种数据组织，它能提高访问表中一条或多条记录的特定查询效率。因此，适当的索引调优是很重要的。

　　对于索引调优存在如下的几个误区：

　　误区1：索引创建得越多越好?

　　实际上：创建的索引可能建立后从来未使用。索引的创建也是需要代价的，对于删除、某些更新、插入操作，对于每个索引都要进行相应的删除、更新、插入操作。从而导致删除、某些更新、插入操作的效率变低。

　　误区2：对于一个单表的查询，可以索引1进行过滤再使用索引2进行过滤?

　　实际上：假设查询语句如下select * from t1 where c1=1 and c2=2，c1列和c2列上分别建有索引ic1、ic2。先使用ic1(或ic2)进行过滤，产生的结果集是临时数据，不再具有索引，所以不可使用ic2(或ic1)进行再次过滤。

　　索引优化的基本原则：

　　1.将索引和数据存放到不同的文件组

　　没有将表数据和索引数据存储到不同的文件组，而不加区别地将它们存储到同一文件组。这样，不但会造成I/O竞争，也为数据库的维护工作带来不变。

　　2.组合索引的使用

　　假设存在组合索引it1c1c2(c1,c2)，查询语句select * from t1 where c1=1 and c2=2能够使用该索引。查询语句select * from t1 where c1=1也能够使用该索引。但是，查询语句select * from t1 where c2=2不能够使用该索引，因为没有组合索引的引导列，即，要想使用c2列进行查找，必需出现c1等于某值。

　　根据where条件的不同，归纳如下：

　　1) c1=1 and c2=2：使用索引it1c1c2进行等值查找。

　　2) c1=1 and c2>2：使用索引it1c1c2进行范围查找，可以有两种方法。

　　方法1，使用通过索引键(1,2)在B树中命中一条记录，然后向后扫描找出 第一条符合条件的记录，从此记录往后的每一条记录都是符合条件的。这种方法的弊端在于：如果c1=1 and c2=2对应的记录数很多，会产生很多无效的扫描。

　　方法2，如果c2对应的int型数据，可以使用索引键(1,3)在B树中命中一条记录，从此记录往后的每一条记录都是符合条件的。

　　本文中的例子均采用方法1。

　　3)c1>1 and c2=2：因为索引的第一个列不是等于号的，索引即使后面出现了c2=2，也不能将c2=2应用于索引查找。这里，通过索引键(1,- ∞)在B树中命中一条记录，向后扫描找出第一条符合c1>1的记录，此后的每一条记录判断是否符合c2=2，如果符合则输出，否则过滤掉。这里我们称c2=2没有参与到索引运算中去。这种情况在实际应用中经常出现。

　　4)c1>1：通过索引键(1,- ∞) 在B树中命中一条记录，以此向后扫描找出第一条符合c1>1的记录，此后的每条记录都是符合条件的。

　　3.唯一索引与非唯一索引的差异

　　假设索引int1c1(c1)是唯一索引，对于查询语句select c1 from t1 where c1=1，数据库使用索引键(1)命中B树中一条记录，命中之后直接返回该记录(因为是唯一索引，所以最多只能有一条c1=1的记录)。

　　假设索引it1c2(c2)是非唯一索引，对于查询语句select c2 from t2 where c2=2，数据库使用索引键(2)命中B树中一条记录，返回该记录，并继续向后扫描，如果该记录是满足c=2，返回该记录，继续扫描，直到遇到第一条不符合条件c2=2的记录。

　　于是，我们可以得知，对于不存在重复值的列，创建唯一索引优于创建非唯一索引。

　　4.非聚集索引的作用

　　每张表只可能一个聚集索引，聚集索引用来组织真实数据。语句“create table employee (id int cluster primary key,name varchar(20),addr varchar(20))”。表employee的数据用id来组织。如果要查找id=1000的员工记录，只要用索引键(1000)命中该聚集索引。但是，对于要查找name=’张三’的员工记录就不能使用该索引了，需要进行全表扫描，对于每一条记录判断是否满足name=’张三’，这样会导致查询效率非常低。

　　要使用聚集索引，必需提供id，我们只能提供name，于是需要引入一个辅助结构实现name到id的转换，这就是非聚集索引的作用。该非聚集索引的键是name，值是id。于是语句“select * from employee where name=’张三’”的执行流程是：通过键(’张三’)命中非聚集索引，得到对应的id值3(假设’张三’对应的id为3)，然后用键(3)命中聚集索引，得到相应的记录。

　　5.是不是使用非聚集索引的查询都需要进行聚集的查询?

　　不是的，虽然在上一点中查询转换为聚集索引的查找，有时候可以只需要使用非聚集索引。

　　创建表并创建相应的索引：create table t1(c1 int,c2 int,c3 int);create index it1c2c3 on t1(c2,c3)。查询语句为：select c3 from t1 where c2=1。

　　因为索引it1c2c3(c2,c3)覆盖查询语句中的列(c2,c3)。所以，该查询语句的执行流程为：通过索引键(1,- ∞)命中索引it1c2c3，对于该记录直接返回c3对应的值，继续向后扫描，如果索引记录中c1还是等于1，那么输出c3，以此类推，直到出现第一条c1不等于1的索引记录,结束查询。

　　6.创建索引的规则

　　创建索引首先要考虑的是列的可选择性。比较一下列中唯一键的数量和表中记录的行数，就可以判断该列的可选择性。如果该列的“唯一键的数量/表中记录行数”的比值越接近于1，则该列的可选择行越高。在可选择性高的列上进行查询，返回的数据就较少，比较适合索引查询。相反，比如性别列上只有两个值，可选择行就很小，不适合索引查询。


一、概述
      这篇文章是数据库性能调优技术的第二篇。上一篇讲解的索引调优是数据库性能调优技术的基础。这篇讲解的深入理解单表执行计划，是数据库性能调优的有力工具。
      查询语句可以有多种可选执行计划，如何选择效率最高的执行计划?达梦数据库、oracle数据库、sql server数据库都是采用基于成本的查询优化，对备选执行计划进行打分，选择代价最小的执行计划进行执行。这些内容，我会在后续的几篇文章中进行详细的描述。在此之前，我们首先需要掌握如何理解数据库执行计划。这篇文章讲解只涉及单表操作的执行计划。
达梦数据库、oracle数据库、sql server数据库都可以显示给定语句的执行计划。我详细分析了这三个数据库的执行计划，三者之间并无本质区别。
所以本文的内容适合于这三个数据库。同样，也应该适合绝大多数其它的数据库。
单表执行的深入理解，是了解多表执行计划的基础。达梦数据库显示的执行计划时，显示的信息会多一些。
因此，这篇文章中我选择达梦数据库作为实例数据库来讲解执行计划的原理。
读完本文后，应该能够读懂这三个数据库的单表执行计划。


二、深入理解数据库执行计划
达梦数据库的执行计划有两种显示方式：第一种为图形化的显示方式；第二种为文本式的显示方式。这里采用第二种方式进行讲解。
理解执行计划，是迈向理解数据库性能调优的重要一步。从执行计划中，我们可以看出数据库是如何执行查询语句，并根据执行计划判断出该查询语句的执行是否高效，以及如何进行优化。
      下面我们将通过一些例子来理解数据库执行计划。

1.没有索引的全表扫描过滤如何执行？
   构造处执行场景：
create table t1(c1 int,c2 int);
insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);
insert into t1 values(4,4);
insert into t1 values(5,5);
insert into t1 values(6,6);
   查询语句为：
select * from t1 where c1=2;
   该语句的执行过程，如果用语言描述可以描述成这样：
1）如果是第一次执行该步骤，则取得表的第一条记录；否则取得当前记录的下一条记录。如果记录已经扫描结束，则执行步骤4，否则执行步骤2。
2）判断该记录是否满足过滤条件c1=2，满足则执行步骤3，否则执行步骤1。
3）把该记录放到结果集中，执行步骤1。
4）将结果集返回给客户端。
   实际上，数据库执行查询语句的过程也是类似的，下面是该查询语句的执行计划：
#RSET:[21, 1, 1];
        #XFLT:[0, 0, 0]; EXPR0 = 2
                #CSEK:[21, 1, 1]; INDEX33555545(T1), FULL_SCAN
   该执行计划中出现的内容，在此做出解释：
1)CSEK（查找）类似于上文中描述的步骤1，方括号中的内容是执行该操作的评估代价，本文不作分析。“INDEX33555545(T1)”说明使用了T1表的聚集索引，“FULL_SCAN”表示对聚集索引INDEX33555545(T1)进行全扫描。
  这里需要注意的是，达梦数据库中的表默认情况下是索引组织的。如果建表时指定了cluster primary key，那么数据以该clsuter primary key组织数据，否则以rowid组织数据。
2）XFLT（过滤）类似于上文中描述的步骤2，“EXPR0 = 2”是过滤条件。
3）RSET（结果集）类似于上文中描述的步骤3，用来存放符合条件的记录集。

   我们可以看出，数据库的执行过程和我们用语言描述的步骤是一致的。
   该查询语句完整的执行流程如下：
1）CSEK取得第一条记录（1，1）传给XFLT，将控制权传给XFLT。
2）XFLT发现该记录（1，1）不符合条件，将控制权传给CSEK。
3）CSEK取得下一条记录（2，2）传给XFLT，将控制权传给XFLT。
4）XFLT发现记录（2，2）符合条件，将该记录传给RSET，将控制权传给RSET。
5）RSET将记录（2，2）放入结果集，将控制权传给XFLT。
6）XFLT给控制权传给CSEK。
7）CSEK取得下一条（3，3）传给XFLT，将控制权传给XFLT。
8）XFLT发现该记录（3，3）不符合条件，将控制权传给CSEK。
9）CSEK取得下一条（4，4）传给XFLT，将控制权传给XFLT。
10）XFLT发现该记录（4，4）不符合条件，将控制权传给CSEK
11）CSEK取得下一条（5，5）传给XFLT，将控制权传给XFLT。
12）XFLT发现该记录（5，5）不符合条件，将控制权传给CSEK。
13）CSEK取得下一条（6，6）传给XFLT，将控制权传给XFLT。
14）XFLT发现该记录（6，6）不符合条件，将控制权传给CSEK。
15）CSEK发现描述操作已经结束，通知XFLT结束。将控制权传给XFLT。
16）XFLT得知查询操作结束，通知RSET结束。将控制权传给RSET。
17）RSET得知操作结束。
18）发送结果集（包含记录（2，2））给客户端。


2.如果表t1上的c1列有非唯一索引，如何执行呢？
   表t1的定义以及数据和1中描述的一样。
   创建索引：
create index it1c1 on t1(c1);
查询语句“select * from t1 where c1=2;”对应的执行计划为：
#RSET:[201, 2, 1];
        #CSEK(SECOND):[201, 2, 1]; IT1C1(T1), INDEX_EQU_SEARCH
       CSEK行的“SECOND”表示使用非聚集索引“IT1C1”，对该索引进行索引等值（INDEX_EQU_SEARCH）查找。
      该执行计划的执行流程为：
1）CSEK使用c1=2查找非聚集索引，得到第一条c1=2的索引记录（2，rowid1）中的rowid1（为数值）。使用rowid1查找聚集索引得到对应的数据记录（2，2）传递给RSET，将控制权传给RSET。
2）RSET将记录（2，2）放入结果集，将控制权传给CSEK。（因为c1上的索引是非唯一的，所以可能出现两条以上的记录满足c1=2，所以需要将控制权传给CSEK）。
3）CSEK取得当前非聚集记录的下一条记录（3，rowid2），因为3!=2，所以扫描结束。将控制权传给RSET。（如果满足c1=2的记录数大于1条，需要继续传递记录给RSET，以此类推，直到遇到不满足c1=2的那条记录，结束操作。）
4）RSET得知操作结束。
5）发送结果集（包含记录（2，2））给客户端。


3.如果表t1上的c1列有唯一索引，如何执行呢？
   首先删除c1列上的非唯一索引，然后在c1列上创建唯一索引：
drop index it1c1;
create unique index uit1c1 on t1(c1);
   查询语句“select * from t1 where c1=2;”对应的执行计划为：
#RSET:[201, 2, 1];
        #CSEK(SECOND):[201, 2, 1]; UIT1C1(T1), INDEX_EQU_SEARCH
   该执行计划的执行流程为：
1）CSEK使用c1=2查找非聚集索引，得到c1=2的索引记录（2，rowid1）中的rowid1（为数值）。使用rowid1查找聚集索引得到对应的数据记录（2，2）传递给RSET，将控制权传给RSET。（当然，有人也许会问，如果没有记录满足c1=2怎么办呢？那么，此处什么记录都不传递给RSET，通知RSET查询操作结束，最后返回空集给客户端）。
2）RSET将记录（2，2）放入结果集，操作结束（因为是唯一索引，所以最多只有1条记录满足c1=2）。
3）发送结果集（包含记录（2，2））给客户端。
     这里我们发现，例3使用了唯一索引，例2使用了非唯一索引。例3的执行速度大于例2的执行速度。

4.如何理解执行计划中的top n操作？
   查询语句“select top 10 * from t1 where c1>2;”对应的执行计划为：
#RSET:[21, 1, 1];
        #XTOP:[0, 0, 0]; top_off(0), top_num(10)
                #XFLT:[0, 0, 0]; EXPR1 > 2
                        #CSEK:[21, 1, 1]; INDEX33555545(T1), FULL_SCAN
     XTOP（取得前N条记录）：将XFLT操作符传递来的记录放入到RSET（结果集）中，并判断记录数是否已经等于给定值10（语句中的top 10）。如果已经等于10，则查询已经执行成功，退出。否则将控制权限传给XFLT，继续执行。依次执行，直到取得10条记录，或者表CSEK操作已经查询结束（即符合条件的记录不满10条）。

5.如何理解执行计划中的order by操作？
   查询语句“select top 10 * from t1 where c2>2 order by c1;”对应的执行计划为：
#RSET:[21, 1, 1];
        #XSORT:[0, 0, 0]; keys_num(1), is_distinct(FALSE)
                #XFLT:[0, 0, 0]; EXPR1 > 2
                        #CSEK:[21, 1, 1]; INDEX33555545(T1), FULL_SCAN
    XSORT（对记录进行排序）：将XFLT操作符传递来的记录插入到XSORT维护的临时空间中的合理位置，按c1进行有序排列。然后将控制权传给XFLT以取得下一条符合条件的记录。等处理完所有符合条件的记录。XSORT操作符才会将控制权限传给RSET。

6.是不是查询语句中一旦出现order by字句，执行计划中就会出现XSORT操作符？
不是。
比如，查询语句“select c1 from t1 order by c1;”对应的执行计划为：
#RSET:[0, 0, 0];
        #CSEK:[0, 0, 0]; UIT1C1(T1), FULL_SCAN
从执行中我们可以看出，达梦直接对索引UIT1C1进行全索引扫描，对于得到的每一条记录不需要进行XSORT排序操作，直接放入RSET（结果集）中。因为索引UIT1C1本身就是按照c1进行排序的。

7.有文档说，对于语句“select max(c1) from t1”，可以在c1列上创建索引从而查询速度变快。那么在执行计划中是如何体现的呢？
     查询语句“select max(c1) from t1”对应的执行计划：
#RSET:[0, 0, 0];
        #XEVL:[0, 0, 0];
                #FAGR:[0, 0, 0]; function_num(1)
     在这个执行计划中，我们没有看到CSEK操作符。因为c1上存在索引UIT1C1，该索引叶子节点的最右端就是c1的最大值。FARG直接返回该最大值。语句“select min(c1) from t1;”、语句“select count(*) from t1;”的执行原理一样。XEVL是表达式计算，本文不进行讲解。

8.如果列上存在索引，如何理解中的group by操作？
    查询语句“select c1,count(*) from t1 where c1>=2 group by c1;”对应的执行计划为：
#RSET:[11, 1, 1];
        #XEVL:[0, 0, 0];
                #SAGR:[0, 0, 0]; group_by_num(1), function_num(1)
                        #CSEK:[11, 1, 1]; UIT1C1(T1), INDEX_GE_SEARCH
     我们可以得到，CSEK使用了索引UIT1C1进行了范围查找。首先传递给SARG的是连续的c1=2的记录组，然后是c1=3的记录组，然后是c1=4的记录组，……
     此处SARG的执行流程是
1）从CSEK取得一条c1=2记录，将计数加1，
2）从CSEK取得下一条记录，如果该记录满足c1=2，将计数+1。
3）重复执行步骤2，直到取得第一条不满足c1=2的记录，将(2,对应的计算)传递给XEVL，再传给RSET（结果集）。接着对c1=3的记录组执行同样的流程。依此类推，直到处理完所有符合条件的记录。
     这里我们的分组函数是count(*)，如果是其它的分组函数，处理过程类似。

9.如果列上不存在索引，如何理解中的group by操作？
   查询语句“select c2,count(*) from t1 where c2>=2 group by c2;”对应的执行计划为：
#RSET:[21, 1, 1];
     #XEVL:[0, 0, 0];
           #HAGR:[0, 0, 0]; group_by_num(1), function_num(1)
                 #XFLT:[0, 0, 0]; EXPR0 >= 2
                        #CSEK:[21, 1, 1]; INDEX33555550(T1), FULL_SCAN
     这里因为c2上没有索引，HARG的作用是HASH分组。
     HARG的执行流程是：
1）从XFLT取得一条记录
2）记录的c1=m，如果在hash表中已经对应项，计数+1，如果不存在对应项，在创建一个新的hash项。
3）所有的符合过滤条件的记录处理完成之后，HARG才会将控制权限传给上层操作符，HARG每次向上层操作符传递一条（m,m对应的计数）。
这里我们的分组函数是count(*)，如果是其它的分组函数，处理过程类似。


一、概述
       这篇文章是数据库性能调优技术的第三篇。上一篇文章讲解了深入了解单表执行计划，单表执行计划是理解多表执行计划的基础。
       两张表的连接有三种执行方式：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））发送给客户端。


一、概述
这篇文章是数据库性能调优技术系列的第四篇。上一篇文章讲解了深入理解嵌套循环连接执行计划。
上一篇文章中提到两张表的连接有三种执行方式：1）嵌套循环连接；2）散列连接；3）归并连接。散列连接是很重要的连接方式，包含比较多的内容，这篇文章中讲解为什么需要散列连接？如何理解散列连接？
和前三篇文章一样，本文讲解的是些比较抽象的内容，不拘泥于具体的数据。所以本文中使用的代价评估模型也是抽象的，假设了数据库缓冲区大小只有一个页，新页的读取必然导致旧页的释放。读完本文之后应该能够读懂达梦数据库、oracle数据库、sqlserver数据库的执行计划。


二、深入理解嵌套循环执行计划
为什么要引入散列连接呢？假设两张表t1(c1 int,c2 int),t2(d1 int,d2 int)，查询语句为select c1,d1 from t1 inner join t2 on c1=d1。如果数据库没有实现散列连接、合并连接的话，只能选择使用嵌套循环。从上篇文章中我们可以得到，对于t1的每一条记录，都需要遍历t2的每一条记录。因此，当t1的记录数数为m,t2的记录数为n，那么该查询语句访问的记录次数为m*n。当m=10000、n=10000时，那么m*n=100000000（1亿）。这是比较夸张的浪费时间。如果m是100万，n是100万，那么m*n就是1万亿次，读一万亿次记录，这是不能忍受的。
这里需要提到的一点是：我们不以读取记录的多少作为评价标准，在实际代价评估中，采用数据页（也可称为数据块，I/O的基本单位）。但是两者之间又是有联系的，假设每个页存放100个数据，那么t1的数据页为100页(10000/100)，t2的数据页为100页，那么对于t1中的每一条记录，需要遍历t2的100页，加上该记录在t1中也属于一个数据页。因此，对于t1中的每一个记录，需要访问101个数据页。那么该查询的I/O量为：10000*（100+1）=1010000页。如果考虑到数据页的缓冲，情况会更加复杂。代价评估是个很复杂的课题，可能需要单独写个系列来阐述数据库查询优化系统的代价评估模型。这里我们不考虑数据页缓冲，也就相当于假设数据库缓冲区的大小仅仅为1个页。
好了，继续前面的话题。
如果t1(c1)上建立有唯一索引iut1c1（非唯一索引也是一样），那么可以将t2作为外表，对于t2的每一条记录，使用d1的值去命中索引iut1c1对应的B树。假设该B树的高度为3层，那么对于t2的每一条记录，需要访问t1表索引iut1c1中三个页（B树的高度），加上本身在t2中属于一个页。所以，在这种情况下，查询代价为：10000*(3+1)=40000页。
我们来对比一下，没有索引与有索引，两者之间的代价对比约等于25:1（比值1010000：40000）。也可以这么认为，假设没有索引的时候执行需要25s，那么有索引的情况下只需要1s。
    这里我们把话题再延展下，如果m,n都为1000000，占用的块都为10000页（1000000/100）。没有索引的情况的I/O量为：1000000*(10000+1)=10001000000页。在t1(c1)有索引，该索引的高度对应的高度为4的情况下，假设I/O量为：100000*(4+1)=5000000。对比一下，没有索引与有索引，两者之间的代价比约等于2000:1。相等于，假设没有索引的情况下执行需要2000s，那么有索引的情况下只需要1s。
从上面的对比当中，我们可以发现索引的重要性，在实际应用当中，80%的查询性能问题来源于没有创建索引或者没有创建合适的索引。
索引，真是个好东西。如果用户没有创建索引，数据库内核也拿用户没办法，只能自己想办法。这里提出两种解决方法：1）建立临时索引；2）使用散列连接。

1）数据库内核使用建立临时索引的方法
大家可能听到过一个这样的概念：“在sqlserver系统中，如果用户没有创建索引，执行查询时，sqlserver会自动创建该索引。”
这里我们先撇开sqlserver到底是使用临时索引还是散列连接，我们只是对这句话加以理解。
对于上文提到的查询语句，执行过程描述如下：
1)      create index itemp on t1(c1);
2)      执行查询语句select c1,d1 from t1 inner join t2 on c1=d1；
3)      drop index itemp;
我们来评估下代价。如上文锁描述，假设m,n都为1000000，占用的块都为10000页。
首先是计算构造索引的代价：对t1的数据进行全扫描，对于每一条记录要插入到B树中，假设插入操作平均需要使用3个页。（因为起始时，B树只有一层，插入只需要访问1页，B树两层使需要访问2页，等等）。该步骤的代价为：1000000*（3+1）=4000000页。
然后计算查询的代价，前面已经计算过：100000*(4+1)=5000000页。
所以，整个代价为4000000+5000000=9000000页。
进行对比：10000:9:5(比值10001000000:9000000:5000000)。不使用索引的代价为10000，使用临时索引的代价为9，使用用户创建的索引代价为5。
    所以，我们发现使用临时索引还是个不错的选择。


2）数据库内核使用散列连接的方法
   首先我们讲下散列连接的原理：
1）对t1表（称为构建表）进行全扫描，对于每一个记录，对c1值进行使用内部散列函数，然后将该数据存放到相应的散列桶。
2）开始读t2表（称为探查散列表），对于t2的每一个记录，对d1值使用同样的散列函数，得到相应的散列值，查看该桶中是否有行。
如果相应的桶中没有行，则会丢失t2中这一行记录。如果散列桶中如果有一些行呢，则会精通的检查散列连接判断是否存在合适的匹配。因为不同的值可以产生同样的散列值。找到精确匹配的值，组合成记录放入结果集中。
   我们来评估下代价。
1）首先我们先看构建散列的代价，对于t1的每一个记录，一般只需要访问一个散列桶。所以该步骤的代价为：1000000*(1+1)=2000000页。
2）对于t2的每一个记录，一般只需要访问一个散列桶。所以该步骤的代价为：1000000*(1+1)=2000000页。
   所以，整个代价为2000000+2000000=4000000页。
   进行对比：10000:4:5(比值10001000000:4000000:5000000)，不使用索引的代价为10000，使用散列连接的代价为4，使用用户创建的索引代价为5。
   是不是觉得不可思议？散列连接的代价竟然比使用索引的连接还小。我们通过一个例子来验证一下：
SQL> create table t1(c1 int,c2 int);
Table created.

SQL> begin
2 for colval in 1..10000
3 loop
4     insert into t1 values(colval,colval);
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> create table t2(d1 int,d2 int);

Table created.

SQL> begin
2 for colval in 1..10000
3 loop
4     insert into t2 values(colval,colval);
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> create index it1c1 on t1(c1);

Index created.

SQL>
     查询语句“select c1,d1 from t1 inner join t2 on c1=d1;”对应的执行计划为：
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13 Card=10000 Byte
          s=260000)
   1    0   HASH JOIN (Cost=13 Card=10000 Bytes=260000)
   2    1     TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=6 Card=10000 B
          ytes=130000)
   3    1     TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=6 Card=10000 B
          ytes=130000)
      从执行计划中，我们看出尽管t1(c1)建立了索引，数据库还是采用了散列连接。我们也许会经常疑惑：“为什么我创建了索引，数据库没使用该索引。”
      各位可以验证一下，当你觉得应该可以使用索引，而数据库没有使用索引的情况一般会是：数据库使用散列连接代替了嵌套循环连接。千万不要将该结论进行延伸，从而得出：“我们不需要建立索引，数据库不使用索引”。数据库会根据查询代价进行合理的选择。哪种代价小，就会使用哪种执行计划进行执行。
      我们再看该执行计划，“TABLE ACCESS (FULL) OF 'T1' (TABLE)”就是构建散列表，散列表构建之后就会执行“TABLE ACCESS (FULL) OF 'T2' (TABLE)”。比如对于t2的记录(1,1)，使用散列函数得出hashvalue1，找到hashvalue1对应的桶，里面可能有几个值，这要看使用什么样的散列函数。假设散列函数是mod 10001，那么该桶里只会有一个记录(1,1)。如果散列函数是mod 9000。里面就会有记录(1,1)与(9001,9001)。这种情况下，我们要进行对比，对于记录(1,1)（对应(c1,c2)）,因为满足c1=d1，所以构造处记录(1,1)（对应查询项(c1,d1)）放入结果集，对于记录(9001,9001)不满足c1=d1，所以该记录不符合。如果t1表中有重复记录(1,1)，那么这里就会产生两条记录插入到结果集中，因为：对于每个精确匹配c1=d1的记录都会组合成结果记录放入到结果集中