日期范围查询的SQL索引

Question

最近几天，我一直在努力提高我的数据库性能，关于 SQL Server 数据库中的索引，我对其中一些问题仍然有些困惑。

我会尽可能提供详细信息。

目前我的数据库大约包含 10 万行数据，并将继续增长，因此我正在尝试找到一种使其运行更快的方法。

同时我也在写入这个表格，所以如果您的建议会大幅度减少写入时间，请告知我。

总体目标是选择处于日期范围内的特定名称的所有行。

这通常意味着要从许多数据中选择超过 3,000 行......

表格架构：

CREATE TABLE [dbo].[reports]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [IsDuplicate] [bit] NOT NULL,
    [IsNotValid] [bit] NOT NULL,
    [Time] [datetime] NOT NULL,
    [ShortDate] [date] NOT NULL,
    [Source] [nvarchar](350) NULL,
    [Email] [nvarchar](350) NULL,

    CONSTRAINT [PK_dbo.reports] 
        PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY]

这是我正在使用的SQL查询：

SELECT * 
FROM [db].[dbo].[reports]
WHERE Source = 'name1' 
  AND ShortDate BETWEEN '2017-10-13' AND '2017-10-15'

据我所理解，提高效率的最佳方法而不会对写作时间造成过多影响，是在Source和ShortDate上创建非聚集索引。

我已经按照以下方式创建了该索引结构：

CREATE NONCLUSTERED INDEX [Source&Time] 
ON [dbo].[reports]([Source] ASC, [ShortDate] ASC)

现在我们进入了让我完全迷失的棘手部分，上面的索引有时有效，有时部分有效，有时根本不起作用...

（不确定是否重要，但目前数据库行的90％具有相同的源，虽然这种情况不会持续太久）

1. 使用以下查询，索引根本没有被使用。我正在使用SQL Server 2014，在执行计划中它只使用了聚集索引扫描：

   SELECT * 
   FROM [db].[dbo].[reports]
   WHERE Source = 'name1' 
     AND ShortDate BETWEEN '2017-10-10' AND '2017-10-15'
   

2. 使用此查询时，索引根本未被使用，尽管我从SQL Server中得到了一个建议，建议按日期第一和来源第二创建索引...我读到说索引应该按照查询的顺序进行制作？另外，它说要包括我选择的所有列，这是必须的吗？...我再次读到说我应该仅在索引中包括我正在搜索的列。

SELECT * 
FROM [db].[dbo].[reports]
WHERE Source = 'name1' 
  AND ShortDate = '2017-10-13'

SQL Server索引建议 -

/* The Query Processor estimates that implementing the following 
   index could improve the query cost by 86.2728%. */

/*
USE [db]
GO

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[reports] ([ShortDate], [Source])
INCLUDE ([id], [IsDuplicate], [IsNotValid], [Time], [Email])
GO
*/

现在我尝试使用SQL Server建议的索引进行操作，它能够工作。似乎使用了非聚集索引100%来执行上述两个查询。

我尝试使用该索引但删除了包含的列，结果它无法工作... 似乎我必须在索引中包含我选择的所有列吗？

顺便说一下，如果我包含了所有列，使用我创建的索引时也能够工作。

总结一下：索引的顺序似乎并不重要，因为当创建Source + ShortDate和ShortDate + Source时都可以工作。

但由于某种原因，必须包含所有列...（这将极大地影响对该表的写入？）

非常感谢您的阅读，我的目标是了解为什么会发生这种情况，以及我应该怎么做（不仅仅是解决方案，因为我需要在其他项目中应用它）。

祝好 :)

- Ben

---

标记您正在使用的 dbms。这是一个特定于产品的问题。 - jarlh

已添加 sql-server-2014 标签。谢谢。 - Ben

需要使用 SELECT * 吗？https://www.red-gate.com/simple-talk/sql/t-sql-programming/sql-code-smells/#using-select-in-a-batch - Radim Bača

Answer 1

在 SQL Server 中建立索引是一部分基于长期经验（以及许多沮丧的小时），另一部分则是黑魔法。不要过分自责 - 这就是像 SO 这样的地方的理想之处 - 您可以利用许多大脑，来自许多优化小时的经验。

如果您读到了这篇文章 - 它绝对不正确 - 列的顺序是相关的 - 但是以不同的方式：仅当您在查询中指定了索引定义中的n个最左侧列时，复合索引（由多个列组成）才会被考虑。

经典案例：电话簿上有一个(city,lastname,firstname) 的索引。这样的索引可能会在以下情况下使用：

- 在指定了WHERE子句中的所有三个列的查询中 - 在使用city和lastname的查询中(查找"Detroit"中的所有 "Miller") - 或者在仅通过城市过滤的查询中

但如果你只想搜索firstname，它永远也不会被使用...... 这就是复合索引需要注意的技巧。但是，如果您始终使用来自索引的所有列，则它们的排序通常并不重要 - 查询优化器将为您处理这个问题。

至于包含的列 - 它们仅存储在非聚集索引的叶级别中 - 它们不是索引的搜索结构的一部分，并且您不能在WHERE子句中为这些包含的列指定过滤器值。这些被包含的列的主要好处在于：如果您在非聚集索引中进行搜索并最终找到了您正在寻找的值，那么此时您有什么可用？非聚集索引将存储在非聚集索引定义（ShortDate和Source）中的列，并且它将存储聚集键（如果您有一个——并且您应该有！），但是没有其他内容。

因此，在这种情况下，一旦找到匹配项，并且您的查询希望从该表中获取所有内容，SQL Server必须执行所谓的键查找操作（通常也称为书签查找），其中它获取聚集键，然后对聚集索引执行查找操作，以获取包含您要查找的所有值的实际数据页。

如果您的索引中有包含的列，则您的非聚集索引的叶级页面包含：

• 在非聚集索引中定义的列
• 聚集键列
• 在您的INCLUDE语句中定义的所有其他列

如果这些列"覆盖"您的查询，例如提供您的查询所需的所有值，则一旦在非聚集索引中找到您搜索的值，SQL Server就完成了——它可以从该非聚集索引的叶级页面获取其所需的所有值，并且不需要再次执行（昂贵的）键查找操作来获取实际的值。

因此，在SELECT语句中，尽量明确指定您真正需要的那些列可能会很有益 - 在这种情况下，您可能能够创建一个有效的覆盖索引，为您的SELECT提供所有值 - 始终使用SELECT *会使这变得非常困难或几乎不可能...

- marc_s

---

首先感谢您纠正我的错误并提供这个信息丰富的答案！ 阅读了您的评论三次后，我可以自信地说我更好地理解了索引的工作原理。 如果我理解有误，请纠正我，但我相信当我不包括列时，索引未被使用的原因是因为它找到了成千上万行，然后需要为所有这些行执行关键查找以获取所有未包含的列，这样效率就不高。 - Ben

目前我认为覆盖索引是最好的方法，因为我不是在搜索特定的行，而是在同一查询中搜索很多行。由于我包含的列很少且大小不大，希望它不会占用太多空间...尽管仍然不确定它对表格插入操作的影响有多大。再次感谢您的评论@marc_s :) - Ben

@Ben：确切地说，如果在非聚集索引中找到了太多的命中结果，那么执行成千上万次针对聚集索引的键查找的代价可能会很高，因此查询优化器会转而执行聚集索引扫描。 - marc_s

Answer 2

通常情况下，您希望索引从最具选择性（即过滤掉最多可能的记录）到最不具选择性；如果一个列具有低基数，查询优化器可能会忽略它。

这是有直觉意义的-如果你有一本电话簿，你正在寻找姓为“史密斯”的人，名字以“A”开头，你想首先搜索“史密斯”，然后是“A”，而不是所有以“A”开头的人，再筛选出那些被称为“史密斯”的人。毕竟，26个人中只有一个人有名字以“A”开头。

因此，在您的示例中，我猜您在短日期中拥有各种各样的值-因此，查询优化器正在尝试过滤掉该列。您说您在“来源”中有很少的不同值，因此查询优化器可能会决定忽略它；在这种情况下，该索引中的第二列也无用。

索引中where子句的顺序无关紧要-您可以交换它们并获得完全相同的结果，因此查询优化器会忽略它们。

编辑：所以，是的，请创建该索引。想象一下您需要排序的一堆卡片-在第一次运行中，您希望尽可能地删除尽可能多的卡片。假设它是均匀分布的-如果您有1000个单独的短日期超过一百万行，那么如果您的第一次运行从short_date开始，这意味着您最终会得到1000个项目；如果按来源排序，则有100000行。

- Neville Kuyt

---

我明白了，对于我的使用，我可能会有多达10个不同的来源，超过数百万行数据。而且ShortDates可能会超过1000个。那么我应该如何建立索引呢？也许只针对日期建立索引？谢谢，Neville。 - Ben

如果是这样，为什么它还要使用索引，如果我包括了所有列？@Neville - Ben

Answer 3

索引中包含的列是您选择的列。 由于您使用了select *（这不是好的实践方法），因此索引将不会被使用，因为它必须查找整个表以获取列的值。

对于您的情况，我建议删除默认的聚集索引（如果有的话），并使用以下语句创建一个新的聚集索引：

USE [db]
GO
CREATE CLUSTERED INDEX CIX_reports
    ON [dbo].[reports] ([ShortDate],[Source])
GO

- Raphael Müllner

---

将列包含在聚集索引中是没有意义的。 - Radim Bača

聚集索引是数据在物理文件中存储的顺序。因此，您始终只有一个。 索引定义中列的顺序并不重要。 - Raphael Müllner

@Neville：这取决于插入和选择数据的比例。由于该表名为“报告”，我认为选择性能比插入性能更重要。 - Raphael Müllner

我会尝试一下。对于 SQL 服务器来说，100k 行并不算太多，如果您每分钟没有插入数千行数据，我认为索引不会影响插入性能。 - Raphael Müllner

这篇文章的标题是“重要的不是星号”。 - Dragas