如何强制查询不使用给定表上的索引？ [英] How can I force a query to not use a index on a given table?

问题描述

我正在进行一些测试，以确定在SQL Server 2005中为某个列包含索引的性能影响。

I'm currently doing some testing to determine the performance implications of including an index on a given column in SQL Server 2005.

测试数据集我是使用大约有大约7200万行（大约6 GB的数据）。为了实际测试索引的性能，我需要能够比较有和没有索引的性能。

The test data set I'm using has approximately ~72 million rows (about 6 GB of data). In order to actually test the performance of the index I need to be able to compare the performance with and without the index there.

这就是全部好吧，但是首先创建一个索引并不是一个便宜的操作。如果我想测试没有索引的表，我至少需要禁用索引。要使用索引进行测试，我需要重新启用它，这需要相当长的时间。

That's all well and fine, but creating an index in the first place is not a cheap operation. If I want to test the table without the index, I need to, at the very least, disable the index. To test with the index I need to re-enable it which takes quite a long time.

有什么方法可以强制SQL Server 2005忽略给定的索引什么时候执行查询？我不想为了测试查询而禁用索引，因为它需要很长时间来禁用索引。

Is there any way that I can force SQL Server 2005 to ignore a given index when it's executing a query? I don't want to have to disable the index just to test a query since it takes such a long time to disable the index.

推荐答案

SELECT *
FROM MyTable WITH (INDEX(0))
WHERE MyIndexedColumn = 0

查询通常会使用MyIndexedColumn上的索引，但由于表提示，它将代替表扫描。

Query would normally use the index on MyIndexedColumn, but due to the table hint, it will instead tablescan.

SELECT *
FROM MyTable WITH (INDEX(IndexName))
WHERE MyIndexedColumn = 0

查询通常会使用MyIndexedColumn上的索引，但由于表提示，它将使用名为IndexName的索引。

Query would normally use the index on MyIndexedColumn, but due to the table hint, it will instead use the index named IndexName.

这篇关于如何强制查询不使用给定表上的索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！