主页 / dba / 问题 / 180409
Accepted
Chris Halcrow
Asked: 2017-07-21 21:40:19 +0800 CST

在 WHERE 中具有日期范围的 SQL Server 查询 - 执行计划使用聚集的 PK 实例而不是日期列索引

  • 772

我在 SQL Server 中有以下查询：

declare @timestamp_start date = getdate() - 1
declare @timestamp_end date = getdate()

SELECT  
    my_date = a.itemTimestamp
FROM
    my_table a with(nolock)
WHERE 
    (@timestamp_start is null OR a.itemTimestamp >= @timestamp_start)
    AND (@timestamp_end is null OR a.itemTimestamp < @timestamp_end)

它非常慢（< 2000 条记录需要 90 秒），因为执行计划使用 'my_table' 的 PK 列的聚集索引：

在此处输入图像描述

为什么会发生这种情况，如何优化查询性能？

sql-server execution-plan

3 个回答

  1. Best Answer

    itemTimestamp 的数据类型是什么？该列是否已编入索引？该列是否可以为空？

    假设答案分别是datetime，是和否，您可能会考虑

    WHERE  a.itemTimestamp >= isnull(@timestamp_start, '17530101')
       AND a.itemTimestamp < isnull(@timestamp_end, '99991231')

    如果您要更改datetime2并实际存储 1753 之前的日期，则需要将下限条件更改为00010101。这也不会带回与上限匹配的日期9999-12-31- 但在现实世界中，这不太可能导致问题。

    • 5

  2. 是my_table.itemTimestamp实际DATE数据类型还是DATETIME？对于没有时间组件的数据类型，似乎是一个奇怪的列名；-)。如果它是 a DATETIME，那么第 1 步是为变量使用正确的数据类型。但是，如果它确实是一DATE列，请尝试以下查询。它打破了 4 种排列，以便 SQL Server 可以拥有简单、简洁的查询，而不是一个包含一堆OR通常使查询计划复杂化的 s 的全能查询。

    DECLARE @timestamp_start DATE = GETDATE() - 1;
DECLARE @timestamp_end DATE = GETDATE();

IF (@timestamp_start IS NULL AND @timestamp_end IS NULL)
BEGIN
  SELECT   a.itemTimestamp AS [my_date]
  FROM     my_table a WITH (NOLOCK);
END;
ELSE
BEGIN
  IF (@timestamp_start IS NOT NULL AND @timestamp_end IS NOT NULL)
  BEGIN
    SELECT   a.itemTimestamp AS [my_date]
    FROM     my_table a WITH (NOLOCK)
    WHERE    a.itemTimestamp >= @timestamp_start
    AND      a.itemTimestamp < @timestamp_end;
  END;
  ELSE
  BEGIN
    IF (@timestamp_start IS NULL)
    BEGIN
      SELECT   a.itemTimestamp AS [my_date]
      FROM     my_table a WITH (NOLOCK)
      WHERE    a.itemTimestamp < @timestamp_end;
    END;
    ELSE
    BEGIN
      SELECT   a.itemTimestamp AS [my_date]
      FROM     my_table a WITH (NOLOCK)
      WHERE    a.itemTimestamp >= @timestamp_start;
    END;
  END;
END;
    • 1

  3. 只是为了扮演魔鬼的拥护者（在某些圈子里这是不赞成的），如果你确定你希望它使用那个索引并且它会改进事情，添加一个索引提示......只要记住你把它放在那里，如果你做出改变将来的索引或查询。添加：WITH (INDEX (###YOUR INDEX NAME HERE##)) 

    declare @timestamp_start date = getdate() - 1
declare @timestamp_end date = getdate()

SELECT  
    my_date = a.itemTimestamp
FROM
    my_table a WITH (INDEX (###YOUR INDEX NAME HERE##))
WHERE 
    (@timestamp_start is null OR a.itemTimestamp >= @timestamp_start)
    AND (@timestamp_end is null OR a.itemTimestamp < @timestamp_end)
    • 0

相关问题