我欠 SQL Server 优化器一个道歉

点击蓝色“有关SQL”关注我哟

加个“星标”，天天与10000人一起快乐成长

图 |Lenis

前天的文章，写了 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
) 
GO

CREATE NONCLUSTERED INDEX [IDX_MOD_QTY_PROD_1] ON [Sales].[SalesOrderDetail]
(
 [ModifiedDate] ASC
)
INCLUDE ( [OrderQty], [ProductID]) 
 
 
GO

 

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

image

从它的执行计划，可以看到，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 在外层查询，那么它也是不能被下推到子查询：

image

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

 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 

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

image

image

稍稍要注意的是，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. 虽然子查询的列名换了，但依旧还是被优化器识别到了.

image

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

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

--完--

往期精彩：

本号精华合集（三）

如何写好 5000 行的 SQL 代码

如何提高阅读 SQL 源代码的快感

我在面试数据库工程师候选人时，常问的一些题

零基础 SQL 数据库小白，从入门到精通的学习路线与书单