你的位置:首页 > 数据库

[数据库]Design7:数据删除设计


在设计一个新系统的Table Schema的时候,不仅需要满足业务逻辑的复杂需求,而且需要考虑如何设计schema能够更快的增加数据和删除数据。

模拟一个场景:

Product(ID,Name,Description)

ID是自增的ID字段(Identity),用以唯一标识一个Product,在业务逻辑上要求以Name字段是唯一,通过Name能够确定一个Product。业务上和设计上有所冲突在所难免,解决冲突的方法其实很简单:将ID做主键,并创建聚集index;在name上创建唯一约束,保证name字段是唯一的。

如果业务人员操作失误,将Product 的 Name 写错,需要将其删除,最简单的方式是使用delete 命令,直接将数据行删除,但是这种方式带来的隐患特别大:如果业务人员一不小心将重要的数据删除,那么,恢复数据的成本可能非常高,如果数据库很大,仅仅为恢复一条数据,可能需要N个小时执行还原操作。如何设计Table Schema,以避免在维护系统上出现被动的情况?

delete Productwhere Name='xxx'

设计目的:在短时间内恢复被误删除的数据,以使系统尽快恢复

数据删除操作有两种方式:软删除和硬删除,也称作Logic Delete 和 Physical Delete。硬删除是指使用delete命令,从table中直接删除数据行;软删除是在Table Schema中增加一个bit类型的column:IsDeleted,默认值是0,设置IsDeleted=1,表示该数据行在逻辑上是已删除的。

Product(ID,Name,Content,IsDeleted,DeletedBy)

软删除实际上是一个Update 操作,将IsDeleted字段更新为1,逻辑上将数据删除,并没有将数据行从物理上删除。使用软删除,能够保留有限的数据删除的历史记录,以便audit,但是,这可能导致外键关系引用被逻辑删除的数据;如果历史记录太多,这又会导致数据表中有效数据行的密度降低,降低查询速度。

1,能够快速恢复被误删除的数据

用户的删除操作是将IsDeleted设置为1,在逻辑上表示删除数据,如果用户由于误操作,将重要数据行删除,那么只需要将IsDeleted重置为0,就能恢复数据。

update Productset IsDeleted=1where Name='xxx' -- or use ID=yyyy as filter

2,每次引用该表时,必须设置filter

任何引用该表的查询语句中,必须设置Filter:IsDeleted=0,为来避免遗漏filter,可以创建视图,不直接引用该表,而是直接引用视图。

--view definitionselect ID,Name,Contentfrom Productwhere IsDeleted=0

3,手动处理外键关系

如果在该表上创建外键关系,那么可能存在外键关系引用被逻辑删除的数据,造成数据的不一致性,这可能是很难发现的bug:如果需要保持关键关系的一致性,需要做特殊的处理。在将数据行逻辑删除之时,必须在一个事务中,将外键关系全部删除。

4,不能被用作历史表

数据表是用来存储数据的,不是用来用户操作的历史记录。如果需要存储用户操作的历史记录,必须使用另外一个HistoryOperation来存储。

上述Product表中Name字段上存在一个唯一约束,如果用户将相同Name的Product重新插入到table中,Insert 操作因为违反唯一约束而失败,针对这种情况,软删除操作必须额外进行一次判断:

if exists(  select null   from Product   where name ='xxx' and IsDeleted=1)update   set IsDeleted=0,    ...from Product where name ='xxx' and IsDeleted=1else insert Product(...) values(....)

如果Product表的数据量十分大,额外的查询操作,会增加插入操作的延迟,同时,也会降低数据查询的速度。
5,将删除的数据存储到History表

使用软删除设计,增加IsDelete=1 字段,实际上降低了有效数据的密度,在使用软删除时,必须慎重考虑这一点。改进的删除数据的设计是:在一个事务中,将删除的数据存储到另外一个History表中。

delete from Product output deleted.ID,  deleted.Name,  deleted.Content,  'Delete' as CommandType   '' as UpdatedBy,  getdate() as UpdatedTimeinto History_tablewhere Name ='xxx' -- or use Id=yyy as filter

恢复误删的数据,只需要到History表找到相应的数据,将其重新插入到Prodcut 表中,并且,History 表中不仅可以存储用户删除操作的历史记录,而且可以存储用户更新的历史记录,对于系统的维护,解决用户纠纷和故障排除,十分有帮助。

Product(ID,Name,Content)OperationHistory(ID,ProductID,ProductName,ProductContent,CommandType,UpdatedBy,UpdatedTime)

为设计Product 表的删除操作,需要两个Table,对于OperationHistory表,可以做的更通用一些。抛砖引玉,提供一个思路,我就不做扩展了。