聚集和非聚集索引性能 [英] Clustered and nonclustered indexes performance
问题描述
我有一个巨大的表(约 1000 万行)，在随机的 uniqueidentifier 列上有聚集的 PK.如果还没有具有相同 pk 的行，我对该表执行的大多数操作是插入一个新行.(为了提高它的性能，我使用 IGNORE_DUP_KEY = ON 选项)
我的问题是
我可以完全去掉这个表上的聚集索引吗?我的意思是当我将一行插入到带有聚集索引的表中时，它应该物理地重新排列数据.删除聚集索引并在该列上创建非聚集索引以避免数据重新排列可能更好?
我无法在实时数据库上进行实验，因为如果性能下降，那将是一件令人头疼的事情.在测试数据库中，我只能看到聚集索引插入 100%"(在使用聚集索引的情况下)和表插入"+ 在使用非聚集索引的情况下在非聚集索引中的一些查找操作.
提前致谢
GUID 似乎是您的主键的自然选择 - 如果您真的必须，您可能会争论将其用于表的 PRIMARY KEY.我强烈建议不要做的是使用 GUID 列作为群集键，SQL Server 默认情况下会这样做，除非您明确告诉它不要这样做.>
你真的需要把两个问题分开:
1) 主键 是一种逻辑结构 - 唯一且可靠地标识表中每一行的候选键之一.这实际上可以是任何东西 - 一个 INT、一个 GUID、一个字符串 - 选择最适合您的场景的内容.
2) 聚簇键(在表上定义聚簇索引"的一列或多列)——这是一个物理存储相关的东西，这里，小型、稳定、不断增加的数据类型是您的最佳选择 - INT 或 BIGINT 作为您的默认选项.
默认情况下，SQL Server 表上的主键也用作集群键 - 但不必如此！当将以前的基于 GUID 的主键/集群键分解为两个单独的键时，我个人已经看到了巨大的性能提升 - GUID 上的主(逻辑)键和上的集群(排序)键一个单独的 INT IDENTITY(1,1) 列.
作为 Kimberly Tripp- 索引女王 - 和其他人已经多次说过 - 作为集群键的 GUID 不是最佳的，因为由于它的随机性，它会导致大量的页面和索引碎片，并且通常会导致性能不佳.
是的，我知道 - 在 SQL Server 2005 及更高版本中有 newsequentialid() - 但即使这样也不是真正和完全顺序的，因此也遇到与 GUID 相同的问题 - 只是一点点不那么显眼.
然后还有另一个问题需要考虑:表上的聚簇键也将添加到表上每个非聚簇索引的每个条目中 - 因此您真的想确保它尽可能小.通常，对于绝大多数表来说，具有 2+ 十亿行的 INT 应该足够了 - 与作为集群键的 GUID 相比，您可以在磁盘和服务器内存上节省数百兆字节的存储空间.
快速计算 - 使用 INT 与 GUID 作为主键和聚类键:
- 具有 1'000'000 行的基表(3.8 MB 与 15.26 MB)
- 6 个非聚集索引(22.89 MB 与 91.55 MB)
总计:25 MB 对 106 MB - 这只是在一张桌子上！
一些值得深思的食物 - 金伯利·特里普 (Kimberly Tripp) 的优秀作品 - 阅读、再阅读、消化！这是 SQL Server 索引的福音，真的.正如她在集群索引辩论继续"中所展示的那样，拥有一个好的集群键(而不是没有或坏的)确实可以加快几乎所有的数据库操作！这是一个好主意 - 但它必须是一个很好的聚类键....
马克
I have a huge table (~ 10 million rows) with clustered PK on a random uniqueidentifier column. The most operations I do with this table is inserting a new row if there is not yet a row with the same pk. (To improve performance of it I use IGNORE_DUP_KEY = ON option)
My question is
Can I get rid of clustered index at all on this table? I mean when I insert a row into a table with clustered index it should rearrange data physicaly. May be it is better to drop clustered index and create nonclustered index on that colum to avoid data rearrangement?
I can't do an experiment on the live db because if performance falls down it will be a headache. On the test db I can only see 'Clustered Index Insert 100%' in the case with clustered index and 'table insert' + some seeking opertations in the nonclustered index in the case with non-clustered index.
Thanks in advance
GUIDs may seem to be a natural choice for your primary key - and if you really must, you could probably argue to use it for the PRIMARY KEY of the table. What I'd strongly recommend not to do is use the GUID column as the clustering key, which SQL Server does by default, unless you specifically tell it not to.
You really need to keep two issues apart:
1) the primary key is a logical construct - one of the candidate keys that uniquely and reliably identifies every row in your table. This can be anything, really - an INT, a GUID, a string - pick what makes most sense for your scenario.
2) the clustering key (the column or columns that define the "clustered index" on the table) - this is a physical storage-related thing, and here, a small, stable, ever-increasing data type is your best pick - INT or BIGINT as your default option.
By default, the primary key on a SQL Server table is also used as the clustering key - but that doesn't need to be that way! I've personally seen massive performance gains when breaking up the previous GUID-based Primary / Clustered Key into two separate key - the primary (logical) key on the GUID, and the clustering (ordering) key on a separate INT IDENTITY(1,1) column.
As Kimberly Tripp - the Queen of Indexing - and others have stated a great many times - a GUID as the clustering key isn't optimal, since due to its randomness, it will lead to massive page and index fragmentation and to generally bad performance.
Yes, I know - there's newsequentialid() in SQL Server 2005 and up - but even that is not truly and fully sequential and thus also suffers from the same problems as the GUID - just a bit less prominently so.
Then there's another issue to consider: the clustering key on a table will be added to each and every entry on each and every non-clustered index on your table as well - thus you really want to make sure it's as small as possible. Typically, an INT with 2+ billion rows should be sufficient for the vast majority of tables - and compared to a GUID as the clustering key, you can save yourself hundreds of megabytes of storage on disk and in server memory.
Quick calculation - using INT vs. GUID as Primary and Clustering Key:
- Base Table with 1'000'000 rows (3.8 MB vs. 15.26 MB)
- 6 nonclustered indexes (22.89 MB vs. 91.55 MB)
TOTAL: 25 MB vs. 106 MB - and that's just on a single table!
Some more food for thought - excellent stuff by Kimberly Tripp - read it, read it again, digest it! It's the SQL Server indexing gospel, really. As she shows in her "The Clustered Index Debate contiues", having a good clustering key (as opposed to none or a bad one) really does speed up pretty much all database operations! It's a good idea - but it has to be a good clustering key....
- GUIDs as PRIMARY KEY and/or clustered key
- The clustered index debate continues
- Ever-increasing clustering key - the Clustered Index Debate..........again!
- Disk space is cheap - that's not the point!
Marc
这篇关于聚集和非聚集索引性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
