关于复合主键和自增长主键设计 以及 增删改 性能 讨论

[jldzy]

在数据库设计的时候有2 种方式：
一
tba(FID int, RID int, 。。。。。，primary key(FID,RID)

二
tba(Id int identity(1,1) primary, FID int, RID int,。。。。。，unqiue key(FID,RID)

业务场景：
对表的 新增加 修改 删除 比例大致是 45%，45%，10% ，
对于新增加的 可能是无序的，例如 当前的FID是100， 可能新增加的是 FID=10,FID=1 等
对于修改的 不会修改FID RID 2字段，只是修改其他字段。

疑问：
如果按照方式一设计，主键即是聚集索引，聚集索引在物理上是有序存放的，可以提高读取效率， 对于无序的新增加，和删除 岂不是要移动非常多的位置，影响插入性能 ？
对于无序和有序插入，索引结构是怎么变化的？

[rfq]

您说的这个问题 确实需要考量 我个人认为 如果您的数据表非常大， 我建议 用第二种 的表结构。 主要是担心数据量大， 数据修改太频繁 造成大量分页 ， 同时 造成碎片增多 整体结构性能偏下。 如果数据量不大， 采用第一种结构。 对于数据查询，效率 应该很好。 实际情况，如果数据量比较大，最好做个测试。否则 很难说那种结构更好。

[發糞塗牆]

第二种方式里面的ID，你以后会用到吗？

[jldzy]

不会和其他表 关联，或者 做外键，， 现在主要是考虑 无序插入 删除 对聚集索引 影响多大 ？

[發糞塗牆]

有多大，这个涉及很多因素，但是可以肯定的是有影响，对表的 新增加 修改 删除 比例大致是 45%，45%，10% ，其实这个参考价值不大，应该用增删改和查询的比例来对比。

[jldzy]

查询 一般是 按照 FID 来查询一次， 然后 经过 业务处理，得到一个增加删除修改结果集。 这个比例不好统计， 对于增删改 聚集索引是怎么维护的 ？

[發糞塗牆]

增删改的确会引起聚集索引键的改动，进而引起所有非聚集索引的改动，如果频繁修改FID，那么用第二钟方案会相对影响面较小

[Tiger_Zhao]

聚集索引的有序存放并不是紧密相邻的，存放数据的分页是可以有空白位置的。
删除：只是标记为不可查询，无需移动数据。
插入：如果页中没满，最多在该页中调整位置；如果满了，就分页，又可以按照没满的方式处理。
虽然性能没有方案二高，但是移动的量没有你想象的那么高。
只有在备份后压缩数据库，才有可能大量移动数据。

如果删除、更新都是用 FID,RID 做条件，选第一方案（和1楼一个意思）。

[Tiger_Zhao]

引用 5 楼 DBA_Huangzj 的回复:

增删改的确会引起聚集索引键的改动，进而引起所有非聚集索引的改动，如果频繁修改FID，那么用第二钟方案会相对影响面较小

楼主已经说了 不会修改FID RID 2字段

[發糞塗牆]

引用 7 楼 Tiger_Zhao 的回复:

[quote=引用 5 楼 DBA_Huangzj 的回复:]增删改的确会引起聚集索引键的改动，进而引起所有非聚集索引的改动，如果频繁修改FID，那么用第二钟方案会相对影响面较小

楼主已经说了 不会修改FID RID 2字段[/quote]他说的“修改”我认为仅update，不包含insert和delete

[jldzy]

重复下我的意思， 有三种情况 1 新增加 这个新增加的FID 有可能是无序的，有可能是有序的数据 2 修改update 这个不会修改FID RID 2 字段，只是修改其他字段。 3 删除 会按照FID+RID 删除数据，删除的FID 可能是无序的，例如当前是 FID =1000, 可能删除 FID=10 的记录。

[jldzy]

Tiger_Zhao， 对于无序的插入 删除， 索引和数据 都不会移动 ？

[sdhp]

方法一对于新增和删除是会影响到索引B树的变化，但是不会“移动非常多的位置”，只会产生一些叶节点的分裂和合并，所有的叶节点你可以想象成是用指针的，而不是像数组那样需要移动非常多的位置

[卖水果的net]

建议使用第二种，把业务数据做为PK，不是很爽。

[jldzy]

rfq,wmxcn2000 数据量大概会超过 10 亿， 目前是按照 FID 进行了分区

[Tiger_Zhao]

因为你是一个 FID 作为一批操作的，肯定是方案一占优。
至于前后两个 FID 不连续，完全可以看作两个批次的操作，这在数据库调用中很正常。

[jldzy]

因为你是一个 FID 作为一批操作的，肯定是方案一占优。 我也偏向方案一，因为业务是根据FID 取出 假如 5000 个 RID， 经过处理后，然后 会产生8000 RID，然后保存到数据库， 在这 8000 RID 和 原来的 5000 RID 相比， 有新增加的， 修改的(不会修改FID RID)， 删除的。 现在疑问的是，对于在原来新增加的，对整个表的聚集索引的影响有多大，这个怎么测试，怎么查看索引等，简单办法就是看索引的使用，和 更新频率

[Tiger_Zhao]

前面那么多都白说了！
必要的变更谈什么影响？和谁比较？谁？？

[jldzy]

和第二种方案的性能比较，我现在在测试，看他的 页的拆分情况 [sys].[dm_db_index_physical_stats](，

[Tiger_Zhao]

方案二还影响读的性能。
多次更新后同个 FID 下的记录会分布在非连续的页上。

[jldzy]

恩，那个知道， 聚集索引在物理在是连续的，读的性能是非常高的， 因为现在的业务主要是以 IUD 为主，所以要进行测试