索引所有列 [英] Index all columns

问题描述

知道索引列会带来更好的性能，是否值得对数据库所有表中的所有列建立索引?这种方法的优点/缺点是什么?

Knowing that an indexed column leads to a better performance, is it worthy to indexes all columns in all tables of the database? What are the advantages/disadvantages of such approach?

如果值得的话，有没有一种方法可以在SQL Server中自动创建索引?我的应用程序动态添加表和列(取决于用户配置)，我希望它们自动索引.

If it is worthy, is there a way to auto create indexes in SQL Server? My application dynamically adds tables and columns (depending on the user configuration) and I would like to have them auto indexed.

推荐答案

由于上述原因，很难想象在现实世界中对每列进行索引都是有用的.场景类型将需要一堆不同的查询，所有查询都只访问表的一列.每个查询可能正在访问不同的列.

It is difficult to imagine real-world scenarios where indexing every column would be useful, for the reasons mentioned above. The type of scenario would require a bunch of different queries, all accessing exactly one column of the table. Each query could be accessing a different column.

其他答案在查询的 select 期间未解决问题.显然，维护索引是一个问题，但是如果您一次创建表然后多次读取，则无需考虑更新/插入/删除的开销.

The other answers don't address the issues during the select side of the query. Obviously, maintaining indexes is an issue, but if you are creating the table/s once and then reading many, many times, the overhead of updates/inserts/deletes is not a consideration.

索引包含原始数据以及指向数据所在的记录/页面的点.索引的结构使您可以快速执行以下操作:查找单个值，按顺序检索值，计算不同值的数量以及查找最小值和最大值.

An index contains the original data along with points to records/pages where the data resides. The structure of an index makes it fast to do things like: find a single value, retrieve values in order, count the number of distinct values, and find the minimum and maximum values.

索引不仅占用磁盘空间.更重要的是，它占用内存.而且，内存争用通常是决定查询性能的因素.通常，在每列上建立索引将比原始数据占用更多的空间. (一个例外是一列相对较宽且值相对较少的列.)

An index does not only take space up on disk. More importantly, it occupies memory. And, memory contention is often the factor that determines query performance. In general, building an index on every column will occupy more space than then original data. (One exception would be a column that is relative wide and has relatively few values.)

此外，要满足许多查询，您可能需要一个或多个索引 plus 原始数据.您的页面缓存中充斥着大量数据，这可能会增加缓存未命中的次数，从而导致更多的开销.

In addition, to satisfy many queries you may need one or more indexes plus the original data. Your page cache gets rather filled with data, which can increase the number of cache misses, which in turn incurs more overhead.

我想知道您的问题是否真的表明您没有对数据结构进行适当的建模.在少数情况下，您希望用户构建临时永久表.通常，他们的数据将以预定义的格式存储，您可以根据访问要求对其进行优化.

I wonder if your question is really a sign that you have not modelled your data structures adequately. There are few cases where you want users to build ad hoc permanent tables. More typically, their data would be stored in a pre-defined format, which you can optimize for the access requirements.

这篇关于索引所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！