每个N的最新记录的最佳执行查询

Question

我所处的情境如下。

我有一个相当大的表格，需要查询其中最新的记录。以下是针对查询必要列的创建语句：

CREATE TABLE [dbo].[ChannelValue](
   [ID] [bigint] IDENTITY(1,1) NOT NULL,
   [UpdateRecord] [bit] NOT NULL,
   [VehicleID] [int] NOT NULL,
   [UnitID] [int] NOT NULL,
   [RecordInsert] [datetime] NOT NULL,
   [TimeStamp] [datetime] NOT NULL
   ) ON [PRIMARY]
GO

ID列是一个主键，而VehicleID和TimeStamp上有一个非聚集索引。

CREATE NONCLUSTERED INDEX [IX_ChannelValue_TimeStamp_VehicleID] ON [dbo].[ChannelValue] 
(
    [TimeStamp] ASC,
    [VehicleID] ASC
)ON [PRIMARY]
GO

我正在优化的表格有超过2300万行，并且只有查询需要操作的大小的十分之一。

我需要返回每个VehicleID的最新行。

我已经在StackOverflow上查看了此问题的回答，并进行了大量的谷歌搜索。似乎在SQL Server 2005及以上版本中有3或4种常见方法可以实现这一点。

到目前为止，我找到的最快方法是以下查询：

SELECT cv.*
FROM ChannelValue cv
WHERE cv.TimeStamp = (
SELECT
    MAX(TimeStamp)
FROM ChannelValue
WHERE ChannelValue.VehicleID = cv.VehicleID
)

目前表中的数据量大约需要6秒执行，这在合理范围内，但是在生产环境中，表将包含更多数据，查询开始变得太慢。

通过查看执行计划，我担心的是SQL Server为返回行所做的工作。

我不能发布执行计划图像，因为我的声望还不够高，但索引扫描正在解析表中的每一行，从而使查询变得非常缓慢。

我尝试过使用多种不同的方法重写查询，包括使用SQL 2005分区方法，如下所示：

WITH cte
AS (
    SELECT *,
    ROW_NUMBER() OVER(PARTITION BY VehicleID ORDER BY TimeStamp DESC) AS seq
     FROM ChannelValue
)

SELECT
   VehicleID,
   TimeStamp,
   Col1
FROM cte
WHERE seq = 1

但是那个查询的性能甚至比较糟糕。

我已经尝试了像这样重新构造查询，但结果速度和查询执行计划几乎相同：

SELECT cv.*
FROM (
   SELECT VehicleID
    ,MAX(TimeStamp) AS [TimeStamp]
   FROM ChannelValue
   GROUP BY VehicleID
) AS [q]
INNER JOIN ChannelValue cv
   ON cv.VehicleID = q.VehicleID
   AND cv.TimeStamp = q.TimeStamp

在表结构方面我有一些灵活性可用（虽然受到限制），因此我可以添加索引、索引视图等，甚至可以向数据库中添加其他表。

非常感谢任何帮助。

编辑：已添加执行计划图像链接。

- AJax

---

问题已经得到点赞，您现在应该可以发布您的图片了 :) - Jamiec

你能展示一下在VehicleID和TimeStamp上创建非聚集索引的create index命令吗？ - Aaron Bertrand

你尝试过在 TimeStamp 上添加索引吗？ 同时要注意测试时间戳相等性可能会导致问题。 - Frazz

我已经添加了非聚集索引创建脚本。我也尝试过对索引进行调整，但是在查询执行计划方面仍然遇到了同样的问题。 - AJax

对于那些感兴趣的人，CROSS APPLY 方法在我们的环境中被证明是最快的，但它需要正确的索引。我在 VehicleID 和 TimeStamp 上添加了一个唯一索引，并在 CROSS APPLY 查询中添加了一个索引查询提示。最终结果是查询在我的开发机器上执行大约需要 500 毫秒。 - AJax

Answer 1

根据您的数据（每个组有多少行？）和索引情况而定。

请参考优化每组前N个查询，了解三种方法的性能比较。

对于您的情况，由于只有少量车辆但拥有数百万行数据，我建议在VehicleID, Timestamp上添加索引并执行以下操作：

SELECT CA.*
FROM   Vehicles V
       CROSS APPLY (SELECT TOP 1 *
                    FROM   ChannelValue CV
                    WHERE  CV.VehicleID = V.VehicleID
                    ORDER  BY TimeStamp DESC) CA  

- Martin Smith

---

仅提供链接的答案应该被视为评论（这是“管理层”有时告诉我的...） - gbn

@gbn - 我很高兴这可以回答OP的问题！ - Martin Smith

谢谢Martin，我现在正忙着阅读那篇文章。 - AJax

在我的测试环境中有 8 个不同的车辆ID，但在生产环境中大约会有 286 个。 - AJax

@Ajax - 你需要在 VehicleID, Timestamp 上建立索引，然后从另一个表中选择仅包含不同车辆的记录（我假设你有一个名为 Vehicles 的表，其中 VehicleId 是主键），并使用 CROSS APPLY ... TOP 1。 - Martin Smith

Answer 2

试试这个：

SELECT SequencedChannelValue.* -- Specify only the columns you need, exclude the SequencedChannelValue
FROM
    (
        SELECT 
            ChannelValue.*,   -- Specify only the columns you need
            SeqValue = ROW_NUMBER() OVER(PARTITION BY VehicleID ORDER BY TimeStamp DESC)
        FROM ChannelValue
    ) AS SequencedChannelValue
WHERE SequencedChannelValue.SeqValue = 1

预计进行表格或索引扫描，因为您没有以任何方式过滤数据。您正在请求所有车辆ID的最新时间戳 - 查询引擎必须查看每一行以找到最新的时间戳。

您可以通过缩小返回的列数（不要使用SELECT *）并提供由VehicleID + TimeStamp组成的索引来帮助它。

- Tracy McKibben

---

我在VehicleID和TimeStamp上建立了索引，并尝试使用Partition By方法，但效果明显更差。为了保险起见，我运行了您发布的查询，但返回数据需要几分钟的时间。 - AJax

请注意我提到了限制返回的列。如果您从表中返回每一列，那么仅在VehicleID和TimeStamp上创建索引是没有用的。我的语句的查询计划是什么样子？ - Tracy McKibben

嗨，特蕾西，很遗憾我需要表中的所有列。这里是一个链接，指向你的查询执行计划的图像。它与我尝试使用ROW_NUMBER() OVER()方法得到的执行计划完全相同。 - AJax

好的，这种情况下，您的原始代码可能是您最好的选择。为了消除聚集索引扫描，您可以使用查询提示来强制使用其他索引，但这将通过昂贵的书签查找来抵消，以获取其他列的信息。 - Tracy McKibben

Answer 3

如果您的记录是按顺序插入的，将查询中的 TimeStamp 替换为 ID 可能会有所改善。

另外一提，这个查询返回了多少条记录？如果返回了数十万行数据，您的延迟可能是由于网络开销。

- cjk

---

不幸的是，记录并不总是按顺序插入，因此VehicleID和TimeStamp的组合将是确定每辆车最近的记录的唯一方法。 - AJax

查询每辆车只会返回1条记录，因此无论表中存在多少百万行，最多只会返回286条记录。 - AJax