主页 / dba / 问题 / 140425
Accepted
Jonesome Reinstate Monica
Asked: 2016-06-05 23:32:12 +0800 CST

在这种情况下将使用哪个索引？

  • 772

SQL Server 2014 标准版

我需要找到特定月份往返特定城市的航班数量。例如

select count(*) 
from flights 
where flightTo_AirportCode = 'aaaa' 
and flightFrom_Airportcode = 'bbbb' 
and flightdate < '2016-04-01' 
and flightdate > '2016-02-28' ;

表架构如下。

我正在尝试估计索引 modelA 或索引 modelB（如下）是否更可取（构建索引需要很多小时，并且磁盘空间一次只允许存在一个，因此我试图在跳跃之前先看看）。

根据我的经验，任何一个索引都可以。我对吗？

  create index [modelA] on flights (flightTo_AirportCode, flightFrom_AirportCode, flightDate)

  create index [modelB] on flights (flightDate, flightTo_AirportCode, flightFrom_AirportCode)

（或者，更好的是，我可以使用二进制索引或高级机制来解决这个问题吗？）

CREATE TABLE [dbo].[flights](
    [flightId] [uniqueidentifier] NOT NULL,
    [accountId] [uniqueidentifier] NULL,
    [flightDate] [datetime] NULL,
    [flightTo_AirportCode] [nvarchar](30) NULL,
    [flightFrom_AirportCode] [nvarchar](30) NULL,
    -- ... 45 more fields
    CONSTRAINT [PK_flight] PRIMARY KEY CLUSTERED 
(
    [flightId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
sql-server sql-server-2014

1 个回答

  1. Best Answer

    索引 A 更适合此查询。当WHERE除了在列上使用范围条件或IN运算符的条件之外，所有条件都是相等性检查时，最后一列应该在索引中排在最后，在所有具有相等性检查的列之后。

    这允许优化器使用索引查找匹配条件的第一行，然后遍历索引直到找到不匹配的行。中间的所有行也是匹配项。

    因此，此查询的最佳索引是(to, from, date)（您的模型 A）或(from, to, date).

    模型 B 索引的日期在前，因此它不是最好的，尽管它仍然是查询的覆盖索引。如果使用它，查询计划将几乎相同。索引会寻找与范围条件 ( date > '2016-02-28') 匹配的第一行，然后遍历索引，直到找到与范围条件不匹配的行date < '2016-04-01'。但是中间的所有行不一定与其他 2 个条件匹配，因此必须根据这些条件检查它们并（可能其中很多）被拒绝。

    因此，虽然计划是相似的，但模型 A 计划只需要遍历索引中包含所有需要的行的部分，而且只有这些行，而模型 B 计划将遍历索引的（可能更多）更大的部分指数。

    • 最好对日期使用 100% 安全的格式 ( YYYYMMDD)。

    • 如果你想要三月份的日期，你应该使用包含 - 排除检查：

      AND flightdate >= '20160301' AND flightdate < '20160401'

      保证使用日期和日期时间类型。您当前的查询还将包括任何与我假设您不想要'2016-02-28'的时间不同的行'00:00:00'（您能保证没有任何时间吗？）。inclusive-exclusive 方法也适用于闰年（提醒 2016 年是闰年，因此还有一个 2 月 29 日的日期以及您的查询将返回的日期）。

    另请阅读 Aaron Bertrand 的这些博客文章：

    • 18

相关问题