基于表驱动的参数化视图因基数估计不佳导致性能问题

需求背景 #

我们尝试创建若干通过参数表存储的参数进行过滤的视图，目标是当过滤条件变更时，无需重新部署大量视图、更新报表或前端代码，仅修改参数表就能实现数据驱动的过滤。

基于StackOverflow2010数据库的示例 #

1. 创建参数表并插入数据 #

CREATE TABLE Parameters
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    GroupName NVARCHAR(30),
    ParameterName NVARCHAR(30),
    ParameterValue NVARCHAR(255),
)

INSERT INTO Parameters VALUES ('Filtering Rules','LocationEqualityFilter','Paris, France')
INSERT INTO Parameters VALUES ('Filtering Rules','TitleLikeFilter','sql')

2. 创建覆盖索引 #

CREATE INDEX IX_Location ON Users
(
    [Location]
)
INCLUDE
(
    DisplayName,
    Reputation
)

CREATE INDEX IX_Title ON Posts
(
    Title
)
INCLUDE
(
    Tags,
    OwnerUserId
)

3. 创建参数化视图 #

CREATE OR ALTER VIEW vw_PostsByLocationWithTitle
AS
SELECT  u.DisplayName,
        u.Reputation,
        pos.tags
FROM    Users u
        JOIN Parameters p
            ON u.Location = p.ParameterValue AND
                p.ParameterName = 'LocationEqualityFilter'
        JOIN Posts pos
            ON pos.OwnerUserId = u.Id
        JOIN Parameters p1
            ON pos.Title LIKE p1.ParameterValue + N'%' AND
                p1.ParameterName = N'TitleEqualityFilter' 

4. 查询视图及性能问题 #

执行查询：

SET STATISTICS IO ON
SELECT * FROM vw_PostsByLocationWithTitle

逻辑读取情况：

Table 'Users'. Scan count 7, logical reads 1873, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Parameters'. Scan count 2, logical reads 4, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Posts'. Scan count 1, logical reads 222, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

对比硬编码字面量的等效查询：

SELECT  u.DisplayName,
        u.Reputation,
        p.Tags
FROM    Users u
        JOIN posts p
            ON p.OwnerUserId = u.Id
WHERE   Location = N'Paris, France' AND
        p.Title LIKE N'SQL%'

逻辑读取情况：

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Posts'. Scan count 1, logical reads 222, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Users'. Scan count 1, logical reads 10, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

即使是更简单的关联查询，基数估计也远不如字面量查询：
参数化关联查询：

SELECT  u.DisplayName,
        u.Reputation
FROM    Users u
        JOIN Parameters p
            ON u.Location = p.ParameterValue AND
                p.ParameterName = N'LocationEqualityFilter'

等效字面量查询：

SELECT  u.DisplayName,
        u.Reputation
FROM    Users u
WHERE   Location = N'Paris, France'

前者基数估计偏差明显，导致性能问题，替换为字面量即可解决。

5. 修复简单查询基数估计的方法（无法用于视图） #

方法1：使用变量

DECLARE @Location NVARCHAR(100) = (SELECT ParameterValue FROM Parameters WHERE ParameterName = N'LocationEqualityFilter')

SELECT  u.DisplayName,
        u.Reputation
FROM    Users u
WHERE   Location = @Location

SQL Server可利用变量值进行直方图查询，获得准确的基数估计。

方法2：使用临时表

SELECT  ParameterValue 
INTO    #Location
FROM    Parameters 
WHERE   ParameterName = N'LocationEqualityFilter'

SELECT  u.DisplayName,
        u.Reputation
FROM    Users u
        JOIN #Location l
            ON u.Location = l.ParameterValue

DROP TABLE #Location

临时表的统计信息能让SQL Server获取ParameterValue的具体值，进而对Users表做直方图查询，获得准确估计。

6. 其他尝试仍存在问题 #

尝试子查询写法，依然有基数估计问题：

SELECT  u.DisplayName,
        u.Reputation
FROM    Users u
WHERE   Location = (SELECT CAST(ParameterValue AS NVARCHAR(100)) FROM Parameters WHERE ParameterName = N'LocationEqualityFilter')

尝试创建索引视图：

CREATE OR ALTER VIEW dbo.LocationEqualityFilter
WITH SCHEMABINDING AS
SELECT  ParameterValue
FROM    dbo.Parameters
WHERE   ParameterName = N'LocationEqualityFilter' 
GO

CREATE UNIQUE CLUSTERED INDEX IX_LocationEqualityFilter ON dbo.LocationEqualityFilter
(
    ParameterValue
)

该索引视图的统计信息显示返回1行且包含具体值，但优化器扫描索引后，对Users表的查找估计依然不准确。即使显式引用该视图，问题仍存在，只有添加WITH (NOEXPAND)提示才能获得正确估计。

问题与可选方案 #

是否有方法在视图中修复基数估计问题，或采用其他方式实现这种数据驱动的参数化？

已想到的可选方案：

• 将每个ParameterName拆分到单独的表中，模拟临时表的修复效果，需在Parameters表上创建触发器，当记录变更时更新这些新表。
• 创建包含字面量的视图，当Parameters表中相关值更新时，通过触发器修改视图定义。
• 创建标量UDF返回ParameterValue的字面量，在WHERE子句中使用，当参数表更新时通过触发器修改UDF定义（需SQL Server 2019及以上版本支持标量函数内联）。
• 使用上述索引视图方案（需添加WITH (NOEXPAND)提示）。

内容的提问来源于stack exchange，提问作者dualcoredba