为什么我不能简单地添加一个包含所有列的索引? [英] 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
名字单独城市街道
该索引对搜索有用
(LastName)或(LastName, FirstName)或(LastName, FirstName, Street)或(姓氏、名字、街道、城市)
(LastName), or(LastName, FirstName), or(LastName, FirstName, Street), or(LastName, FirstName, Street, City)
但真的没有别的 - 如果您只搜索 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.
考虑一下您的电话簿:它的顺序可能是姓氏、名字，也可能是街道.那么该索引是否可以帮助您找到您所在城市中的所有Joe's"?所有住在大街"的人??不 - 您可以先通过姓氏查找 - 然后您可以在该组数据中获得更具体的信息.仅仅对所有内容建立索引无助于加快搜索所有列.
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.
如果您希望能够通过 Street 进行搜索 - 您需要在 (Street) 上添加一个单独的索引(可能还有一两列有意义).
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).
如果您希望能够通过 Occupation 或其他任何方式进行搜索 - 您需要另一个特定的索引.
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屋！
