添加更多列会减慢SELECT查询的速度 [英] Adding more columns slows down the SELECT query

问题描述

想象一下，Foo表在ColA和ColB上有非聚集索引，在ColC上没有索引，ColD

Imagine Foo table has non-clustered indexes on ColA and ColB and NO Indexes on ColC, ColD

SELECT colA, colB
FROM Foo

大约需要30秒。

takes about 30 seconds.

SELECT colA, colB, colC, colD
FROM Foo

需要大约2分钟。

Foo表有超过500万行。

问题：包含不属于索引的列可能会减慢查询速度吗？如果是，为什么？ - 它们不是已读取的PAGE的一部分吗？

takes about 2 minutes.
Foo table has more than 5 million rows.
Question: Is it possible that including columns that are not part of the indexes can slow down the query? If yes, WHY? -Are not they part of the already read PAGEs?

推荐答案

您可以根据需要通过使用不同的列组合创建多个索引来提高性能。另一个选项是，如果你想获取列，我们只能根据colA索引说colB，colC，colD，那么你可以在创建索引时使用include，如示例所示。

You can improve the performace by creating multiple index with different combinations of columns as per the need. The other option is if you want to fetch the columns let's say colB, colC, colD only based on colA index then you can use include while creating the index as shown in the example.

CREATE NONCLUSTERED INDEX IX_INDEX1    ON Foo (colA)
    INCLUDE (colB, colC, colD);

并查看执行计划以识别bottelnecks。有关创建索引的更多详细信息，请尝试此链接 http://msdn.microsoft.com/en-us/library /ms188783.aspx [ ^ ]



希望这很有用。

And also look at the execution plan to identify the bottelnecks. For more details about creating index try this link http://msdn.microsoft.com/en-us/library/ms188783.aspx[^]

Hope this is useful.

聚簇索引与数据一起存储，而非聚簇索引则不存储。在SELECT语句中混合它们绝不是明智的选择。

Clustered indexes are stored along with data while non-clustered indexes are not. Mixing them in SELECT statement is never a smart choice.

这篇关于添加更多列会减慢SELECT查询的速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！