数据删除设计,server质量的因素

在设计一个新系统的Table
Schema的时候,不仅需要满足业务逻辑的复杂需求,而且需要考虑如何设计schema才能更快的更新和查询数据,减少维护成本。

在设计一个新系统的Table
Schema的时候,不仅需要满足业务逻辑的复杂需求,而且需要考虑如何设计schema才能更快的更新和查询数据,减少维护成本。

目前本人在看《SQL Server性能调优实战》 ,以下内容是本人看书笔记

模拟一个场景,有如下Table Schema:

模拟一个场景,有如下Table Schema:

数据库性能取决于各方面综合因素:
  硬件,操作系统,软件
硬件:内存,CPU,磁盘
  当服务器的物理内存不足时,会产生大量的磁盘I/O,给磁盘带来压力;
  当内存不足时,一些占用CPU资源较多的对象可能就无法被正常缓存在内存中,需要使用大量的CUP资源来处理这些对象的计算,从而给CPU带来更大的压力
  内存:
    1.执行计划缓存
      数据库引擎接收到需要执行的语句时,首先会经过一系列复杂的计算和分析,得到相应的执行计划,然后再根据执行计划进行各种操作,
      由于执行计划的计算和分析需要的CPU资源比较多,所以很容易引起CPU资源的紧张,为了解决这个问题,数据库就会把执行计划缓存起来
    2.数据缓存
      若遇到数据访问操作符,则会首先检查对应的数据是否在数据缓存中,如果存在就从缓存中取数据,否则会从磁盘中读取数据,然后再把数据缓存到缓存中
      如果数据库内存不足,数据库引擎会算法把不常用的缓存清理,把需要的数据从磁盘中读取并缓存到数据缓存中,这会引起大量的磁盘I/O,并且导致执行语句的执行效率低下
      若大批的语句出现这种情况,就会导致服务器CPU占用率飙高。如果服务器CPU长期处于繁忙状态,并且数据库的磁盘I/O偏高,估计是数据库的内存达到了瓶颈
  CPU:
    数据库在进行任务调度,执行计划分析,排序等计算时都需要使用大量的CPU资源
      CPU资源在30%上下时:当前服务器较空闲
      CPU资源在60%上下时:当前服务器较繁忙
      CPU资源达到80%时:服务器非常繁忙,就要查找原因了(通常情况下,一些执行效率较高,并且性能不理想的语句会造成这样的问题;少数情况下是由于数据库服务器达到了瓶颈)
  针对高并发的数据库系统,建议使用如下配置方案:
    将主数据库的数据文件拆分成多个文件
    将数据文件和日志文件存放在不同的物理磁盘,从而提高I/O的并发。
    系统数据库文件,特别是Tempdb的数据文件要放在独立的物理磁盘,并将数据文件拆分成多个,建议与逻辑CPU的个数相同,以提高并发

Product(ID,Name,Description)
Product(ID,Name,Description)

设计表建议:
  尽可能的添加数据完整约束,例如:非空约束,默认值约束,check约束,唯一约束,外键约束等,这些约束的添加降有助于数据库关系引擎分析执行计划
  使用尽可能小的字段类型,特别是大表,尽量小的空间将可以带来更佳的性能
  表结构的设计应考虑业务需求带来的操作及频率,尽可能的使业务逻辑实现简洁,使用简单的SQL语句,可避免过多的表关联
编写SQL语句建议:
  编写语句前,先明确已经完全理解了业务需求,并知道表的用途及用法
  确定业务需要用到的过滤字段能否使用索引,是否有必要在字段上添加索引
  不要多有索引的字段进行任何的计算,包括函数,因为这会导致无法使用索引进行数据检索,从而导致扫描操作
  小表操作优先,以小表驱动大表,使其尽量使用NESTED
LOOP-嵌套循环(NESTED
LOOP是表关联操作的一种物理操作方式,它使用foreach的方式以较小数据量的数据集为驱动,内嵌foreach循环较大的表进行对比,其效率比其他几个关联操作高,)
  只查询需要的字段,避免*
  尽量使用简单的SQL语句来实现业务功能,如果功能过于复杂,可以考虑将其拆分成若干个简单的SQL语句
简单的SQL:
  关联的表最多不超过4个
  没有复杂的过滤条件,只有2到3个过滤条件,可以使用索引查找操作

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

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

 

这样的Table Schema 设计看似完美:ID字段具有做clustered
index的天赋:窄类型,自增,不会改变;Name上的唯一约束,能够满足业务逻辑上的需求。但是,如果业务人员操作失误,将Product
的 Name 写错,需要将其删除,最简单的方式是使用delete
命令,直接将数据行删除,但是这种方式带来的隐患特别大:如果业务人员一不小心将重要的数据删除,那么,恢复数据的成本可能非常高。如果数据库很大,仅仅为恢复一条数据,可能需要N个小时执行还原操作。如何设计Table
Schema,才能避免在维护系统时出现被动的情况?

这样的Table Schema 设计看似完美:ID字段具有做clustered
index的天赋:窄类型,自增,不会改变;Name上的唯一约束,能够满足业务逻辑上的需求。但是,如果业务人员操作失误,将Product
的 Name 写错,需要将其删除,最简单的方式是使用delete
命令,直接将数据行删除,但是这种方式带来的隐患特别大:如果业务人员一不小心将重要的数据删除,那么,恢复数据的成本可能非常高。如果数据库很大,仅仅为恢复一条数据,可能需要N个小时执行还原操作。如何设计Table
Schema,才能避免在维护系统时出现被动的情况?

查询设计原则:
  1.不要在过滤字段上使用任何的计算,包括:函数,逻辑计算,普通的计算等,因为这些计算公式的加入,将造成查询优化器无法使用相应字段的索引
  2.尽量使用有索引的字段进行排序,特别是排序的数据量比较大时,这可以很大程度上降低排序操作带来的成本开销
  3.填写查询表时,尽量使用Join关键字连接表,这样的语句清晰,易于阅读,不易缺失关联条件

delete Product
where Name='xxx'
delete Product
where Name='xxx'

排序优化:
  在加入排序计算以后,尽量控制排序的数据量,尽量使排序操作能在额定的内存空间中完成,避免使用Tempdb。数据量较大时,可以考虑在排序字段上添加索引来避免执行排序操作。
  查询大量的数据会造成CUP资源消耗,并且,在查询数据超过可以分配的内存大小时,会把查询的中间数据存放在Tempdb数据库中,这将增加I/O操作,导致语句的性能大大下降
  在进行排序操作时,如果工作区内存不足,就需要使用Tempdb数据库来完成数据的排序,此时排序操作产品的部分中间数据将被写入Tempdb中,
  由于有了磁盘I/O操作的开销,排序操作将受到影响,这时不在由单纯的内存操作那样快速了,而且当Tempdb比较繁忙时,若并发量达到一定量级,也会对排序操作造成影响

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

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

 

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

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

分组优化:
  Group by
和distinct需要进行哈希或排序计算。与排序相同,哈希计算需要使用大量的内存空间,当工作区内存不足时,会将一部分中间数据存放到Tempdb中。
  因此,使用哈希计算时同样需要注意数据量的控制。当然,如果有必要,可以在Group
by的字段上建立索引,以避免哈希计算

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

 

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

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

更新优化:
  由于update语句对数据加的是更新锁或排他锁,更容易并发阻塞,因此,我们应尽量保证更新语句高效,以减少阻塞的影响
  在默认事务级别中,update语句进行查询时会添加更新锁,若找到了需要更新的数据,就会将更新锁转换为排他锁,由于更新锁和排他锁的兼容性弱,
  在需要较长执行时间的更新语句或事务中,容易造成阻塞。为了避免造成大量阻塞,应尽量保证更新语句的效率,适当建立索引。
  长时间的更新维护通常会造成表被锁,从而影响业务功能,无法正常运作,不得不停机操作,解决办法:预先计算好结果,然后分批更新,这样就可避免在更新物理表时再进行计算,从而影响更新时间

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

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

过滤条件:
  是否有合适索引可供使用
  字段上是否有函数计算
  返回的结果集是否过大
  是否紧查询需要的字段

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

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

在语句存在性能瓶颈下,如果有扫描操作或者是标签查找操作,都会被认为是有问题的,问题产生的原因是索引缺少,或者索引没有真正的被覆盖到语句中。

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

查询设计原则
  使查询结果尽可能小.例如:Top ,分页
  返回大量的查询结果可能意味着需求存在问题
  尽量避免表扫描和索引扫描
  充分使用索引
  扫描并不总是有害的

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

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

尽量使用有索引的字段进行排序,特别是排序的数据量比较大时,这可以很大程度上降低排序操作带来的成本消耗

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

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

 

--view definition
select ID,Name,Content
from Product
where IsDeleted=0
--view definition
select ID,Name,Content
from Product
where IsDeleted=0

索引:

3,手动处理外键关系

3,手动处理外键关系

索引的选择:
  1.超高频率的查询,以及低频率的数据更新,插入,删除(索引覆盖,如果覆盖索引含比较多的字段,可以使用包含索引来解决:include关键字)

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

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

    对于这种情况除了索引还有其他解决办法:读写分离或者开启数据库的行版本控制功能
  2.超高频率的查询,以及超高频率的数据更新

4,不能被用作历史表

4,不能被用作历史表

     若是高频率的查询需求,通常会因为更新的频率过高,而导致存在一定的阻塞,或者产生死锁,
其他解决办法:

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

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

      1.创建索引时避免让高频率的更新字段成为索引的一部分;

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

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

      2.读写分离;

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

      3.根据需求将表纵向拆分成多个窄表

如果Product表的数据量十分大,额外的查询操作,会增加插入操作的延迟,同时,"无效"的历史数据降充斥在数据表中,也会降低数据查询的速度。

如果Product表的数据量十分大,额外的查询操作,会增加插入操作的延迟,同时,"无效"的历史数据降充斥在数据表中,也会降低数据查询的速度。

索引覆盖:覆盖索引通常都是复合索引,即:索引字段有多个
  1.查询字段
  2.过滤字段
  3.关联字段:链接两个表的字段,on后的字段
  索引覆盖可以考虑onclude关键字

单纯从业务需求上考虑,软删是首选的design,定期清理软删的冗余数据,也可以提高数据查询的速度,不过,在清理数据时,可能会产生大量的索引碎片,造成并发性降低等问题。

单纯从业务需求上考虑,软删是首选的design,定期清理软删的冗余数据,也可以提高数据查询的速度,不过,在清理数据时,可能会产生大量的索引碎片,造成并发性降低等问题。

  例如:复合索引 IX_tbTable_Index
,包含字段column1,column2,column3三个字段

5,将删除的数据存储到History表

5,将删除的数据存储到History表

     当查询使用column1时,被识别

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

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

     当查询使用column1,column2时,被识别

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

       当查询只使用column2时,将无法使用索引

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

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

书签查找:

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

  当一条SQL通过非聚集索引找到请求的数据,如果查询还需要返回除索引键以外的字段,那么这个查询很有可能使用书签查找来查找其他字段,书签查找分为:建查找和RID查找
  对于书签查找的可以使用索引覆盖

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

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

  在使用非覆盖索引时,对于不在索引中输出的字段,将引起bookmark
loop的操作,在一些高性能要求的场景中,bookmark
loop往往是造成性能问题的一个主要因素,在这种情况下,

 

 

  覆盖索引可以考虑使用include关键字将输出字段包含在叶子节点中,从而避免bookmark
loop

seek操作针对的是筛选率高的小数据量返回的情况,如果所筛选的数据量很大,查询优化器发现bookmark
loop操作会给当前查询带来很大的成本负担,会进行复杂的成本计算和评估,然后可能使用Scan操作以避免过多的成本消耗,解决办法如下:

  1.修改当前索引,将不包含的字段列入索引中,即将该字段直接加到索引中,使索引成为一个更大的复合索引

  2.使用include子句将需要查下出的字段包含在索引的叶子结点中,以避免bookmark
loop的额外操作,这种情况要根据具体需求,权衡利弊之后再做决定

       索引查询的数据量要尽可能的小,否则会对进行全表扫描操作

索引覆盖语法
  CREATE nonclustered index IX_tbTest_UserID
  ON tbTest(UserID)
  INCLUDE (Name,AddTime);

 

创建索引:[unique] [clustered]
[nonclustered]表示要创建索引的类型,以此为唯一索引,聚集索引,和非聚集索引     
 

     
 若添加索引的表非常频繁,可以使用online选项,令索引在线创建,以避免长时间的锁定相应的表

    create nonclustered index IX_Product_Name_Class
    on Production.Product(Name,Class)
    with(online=on,sort_in_tempdb=on)

 

查询索引:EXEC Sp_helpindex table_name

删除索引:drop Index tbTest.IX_tbTest_UserID_Name

 

set statistics io on
set statistics profile on
set statistics time on
select * from Table
set statistics time off
set statistics profile off
set statistics io off

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website