logo标签列表

聚集索引在同一列上的“查找谓词”和“谓词”。

sql-serverindex
我有一个带有聚集索引的主键列，并且正在对其进行范围查询。 问题是扫描只使用范围的第一部分作为寻找谓词，将范围的另一侧作为剩余谓词。 这导致读取所有行直到@upper限制。 我正在使用两个参数来定义范围。 
declare
    @lower numeric(18,0) = 1000,
    @upper numeric(18,0) = 1005;

select * from messages
where msg_id between @lower+1 and @upper;
在这种情况下，实际执行计划显示： - 谓词：messages.msg_id >= lower - 寻找谓词：messages.msg_id < @upper - 读取的行数：1005 表定义（简化）： 
CREATE TABLE [dbo].[messages](
    [msg_id] [numeric](18, 0) IDENTITY(0,1) NOT NULL,
    [col2] [varchar](32) NOT NULL,
 CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED 
(
    [msg_id] ASC
)

更多信息

当使用常量而不是变量时，两个谓词都是“Seek” 尝试过Option (Optimize for (@lower=1000))，但没有成功

2个回答

从您的原始查询开始：

declare
    @lower numeric(18,0) = 1000,
    @upper numeric(18,0) = 1005;

select * from [messages]
where msg_id between @lower+1 and @upper;
你添加的1默认具有整数数据类型。当将一个整数值添加到一个numeric(18,0)值时，SQL Server会应用data type precedence的规则。int的优先级较低，因此它会被转换为numeric(1,0)。你的查询等同于以下内容： 
declare
    @lower numeric(18,0) = 1000,
    @upper numeric(18,0) = 1005;

select * from [messages]
where msg_id between @lower+CAST(1 AS NUMERIC(1, 0)) and @upper;

对涉及@lower的表达式的数据类型，应用了一组不同的规则来确定精度、标度和长度。仅仅使用NUMERIC(18,0)是不安全的，因为可能会发生溢出（以999,999,999,999,999,999和1为例）。这里适用的规则是：

╔═══════════╦═════════════════════════════════════╦════════════════╗
║ Operation ║          Result precision           ║ Result scale * ║
╠═══════════╬═════════════════════════════════════╬════════════════╣
║ e1 + e2   ║ max(s1, s2) + max(p1-s1, p2-s2) + 1 ║ max(s1, s2)    ║
╚═══════════╩═════════════════════════════════════╩════════════════╝
对于您的表达式，结果的精度为： 
max(0, 0) + max(18 - 0, 1 - 0) + 1 = 0 + 18 + 1 = 19

并且结果的比例为0。您可以通过在SQL Server中运行以下代码来验证：

declare
@lower numeric(18,0) = 1000,
@upper numeric(18,0) = 1005;

SELECT 
  SQL_VARIANT_PROPERTY(@lower+1, 'BaseType') lower_exp_BaseType
, SQL_VARIANT_PROPERTY(@lower+1, 'Precision') lower_exp_Precision
, SQL_VARIANT_PROPERTY(@lower+1, 'Scale') lower_exp_Scale;
这意味着你的原始查询等同于以下内容： 
declare
    @lower numeric(19,0) = 1000 + 1,
    @upper numeric(18,0) = 1005;

select * from [messages]
where msg_id between @lower and @upper;

如果值能够隐式转换为NUMERIC(18, 0)，SQL Server只能使用@lower进行聚集索引搜索。将NUMERIC(19, 0)的值转换为NUMERIC(18, 0)是不安全的。因此，该值被应用为谓词而不是搜索谓词。一种解决方法是执行以下操作：

declare
    @lower numeric(18,0) = 1000,
    @upper numeric(18,0) = 1005;

select * from [messages]
where msg_id between TRY_CAST(@lower+1 AS NUMERIC(18,0)) and @upper;

该查询可以同时处理过滤器和搜索谓词：

seek predicates

我的建议是，如果可能的话，将表中的数据类型更改为BIGINT。 BIGINT比NUMERIC(18,0)少占用一个字节，并且可以享受到NUMERIC(18,0)无法获得的性能优化，包括更好的位图过滤支持。

在您的过滤器之一（@lower+1）中有一个表达式，这导致引擎执行常规谓词而不是寻求谓词（使其无法进行 SARG 优化）。

尝试在SELECT语句之前更改过滤器的值，您将看到两个边界都被正确地用作寻找边界。

declare
    @lower numeric(18,0) = 1000 + 1,
    @upper numeric(18,0) = 1005;

select * from messages
where msg_id between @lower and @upper;

enter image description here