SQL Server 2005 聚集索引查询速度
我们的网站受到了很大的打击，因此我们正在考虑优化一些现有的查询。
在研究这个问题时，我们遇到了几个查询，当查询中存在聚集索引的简单引用时，其执行计划大约快 4-5 倍...例如，
如果这是旧查询：
SELECT ...
FROM myTable
WHERE categoryID = @category
以下查询将快 4 倍根据 SSMS 中的执行计划更快：
SELECT ...
FROM myTable
WHERE categoryID = @category
AND lotID = lotID
我们似乎无法理解这将如何使查询更快。 聚集索引位于lotID 上，但由于它与自身进行比较，这有什么帮助？
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
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:
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
对我来说似乎很明显
你的第一个查询没有被聚集索引覆盖，而第二个查询是因为lotID不在第一个查询的WHERE子句中
你可能想阅读SQL Server 覆盖索引 要了解这一切是如何工作的，
您还需要了解聚集索引就是数据，表的所有数据都在聚集索引中。 当您在具有聚集索引的表上创建非聚集索引时，非聚集索引将有一个指向聚集索引的指针（因为这是其余数据所在的位置），除非您可以通过非聚集索引完全满足您的查询索引，在这种情况下，将仅使用非聚集索引...我现在将停止胡言乱语
编辑
我读到 AND lotID = @lotID
NOT AND LotID = LotID
有时，您可以通过执行 where LotID > 0 （选择您拥有的最小数字）来伪造聚集索引，并且您将获得搜索
因此，如果您的最小 LotID = 1 并且您添加
且lotID> 0
您还可以看到查找而不是扫描，我演示了 WHERE IndexValue > '' 在这篇文章中 索引查找总是比索引扫描更好或更快吗？
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
You might want to read SQL Server covering indexes to see how that all works
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
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
So if your smallest lotID = 1 and you add
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?