数据库性能调优技术系列文章(4)--深入理解散列连接执行计划

 

数据库性能调优技术

                       -- 深入理解散列连接执行计划

 

                             作者：杨万富

 

一、概述

这篇文章是数据库性能调优技术系列的第四篇。上一篇文章讲解了深入理解嵌套循环连接执行计划。

上一篇文章中提到两张表的连接有三种执行方式： 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 的记录都会组合成结果记录放入到结果集中。