如何通过合理索引优化SQL Server查询以消除哈希匹配

我们近期通过Microsoft SQL Server Management Studio学习数据库管理，核心目标是利用索引优化查询与数据管理，重点减少哈希匹配（Hash Match）和表扫描（Table Scan）。目前已成功消除所有表扫描，但哈希匹配问题始终无法解决。

背景信息 #

• 使用示例数据库中的4张表：

  [Sales].[OrderDetails]
  [Sales].[Orders]
  [Production].[Products]
  [Production].[Categories]
  

• 查询需求：展示类别名称、产品销售数量及交易总金额。

当前查询语句 #

SELECT C.categoryname
       ,COUNT(*) AS QTY
       ,SUM(OD.unitprice * OD.qty) AS 'TotalAmountUSD'
FROM Sales.OrderDetails AS OD
    JOIN Sales.Orders O 
        ON (OD.orderid = O.orderid)
    JOIN Production.Products P 
        ON (OD.productid = P.productid)
    JOIN Production.Categories C 
        ON (P.categoryid = C.categoryid)
WHERE O.orderdate BETWEEN CAST('2015-01-01' AS DATE) AND CAST('2015-12-31' AS DATE)
GROUP BY C.CategoryName;

已创建的索引（消除表扫描） #

CREATE INDEX IDX_OrderDetails_AllColumns ON Sales.OrderDetails (orderid, productid, unitprice, qty);
CREATE INDEX IDX_Categories_AllColumns ON Production.Categories (categoryid, categoryname);
CREATE INDEX IDX_Products_AllColumns ON Production.Products (productid, categoryid);
CREATE INDEX IDX_Orders_OrderDate_OrderId ON Sales.Orders (orderdate) INCLUDE (orderid);

针对哈希匹配尝试过的索引 #

单值索引 #

CREATE INDEX IDX_Orders_OrderDate ON Sales.Orders(orderdate);
CREATE INDEX IDX_OrderDetails_OrderId ON Sales.OrderDetails(orderid);
CREATE INDEX IDX_Products_CategoryId ON Production.Products(categoryid);
CREATE INDEX IDX_Categories_CategoryName ON Production.Categories(categoryname);

关联连接场景的索引 #

CREATE INDEX IDX_OrderDetails_OrderID_UnitPrice_QTY ON Sales.OrderDetails(orderid, unitprice, qty);
CREATE INDEX IDX_OrdersDetails_OrderID_ProductID_UnitPrice_QTY ON Sales.OrderDetails(orderid, productid, unitprice, qty);

期望目标 #

找到合适的索引方案，消除哈希匹配或将其替换为嵌套循环（Nested Loop）等更优操作。

内容的提问来源于stack exchange，提问作者Iker-JK