数据库性能调优技术 --深入理解散列连接执行计划

一、概述

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

上一篇文章中提到两张表的连接有三种执行方式：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 onc1=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);

Tablecreated.

 

SQL>begin

2  forcolval in 1..10000

3  loop

4     insertinto t1 values(colval,colval);

5  endloop;

6  end;

7    /

PL/SQLprocedure successfully completed.

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

Tablecreated.

SQL>begin

2  forcolval in 1..10000

3  loop

4     insertinto t2 values(colval,colval);

5  endloop;

6  end;

7    /

PL/SQLprocedure successfully completed.

 

 

SQL>create index it1c1 on t1(c1);

 

 

Indexcreated.

SQL>

查询语句“selectc1,d1 from t1 inner join t2 on c1=d1;”对应的执行计划为：

ExecutionPlan

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

0                SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13Card=10000

Bytes=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)”就是构建散列表，散列表构建之后就会执行“TABLEACCESS (FULL) OF 'T2' (TABLE)”。比如对于 t2 的记录(1,1)，使用散列函数得出hashvalue1，找到 hashvalue1 对应的桶，里面可能有几个值，这要看使用什么样的散列函数。假设散列函数是mod10001，那么该桶里只会有一个记录(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 的记录都会组合成结果记录放入到结果集中。