为什么我不能简单地添加包含所有列的索引？ [英] Why can't I simply add an index that includes all columns?
问题描述
我在SQL Server数据库中有一个表，我希望能够尽快搜索和检索数据。我不关心插入表格需要多长时间，我只对获取数据的速度感兴趣。
I have a table in SQL Server database which I want to be able to search and retrieve data from as fast as possible. I don't care about how long time it takes to insert into the table, I am only interested in the speed at which I can get data.
问题是使用20个或更多不同类型的查询访问表。这使得添加专为每个查询设计的索引变得繁琐。我正在考虑只是添加一个包含表的所有列的索引。这不是你通常在好的数据库设计中所做的事情，所以我假设有一些很好的理由我不应该这样做。
The problem is the table is accessed with 20 or more different types of queries. This makes it a tedious task to add an index specially designed for each query. I'm considering instead simply adding an index that includes ALL columns of the table. It's not something you would normally do in "good" database design, so I'm assuming there is some good reason why I shouldn't do it.
任何人都可以告诉我为什么不这样做？
Can anyone tell me why I shouldn't do this?
更新：我忘了提，我也不关心我的数据库的大小。没关系，这意味着我的数据库大小将比它需要的大得多
UPDATE: I forgot to mention, I also don't care about the size of my database. It's OK that it means my database size will grow larger than it needed to
推荐答案
首先，SQL Server中的索引可以在其索引条目中只有最多900个字节。仅这一点就不可能有一个包含所有列的索引。
First of all, an index in SQL Server can only have at most 900 bytes in its index entry. That alone makes it impossible to have an index with all columns.
最重要的是：这样的索引完全没有意义。你想要实现什么？
Most of all: such an index makes no sense at all. What are you trying to achieve??
考虑一下：如果你有的索引（LastName，FirstName，Street，City），该指数将不能够用于加速查询
Consider this: if you have an index on (LastName, FirstName, Street, City), that index will not be able to be used to speed up queries on
-
FirstName单独 -
城市 -
街道
FirstNamealoneCityStreet
该指数对于
-
（LastName），或 -
（LastName，FirstName），或 -
（LastName，FirstName，Street），或 -
（姓氏，名字，街道，城市）
(LastName), or(LastName, FirstName), or(LastName, FirstName, Street), or(LastName, FirstName, Street, City)
但实际上没有别的 - 当然不是只搜索街道或只是城市！
but really nothing else - certainly not if you search for just Street or just City!
索引中列的顺序有很大不同，查询优化器不能只使用索引中间某处的任何列进行查找。
The order of the columns in your index makes quite a difference, and the query optimizer can't just use any column somewhere in the middle of an index for lookups.
考虑一下你的电话簿：它的订单可能是LastName，FirstName，也许是Street。那么索引是否可以帮助您找到您所在城市的所有Joe's？所有人都住在主街？不 - 您可以先通过LastName查找 - 然后在该组数据中获得更具体的信息。只对所有内容编制索引并没有帮助加快搜索所有>列。
Consider your phone book: it's order probably by LastName, FirstName, maybe Street. So does that indexing help you find all "Joe's" in your city? All people living on "Main Street" ?? No - you can lookup by LastName first - then you get more specific inside that set of data. Just having an index over everything doesn't help speed up searching for all columns at all.
如果您希望能够搜索到街道 - 你需要在（街道）上添加一个单独的索引（可能还有另外一两列有意义） 。
If you want to be able to search by Street - you need to add a separate index on (Street) (and possibly another column or two that make sense).
如果你想通过占领或其他任何东西进行搜索 - 你需要另一个特定的指数。
If you want to be able to search by Occupation or whatever else - you need another specific index for that.
仅仅因为您的列存在于索引中并不意味着会加快该列的所有搜索速度！
Just because your column exists in an index doesn't mean that'll speed up all searches for that column!
主要规则是：使用尽可能少的索引 - 对于系统来说，太多的索引可能比没有索引更糟糕了......构建系统，监视其性能，并找到那些成本的查询最多 - 然后优化这些，例如通过添加索引。
The main rule is: use as few indices as possible - too many indices can be even worse for a system than having no indices at all.... build your system, monitor its performance, and find those queries that cost the most - then optimize these, e.g. by adding indices.
不要只是盲目地索引每一列只是因为你可以 - 这是糟糕的系统性能的保证 - 任何索引也需要维护和维护，所以索引越多你你的INSERT，UPDATE和DELETE操作会越多（变得越来越慢），因为所有这些索引都需要更新。
Don't just blindly index every column just because you can - this is a guarantee for lousy system performance - any index also requires maintenance and upkeep, so the more indices you have, the more your INSERT, UPDATE and DELETE operations will suffer (get slower) since all those indices need to be updated.
这篇关于为什么我不能简单地添加包含所有列的索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
