安装
以下硬件可以运行我将要描述的内容:
- 磁盘:6块2TB的HDD组成RAID5(带1个冗余驱动器)
- CPU:Intel Xeon E5-2640 @ 2.4 GHz,6核
- 内存:64 GB
- SQL Server版本:SQL Server 2016 Developer
SQL Server Management Studio(SSMS)和sql server实例都在此服务器上运行。因此,所有查询都在本地执行。此外,在执行任何查询之前,我始终运行以下命令以确保没有数据访问被缓存在内存中:
DBCC DROPCLEANBUFFERS
问题
我们有一个大约有11,600,000行的SQL Server表。在整体方案中,这不是特别大的表,但它会随着时间的推移而不断增长。
该表具有以下结构:
CREATE TABLE [Trajectory](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FlightDate] [date] NOT NULL,
[EntryTime] [datetime2] NOT NULL,
[ExitTime] [datetime2] NOT NULL,
[Geography] [geography] NOT NULL,
[GreatArcDistance] [real] NULL,
CONSTRAINT [PK_Trajectory] PRIMARY KEY CLUSTERED ([Id])
)
为了简化,一些列被排除在外,但它们的数量和大小非常小。
虽然行数不是很多,但由于[Geography]
列,表占用了相当大的磁盘空间。该列的内容是具有大约3000个点(包括Z和M值)的LINESTRINGS。
现在,我们只需考虑在表的Id列上有一个聚集索引,该索引也表示DDL中描述的主键约束。
我们遇到的问题是,当我们查询日期范围和特定地理交叉点时,完成该查询需要相当长的时间。
我们要查询的查询如下:
DEFINE @p1 = [...]
SELECT [Id], [Geography]--, (+ some other columns)
WHERE [FlightDate] BETWEEN '2018-09-04' AND '2018-09-12' AND [Geography].STIntersects(@p1) = 1
这是一个相当简单的查询,具有我上面提到的两个过滤器。为了使查询速度快,我们尝试了几种不同类型的索引:
1. CREATE NONCLUSTERED INDEX [IX_Trajectory_FlightDate] ON [Trajectory] ([FlightDate] ASC)
当我们查询表时,在添加此类索引后,期望的查询计划如下所示:
- 在索引上执行INDEX SEEK操作(此操作将11,600,000行的数据筛选为大约50,000行)
- 查找主表以获取[Geography]列及任何其他选择的列
- 对返回的每一行执行地理筛选
[Geography].STIntersects(@p1) = 1
--- 更新1开始 ---
其他查询计划信息(针对主要步骤,注意:与上面显示的查询不是同一次执行,因此时间会有所变化。这个查询花费了2分39秒):
SELECT.QueryTimeStats
CpuTime=12241毫秒
ElapsedTime=157591毫秒
Key Lookup (97%)
Actual I/O Statistics
Actual Logical Reads=48165
Actual Physical Reads=81
Actual Time Statistics
Actual Elapsed CPU Time=144毫秒
Actual Elapsed Time=266毫秒
Index Seek (0%)
Actual I/O Statistics
Actual Logical Reads=85
Actual Physical Reads=0
Actual Read Aheads=73
Actual Scans=21
Filter (3%)
Actual Time Statistics
Actual Elapsed CPU Time=12156毫秒
Actual Elapsed Time=157583毫秒
- 它表明,耗时/资源的3%步骤需要157583ms,而耗时/资源的97%步骤只需要266ms。我觉得这很奇怪。
- 如果我用一个不使用索引的不同过滤器(使用
EntryTime
列代替),它大致返回相同数量的行,那么查询时间将缩短到大约20秒,尽管我仍然选择相同数量的行。我想唯一的解释是,在可以丢弃行之前,查询实际上并不需要读取昂贵的[Geography]
列。
--- UPDATE 1 END ---
2. CREATE NONCLUSTERED INDEX [IX_Trajectory_FlightDate_Includes_Geography] ON [Trajectory] ([FlightDate] ASC) INCLUDE ([Geography])
这个索引唯一不同的地方就是它将大型的[Geography]列与索引一起存储。但是对于查询计划的期望基本相同:
当我们查询该表时,添加了如下索引后,期望的查询计划应该是这样的:- 在索引上执行INDEX SEEK操作(此操作将把11600000行数据过滤到大约50000行)
- 对返回的每一行执行地理筛选器
[Geography].STIntersects(@p1) = 1
- 查找主表以获得额外选择的列
现在,维护这样的索引并不是我们想要做的理想情况,因为考虑到表中的[Geography]列有多大以及它将来会增长多少,它使用了相当多的空间。像这样重建索引需要几个小时。
--- UPDATE 2 START ---
其他查询计划信息:
SELECT.QueryTimeStats
CpuTime=11648ms
ElapsedTime=7533ms
Key Lookup (88%)
Actual I/O Statistics
Actual Logical Reads=1191
Actual Physical Reads=0
Actual Time Statistics
Actual Elapsed CPU Time=0ms
Actual Elapsed Time=0ms
Index Seek (3%)
Actual I/O Statistics
Actual Logical Reads=7119
Actual Physical Reads=4
Actual Read Aheads=6678
Actual Scans=21
Actual Time Statistics
Actual Elapsed CPU Time=104ms
Actual Elapsed Time=168ms
Filter (9%)
Actual Time Statistics
Actual Elapsed CPU Time=11535ms
Actual Elapsed Time=6888ms
关于统计数据的补充说明:
- 当深入研究大多数这些数字时,它们在可用线程之间分配得非常好。
- 我的猜测是,这些统计数据的主要收获是,在“键查找”期间,此查询花费的时间恰好为零,而其他查询则必须大量进行IO工作。我不确定为什么它在这方面要好得多,因为它仍然必须找到另外选择的列(我选择的那个不是
[Geography]
列)。但由于过滤器已经应用于查找之前,因此显然必须做得少得多。但即便如此,零IO也让我感到困惑。 - 物理读取非常少。所有所需数据(包括[Geography]列)仅通过4个物理读取从索引搜索中读取。
--- 更新2结束 ---
3. 修改表格使其按([FlightDate] ASC,[Id] ASC)聚集
现在,考虑到我们已经考虑将表进行分区,我们还考虑更改聚集索引,使其包括[FlightDate]。请查看以下SQL DDL:
ALTER TABLE [Trajectory] DROP CONSTRAINT [PK_Trajectory]
ALTER TABLE [Trajectory] ADD CONSTRAINT [PK_Trajectory] PRIMARY KEY CLUSTERED ([FlightDate] ASC, [Id] ASC)
CREATE UNIQUE INDEX [AK_Trajectory] ON [Trajectory] ([Id] ASC)
这将更改表,使其现在基于[FlightDate]聚集,然后是[Id],确保唯一性。此外,我们在[Id]上添加了一个备用键约束,因此理论上仍然可以用于引用表格。
这三个SQL语句需要几个小时才能完成,但这样做的额外好处是,它非常容易在未来对[FlightDate]进行分区,从而允许在针对表格进行的所有查询中进行分区消除。
我们现在执行相同的查询到表时的预期是查询计划如下:
1.在索引上执行聚集索引查找(此操作将将11600000行过滤为大约50000)
2.在返回的每一行上执行地理筛选器[Geography].STIntersects(@p1)=1
这是比前面的示例中描述的查询计划更简单的查询计划,实际上它确实使用了此计划,如下所示: 唯一的问题是,完成此操作大约需要一分钟时间。但是,如果我们更仔细地查看查询计划本身,它还会反驳先前的结论,即查询中实际上花费时间的是对主表的查找,因为在这里它说大部分时间都花费在[Geography]列上的筛选上。
我有一个额外的评论可能会很有趣: 即使我不删除之前创建的索引([IX_Trajectory_FlightDate_Includes_Geography]),在像这样改变表结构后,查询也会很慢。但是,如果我提示查询编译器应该使用前一节中使用的替代键创建的索引[AK_Trajectory]和[IX_Trajectory_FlightDate_Includes_Geography],使用WITH (INDEX([AK_Trajectory], [IX_Trajectory_FlightDate_Includes_Geography])),那么查询的性能将与(2)中大致相同。
因此,SQL Server实际上积极决定使用较慢的查询计划,显然认为它更快。老实说,我不怪它。我会做同样的事情,因为那个查询计划简单得多。发生了什么事?
现在,您可能会有疑问,我们是否考虑将
[Geography]
列添加SPATIAL INDEX
。这是一个考虑因素。这种索引的问题(以及为什么它实际上无法使用)有两个方面:
[FlightDate]
索引能够过滤掉比这种索引更多的[Trajectory]
行。问题的关键在于,随着表的增长,这种 SPATIAL INDEX "SEEK" 的结果会线性增长,而[FlightDate]
上的INDEX SEEK则不会。- 维护这样的 SPATIAL INDEX 是昂贵的,并且插入操作会随着索引变得越来越大而变得越来越慢。
--- UPDATE 3 START ---
其他查询计划信息(主要步骤,注意:与上面显示的查询的执行不同,因此时间上有所变化。此查询花费了0:49):
SELECT.QueryTimeStats
CpuTime=11818ms
ElapsedTime=48253ms
Parallelism (7%)
Actual Time Statistics
Actual Elapsed CPU Time=7ms
Actual Elapsed Time=47638ms
Clustered Index Seek (25%)
Actual I/O Statistics
Actual Logical Reads=7403
Actual Physical Reads=4
Actual Read Aheads=6939
Actual Scans=21
Actual Time Statistics
Actual Elapsed CPU Time=107ms
Actual Elapsed Time=57ms
Filter (69%)
Actual Time Statistics
Actual Elapsed CPU Time=11727ms
Actual Elapsed Time=48250ms
- 聚集索引中的逻辑读取在所有线程之间分散。
- 预读取不会在线程之间分散(但其他索引也不会如此)。
- 不确定这些内容如何解释为什么这比索引#2慢。
Lucky Brain建议可能会更慢,因为数据实际上存储在ROW_OVERFLOW_DATA页面而不是IN_ROW_PAGES中。以下是对表中数据实际存储方式的更详细查看,使用以下查询进行查询:
SELECT
OBJECT_SCHEMA_NAME(p.object_id) table_schema,
OBJECT_NAME(p.object_id) table_name,
p.index_id,
p.partition_number,
au.allocation_unit_id,
au.type_desc,
au.total_pages,
au.used_pages,
au.data_pages
FROM sys.system_internals_allocation_units au
JOIN sys.partitions p
ON au.container_id = p.partition_id
WHERE OBJECT_NAME(p.object_id) = 'Trajectory'
ORDER BY table_schema, table_name, p.index_id, p.partition_number, au.type;
这提供了有关如何存储主表(聚集索引)和每个其他索引的数据的信息。其结果为:
聚集索引
IN_ROW_DATA: total_pages=705137, used_pages=705137, data_pages=697811
LOB_DATA: total_pages=10302796, used_pages=10248361, data_pages=0
ROW_OVERFLOW_DATA: total_pages=9, used_pages=2, data_pages=0
索引 #2
IN_ROW_DATA: total_pages=497639, used_pages=494629, data_pages=496531
LOB_DATA: total_pages=10219824, used_pages=10217546, data_pages=0
ROW_OVERFLOW_DATA: ------------------------------------------------------------
但正如上面所述,主表(聚集索引)和索引#2都使用LOB_DATA页面,因此我不完全确定为什么索引#2会快得多,除非LOB_DATA在与聚集索引相比时对于索引意味着不同的东西。
但我觉得我看到的一切都支持同样的结论:
当查询需要在包含
LOB_DATA
数据的主表上进行查找时,即使它作为聚集索引的一部分执行索引搜索,该查找也总是非常缓慢。基本上我所做的每个查询(快或慢)都表明了这一点。例如,考虑索引#1:
- 使用原始查询,它将不得不进行大约50000次键查找,需要近3分钟才能完成。
- 如果我更改查询以便通过[EntryTime](示例已在
UPDATE 1
中解释)对其进行过滤,以使结果集保持大致相等(约1000行左右),则查询突然需要约20秒钟。这种更改意味着它只需针对实际结果集在主表中进行到LOB_DATA
页面的查找,而不是在索引#1中寻找的所有50000个条目。 (重要的是,它仍然必须对所有内容进行主表的键查找,但不需要为每个条目转到LOB_DATA
。) - 但是,虽然20秒比3分钟快得多,但它仍然不及使用索引#2执行的原始查询(必须触及所有50000个[Geometry]值!)。现在我感觉唯一合理的解释是,对主表的
LOB_DATA
进行查找会显着减慢查询速度。 - 我认为这可以解释索引#1和索引#2之间性能差异的相当大的差异。而索引#2和聚集索引#3之间的差异则不是那么明显。
--- 更新 4 结束 ---
--- 更新 5 开始 ---
前一个更新包括两个索引的物理页面统计信息,这里是第一个索引的相同统计信息:
索引 #1
IN_ROW_DATA:total_pages=18705,used_pages=18698,data_pages=18659
LOB_DATA:--------------------------------------------------
ROW_OVERFLOW_DATA:--------------------------------------------------
显然,这不包括LOB_DATA
或ROW_OVERFLOW_DATA
。但更令人惊讶的是,IN_ROW_DATA
使用的页面数量比索引 #2少得多(大约20-30个数量级)。这表明,正如Lucky Brain所建议的那样,当在索引中包括空间列时,SQL Server可能会将有关该几何/地理信息的某些信息(例如边界框)直接存储在IN_ROW_DATA
中,以便快速执行几何操作。
当然,这假设表在作为聚集索引的一部分时没有针对“常规”空间列执行此操作。
--- 更新 5 结束 ---
问题
有人能回答以下两个问题吗:
- 是否可能简单的查找操作可以解释(1)和(2)中描述的索引之间性能差异?
- 为什么(3)中描述的聚集索引比(2)中的索引慢得多?
- 如果以上两个问题都无法回答,当比较1和2中描述的这两个索引时,我们应该看到如此大的性能差距吗?还是我们的设置可能出了其他问题?
DBCC DROPCLEANBUFFERS
的情况下重复操作,以评估热缓存时间。存储引擎可能会在冷缓存的情况下表现不同。 - Dan Guzman