SQL Server 2005聚簇索引查询速度

Our sites are getting pounded pretty hard so we're taking a look into optimizing some of our existing queries.

我们的网站变得非常困难,所以我们正在考虑优化一些现有的查询。

While looking into this we ran across several queries whose execution plan was about 4-5 times faster when a simple reference of the clustered index is in the query... for example

在研究这个时,我们遇到了几个查询,当查询中有一个简单的聚集索引引用时,执行计划的速度提高了4-5倍...例如

If this was the old query:

如果这是旧查询:

SELECT ...
FROM myTable
WHERE categoryID =  @category

the following query would be 4 times faster according to the execution plan in SSMS:

根据SSMS中的执行计划,以下查询将快4倍:

SELECT ...
FROM myTable
WHERE categoryID =  @category 
AND lotID = lotID

We can't seem to make sense of how this would make the query faster. The clustered index is on lotID but since its doing a comparison against itself how is this helping?

我们似乎无法理解这将如何使查询更快。聚集索引在lotID上,但由于它与自身进行比较,这有什么帮助?

1 个解决方案

#1

seems pretty obvious to me

对我来说似乎很明显

your first query is not covered by the clustered index while the second is since lotID is not in the WHERE clause of the first query

第一个查询不包含在聚簇索引中,而第二个查询则不在第一个查询的WHERE子句中

You might want to read SQL Server covering indexes to see how that all works

您可能希望阅读覆盖索引的SQL Server,以了解这一切是如何工作的

you also need to understand that a clustered index IS the data, all the data for a table is in the clustered index. when you create a non clustered index on table that has a clustered index then the non clustered index will have a pointer to the clustered index (since that is where the rest of the data is) unless you can satisfy your query completely by the non clustered index and in that case only the non clustered index will be used...I will stop rambling now

您还需要了解聚簇索引是数据,表的所有数据都在聚簇索引中。当您在具有聚簇索引的表上创建非聚集索引时,非聚簇索引将具有指向聚簇索引的指针(因为这是其余数据所在的位置),除非您可以完全满足非群集的查询索引,在这种情况下,只会使用非聚集索引......我现在将停止漫游

EDIT

I read AND lotID = @lotID NOT AND lotID = lotID

我读了AND lotID = @lotID NOT AND lotID = lotID

sometimes you can fake out a clustered index by doing where lotID >0 (picking the lowest number you have) and you will get a seek

有时你可以通过执行lotID> 0(选择你拥有的最低数字)来伪造聚集索引,你会得到一个搜索

So if your smallest lotID = 1 and you add AND lotID > 0

因此,如果您的最小lotID = 1并且您添加AND lotID> 0

you could also see a seek instead of a scan, I demonstrate WHERE IndexValue > '' in this post Is an index seek always better or faster than an index scan?

您还可以看到搜索而不是扫描,我在这篇文章中演示了WHERE IndexValue>''索引搜索总是比索引扫描更好或更快?

#1