我欠 SQL Server 优化器一个道歉
共 4087字,需浏览 9分钟
·
2021-02-17 08:19
点击蓝色“有关SQL”关注我哟
加个“星标”,天天与10000人一起快乐成长
图 |Lenis
前天的文章,写了 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 的条件筛选,正好与之冲突。
从它的执行计划,可以看到,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 很智能,也很强大。
往期精彩: