我欠 SQL Server 优化器一个道歉

前天的文章，写了 SQL Server 的 Predicate Pushdown.

SQL Server 的弱鸡 Predicate Pushdown

举例想说明的是，子查询能不能接收外层的条件判断。

文中的例子没有举好，误判了 SQL Server 不能进行 Predicate Pushdown, 实际上它的优化器总是进行谓词下推。

下面在原来的基础上，再做一遍实验与说明。

上例子，以 AdventureWorks2016 数据库为背景，完成一段带子查询的SQL:

  SELECT Prod.Name AS ProductName, Sales.ModifiedDate  FROM Production.Product Prod    INNER JOIN (   SELECT TOP 10 ProductID, ModifiedDate,OrderQty   FROM Sales.SalesOrderDetail Detail    ORDER BY ProductID,ModifiedDate DESC   ) Sales  on Sales.ProductID = Prod.ProductID  WHERE Sales.ModifiedDate BETWEEN '2011-01-01' AND '2011-10-01' AND Sales.OrderQty= 2 

例子中，涉及到的两张表，结构与索引如下：

--表结构CREATE TABLE [Production].[Product]( [ProductID] [int] IDENTITY(1,1) NOT NULL, [Name] [dbo].[Name] NOT NULL, [ProductNumber] [nvarchar](25) NOT NULL, ... [DiscontinuedDate] [datetime] NULL, [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, [ModifiedDate] [datetime] NOT NULL, CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED ( [ProductID] ASC) ) ON [PRIMARY]GO CREATE TABLE [Sales].[SalesOrderDetail]( [SalesOrderID] [int] NOT NULL, [SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL, [CarrierTrackingNumber] [nvarchar](25) NULL, [OrderQty] [smallint] NOT NULL, [ProductID] [int] NOT NULL, [SpecialOfferID] [int] NOT NULL, [UnitPrice] [money] NOT NULL, [UnitPriceDiscount] [money] NOT NULL, [LineTotal]  AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))), [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, [ModifiedDate] [datetime] NOT NULL, CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED ( [SalesOrderID] ASC, [SalesOrderDetailID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO--索引：ALTER TABLE [Sales].[SalesOrderDetail] ADD  CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED ( [SalesOrderID] ASC, [SalesOrderDetailID] ASC) GOCREATE NONCLUSTERED INDEX [IDX_MOD_QTY_PROD_1] ON [Sales].[SalesOrderDetail]( [ModifiedDate] ASC)INCLUDE ( [OrderQty], [ProductID])   GO 

这段 SQL 里，子查询有了 Top N 这样的逻辑，外层明明有条件筛选，也是不能下推到子查询的. 因为这 Top  N 就是要依据 ModifiedDate 来判断的。而外层的 ModifiedDate 的条件筛选，正好与之冲突。

从它的执行计划，可以看到，filter这一层已经放到子查询后面（见图左边）；子查询使用了 index scan，没有做任何的条件筛选(Predicate).

同样，衍生下子查询：

   SELECT Prod.Name AS ProductName, Sales.ModifiedDate  FROM Production.Product Prod    INNER JOIN (   SELECT TOP 10 ProductID, ModifiedDate,OrderQty   FROM Sales.SalesOrderDetail Detail    WHERE Detail.ModifiedDate BETWEEN '2011-01-01' AND '2011-10-01'     ORDER BY ProductID,ModifiedDate DESC   ) Sales  on Sales.ProductID = Prod.ProductID  WHERE  Sales.OrderQty= 2 

当我们留 OrderQty = 2 在外层查询，那么它也是不能被下推到子查询：

而什么样的条件下，外层的条件筛选，能够下推到子查询呢？

 SELECT Prod.Name AS ProductName, Sales.ModifiedDate FROM Production.Product Prod  INNER JOIN (   SELECT ProductID,ModifiedDate,OrderQty,ROW_NUMBER()OVER(PARTITION BY ProductID,OrderQty ORDER BY ModifiedDate DESC) AS RNK   FROM Sales.SalesOrderDetail Detail    ) Sales   ON Sales.ProductID = Prod.ProductID WHERE Sales.ModifiedDate BETWEEN '2011-01-01' AND '2011-10-01'   and Sales.OrderQty = 2 

像这样的，子查询没有筛检数据，就可以。

稍稍要注意的是，seek predicate 与 predicate 区别。

Seek Predicate 是索引访问方式，这里的索引，以ModifiedDate作为键。OrderQty, ProductID放在叶子节点。

Predicate 是筛选条件， OrderQty 不能作为 SARG 键，所以只做筛选。

SARG: Search Argument-able

仔细研究 Predicate,  跟在子查询外，就是 Filter ，说明没有被下推(Pushdown); 而用在子查询内，在索引上做了条件筛选，说明被下推了。

再来个更复杂的下推：

 SELECT Prod.Name AS ProductName, Sales.ModifiedDate FROM Production.Product Prod  INNER JOIN (     SELECT ProductID,ModifiedDate,OrderQty AS Quantity,ROW_NUMBER()OVER(PARTITION BY ProductID,OrderQty ORDER BY ModifiedDate DESC) AS RNK  FROM Sales.SalesOrderDetail Detail      UNION ALL   SELECT *   FROM (   SELECT ProductID,ModifiedDate,OrderQty AS Quantity,ROW_NUMBER()OVER(PARTITION BY ProductID,OrderQty ORDER BY ModifiedDate ASC) AS RNK   FROM Sales.SalesOrderDetail Detail   )BTM2   WHERE BTM2.RNK<=2   ) Sales   ON Sales.ProductID = Prod.ProductID WHERE Sales.ModifiedDate BETWEEN '2011-01-01' AND '2011-10-01'   and Sales.Quantity = 2 

针对子查询上半部分：

SELECT ProductID,ModifiedDate,OrderQty AS Quantity,ROW_NUMBER()OVER(PARTITION BY ProductID,OrderQty ORDER BY ModifiedDate DESC) AS RNK
  FROM Sales.SalesOrderDetail Detail 

优化器做了Predicate 下推，是可以理解的。

但优化器对下半部分，也做了下推，但没有全推，而是只推了 Quantity =2. 虽然子查询的列名换了，但依旧还是被优化器识别到了.

但 ModifiedDate 就不能被下推了，只能跟在子查询后面做 Filter.

嗯，SQL Server Predicate Pushdown 很智能，也很强大。