我欠 SQL Server 优化器一个道歉

有关SQL

共 4087字,需浏览 9分钟

 ·

2021-02-17 08:19

点击蓝色“有关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 数据库小白,从入门到精通的学习路线与书单










浏览 29
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报