最近几天,我一直在努力提高我的数据库性能,关于 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%具有相同的源,虽然这种情况不会持续太久)
使用以下查询,索引根本没有被使用。我正在使用SQL Server 2014,在执行计划中它只使用了聚集索引扫描:
SELECT * FROM [db].[dbo].[reports] WHERE Source = 'name1' AND ShortDate BETWEEN '2017-10-10' AND '2017-10-15'
使用此查询时,索引根本未被使用,尽管我从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
时都可以工作。
但由于某种原因,必须包含所有列...(这将极大地影响对该表的写入?)
非常感谢您的阅读,我的目标是了解为什么会发生这种情况,以及我应该怎么做(不仅仅是解决方案,因为我需要在其他项目中应用它)。
祝好 :)
SELECT *
吗?https://www.red-gate.com/simple-talk/sql/t-sql-programming/sql-code-smells/#using-select-in-a-batch - Radim Bača