递归SQL查询以加速未索引的查询。

Question

这个问题主要出于好奇心，因为我有一个工作查询（只是比我想象中的要慢一点）。

我有一个有400万行的表格。这个表格上唯一的索引是一个自增的BigInt ID。查询正在查找其中一列的不同值，但仅限于最近1天。不幸的是，评估的ReportDate列不是DateTime类型甚至不是BigInt，而是格式为YYYYMMDD的char（8）。所以查询速度有点慢。

  SELECT Category 
    FROM Reports 
   where ReportDate = CONVERT(VARCHAR(8), GETDATE(), 112) 
GROUP BY Category

请注意，上述语句中的日期转换仅是将其转换为YYYYMMDD格式进行比较。

我在想是否有一种方法可以基于我知道我感兴趣的数据仅在表的“底部”来优化此查询。我正在考虑某种递归SELECT函数，该函数逐渐生成一个临时表，可用于最终查询。

例如，在伪SQL中：

N = 128
TemporaryTable = SELECT TOP {N} * 
                   FROM Reports 
               ORDER BY ID DESC 

/* Once we hit a date < Today, we can stop */
if(TemporaryTable does not contain ReportDate < Today) 
  N = N**2
  Repeat Select

/* We now have a smallish table to do our query */
  SELECT Category 
    FROM TemproaryTable 
   where ReportDate = CONVERT(VARCHAR(8), GETDATE(), 112) 
GROUP BY Category

这有意义吗？这样的事情是否可能？

这是在MS SQL Server 2008上。

- Matt

---

@Martin 我不是数据所有者，我只是好奇上面的操作是否可行。理想情况下，我们希望以报告日期为索引（并且将其设置为非字符类型，如bigint或实际日期类型）。 - Matt

“SELECT MAX(ID) FROM YourTable WHERE ReportDate < CONVERT(VARCHAR(8), GETDATE(), 112)”会找到感兴趣范围的限制吗？如果是这样，可能会使扫描从索引末尾开始向后工作，并且它可以在找到前一天的第一条记录时立即停止。 - Martin Smith

是的，你想做的是可能的 - 但是如果没有在“ReportDate”上建立索引，它永远不会像它本应该那样快... - marc_s

Answer 1

我建议您不需要将以YYYYMMDD格式存储为字符数据的Date进行转换；该格式本身就是可排序的。我建议将日期转换为该格式进行输出。

此外，您的转换方式会针对每个单独的行转换当前DateTime，因此即使为整个查询存储该值可能会加快速度...但我认为只需将要搜索的日期转换为该字符格式即可。

当然，我还建议创建所需的索引...但这不是您提出的问题:P

- Andrew Barber

Answer 2

为什么不直接创建你需要的索引呢？

create index idx_Reports_ReportDate 
    on Reports(ReportDate, Category)

- Joe Stefanelli

---

我不是数据所有者 - 正在更新索引，但正在寻找替代解决方案（更不用说一般情况下的替代方案了）。 - Matt

Answer 3

不，那没意义。优化这个查询的唯一方法是为它创建一个覆盖索引：

CREATE INDEX ndxReportDateCategory ON Reports (ReportDate, Category);

更新

考虑到您不能修改架构的评论，那么您应该修改架构。如果您仍然无法修改，则答案仍然适用：解决方案是拥有一个索引。

最后，更直接地回答您的问题，如果ID和ReportData之间存在强相关性：您寻找的ID是具有比您要查找的日期小的ReportDate最大的ID：

SELECT MAX(Id) 
FROM Reports
WHERE ReportDate < 'YYYYMMDD';

这将对ID索引进行反向扫描，并停止在先于您所需日期的第一个ID处（即不会扫描整个表）。然后，您可以基于找到的最大ID筛选报告。

- Remus Rusanu

---

有趣 - 这是一个反向扫描吗，因为它正在查看Max(ID)？ - Matt

是的。如果在列上有索引，MAX（Column）可以通过快速查看索引的任一端来满足。如果您还有一个过滤器，则扫描运算符可以通过从适当的端开始，然后逐个检查条件来快速找到MAX（或MIN）。通过过滤器的第一条记录就是所需的值。 - Remus Rusanu

请注意，这假定Id和ReportDate之间存在确定性关系，即Id顺序中没有日期被颠倒。 - Remus Rusanu

我很想给你一个绿色的勾 - 但是你的回答是自相矛盾的，“优化查询的唯一方法是为其创建一个覆盖索引”，然后你又有了一个回答，可以在不创建索引的情况下优化查询（查询时间从20秒降至零以下）。哦，我明白了 - 创建索引是好的，但是你的修辞方式妨碍了你的回答。 - Matt

其他人会通过谷歌搜索并进入这个页面。我更喜欢使用修辞手法来强调一个观点，即在关系表中解决性能问题的解决方案几乎总是在你的模式（即覆盖索引）的形状中，而几乎从不在你的查询文本中。 - Remus Rusanu

Answer 4

我认为你会发现Rob Farley的博客上关于SARGability的讨论与你的帖子主题非常相关且有趣。

http://blogs.lobsterpot.com.au/2010/01/22/sargable-functions-in-sql-server/

一个有趣的替代方法，不需要修改现有列数据类型，是利用计算列。

alter table REPORTS
add castAsDate as CAST(ReportDate as date)

create index rf_so2 on REPORTS(castAsDate) include (ReportDate)

- John Sansom

Answer 5

我偶尔使用的查询模式之一是通过子查询限制与您类似索引的日志表：

DECLARE @ReportDate varchar(8)
SET @ReportDate = Convert(varchar(8), GetDate(), 112)

SELECT *
FROM
(
SELECT top 20000 *
FROM Reports
ORDER BY ID desc
) sub
WHERE sub.ReportDate = @ReportDate

20k/4M = 表格的0.5%被读取。

---

这是一个循环解决方案。注意：可能需要将ID设置为主键并在临时表中索引Reportdate。

DECLARE @ReportDate varchar(8)
SET @ReportDate = Convert(varchar(8), GetDate(), 112)

DECLARE @CurrentDate varchar(8), MinKey bigint


SELECT top 2000 * INTO #MyTable
FROM Reports ORDER BY ID desc

SELECT @CurrentDate = MIN(ReportDate), @MinKey = MIN(ID)
FROM #MyTable

WHILE @ReportDate <= @CurrentDate
BEGIN

  SELECT top 2000 * INTO #MyTable
  FROM Reports WHERE ID < @MinKey ORDER BY ID desc

  SELECT @CurrentDate = MIN(ReportDate), @MinKey = MIN(ID)
  FROM #MyTable

END

SELECT * FROM #MyTable
WHERE ReportDate = @ReportDate


DROP TABLE #MyTable

- Amy B

---

这差不多是我所想的 - 但并不一定要提前知道子查询的大小。 - Matt