图片 16

索引阐述系列四,完整模式下的备份与还原

数据库必须具备的四个特性

一.概述

  前面介绍了简单恢复模式和大容量恢复模式,这篇继续写完整恢复模式下的备份与还原。在完整恢复模式里最大的优点是只要能成功备份尾日志,就可以还原到日志备份内包含的任何时点(“时点恢复”)。当然对比前二种模式它是牺牲了磁盘I/O性能。

恢复模式

备份策略

数据安全性

I/O性能

简单恢复

完整备份+差异备份

安全最差。最后一次备份之后,所有数据操作丢失。

最优

大容量恢复

完整备份+差异备份+日志备份

折中。批量操作有丢失风险。尾日志备份失败。最后一次备份之后,所有数据操作丢失

折中

完整恢复

完整备份+差异备份+日志备份

相比上面二种最安全。尾日志备份失败。最后一次备份之后,所有数据操作丢失

最差

  在完整恢复模式下,最常见的备份策略,如下图所示:图片 1

一.概述  

  说到B-tree组织,就是指索引,它可以提供了对数据的快速访问。索引使数据以一种特定的方式组织起来,使查询操作具有最佳性能。当数据表量变得越来越大,索引就变得十分明显,可以利用索引查找快速满足条件的数据行。某些情况还可以利用索引帮助对数据进行排序,组合,分组,筛选。

   一个B-tree,根是唯一的遍历的起点。中间页
层次数是根据表的行数以及索引行的大小而变化。索引中的底层节点称为叶节点。叶节点它容纳了一行或多行具有指定键值的记录,对于聚集或非聚集,叶节点都是按照键值的顺序组成,对于复合索引就是若干键值的组合。

  1.聚集索引

  在聚集索引的叶节点里不仅包含了索引键,还包含了数据页。也就是说数据本身也是聚集索引的一部分。聚集索引基于键值联系使表中的数据有序。决定哪个键值作为聚集键是重要因素,当遍历到叶级别时,可以获取数据本身,而不是简单地得到一个指向数据的指针(非聚集索引数据未覆盖)。聚集索引在 sys.partitions区中有一行,其中,索引使用每个分区的 index_id =
1
,默认情况下,聚集索引是单个分区。如果聚集索引有四个分区,就有四个 B-tree 结构,每个分区中有一个
B-tree结构,关于分区在sql server
分区(上)中有讲到。由于数据页链只能按一种方式排序,因此表只有一个聚集索引,一般情况查询优化器非常倾向于采用聚集索引,因为可以直接在叶级别找到数据。 
查询优化器也只需要在某一段范围的数据页,进行扫描。聚集索引结构按物理顺序存储不是磁盘上的顺序,聚集索引的排序顺序仅是表数据链在逻辑上有序的。

图片 2

  2.非聚集索引

  非聚集索引与聚集索引有一个相似的 B
-tree索引结构。不同的是,非聚集索引不影响数据行的顺序。什么意思呢,就是说非聚集索引,叶级别不包含全部的数据,只包含了键值以及,在每个叶节点中的索引行包含了一个书签(bookmark),书签在聚集索引里就是相应的数据行的聚集索引键,在堆里就是行标识符RID,该书签告诉sql
server可以在哪里找到与索引键相应的数据行。
理解了非聚集索引叶节点不包含全部数据时,就知道非聚集索引的存在并不影响数据分页的组织,因此每张表上最多249个非聚集索引。
非聚集索引在 sys.partitions 区中有一行, 非聚集索引标识 index_id
>1
。默认情况下,一个非聚集索引一个分区。

图片 3

1:原子性:事务包含的所有操作要么全部成功,要么全部失败回滚;成功必须要完全应用到数据库,失败则不能对数据库产生影响;

2:一致性:事务执行前和执行后必须处于一致性状态,

例:用户A和用户B的前加起来一共是5000;
无论AB用户之间是如何相互转换的,事务结束后两个用户的钱加起来还是5000,这就是事务的一致性。

 

3:隔离性:当多个用户并发访问数据库时,数据库为每一个用户开启的事务,不被其他事务的操作所干扰,多个并发事务之间要相互隔离;

 

4:持久性:一个事务一旦被提交了,那么对数据库中的数据的改变就是永久性的,即便在数据库系统遇到故障的情况下也不会丢失事物的操作。

 

二. 备份

  在前章中讲到了大容量恢复模式下的备份。备份策略与大容量模式是一样的,同样是完整备份+差异备份+日志备份。这里要突出点是:当误操作发生后,如何还原到误操作之前的一分钟,找出误操作之前的数据。
在”sql server
日志文件结构及误操作数据找回”中有介绍误操作数据找回,但是基于第三方工具ApexSQL
Log。虽然该工具方便,但要收费哟。

  我这里有一个BackupTest库,库里有个Employees表

use master
--设置完全模式
ALTER DATABASE BackupTest SET  RECOVERY FULL  
--创建备份设备(有就不要执行)
use master
exec sp_addumpdevice 'disk', 'BackupTestDevice','F:\SqlService\backup\BackupTestBackup.bak'
go
--做一次完整备份到备份设备中(备份基准)
backup database  BackupTest to BackupTestDevice

--新增数据
insert BackupTest.dbo.Employees values('湖南长沙')
insert BackupTest.dbo.Employees values('湖南湘潭')
--日志备份
backup log BackupTest to BackupTestDevice

 备份集如下所示:

图片 4

-- 误操作发生, 忘记加where条件,操作时间是:2018-8-12 10:55  
delete from BackupTest.dbo.Employees 

二. 缺少索引与索引查找的区别

   在简单介绍了索引原理后,我们来直观感觉下索引在查询时的重要性。下面演示一个product表,表中的数据有12236142条,如果用户根据表中的型号(model)来搜索。下面来看看缺少索引(没有使用到索引),以及索引查找(就是应用到了索引功能)。二者的区别

  2.1 缺少索引的演示

--查询型号model 值STI5203 在全表中有三条
SELECT Model FROM dbo.Product WHERE Model='STI5203'

图下告诉我们缺少索引,如果加了索引将提高性能99.94%, 该查询扫描计数5 (扫描了5个区),逻辑读取次数为69951次(一次一页),耗时954毫秒。
执行计划告诉我们是索引扫描也叫缺少索引,索引名是ixUpByMemberID,注意索引扫描不是索引查找,索引扫描是说把索引组织上的页全部扫描了一遍。

图片 5

图片 6

  再通过下图我们清楚知道,ixUpByMemberID有5个区。5个区加起来的data_pages总页数是69730。上图逻辑读取是69951。相当于把索引中的页全部扫描了一遍。也可说是把12236142条数据全扫描了一次。

图片 7

  在锁的介绍中我们知道,锁越多,发生阻塞和死锁的几率就越大。
  通过下图,对于page资源来说,就有IS锁(意向共享锁)上1000个。IS锁与X排它锁又不兼容,此时多用户在修改,删除表中数据时,将会发生阻塞或死锁的影响。

图片 8

图片 9

图片 10

    总结:如果在生产环境,面对大数据表,条件查询很频繁,又缺失索引,系统整体性能将会被拖垮。

   2.2 查询索引查找的演示

  用户根据model查询,缺少了一个索引,在给model建立索引后,再来看

--查询型号model 值STI5203 在全表中有三条
SELECT Model FROM dbo.Product WHERE Model='STI5203'

  下图的执行计划告诉我们是索引查找,也就是索引使用上了,该索引名叫ix_mdoel.
扫描计数1 个区,逻辑读取次数为4次,耗时0毫秒.

图片 11

图片 12

  再来看下索引查找的锁状态,下图告诉我们,只有锁往了一个page资源。

图片 13

  总结:在大表上,合理使用了索引查找后,不但查询响应时间变快了,而且没有了大量的锁,相应的在其它page页上的修改,删除应不会受到影响。

隔离性:当多个线程都开启事务来操作数据库中的数据时,数据库系统要进行隔离操作,以保证各个线程获取数据的准确性。 不考虑事务的隔离性,会产生的几种问题:

三.还原(1)

  当误操作发生后,是需要找管理员来进行数据还原。
如果数据库太大,还原是需要很长时间(注意使用副本,不要使用生产库)。
这种情况下就需要等待了。 避免的方法:(1)是做sql审核,不在Managemnet
studio里直接操作,避免此类事情发生.(2)是使用粒度更小的备份方式,但相应的复杂些。

--步骤1 备份尾日志
use master
go
backup log BackupTest to BackupTestDevice with norecovery 

图片 14

go
--步骤2 从备份恢复一个全备份 ,norecovery(正在还原...)不可读写. file指备份集位置号
restore database BackupTest from BackupTestDevice with file=19, norecovery --事务不恢复

--步骤3 
restore log BackupTest from BackupTestDevice  with file=20,  norecovery --事务不恢复

--步骤4 用stopat恢复到10:54
restore log BackupTest from BackupTestDevice  with file=21, stopat='2018/8/12 10:54', recovery --事务恢复

--数据又回来了
select * from  BackupTest.dbo.Employees 

  图片 15

三. B-tree组织存储空间的影响

  我们知道了对于聚集索引,它的叶子层就是数据本身,但当一个表有多个非聚集索引时,就需要对数据库存储空间加倍来支持这些索引的存储,所以从占用存储空间来说,在建非聚集索引时需要好好规划。下面是来自生产环境的一个表,有聚集索引和四个非聚集索引,来看看索引存储空间
在index_id=1的聚集索引中占用的空间total_pages是1448806页,也就是表的数据本身。
而非聚集索引占用空间total_pages是2180034页,
非聚集索引占用空间比表数据本身大了1.5倍。

  图片 16

发表评论

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

标签:
网站地图xml地图