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

很久没有写SQL相关的文章了,主要是现在技术部分工比以前明确了。网站部门并不自己写SQL查询数据，数据有其它部门提供服务。但并不是所有情况都是这样，有些项目由于之前没有管理，所以只能自己完成。在这次写的一个SQL查询中，体会到了在做join联接时，关联的键需要创建索引的重要性。

      说明：
         1：free_room,freeroom这两个表数据量都不大,小于5000行。
         2:room_type_num,这个表有十多万数据，room_type_id没有创建索引。

      查询语句:

1. FROM      free_room f WITH ( NOLOCK ) 
2.                             LEFT JOIN freeroom fr WITH ( NOLOCK ) ON f.id = fr.free_room_id 
3.                             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。修改后的执行效率比较如下:

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

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

1. SQL Server parse and compile time: 
2.    CPU time = 0 ms, elapsed time = 1 ms. 
3.   
4. SQL Server Execution Times: 
5.    CPU time = 0 ms,  elapsed time = 1 ms. 
6. SQL Server parse and compile time: 
7.    CPU time = 15 ms, elapsed time = 189 ms. 
8.   
9. SQL Server Execution Times: 
10.    CPU time = 0 ms,  elapsed time = 1 ms. 
11.   
12. SQL Server Execution Times: 
13.    CPU time = 0 ms,  elapsed time = 1 ms. 
14.   
15. SQL Server Execution Times: 
16.    CPU time = 0 ms,  elapsed time = 1 ms. 
17. DBCC execution completed. If DBCC printed error messages, contact your system administrator.