聚集索引必须是唯一的吗? [英] Do clustered indexes have to be unique?
问题描述
如果聚集索引不唯一会怎样?是否会因为插入的行流向溢出"而导致性能不佳?某种页面?
What happens if a clustered index is not unique? Can it lead to bad performance because inserted rows flow to an "overflow" page of some sorts?
它是制造"的吗?独特的，如果是这样如何?让它独一无二的最佳方法是什么?
Is it "made" unique and if so how? What is the best way to make it unique?
我问是因为我目前使用聚集索引将我的表划分为逻辑部分，但性能一般，最近我得到了 建议 使我的聚集索引独一无二.我想就此发表第二意见.
I am asking because I am currently using a clustered index to divide my table in logical parts, but the performance is so-so, and recently I got the advice to make my clustered indexes unique. I'd like a second opinion on that.
推荐答案
他们不必是独一无二的，但肯定是值得鼓励的.
我还没有遇到过要在非唯一列上创建 CI 的情况.
They don't have to be unique but it certainly is encouraged.
I haven't encountered a scenario yet where I wanted to create a CI on a non-unique column.
如果聚集索引不是唯一的索引，SQL Server 使任何重复通过在内部添加唯一键生成的值称为唯一标识符
If the clustered index is not a unique index, SQL Server makes any duplicate keys unique by adding an internally generated value called a uniqueifier
这是否会导致性能不佳?
添加唯一符肯定会增加计算和存储的开销.
这种开销是否显着取决于几个因素.
Adding a uniqueifier certainly adds some overhead in calculating and in storing it.
If this overhead will be noticable depends on several factors.
- 该表包含多少数据.
- 插入率是多少.
- 在选择中使用 CI 的频率(当不存在覆盖索引时，几乎总是如此).
编辑
正如 Remus 在评论中指出的那样，确实存在创建非唯一 CI 是合理选择的用例.我没有遇到过这些场景中的一个，这只是表明我自己缺乏曝光或能力(选择你的选择).
Edit
as been pointed out by Remus in comments, there do exist use cases where creating a non-unique CI would be a reasonable choice. Me not having encountered one off those scenarios merely shows my own lack of exposure or competence (pick your choice).
这篇关于聚集索引必须是唯一的吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
