如何在SQL Server中提高日期时间过滤的性能？

Question

我在按照datetime列进行过滤时遇到了问题。

我尝试了以下两种方法：

datefield < '2013-03-15 17:17:55.179'
datefield < CAST('2013-03-15 17:17:55.179' AS datetime)

我有一个超过 3,000,000 个主要对象的大型数据库。

因此，我需要改进我的 datetime 过滤性能。我读过关于 UNIX 时间戳的文章（将所有的 datetime 转换为 UNIX 时间戳，然后通过这个 UNIX 字段进行过滤）。

我认为这比按 datetime 进行过滤更好。但如果有人知道其他方法，我会很感激。

我的查询语句是：

SELECT TOP (100)  ev.Title as Event_name, po.Name as POI_name, 
po.Address, po.City, po.Region, po.Country, po.Latitude, po.Longitude, ev.Start_time, 
(Select ID_Category FROM SubCategory s where ev.ID_SubCategory = s.ID_SubCategory) as ID_Category, 
ev.ID_SubCategory, ev.ID_Event, ev.ID_Channel, IDChanelEvent, 
ev.FavoriteCount, po.gmtOffset, v.IsFavorite, v1.IsFavorite  
FROM Events ev 
JOIN POI po ON ev.ID_POI = po.ID_POI 
JOIN (SELECT et.id_event as joinIdEv FROM EventTagLink et, tags t 
 WHERE t.id_tag = et.id_tag 
 AND ( t.Title = N'music' ) 
 ) as joinEvents 
 ON joinEvents.joinIdEv = ev.ID_Event 
LEFT JOIN Viewed v ON v.ID_Event = ev.ID_Event AND v.ID_User = 1 AND v.IsFavorite = 1 LEFT join Viewed v1 ON v1.ID_Event = ev.ID_Event AND v1.ID_User = 1 AND v1.IsFavorite = 0
WHERE 
--ev.GmtStop_time > '2013-03-15 14:17:55.188' AND 
po.Latitude > 41.31423 AND po.Latitude < 61.60511 
AND  po.Longitude > -6.676602 AND po.Longitude < 17.04498  
AND ev.ID_SubCategory in (3, 12, 21, 4, 30, 13, 22, 6, 14, 40, 23, 7, 32, 15, 41, 8, 50, 33, 16, 42, 25, 9, 34, 17, 35, 18, 44, 27, 36, 19, 45, 28, 37, 46, 29, 38, 47, 39, 48, 49, 10, 1, 11, 2, 20) 
--AND ev.GmtStart_time< '2013-03-15 17:17:55.179'
AND v1.IsFavorite is null

按照我评论的时间进行过滤。

如果我关闭这些过滤器，请求持续时间为几秒钟。如果我打开它们，那么请求持续时间超过25秒。

• 带有日期时间过滤器的执行计划
• 不带日期时间过滤器的执行计划

因此，关于执行计划、索引等问题有很多讨论。但是UNIX时间戳呢？这正是我提出这个问题的主要原因。使用datetime过滤器是否会提高性能？

- Oleksandr Fentsyk

---

你的datefield列是否创建了索引？ - a1ex07

首先，请确保您在该列上有一个相关的索引。其次，请勿在 WHERE 表达式中使用任何 函数（例如 CAST）来处理日期时间列。 - marc_s

我不使用 CAST，我只是尝试。 - Oleksandr Fentsyk

这里的信息不足以了解问题所在。您需要展示整个SQL，然后告诉我们您期望从中得到什么，例如您期望它返回多少行，您期望/希望它消耗多少时间，它实际上消耗了多少时间等等。 - Lasse V. Karlsen

根据您的条件与总共三百万行数据匹配的数量，过滤的数据可能并不多——但是这也同样适用于“Unix时间戳”过滤或其他任何过滤方式。如果您的查询请求了20个数据，即50%的数据，那么它将始终很慢。这里没有什么魔法子弹…… - marc_s

Answer 1

关于在msql中对datetime进行索引时的建议是，索引的足迹会影响搜索时间（这似乎很明显...但请继续阅读）。

当以datetime为索引进行索引时，例如'2015-06-05 22:47:20.102'，索引必须考虑到datetime内的每个位置。这变得非常大而笨重。我采用的成功方法是创建一个新的datetime列，并将数据舍入到小时，然后在此新列上构建索引。例如，'2015-06-05 22:47:20.102'转换为'2015-06-05 22:00:00.000'。采用这种方法，我们保留详细数据并且可以通过搜索此新列来显示或使用它，从而使返回结果的速度提高了至少10倍。这是因为索引不必考虑分钟、秒和毫秒字段。

- Sean Ford

---

BTREE索引适用于任何数据类型，其空间复杂度为O（size * log（size）），其中size是被索引的数据量。高精度值会导致索引大小增大的观点是不正确的。 - O. Jones

有证据吗？为什么指数会关心时间价值内部的内容？它只关心等于、小于、大于。 - Artem Novikov

我认为索引包括时间的日期在几乎所有情况下都不是一个好主意。这基本上意味着您永远不会在涉及该字段的任何搜索条件中获得精确匹配。我建议使用两个字段，一个用于日期，一个用于时间，并对它们进行索引。然后，您的日期搜索将始终找到精确匹配，从而更快地让您找到相关数据。然后，您可以在该数据集内筛选时间。 - Rodney P. Barbati

更改索引键的粒度不会改变索引中行数的数量，这主要决定了索引的大小。只有当您另外应用页面压缩时，索引大小本身才会明显减小，即使这样减小的大小也只有在索引扫描而不是查找时才能明显看到。想象一下一个只存储年份（因此将所有行“舍入”为2015，但仍必须索引所有行）的索引，就可以看出为什么这并没有真正帮助。如果全新的索引“有用”，那可能只是因为旧索引是碎片化的。 - Jeroen Mostert

@OleksandrFentsyk...我恭敬地建议您取消接受此答案。原帖似乎不理解索引的工作原理，并且在传播误导信息。 - Gordon Linoff

Answer 2

你需要先查看执行计划，以了解SQL Server正在执行什么操作。很有可能，你只需要添加索引就能解决问题。像这样的小转换几乎不会导致查询缓慢。索引是修复查询的好方法。

你不需要将其设置为聚集索引。将其设置为聚集索引意味着你不需要执行查找操作，但对于只有100行的数据，查找速度非常快。我建议按照日期时间和子类别的顺序创建一个非聚集索引。

如果你正在排序，也应该确保它在索引中。由于每个表只能使用一个索引，所以你需要确保所有相关列按正确的顺序放在同一索引中。

但首先，要获取实际的执行计划！

- John Tseng

---

在主表中，我有很多索引需要执行计划询问。每个索引都有一些包含的列。你认为我应该将它们合并吗？ - Oleksandr Fentsyk

你能否在问题中发布计划吗？我会先不包括列，然后如果您认为性能不佳，则包括它们。包含的列增加了索引的维护，并且需要在每次向表中添加列时进行修改。除非查找时间很长，否则我不会包括它们。 - John Tseng

我添加了问题执行计划。请看一下。 - Oleksandr Fentsyk

@SashaFencyk 这非常有趣。您查询中最昂贵的部分是索引查找和RID查找。此外，总计大于100％。我不明白这怎么可能慢25倍。运行时间是否始终慢25倍？您能否还发布原始查询的执行计划？是否存在某些锁争用问题？ - John Tseng

你关于索引的看法是正确的。我本来想随意删除它们的。我采纳了你的建议，做了一个执行计划，发现查询瞬间完成了...更深入的挖掘揭示了一系列非常糟糕的问题...优化后，一切都像梦一样顺畅。没有添加任何索引！+1 +啤酒 - Piotr Kula

Answer 3

为了获得更好的性能，建议您创建新索引：

CREATE INDEX x1 ON LiveCity.dbo.Tags(Title) INCLUDE(ID_Tag)
CREATE INDEX x2 ON LiveCity.dbo.Tags(ID_Event, GmtStart_time, GmtStop_time) 
  INCLUDE(
          FavoriteCount, 
          ID_Channel, 
          ID_POI, 
          ID_SubCategory, 
          IDChanelEvent, 
          Start_time, 
          Title
          )
CREATE INDEX x ON LiveCity.dbo.POI(ID_POI, Latitude, Longitude) 
  INCLUDE(
          Address, 
          City, 
          Country, 
          gmtOffset, 
          Name, 
          Region
          )

这将帮助您避免RID查找操作，提高查询的整体性能。

- Aleksandr Fedorenko

Answer 4

试试这个 -

;WITH cte AS (
     SELECT IsFavorite, ID_Event  
     FROM Viewed
     WHERE ID_User = 1 
)
SELECT TOP (100)
      Event_name = ev.Title 
    , POI_name = po.Name 
    , po.[address]
    , po.City
    , po.Region
    , po.Country
    , po.Latitude
    , po.Longitude
    , ev.start_time
    , s.ID_Category
    , ev.ID_SubCategory
    , ev.ID_Event
    , ev.ID_Channel
    , IDChanelEvent
    , ev.FavoriteCount
    , po.gmtOffset
    , v.IsFavorite
    , IsFavorite = NULL
FROM [events] ev
JOIN POI po ON ev.ID_POI = po.ID_POI
LEFT JOIN SubCategory s ON ev.ID_SubCategory = s.ID_SubCategory
LEFT JOIN cte v ON v.ID_Event = ev.ID_Event AND v.IsFavorite = 1
WHERE po.Latitude BETWEEN 41.31423 AND 61.60511
     AND po.Longitude BETWEEN -6.676602 AND 17.04498
     AND ev.ID_SubCategory IN (3, 12, 21, 4, 30, 13, 22, 6, 14, 40, 23, 7, 32, 15, 41, 8, 50, 33, 16, 42, 25, 9, 34, 17, 35, 18, 44, 27, 36, 19, 45, 28, 37, 46, 29, 38, 47, 39, 48, 49, 10, 1, 11, 2, 20)
     AND v1.IsFavorite IS NULL
     AND EXISTS(
          SELECT 1 
          FROM EventTagLink et
          WHERE t.Title = 'music'
               AND et.joinIdEv = ev.ID_Event
     )
     AND NOT EXISTS (
          SELECT * 
          FROM cte v1 
          WHERE v1.ID_Event = ev.ID_Event AND v1.IsFavorite = 0
     )

- Devart

Answer 5

在日期时间字段上创建集群索引肯定会有所帮助。我们之前也遇到了同样的问题。通过在日期时间列上创建索引，我们解决了这个问题。

- Hiren Dhaduk

---

是的，聚集索引比非聚集索引具有更好的性能，因为最终非聚集索引在内部使用聚集索引。你能给我一个样例数据库吗？我想试着操作一下。我真的很感兴趣做这种类型的事情。 - Hiren Dhaduk

认真？@SashaFencyk，不要直接给这里的人访问你的数据或服务器的权限。 - Mike Sherrill 'Cat Recall'

感谢@MikeSherrill'Catcall'的关注。我理解。 - Oleksandr Fentsyk