我正在测试一些索引,并想对哪些索引执行得更好进行一些 AB 测试(主要是索引中字段的顺序和指定的其他字段)。
我有一个正在测试的数据库的备份,但该备份需要很长时间才能恢复。我更愿意只创建索引 foo,收集性能指标,然后删除 foo 以将数据库返回到索引前状态并创建索引 bar。这是创建索引的工作方式,还是创建索引会以某种删除索引无法撤消的方式更改表?
我正在测试一些索引,并想对哪些索引执行得更好进行一些 AB 测试(主要是索引中字段的顺序和指定的其他字段)。
我有一个正在测试的数据库的备份,但该备份需要很长时间才能恢复。我更愿意只创建索引 foo,收集性能指标,然后删除 foo 以将数据库返回到索引前状态并创建索引 bar。这是创建索引的工作方式,还是创建索引会以某种删除索引无法撤消的方式更改表?
更长
表上的索引更改(创建、重建、删除等)会使计划缓存中的任何相关计划失效,并导致在下次执行相关查询时重新编译新计划。
在缓存原始计划和重新编译新计划之间数据发生变化的数据库上,可能会生成与原始计划不同的计划。
实际上,由于您只是在我假设不会主动更改的数据库副本上进行 A/B 测试,所以是的,Erik很简洁。删除索引很可能会导致在后续运行中为您的查询缓存相同的原始执行计划 - 这意味着您的数据库将有效地返回到其以前的状态。
选择
对于这种测试,我喜欢使用数据库快照。
请参阅链接文档以了解确切的语法和需要注意的事项。
在 SQL Server 2016 Service Pack 1 之前,此功能需要企业版。从那时起,它在每个版本中都可用。
短的
删除索引足以将表重置为之前的状态。如果禁用索引,为其创建的统计信息可能仍用于基数估计。
您必须注意一些小陷阱,因为这取决于您正在创建和/或删除的索引。
聚簇索引随之而来
有提示建议在堆(未排序的数据)上创建聚簇索引以允许对它们进行排序(由于聚簇索引的创建),然后删除聚簇索引以再次拥有堆。删除索引后重建表将释放一些未使用的空间,因为记录在页面中被删除。
堆(没有聚集索引的表)(Microsoft | Learn)。
上面的文章中有一个小警告,指出在存在其他索引的堆上创建和删除聚集索引将需要完全重建所有非聚集索引。警告!
如果在包含非聚集索引的堆上创建聚集索引,然后再次删除聚集索引,则可能会对性能产生重大影响。
SQL Server 聚簇表与堆表(MSSQLTips)
从堆(表)中删除聚簇索引不会将现在排序的数据(由于聚簇索引的创建)返回到未排序状态。
回答你的问题
如上所述,并非在所有情况下。在堆中添加和删除聚簇索引可能会对性能产生重大影响,并且可能会对数据库对这些更改的反应(执行计划方面)产生重大影响。
使用 db<>fiddle 的例子
这是一个简短的示例,显示在堆上创建聚集索引然后删除聚集索引不会将数据返回到之前的状态。即使经过
ALTER TABLE ... REBUILD
...数据库<>小提琴示例
小提琴
数据已更改!
可能的解决方案
...并且请不要在生产环境中创建索引以进行快速测试。