6.2.4 加速联接操作的索引

 

联接操作用于将表或者中间结果联接在一起。SQL Server使用三种类型的联接操作：

l          嵌套循环联接  这种联接使用一个联接输入作为内部输入表，另一个联接输入作为外部输入表。嵌套循环会在每一个内部输入行进行一次并在外部输入中搜索相应的行。当外部输入联接条件的列上有索引的时候，SQL Server可以使用索引查找在外部输入中查找行。如果不存在索引，SQL Server就只好根据内部输入的每一个行，使用扫描操作在外部输入中找出匹配的行。嵌套循环常用于内部输入的行数较少的情况，因为此时这种联接方式最高效。

l          合并联接  这种联接适用于联接输入在其联接列上已排好序时。在合并联接操作中，SQL Server对已排序的输入进行一次扫描，然后将数据合并在一起。合并联接非常高效，但数据必须被提前排序，这意味着在联接列上必须有索引。如果联接列上没有索引，SQL Server会将输入行先排序。但是这种情况并不经常发生，因为排序数据工作的效率通常都较低。

l          哈希联接  这种联接用于海量、未排序、没有索引的输入。哈希联接在联接列上使用哈希操作来将输入联接在一起。为了计算并存储哈希操作的结果，SQL Server所需要的内存和CPU时间都比其它联接操作多。

Ø       检验联接操作

1.    打开SQL Server Management Studio。打开“新建查询”窗口并更改数据库上下文为“AdventureWorks”。

2.    键入并执行以下语句，不要忘了包括实际的执行计划。此示例的代码包含在示例文件ExaminingJoinOperations.sql中。这个语句的执行计划如图6.16所示。可以看出，SQL Server使用一个索引查找来获取内部输入的行(来自OrderDetails表的数据)，然后使用一个嵌套循环联接操作。因为此时在外部输入(来自Orders表的数据)中只有一个匹配行。由于匹配的索引存在，因此外部输入的匹配行同样通过索引查找获取。还可以在“消息”选项卡中看到，SQL Server只需要5次页读操作来获取数据。

 

SET STATISTICS IO ON

SELECT o.SalesOrderID, o.OrderDate, od.ProductID

FROM dbo.Orders o INNER JOIN dbo.OrderDetails od

ON o.SalesOrderID = od.SalesOrderID

WHERE o.SalesOrderID = 43659

图6.16  执行计划

 

3.    更改查询获取更多的SalesOrderID。对此，由于在索引中有排序的行并且内部输入的行较多，因此SQL Server会使用一个合并联接。键入并执行以下语句。可以看出，SQL Server需要19次页读操作来执行这个查询。

图6.17  执行计划

 

SELECT o.SalesOrderID, o.OrderDate, od.ProductID

FROM dbo.Orders o INNER JOIN dbo.OrderDetails od

ON o.SalesOrderID = od.SalesOrderID

WHERE o.SalesOrderID BETWEEN 43659 AND 44000

 

4.    键入并执行以下批操作，它将联接所需要的索引删除。

 

DROP INDEX CLIDX_Orders_SalesOrderID

ON dbo.Orders

 

DROP INDEX CLIDX_OrderDetails

ON dbo.OrderDetails

 

5.    查询执行刚才执行的SELECT语句(如下所示)并检查执行计划及I/O操作发生了什么变化。如图6.18所示。

 

SELECT o.SalesOrderID, o.OrderDate, od.ProductID

FROM dbo.Orders o INNER JOIN dbo.OrderDetails od

ON o.SalesOrderID = od.SalesOrderID

WHERE o.SalesOrderID = 43659

 

SELECT o.SalesOrderID, o.OrderDate, od.ProductID

FROM dbo.Orders o INNER JOIN dbo.OrderDetails od

ON o.SalesOrderID = od.SalesOrderID

WHERE o.SalesOrderID BETWEEN 43659 AND 44000

       前一个查询计划表明，由于第一个查询联接的内部输入很小，因此SQL Server对其再次使用了嵌套循环联接。而对于第二个联接，由于其数据输入不再是已排序的，因此SQL Server对其使用了哈希联接。由于没有可用的索引，SQL Server对于前面的查询只能对基表进行表扫描，这两个查询共请求了1000次页读操作。

通过本小节可以了解到，具有能提供支持的索引对于联接操作是非常重要的。最为一个通用的规则，由于外键约束几乎是所有查询的联接条件，因此它们都应该被索引。

图6.18  执行计划