对于MS [主键默认是聚集索引] 的质疑

[bapi]

1.ms sqlserver的默认特性：主键默认是聚集索引
2.我听到的一些与我的理解明显不同的言论：
..聚集索引所在的列或列的组合最好是唯一的
..最好使用自增列作为聚集索引列


上面这些我一直理解不能，而且持上述看法的不在少数，这与我的认知严重违背，有谁能有办法说服我，告诉我是我错了？


================================以下是我的看法================================

1.在大多数情况下我们的性能问题是读取而不是写入，客户反映我们的系统慢，通常说的是某个功能或报表的查询慢，而不是保存慢，即使对于一个写操作非常频繁的系统来说，读写的比例至少在10:1以上，普通的信息系统这个比例就更高了，所以"从性能的角度考虑"这句话，我认为是要重点考虑读取性能，而不是写入的性能。
基于上述原因，我认为我们在做设计的时候，如果能通过聚集显著的提升读取性能，那么我们就可以接受聚集带来的负面效果（写性能下降，以及表上其它非聚集索引的读性能下降）

2.聚集的优势在于读取批量且连续的记录，这是由聚集的物理形态决定的。比如读取一个月内的销售数据，如果按日期聚集，则这一个月内的数据所在的页面在物理上是连续的，可以极大避免最昂贵的磁头随机移动的操作，这是非聚集索引无法做到的。就好比你查字典，查找所有读音为 [ta] 的字效率很高，但是查找所有偏旁部首为 [亻]的字效率非常低。
因此基于上述分析：我认为聚集索引应该要选择一个离散度适中的列，以保证能够命中适当范围的记录，注意我的表述是[适当范围]：
如果命中范围太大则说明该列没有索引的价值（比如性别更，平均每次命中50%的记录）
如果命中范围太小则说明没有聚集的价值（比如主键，平均只能命中1条记录）

3.表上的聚集索引只能有一个，而且聚集还有负面影响，因此其宝贵程度不言而喻。而自长列通常在业务逻辑上毫无意义（用户不可能按自增长列来进行查询或分析等业务操作），这样的列用做主键没有问题，但用作聚集索引只能说是暴殄天物，原因与上面第2条同理：自增长列的离散度太低，导致选择性过高，每次只能命中一条记录，并不具备聚集的价值，这样的聚集还不如没有，因为我们承受了聚集带来的负面效果，却又没有体现出聚集的优势，因此我一向认为堆表的性能肯定要比那些设计得很糟糕的聚集表要高。

[hgwyl]

这个贴从刚开始发我就开始关注着看了。 感觉楼主有点钻牛角尖。 ——聚集索引这玩意，要看实际业务来建立。 ——一般情况我们都会建立的，不然太浪费这个功能了。 ——如果楼主大人觉得不需要建立的话，让MS默认帮你建立在自增主键上，并没有坏处。 ——如果楼主大人已经在其他列上建立了聚集索引，系统就不会默认建立了。 就如同新建一个excel文件一样，新建直接有sheet1、sheet2、sheet3这三个表。 这只是默认，你觉得用处不大或者不需要就自己修改好了。

[mkt]

学习学习

[开着拖拉机泡妞]

引用 40 楼 bapi 的回复:

好吧，我已经听到我想要的答案：A与B的博弈 A与B谁会胜出，在不同的业务领域没有固定的答案，所以大家所坚持的，我认同，我自己坚持的，我也认同。 就此结束吧，感谢各位倾情演出！

说句你不想听的话 你根本还是没理解别人的意思

[u010896020]

学习了 。。。

[aass0059]

真心好啊，我来求指导了

[缘中人]

很好，我决定默认主键是聚集索引不好，不合理，我都删除重建了

[撸大湿]

引用 43 楼 TravyLee 的回复:

说句你不想听的话 你根本还是没理解别人的意思

对美良景辰乐事 牛子横书孤壮志 弹压山川据要会 琴声长伴读书人

[开着拖拉机泡妞]

引用 49 楼 tntzbzc 的回复:

[quote=引用 43 楼 TravyLee 的回复:] 说句你不想听的话 你根本还是没理解别人的意思

对美良景辰乐事 牛子横书孤壮志 弹压山川据要会 琴声长伴读书人 [/quote]

[qq1054423182]

很好，非常好！

[十八岁的吴彦祖]

楼主，好友思想！！！！！

[dq9005]

学习了

[bwangel]

我个人认为建立索引和有助于提升某些请况下的写效率。特别是UPDATE的效率。

[szm341]

向各位大神学习了~ 不过lz纠结于“MS将主键默认为聚集的做法并不妥当” 就如上面几位所说，只是个默认值，不喜欢可以改嘛 还有就是，我个人理解，主键是为了唯一标识数据行，抛去聚集索引可以认为是逻辑排序 那建表建主键时，如果在没有指定其他列是聚集索引的情况， 默认把逻辑排序与物理排序设置在一起也很符合逻辑啊

[叫我三三]

我是学习的~

[fwjsh0510]

标记学习，看各位大牛的独家见解

[zbdzjx]

个人感觉： 从概率上说，多数情况下主键应该是聚集索引，所以就默认了。如果不是，就自己再修改。 如果不加这个默认，很多初级人员是不会建聚集索引的。 所以，主键默认是聚集索引，好处大于坏处，但不是表示这样就绝对正确了，要看具体情况。

[Sb梁先生]

怎么都是大半夜的 回答问题呀。生命第一啊

[linwaterbin]

引用 27 楼 SQL_Beginner 的回复:

上面有好几位都提到了，用自增列做聚集索引，绝对是有道理的。

没有适用场景吗？比如写操作多的用自增、然而、读操作多的要考虑用业务列 我说的这个情况在MySQL InnoDB 上基本是这样、另外、MySQL InnoDB和MS默认是一样的：IOT

[gongyuan1202]

虽然我谢绝教条主义，但我不明白你为什么将离散性和聚集性能挂钩。查询性能也不是只盯着聚集索引，就拿你举的例子[ta]和[亻]，这是一个很形象的问题，你认为查找所有读音为 [ta] 的字效率很高，我认为查找所有偏旁部首为 [亻]的字更具效率，部首索引条目就有，多极几页，翻聚集索引的[ta]，超10页没问题吧。每一个查询都走聚集，等于没索引设计，如将字典部首索引撕了。

[snp_yzl]

引用 22 楼 Beirut 的回复:

[quote=引用 11 楼 bapi 的回复:] [quote=引用 9 楼 SQL_Beginner 的回复:] 你上面提到多次“聚集还有负面影响”，能否说说负面影响具体指哪些？ 除了你说的“写性能下降”

1.写入性能下降（插入/删除/更新聚集键值等，会导致记录的物理移动、页拆分等额外的磁盘操作） 2.非聚集索引的空间彭胀（聚集表上的非聚集索引，需要记录聚集键） 2.表上其它非聚集索引的读性能下降（在一个聚集表上使用非聚集索引查询，要产生额外的书签查找[bookmark lookup]，尤其在查询大批量的数据的时候，这个bookmark lookup占到总成本的70%甚至更高是完全可能的）[/quote] 这个严重不同意，没有聚集索引的表普通索引的利用率会非常低 [/quote] 这个有道理，主键的设置和创建索引都有都有它们设计的必要之处，聚集索引和非聚集索引也各有优缺。可以根据业务需求来定义 lz一直抓住MS这个主键默认为聚集索引是否合理的问题。。貌似想的太复杂了。