表关联键上创建索引的重要性

红太狼 2022-08-27 00:28 167阅读 0赞

[http://www.cnblogs.com/aspnet2008/archive/2010/06/03/1750911.html][http_www.cnblogs.com_aspnet2008_archive_2010_06_03_1750911.html]

# [表关联键上创建索引的重要性][Link 1] #

很久没有写SQL相关的文章了,主要是现在技术部分工比以前明确了。网站部门并不自己写SQL查询数据，数据有其它部门提供服务。但并不是所有情况都是这样，有些项目由于之前没有管理，所以只能自己完成。在这次写的一个SQL查询中，体会到了在做join联接时，关联的键需要创建索引的重要性。  
       
      **说明：**  
         1：free\_room,freeroom这两个表数据量都不大,小于5000行。  
         2:room\_type\_num,这个表有十多万数据，room\_type\_id没有创建索引。

**查询语句:**

![ExpandedBlockStart.gif][]

FROM      free\_room f WITH ( NOLOCK )  
                            LEFT JOIN freeroom fr WITH ( NOLOCK ) ON f.id  =  fr.free\_room\_id  
                            INNER JOIN room\_type\_num r WITH ( NOLOCK ) ON r.room\_type\_id  =  f.room\_type\_id

执行以上结果，发现在关联room\_type\_num表时，系统选择了hash join，最优的情况绝不应该是hash join,因为不太符合hash join的要求。 我之前的文章简单说明了loop join以及hash join的概念,先贴出来看下：

**第一种算法:NESTED LOOP:**

定义: 对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择。在嵌套循环中，内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大（大于1 万不适合），要把返回子集较小表的作为外表。  
                                                     
      **第二种算法:HASH JOIN :**

定义: 散列连接是做大数据集连接时的常用方式，优化器使用两个表中较小的表（或数据源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高I/O 的性能。

dba建议在room\_type\_id创建索引，然后在SQL脚本中把inner join 改写成 inner loop join room\_type\_num。修改后的执行效率比较如下:

![10-6-3-3.jpg][]

**问题:**我在测试后，发现room\_type\_id创建索引后，SQL查询优化器就不再采用hash join room\_type\_num了，自动选择了最优的loop join。在SQL脚本中人为的写优化提示，并不需要特别指出，只要我们索引创建到位，优化器也会配合我们选择最优的执行计划进行查询,否则只能程序员自己走下后门了。

**图一为没有创建索引前的执行计划:**

![10-6-3-1.jpg][]

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server parse and compile time:

CPU time = 15 ms, elapsed time = 189 ms.

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 1 ms.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times:

CPU time = 94 ms,  elapsed time = 101 ms.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times:

CPU time = 16 ms,  elapsed time = 6 ms.

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 1 ms.

(0 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'free\_room'. Scan count 2, logical reads 16, physical reads 1, read-ahead reads 48, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'free\_room\_use'. Scan count 1, logical reads 8, physical reads 5, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(20 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:

**CPU time = 15 ms,  elapsed time = 100 ms.**

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 1 ms.

**图二为增加索引后的执行计划:**

![10-6-3-2.jpg][]

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server parse and compile time:

CPU time = 16 ms, elapsed time = 45 ms.

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 1 ms.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times:

CPU time = 109 ms,  elapsed time = 102 ms.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 1 ms.

(0 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'free\_room'. Scan count 2, logical reads 16, physical reads 1, read-ahead reads 48, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'free\_room\_use'. Scan count 1, logical reads 8, physical reads 5, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(20 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:

** CPU time = 0 ms,  elapsed time = 27 ms.**

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms,  elapsed time = 1 ms.

**总结:**在关联表的关联键上创建合适的索引会影响到SQL优化器对执行计划的选择。数据量的大小以及数据分布情况都会影响执行计划，所以对于SQL优化来说，不能一味套用概念，要实实在在的看到效果才算优化成功。

[http_www.cnblogs.com_aspnet2008_archive_2010_06_03_1750911.html]: http://www.cnblogs.com/aspnet2008/archive/2010/06/03/1750911.html
[Link 1]: http://www.cnblogs.com/ASPNET2008/archive/2010/06/03/1750911.html
[ExpandedBlockStart.gif]: /images/20220824/f82dc0c054ca4c599c8222d8033ea5fc.png
[10-6-3-3.jpg]: /images/20220824/789a9b1d296340d1acb9eb41c3cbb865.png
[10-6-3-1.jpg]: /images/20220824/965e9241d1c44520afbd1ce62b57996b.png
[10-6-3-2.jpg]: /images/20220824/b42de9dbb6df430e84959b51e771561e.png