高分请教：唯一索引的创建选聚集还是非聚集，详见帖子

scsnsjsl_cs_dn 2014-10-22 04:09:41

create table test(

user_id int,

data_id int,

value   int

)



CREATE NONCLUSTERED INDEX [IX_data_id] ON [dbo].[test] 

(

	[data_id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IX_user_id] ON [dbo].[test] 

(

	[user_id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IX_user_data_id] ON [dbo].[test] 

(

	[user_id] ASC,

	[data_id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

一、表结构如上。现有3个约束；没有主键；然后就是value上有默认约束
二、常用操作:
1、查询user_id为指定值的所有记录
2、修改user_id和data_id为指定值的一条记录的value字段(增加、减少一定数值)
3、如果操作2中把value变为0，删除改条记录(也可以不删除，目前的逻辑是删除的)
4、插入新的一列
三、其他：
userid范围以百万计，data_id很小，应该以百计，应该不会超过500

四、要求：现在要求在user_id和data_id具有联合的唯一性
五、问题：
1、创建联合主键、唯一索引选取哪个更好点
2、主键/唯一所以，使用聚集还是非聚集好点

3、唯一索引与唯一约束区别，

请不吝赐教，请给出比较详细的理由，
定当高分感谢

...全文

414 28 打赏收藏转发到动态举报

写回复

用AI写文章

28 条回复

切换为时间正序

请发表友善的回复…

发表回复

scsnsjsl_cs_dn 2014-10-23

打赏
举报

引用 17 楼 Tiger_Zhao 的回复:

数据是按页读写的。假如都是隔了很长时间分开操作的（数据已经不在缓存中），插入最后一条记录时：按1、2、3顺序插入，读1页写1页。按1、3、2顺序插入，如果有需要也只会在内存中调整顺序（这个消耗可以忽略不计），照样是读1页写1页。

我的意思是两种可能： A、昨天插入1，3两条记录，今天再插入2 B、前天按顺序插入1，2，3共3条记录，然后昨天记录2的value变为0的同时删掉了记录2，今天再插入新的记录2，这样建聚索引，会不会导致磁盘数据的迁移，因为聚集索引的数据都上按顺序存放

scsnsjsl_cs_dn 2014-10-23

打赏
举报

引用 16 楼 wmxcn2000 的回复:

[quote=引用 12 楼 scsnsjsl_cs_dn 的回复:] [quote=引用 10 楼 wmxcn2000 的回复:] 以下是个人建议，欢迎讨论。 1、创建联合主键、唯一索引选取哪个更好点 user_id和data_id具有联合的唯一性 ,这说明，这两列都是业务数据，不建议在业务数据上建立 PK，　建议唯一索引； 2、主键/唯一所以，使用聚集还是非聚集好点数据改动较大的话不建议聚集，如果有较小的变动，并且查询较多，聚集索引是个不错的选择，这个你自己根据实际情况去权衡； 3、唯一索引与唯一约束区别，唯一索引，很显然，就是一个索引，所占用你的空间，来存储这些索引数据，当然某些语句会使用该索引，提高性能；唯一约束，只是在数据插入或更新时，检查有无重复，没有额外的存储，不会对帮助查询语句提交性能。

1、依据你的建议，同时结合我的实际记录1，2，30变为0后，我要删除改条记录，然后过段时间可能又要添加记录1，2，50，那么我的情况应该选非聚集的唯一约束？ 2、当然我也可以在value为0时，不删除改记录，以后有新增，直接在0的基础上递增，但是如果之前根本就没有1,3,50这条记录的时候还是得插入，这样的话选聚集的唯一索引好些？ 3、目前已经有表已经有了user_id,data_id的非聚集索引IX_user_data_id，这个索引是否显得多余？[/quote] 1. 把 1,2,30 都变成 0 后，不管你是建立 PK 还唯一索引，都是不允许的，不过可以做成一个条件索引，不处理为0 的情况。 2.之前没有 1,2,50 这3条记录，如果要插入，这个要看你数据的修改的多少了，如果改的不多，每天也就那么 3、5百条、千数来条，聚集也没什么影响。 3.如查你在 userid 和 dataid 上建立了 PK ，你又要建索引，肯定是多余的，如果没有建立PK，只想建立唯一索引，最好将原来的索引删除。毕竟重复的东西，也没什么用，还占空间，最有可能影响你的执行计划。[/quote] 我的1，2，30是同一条记录的三个字段的不同值，同理，1，250也是这个意思

Neo_whl 2014-10-23

打赏
举报

其实楼上各位大师都已经总结的很清楚了： 1、创建联合主键、唯一索引选取哪个更好点如果创建了联合主键，就不必再选唯一索引了，因为默认情况下主键就是唯一聚集的 2、主键/唯一所以，使用聚集还是非聚集好点选聚集和非聚集的话要看具体业务，如果业务只是查询，建议建聚集索引，聚集索引的效率同等条件下相对高于非聚集索引，如果对添加聚集索引字段会发生频繁且数据量较大的更新操作，建议使用非聚集索引，因为聚集索引确定表中记录的物理顺序，更新操作也会更新索引，这样会降低更新速度，而通过你说的每次只是对特定的user_id或data_id做更新操作，数量级别应该不会很大，其实更新效率估计不会有明显影响吧 3、唯一索引与唯一约束区别，索引的作用就是为了提高查询效率的而约束是为了保证数据的完整性，具体就是唯一性建了唯一索引后其实也能保证值不重复，在某种层面上说没有什么区别，只是开发者设计之初的一个设计需要吧

Tiger_Zhao 2014-10-23

打赏
举报

不要变需求！
只有满足需求的合理设计，没有万能的设计！

以学习为目的 2014-10-23

打赏
举报

返璞归真，严格按照微软官方对于索引的规定和建议理解来操作

scsnsjsl_cs_dn 2014-10-23

打赏
举报

引用 26 楼 Tiger_Zhao 的回复:

磁盘数据有序≠全体记录用有序列表方式存放是分成许多个局部有序的页，无序地存放在磁盘上，这些页通过树状结构索引成整体有序的逻辑列表。无论怎样无序的修改，都只有少量的页、树节点需要变动。

多谢！

Tiger_Zhao 2014-10-23

打赏
举报

数据库远比你想象的要高效，只要按照通用原则选取恰当的主键、索引，其他的不用操心。

Tiger_Zhao 2014-10-23

打赏
举报

磁盘数据有序≠全体记录用有序列表方式存放
是分成许多个局部有序的页，无序地存放在磁盘上，这些页通过树状结构索引成整体有序的逻辑列表。
无论怎样无序的修改，都只有少量的页、树节点需要变动。

还在加载中灬 2014-10-22

打赏
举报

data_id因为他不是复合中的第一列，他不会走索引，如果你考虑效率的话，说不定还得为data_id建个单列索引

scsnsjsl_cs_dn 2014-10-22

打赏
举报

引用 18 楼 Tiger_Zhao 的回复:

#15 主键的第一字段是 user_id，所以单独查 user_id 这个索引也有效。

那如果我还要单独查data_id主键索引还有效吗？或者要单独为data_id建索引？

Tiger_Zhao 2014-10-22

打赏
举报

#15
主键的第一字段是 user_id，所以单独查 user_id 这个索引也有效。

Tiger_Zhao 2014-10-22

打赏
举报

数据是按页读写的。
假如都是隔了很长时间分开操作的（数据已经不在缓存中），插入最后一条记录时：
按1、2、3顺序插入，读1页写1页。
按1、3、2顺序插入，如果有需要也只会在内存中调整顺序（这个消耗可以忽略不计），照样是读1页写1页。

卖水果的net 版主 2014-10-22

打赏
举报

引用 12 楼 scsnsjsl_cs_dn 的回复:

[quote=引用 10 楼 wmxcn2000 的回复:] 以下是个人建议，欢迎讨论。 1、创建联合主键、唯一索引选取哪个更好点 user_id和data_id具有联合的唯一性 ,这说明，这两列都是业务数据，不建议在业务数据上建立 PK，　建议唯一索引； 2、主键/唯一所以，使用聚集还是非聚集好点数据改动较大的话不建议聚集，如果有较小的变动，并且查询较多，聚集索引是个不错的选择，这个你自己根据实际情况去权衡； 3、唯一索引与唯一约束区别，唯一索引，很显然，就是一个索引，所占用你的空间，来存储这些索引数据，当然某些语句会使用该索引，提高性能；唯一约束，只是在数据插入或更新时，检查有无重复，没有额外的存储，不会对帮助查询语句提交性能。

scsnsjsl_cs_dn 2014-10-22

打赏
举报

引用 13 楼 Tiger_Zhao 的回复:

#11: 1、只需要一个主键，其他索引都不需要 2、聚集只保证记录存放的次序，没有“必须紧密无空隙存放”的功能，增删记录都不会造成数据的大量迁移。

我要经常根据user_id来查询的话，也不要在user_id上建索引？

scsnsjsl_cs_dn 2014-10-22

打赏
举报

引用 13 楼 Tiger_Zhao 的回复:

#11: 1、只需要一个主键，其他索引都不需要 2、聚集只保证记录存放的次序，没有“必须紧密无空隙存放”的功能，增删记录都不会造成数据的大量迁移。

如果userid 1,2,3在聚集索引中按1、2、3的实际位置存放，如果插入的记录的顺序为1、3、2，当插入2的时候，需不需要移动3那条记录？

Tiger_Zhao 2014-10-22

打赏
举报

#11:
1、只需要一个主键，其他索引都不需要
2、聚集只保证记录存放的次序，没有“必须紧密无空隙存放”的功能，增删记录都不会造成数据的大量迁移。

scsnsjsl_cs_dn 2014-10-22

打赏
举报

引用 10 楼 wmxcn2000 的回复:

以下是个人建议，欢迎讨论。 1、创建联合主键、唯一索引选取哪个更好点 user_id和data_id具有联合的唯一性 ,这说明，这两列都是业务数据，不建议在业务数据上建立 PK，　建议唯一索引； 2、主键/唯一所以，使用聚集还是非聚集好点数据改动较大的话不建议聚集，如果有较小的变动，并且查询较多，聚集索引是个不错的选择，这个你自己根据实际情况去权衡； 3、唯一索引与唯一约束区别，唯一索引，很显然，就是一个索引，所占用你的空间，来存储这些索引数据，当然某些语句会使用该索引，提高性能；唯一约束，只是在数据插入或更新时，检查有无重复，没有额外的存储，不会对帮助查询语句提交性能。

scsnsjsl_cs_dn 2014-10-22

打赏
举报

引用 4 楼 Tiger_Zhao 的回复:

1、只要一个聚集的联合主键 (user_id,data_id) 2、user_id,data_id 不会变，聚集！ 3、唯一约束会自动创建唯一索引，所以显示创建唯一索引就够了，没想到创建唯一约束的必要。

1、现有表已经有了user_id,data_id的非聚集索引IX_user_data_id，如果创建一个聚集的联合主键 (user_id,data_id) ，会不会显得IX_user_data_id多余？ 2、根据建议1，是创建联合主键，如果我value为0的时候，要删除该条记录如有记录 1，2，30，当 30边为0后，删除该条记录，然后过段时间，有要插入一条1，2，50的记录，会不会造成数据的大量迁移？这么来说是否创建非聚集的的唯一约束好点？

卖水果的net 版主 2014-10-22

打赏
举报

以下是个人建议，欢迎讨论。 1、创建联合主键、唯一索引选取哪个更好点 user_id和data_id具有联合的唯一性 ,这说明，这两列都是业务数据，不建议在业务数据上建立 PK，　建议唯一索引； 2、主键/唯一所以，使用聚集还是非聚集好点数据改动较大的话不建议聚集，如果有较小的变动，并且查询较多，聚集索引是个不错的选择，这个你自己根据实际情况去权衡； 3、唯一索引与唯一约束区别，唯一索引，很显然，就是一个索引，所占用你的空间，来存储这些索引数据，当然某些语句会使用该索引，提高性能；唯一约束，只是在数据插入或更新时，检查有无重复，没有额外的存储，不会对帮助查询语句提交性能。

scsnsjsl_cs_dn 2014-10-22