我应该在事实表上创建聚集索引吗？ 绝不？ 总是？
在数据仓库中，在事实表上创建聚集索引有缺点吗？ （大多数情况下，它会在日期时间列上）
您会回答“是”还是“否”“默认情况下......”？
如果默认情况下我不应该创建聚集索引，那为什么呢？ （我知道聚集索引的优点，但有哪些缺点？）
参考
http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/09/11/365.aspx
In a data warehouse, are there disadvantages to creating clustered indexes on fact tables? (most of the time, it will be on the datetime column)
Would you answer yes or no "by default..."?
If I shouldn't create clustered indexes by default, then why? (I know the pros of clustered indexes, but what are some cons?)
References
http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/09/11/365.aspx
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我总是建议在经常按给定值搜索的表（事务或仓库）上建立聚集索引。 聚集索引（或任何索引）的缺点是您正在创建占用空间的额外数据存储。 如果正在建立索引的表很大...索引也会很大！ 拥有的索引越多，除数据库之外存储的数据就越多。 但是，如果您需要搜索速度，那么您可能需要索引来帮助获得该速度。
但是，您可能还希望在表的 ID 上创建聚集索引。 然后在 Lucene（或 Lucene.NET）等产品中在数据库外部创建索引。 然后，您可以搜索 Lucene 索引（在搜索方面具有更大的灵活性和功能），该索引将返回给定记录（或多个记录）的 ID，然后您可以使用该 ID 来识别数据库中所需的数据。 这是我们在当前项目中经常使用的路线，我必须承认它运行得非常流畅！ 创建索引的速度要快得多（特别是与在 SQL Server 中使用 FullText 选项相比）。 只是需要考虑的事情。
I would always suggest having a clustered index on a table (transaction or warehouse) that is searched by a given value frequently. The downside to a clustered index (or any index) is that you are creating an additional store of data that takes up space. If the table that is being indexed is huge...the index will be too! The more indexes you have the more data you are storing in addition to the database. However, if you need speed for your searches then you may need an index to help gain that speed.
However, you may also look to creating a clustered index on the ID of your table. And then create indexes outside of the database in a product such as Lucene (or Lucene.NET). Then you can search your Lucene index (which has way more flexibility and features when it comes to searching) which would return the ID of a given record (or records) which you can then use to identify the data that you need in your database. This is a route that we have used quite a bit in my current project and I must admit it works quite slick! Creating the indexes is considerably faster (especially when compared to using FullText options in SQL Server). Just something to consider.
我讨厌说总是和从不，但我认为在大多数情况下，您会发现在事实表上使用聚集索引是一件好事。 不建议在插入随机值的列类型上创建聚集索引。 例如，您不希望 GUID 等内容上有聚集索引。 日期和顺序 ID 非常适合聚集索引，尤其是范围查询。
您已经说过，您知道聚集索引的优点，但就缺点而言，实际上取决于您如何使用它们，以及它们是否对您不利。
1.) 每个表只能有一个聚集索引，因为它会更改数据块的布局以匹配索引的顺序。 这就是为什么聚集索引非常适合范围查询
2.) 因为数据块是按索引的顺序排列的，所以更改键顺序的插入和更新可能会导致对数据块进行物理修改，以便将它们保留在按索引排序。 按顺序插入键值可以缓解这个问题。
I hate to say always and never, but I think in most cases you will find using clustered indexes on your fact tables a good thing. It's not recommended to create a clustered index on a column type that is inserted on with values that can be random. For example, you would not want a clustered index on something like a GUID. Dates and sequential ID's are great for clustered indexes especially for range queries.
You've stated that you know the pros of the clustered indexes but as far as the cons are concerned will really be determined by how you use them as to whether they are cons for you.
1.) You can have only one clustered index per table, because it changes the layout of the data blocks to match the order of the index. This is why clustered indexes are great for range queries
2.) Because the data blocks are arranged by the order of the index, inserts and updates that change the order of the key can cause physical modifications to the data blocks in order to keep them in order with the index. Inserting the key value in sequential order can alleviate this con.
拥有
int (bigint)自动增量PK聚集索引极大地简化了分区； 事实表迟早会达到这一点。 因此，即使您认为现在可能不需要它，也可以创建一个。Having an
int (bigint)auto-increment PK-clustered index greatly simplifies partitioning; and sooner, or later a fact table gets to this point. So, even if you think you may not need it now, create one.