适用于日期范围搜索的良好索引在MSSQL中的应用。

huangapple go评论54阅读模式
英文:

Good index for DATE range search in MSSQL

问题

以下是翻译好的部分:

我在Azure SQL数据库中有一张表,看起来像这样:

CREATE TABLE [dbo].[DayStatus](
	[Date] [DATE] NOT NULL,
	[LocationId] [INT] NOT NULL,
	[TypeId] [INT] NOT NULL,
	[Total] [DECIMAL](15, 5) NULL,
	[Timezone] [NVARCHAR](70) NULL,
	[Currency] [NVARCHAR](3) NOT NULL,

PRIMARY KEY CLUSTERED 
(
	[Date] ASC,
	[LocationId] ASC,
	[TypeId] ASC
)
) ON [PRIMARY]
GO

问题

我需要优化以下针对上述表的SELECT语句:

SELECT 
    [Date],
    [LocationId],
    [TypeId],
    [Total],
    [Timezone],
    [Currency]
FROM [dbo].[DayStatus]

WHERE 
Date >= '2022-06-01' and Date <= '2023-01-17'
    and Currency = 'USD'
    and LocationId in (1, 2, 3, 4, 6, 10)
    and TypeId in (1, 2, 3, 5)

我考虑了以下索引,但似乎无法看到显著的性能差异。

哪一个更好,还有更好的吗?

测试1

CREATE NONCLUSTERED INDEX [IX__Test1] ON [dbo].[DayStatus]
(
	[Date] ASC,
	[Currency] ASC,
	[LocationId] ASC,
	[TypeId] ASC
)
GO

测试2

CREATE NONCLUSTERED INDEX [IX__Test2] ON [dbo].[DayStatus]
(
	[Currency] ASC,
	[LocationId] ASC,
	[TypeId] ASC
)
INCLUDE([Date],[Timezone],[Total])
GO

编辑

是否使用以下查询更好?

SELECT 
    [Date],
    [LocationId],
    [TypeId],
    [Total],
    [Timezone],
    [Currency]
FROM [dbo].[DayStatus]

WHERE 
    Currency = 'USD'
    and LocationId in (1, 2, 3, 4, 6, 10)
    and TypeId in (1, 2, 3, 5)
    and Date >= '2022-06-01' and Date <= '2023-01-17'
英文:

I have a table in Azure SQL Database that looks like this:

CREATE TABLE [dbo].[DayStatus](
	[Date] [DATE] NOT NULL,
	[LocationId] [INT] NOT NULL,
	[TypeId] [INT] NOT NULL,
	[Total] [DECIMAL](15, 5) NULL,
	[Timezone] [NVARCHAR](70) NULL,
	[Currency] [NVARCHAR](3) NOT NULL,

PRIMARY KEY CLUSTERED 
(
	[Date] ASC,
	[LocationId] ASC,
	[TypeId] ASC
)
) ON [PRIMARY]
GO

Question

I need to optimize the following SELECT statement to the above table:

SELECT 
    [Date],
    [LocationId],
    [TypeId],
    [Total],
    [Timezone],
    [Currency]
FROM [dbo].[DayStatus]

WHERE 
Date >= '2022-06-01' and Date <= '2023-01-17'
    and Currency = 'USD'
    and LocationId in (1, 2, 3, 4, 6, 10)
    and TypeId in (1, 2, 3, 5)

I have considered the following indexes, but it seems I cannot see a significant performance difference.

Which one is better, and is there an even better one?

Test 1

CREATE NONCLUSTERED INDEX [IX__Test1] ON [dbo].[DayStatus]
(
	[Date] ASC,
	[Currency] ASC,
	[LocationId] ASC,
	[TypeId] ASC
)
GO

Test 2

CREATE NONCLUSTERED INDEX [IX__Test2] ON [dbo].[DayStatus]
(
	[Currency] ASC,
	[LocationId] ASC,
	[TypeId] ASC
)
INCLUDE([Date],[Timezone],[Total])
GO

EDIT

Would it be better to have the query below?

SELECT 
    [Date],
    [LocationId],
    [TypeId],
    [Total],
    [Timezone],
    [Currency]
FROM [dbo].[DayStatus]

WHERE 
    Currency = 'USD'
    and LocationId in (1, 2, 3, 4, 6, 10)
    and TypeId in (1, 2, 3, 5)
    and Date >= '2022-06-01' and Date <= '2023-01-17'

答案1

得分: 1

在“相等列”之前应该放置“范围列”。

可能最好的索引策略是

Currency, LocationId, TypeId的任何顺序作为前三列(选择对工作负载中的其他查询最有用的顺序,不要太过关注这里的选择性),然后将Date作为第四个关键列,INCLUDE (Timezone, Total)

这将允许查询通过对24个不同索引查找结果进行“UNION ALL”来返回结果(因为所需的结果是以下不同范围的连接,可以使用这样的索引有效地查找)。

  • Currency = USD' and LocationId = 1 and TypeId = 1 and Date >= '2022-06-01' and Date <= '2023-01-17'
  • Currency = USD' and LocationId = 10 and TypeId = 1 and Date >= '2022-06-01' and Date <= '2023-01-17'
  • Currency = USD' and LocationId = 2 and TypeId = 1 and Date >= '2022-06-01' and Date <= '2023-01-17'
  • Currency = USD' and LocationId = 3 and TypeId = 1 and Date >= '2022-06-01' and Date <= '2023-01-17'
  • Currency = USD' and LocationId = 4 and TypeId = 1 and Date >= '2022-06-01' and Date <= '2023-01-17'
  • Currency = USD' and LocationId = 6 and TypeId = 1 and Date >= '2022-06-01' and Date <= '2023-01-17'
  • Currency = USD' and LocationId = 1 and TypeId = 2 and Date >= '2022-06-01' and Date <= '2023-01-17'
  • Currency = USD' and LocationId = 10 and TypeId = 2 and Date >= '2022-06-01' and Date <= '2023-01-17'
  • Currency = USD' and LocationId = 2 and TypeId = 2 and Date >= '2022-06-01' and Date <= '2023-01-17'
  • Currency = USD' and LocationId = 3 and TypeId = 2 and Date >= '2022-06-01' and Date <= '2023-01-17'
  • Currency = USD' and LocationId = 4 and TypeId = 2 and Date >= '2022-06-01' and Date <= '2023-01-17'
  • Currency = USD' and LocationId = 6 and TypeId = 2 and Date >= '2022-06-01' and Date <= '2023-01-17'
  • Currency = USD' and LocationId = 1 and TypeId = 3 and Date >= '2022-06-01' and Date <= '2023-01-17'
  • Currency = USD' and LocationId = 10 and TypeId = 3 and Date >= '2022-06-01' and Date <= '2023-01-17'
  • Currency = USD' and LocationId = 2 and TypeId = 3 and Date >= '2022-06-01' and Date <= '2023-01-17'
  • Currency = USD' and LocationId = 3 and TypeId = 3 and Date >= '2022-06-01' and Date <= '2023-01-17'
  • Currency = USD' and LocationId = 4 and TypeId = 3 and Date >= '2022-06-01' and Date <= '2023-01-17'
  • Currency = USD' and LocationId = 6 and TypeId = 3 and Date >= '2022-06-01' and Date <= '2023-01-17'
  • Currency = USD' and LocationId = 1 and TypeId = 6 and Date >= '2022-06-01' and Date <= '2023-01-17'
  • Currency = USD' and LocationId = 10 and TypeId = 6 and Date >= '2022-06-01' and Date <= '2023-01-17'
  • Currency = USD' and LocationId = 2 and TypeId = 6 and Date >= '2022-06-01' and Date <= '2023-01-17'
  • Currency = USD' and LocationId = 3 and TypeId = 6 and Date >= '2022-06-01' and Date <= '2023-01-17'
  • Currency = USD' and LocationId = 4 and TypeId = 6 and Date >= '2022-06-01' and Date <= '2023-01-17'
  • Currency = USD' and LocationId = 6 and TypeId = 6 and Date >= '2022-06-01' and Date <= '2023-01-17'

我将Date添加为您的IX__Test2索引的第四个关键列,执行计划显示索引查找,但深入属性显示SQL Server正在执行上述操作。

英文:

Equality columns should go before range columns.

Potentially the best indexing strategy would be

Any ordering of Currency, LocationId, TypeId as first three columns (choose whichever ordering is most useful for other queries in your workload and don't get hung up on selectivity here) followed by Date as the fourth key column and INCLUDE (Timezone, Total)

This would allow the query to return the results by UNION ALL-ing the results of 24 distinct index seeks (as the desired results are a concatenation of the following distinct ranges which can be seeked efficiently with such an index).

  • Currency = USD&#39; and LocationId = 1 and TypeId = 1 and Date &gt;= &#39;2022-06-01&#39; and Date &lt;= &#39;2023-01-17&#39;
  • Currency = USD&#39; and LocationId = 10 and TypeId = 1 and Date &gt;= &#39;2022-06-01&#39; and Date &lt;= &#39;2023-01-17&#39;
  • Currency = USD&#39; and LocationId = 2 and TypeId = 1 and Date &gt;= &#39;2022-06-01&#39; and Date &lt;= &#39;2023-01-17&#39;
  • Currency = USD&#39; and LocationId = 3 and TypeId = 1 and Date &gt;= &#39;2022-06-01&#39; and Date &lt;= &#39;2023-01-17&#39;
  • Currency = USD&#39; and LocationId = 4 and TypeId = 1 and Date &gt;= &#39;2022-06-01&#39; and Date &lt;= &#39;2023-01-17&#39;
  • Currency = USD&#39; and LocationId = 6 and TypeId = 1 and Date &gt;= &#39;2022-06-01&#39; and Date &lt;= &#39;2023-01-17&#39;
  • Currency = USD&#39; and LocationId = 1 and TypeId = 2 and Date &gt;= &#39;2022-06-01&#39; and Date &lt;= &#39;2023-01-17&#39;
  • Currency = USD&#39; and LocationId = 10 and TypeId = 2 and Date &gt;= &#39;2022-06-01&#39; and Date &lt;= &#39;2023-01-17&#39;
  • Currency = USD&#39; and LocationId = 2 and TypeId = 2 and Date &gt;= &#39;2022-06-01&#39; and Date &lt;= &#39;2023-01-17&#39;
  • Currency = USD&#39; and LocationId = 3 and TypeId = 2 and Date &gt;= &#39;2022-06-01&#39; and Date &lt;= &#39;2023-01-17&#39;
  • Currency = USD&#39; and LocationId = 4 and TypeId = 2 and Date &gt;= &#39;2022-06-01&#39; and Date &lt;= &#39;2023-01-17&#39;
  • Currency = USD&#39; and LocationId = 6 and TypeId = 2 and Date &gt;= &#39;2022-06-01&#39; and Date &lt;= &#39;2023-01-17&#39;
  • Currency = USD&#39; and LocationId = 1 and TypeId = 3 and Date &gt;= &#39;2022-06-01&#39; and Date &lt;= &#39;2023-01-17&#39;
  • Currency = USD&#39; and LocationId = 10 and TypeId = 3 and Date &gt;= &#39;2022-06-01&#39; and Date &lt;= &#39;2023-01-17&#39;
  • Currency = USD&#39; and LocationId = 2 and TypeId = 3 and Date &gt;= &#39;2022-06-01&#39; and Date &lt;= &#39;2023-01-17&#39;
  • Currency = USD&#39; and LocationId = 3 and TypeId = 3 and Date &gt;= &#39;2022-06-01&#39; and Date &lt;= &#39;2023-01-17&#39;
  • Currency = USD&#39; and LocationId = 4 and TypeId = 3 and Date &gt;= &#39;2022-06-01&#39; and Date &lt;= &#39;2023-01-17&#39;
  • Currency = USD&#39; and LocationId = 6 and TypeId = 3 and Date &gt;= &#39;2022-06-01&#39; and Date &lt;= &#39;2023-01-17&#39;
  • Currency = USD&#39; and LocationId = 1 and TypeId = 6 and Date &gt;= &#39;2022-06-01&#39; and Date &lt;= &#39;2023-01-17&#39;
  • Currency = USD&#39; and LocationId = 10 and TypeId = 6 and Date &gt;= &#39;2022-06-01&#39; and Date &lt;= &#39;2023-01-17&#39;
  • Currency = USD&#39; and LocationId = 2 and TypeId = 6 and Date &gt;= &#39;2022-06-01&#39; and Date &lt;= &#39;2023-01-17&#39;
  • Currency = USD&#39; and LocationId = 3 and TypeId = 6 and Date &gt;= &#39;2022-06-01&#39; and Date &lt;= &#39;2023-01-17&#39;
  • Currency = USD&#39; and LocationId = 4 and TypeId = 6 and Date &gt;= &#39;2022-06-01&#39; and Date &lt;= &#39;2023-01-17&#39;
  • Currency = USD&#39; and LocationId = 6 and TypeId = 6 and Date &gt;= &#39;2022-06-01&#39; and Date &lt;= &#39;2023-01-17&#39;

I added Date as a fourth key column to your IX__Test2 index and the execution plan shows an index seek but digging into the properties shows SQL Server is doing the above.

适用于日期范围搜索的良好索引在MSSQL中的应用。

huangapple
  • 本文由 发表于 2023年2月19日 19:51:00
  • 转载请务必保留本文链接:https://go.coder-hub.com/75499931.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定