简介
   
数据库快照,正如其名称所示那样,是数据库在某一时间点的视图。快照设计最开始的目的是为了报表服务。比如我需要出2011的资产负债表,这需要数据保持在2011年12月31日零点时的状态,则利用快照可以实现这一点。快照还可以和镜像结合来达到读写分离的目的。下面我们来看什么是快照。
 
什么是快照
    数据库快照是 SQL Server
数据库(源数据库)的只读静态视图。换句话说,快照可以理解为一个只读的数据库。利用快照,可以提供如下好处:
    提供了一个静态的视图来为报表提供服务
   
可以利用数据库快照来恢复数据库,相比备份恢复来说,这个速度会大大提高(在下面我会解释为什么)
    和数据库镜像结合使用,提供读写分离
   
作为测试环境或数据变更前的备份,比如我要大批导入或删除数据前,或是将数据提供给测试人员进行测试前,做一个快照,如果出现问题,则可以利用快照恢复到快照建立时的状态
 
快照的原理
   
与备份数据库复制整个数据库不同,快照并不复制整个数据库的页,而是仅仅复制在快照建立时间点之后改变的页。因此,当利用快照进行数据库恢复时,也仅仅将那些做出改变的页恢复到源数据库,这个速度无疑会大大高于备份和恢复方式。这个原理如图1所示(图摘自SQL
Server 2008揭秘)。
    
    图1.镜像的原理

一、MySQL数据迁移(由远端主机迁移到本地)

SQL Server 的全文搜索(Full-Text
Search)是基于分词的文本检索功能,依赖于全文索引。全文索引不同于传统的平衡树(B-Tree)索引和列存储索引,它是由数据表构成的,称作倒转索引(Invert
Index),存储分词和行的唯一键的映射关系。倒转索引是在创建全文索引或更新全文索引时,由SQL
Server自动创建和维护的。全文索引主要包含三种分析器:分词器(Word
Breaker)、词干分析器(stemmer)和同义词分析器。全文索引中存储的数据是分词及其位置等信息,分词是基于特定语言的语法规则,按照特定的符号寻找词语的边界,把文本分解为“单词”,每一个单词叫做一个分词(term);全文索引有时会提取分词的词干,把词干的多种派生形式存储为单一词干,这个过程叫做提取词干;根据用户提供的自定义同义词列表,把相关的单词转换为同义词,这个过程叫做提取同义词。

   图片 1

1、导出数据库
mysqldump -u root -p db > dump_db_date.sql
root: 账户
db: 需要导出的数据库名

生成全文索引是把用户表中的文本数据进行分词(Word
breaker)和提取词干(Stemmer),并转换同义词(Thesaurus),过滤掉分词中的停用词(Stopword),最后把处理之后的数据存储到全文索引中。把数据存储到全文数据的过程叫做填充(Populate)或爬虫(Crawl)进程,全文索引的更新方式可以手动填充,自动填充,或增量填充。

   
由图1可以看出,快照并不是复制整个整个数据库,而仅仅利用快照存储原始页。因此可以看出,源数据库上建立快照会给IO增加额外负担.当对快照数据库进行查询时,快照时间点之后更改的数据会查询数据文件,。这个概念如图2所示(图摘自SQL
Server 2008揭秘)。
    图片 2

2、将导出的dump_db_date.sql文件scp到本地

一,全文搜索的基本组件

    图2.查询快照数据库时查询的分布
写入时复制(Copy On Writing)和稀疏文件(Sparse Flie)
    由上图中可以看出,快照数据库的文件是基于稀疏文件(Sparse
File),稀疏文件是NTFS文件系统的一项特性。所谓的稀疏文件,是指文件中出现大量0的数据,这些数据对我们用处并不大,却一样占用着磁盘空间。因此NTFS对此进行了优化,利用算法将这个文件进行压缩。因此当稀疏文件被创建时,稀疏文件刚开始大小会很小(甚至是空文件),比如图3所示的文件就是一个稀疏文件。虽然逻辑上占了21M,但文件实际上占了128KB磁盘空间。
    
    图3.一个稀疏文件
   图片 3

3、在本地机器建立新数据库
mysql > create database new_db;

1,分词器

   
对于快照来说,除了通过快照数据库文件的属性来看快照的大小之外,也可以通过DMV来查看,如图4所示.
    图片 4

4、导入数据
mysql -u root -p new_db < dump_db_date.sql

分词器(Word
Breaker)
,顾名思义,用于分词,它根据特定语言的语法规则,分割文本中的单词,分词器在拆分单词时,还会记录每个分词在字符串中的位置,分词器把分词,分词的位置,文档ID,全文索引列的序号等信息的组合,称作标记(Token)。

    图4.通过DMV查看快照数据库大小
    
   
而当快照创建后,随着对源数据库的改变逐渐增多,稀疏文件也会慢慢增长,概念如图4所示。
    图片 5

 

例如,对于语句”Kitty is a cute
cat”,在全文索引填充时,分词器把该语句拆分成5个单词:Kitty,is,a,cute,cat。 如果使用默认的停用词列表,那么“is”,“a”都是停用词,全文索引会把停用词丢失,只存储分词:Kitty,cute,cat。

    图5.随着源数据库的更改越来越多,稀疏文件不断增长
 
   
所以,通常来说,当稀疏文件增长到源数据库文件大小的30%时,就应该考虑重建快照了。
   
而稀疏文件的写入是利用了微软的写入时复制技术(Copy-On-Writing),意思是在复制一个对象时并不是真正把对象复制到另一个位置,而是在新的对象中映射一个指针,指向原对象的位置。这样当对新对象执行读操作时,直接指向原对象。而在对新的对象执行写操作时,将改变部分对象的指针指向到新的地址中。并修改映射表到新的位置中。
 
使用快照的限制
   
使用快照存在诸多限制,由于列表太长(详细请参考MSDN:),我只概括的说一下主要限制。
    当使用快照恢复数据库时,首先要删除其他快照
    快照在创建时的时间点上没有commit的数据不会被记入快照
    快照是快照整个数据库,而不是数据库的某一部分
   
快照是只读的,意思是不能在快照上加任何更改,即使是你想加一个让报表跑得更快的索引
    在利用快照恢复数据库时,快照和源数据库都不可用
    快照和源数据必须在同一个实例上
    快照数据库的文件必须在NTFS格式的盘上
    当磁盘不能满足快照的增长时,快照数据库会被置为suspect状态
    快照上不能存在全文索引
   
其实,虽然限制看上去很多,但只要明白快照的原理,自然能推测出快照应该有的限制。
 
快照的创建和使用
   
无论是使用SSMS或是命令行,快照只能通过T-SQL语句创建。在创建数据库之前,首先要知道数据库分布在几个文件上,因为快照需要对每一个文件进行copy-on-writing。如图6所示。
    图片 6

二、索引

虽然停用词不会添加到全文索引中,但是分词的位置会被考虑。“Kitty” ,“cute”
和  “cat”的Position 分别是1,4 和 5。通过分词的位置,全文搜索能够进行位置相邻的查询:两个分词之间最多存在N个单词。例如,查询语句:contains(column,
‘near((Kitty,cate),3)’) 的含义是存在两个word,“Kitty” 和
“cate”,其最大距离是3,从column中查询出包含该条件的phrase,字符串“Kitty
is a cute cat.”  满足匹配条件。

    图6.首先查出数据库的文件分布
  
    根据图6的数据库分布,我们通过T-SQL创建快照,如图7所示。
    图片 7

InnoDB引擎本质上是一种B+树结构。传送门:从B树、B+树、B*树谈到R
树。

2,停用词

    图7,根据图6的数据库信息创建一个数据库快照
 
    
当快照数据库创建成功后,就可以像使用普通数据库一样使用快照数据库了,如图8所示。
    图片 8

1、聚簇索引

停用字词列表(StopList)是非索引字词的列表,每个StopList中存储的分词都是不会用于搜索的分词,叫做停用词(StopWords),全文索引不会存储停用词,但是停用词所占的位置会被记录,如果对停用词进行contians查询,即使基础表(underly
table)中的字段中存在该停用词,全文索引也不会返回任何数据行。通常情况下,停用词(Stopword)都是常用的单词,在语句中出现的频率十分高,过滤掉停用词,能够减少全文索引的size,提高全文查询的性能。

    图8.快照数据库和普通数据库一样使用
 
   
通过如下语句可以看到,快照数据库文件和源数据库的文件貌似并无区别,仅仅是快照数据库文件是稀疏文件,如图9所示。
    图片 9

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。索引的顺序就是数据存放的顺序,所以,很容易理解,一张数据表只能有一个聚簇索引。

3,词干Stemmer) 和 同义词(Thesaurus)**

    图9.源数据库和快照数据库
 
   
而删除快照数据库和删除普通数据库并无二至,也仅仅是使用DROP语句,如图10所示。
    图片 10

聚簇索引要比非聚簇索引查询效率高很多,特别是范围查询的时候。具体细节依赖于其实现方式,InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

词干抽取器(Stemmer )用于把同源单词转换为其根形式,能够转换为相同根形式的单词是同源的。例如,对于单词run,有很多同源的单词:

    图10.删除快照数据库
 
   
我们也可以利用快照恢复数据库,这个恢复速度要比普通的备份-恢复来的快得多,这也可以将数据库呈现给测试人员,当测试结束后,恢复数据库到测试之前的状态。如图11所示。
    图片 11

图片 12

  • ran
  • running
  • runs
  • runner (perhaps)

    图11.利用快照恢复数据库
 
使用快照其他一些需要考虑的因素
   
1.快照数据库的安全设置继承源数据库的安全设置。也就是说能访问源数据库的用户或角色也能访问快照数据库,当然,因为快照数据库是只读的,所以无论任何角色或人都无法修改快照数据库。
   
2.我们由文章前面图5看出,随着快照存在的时间越来越长,快照会不断增长。所以推荐在快照达到源数据库大小30%之前,重新创建快照。
    3.由于快照会拖累数据库性能,所以数据库不宜存在过多快照。
总结
   
本文简单讲述了数据库快照的概念,原理以及使用。数据库快照可以在很多场景下使用,无论是用于报表,还是和镜像配合提供负载,以及利用快照恢复数据库,使用得当的话,快照将会是一把利器。
转载于

 

同义词词典(Thesaurus)是一个XML文件,用于定义特定语言的同义词列表,例如,我们可以设置“Author” ,
“Writer” ,“journalist”是同义词。

主键的顺序就是实际数据存放的顺序。因此,按主键进行范围查询的时候,效率会高很多。

二,创建全文索引

2、查询效率?

创建全文索引之前,必须创建全文目录(Full-Text
Catalog),全文目录用于组织全文索引,是全文索引的容器。每一个全文索引必须属于一个全文目录。全文目录是个逻辑结构,跟数据库的架构(Schema)相同,根据全文索引的存储位置无关。

假设有张表有2个索引,主键作为聚簇索引,还有一个二级索引。聚簇索引的B+树高度为h1,二级索引的B+树高度为h2。

create fulltext catalog catalog_test
as default;

那么通过主键来查询,时间复杂度为O(h1);通过二级索引来查询,时间复杂度为O(h1+h2)。原因在于,在InnoDB引擎中,二级索引的叶子节点中存储的不是行指针,而是主键值

为了创建全文索引,基础表上必须存在一个唯一的(unique)、单列的(single-column)、非空的(non-nullable)的索引,全文引擎使用该索引把基础表上的每行数据映射唯一索引键上,倒转索引存储的就是该索引键和分词之间的映射关系。

这样做的好处是当移动行的时候无须更新二级索引中的指针,减少了行移动或者数据页分裂时的维护工作。

create unique index uidx_dbLogID 
on [dbo].[DatabaseLog]
([DatabaseLogID]);

3、采用自增类型还是uuid作为主键?

每个表只能创建一个全文索引,创建全文索引时,必须考虑全文索引存储的文件组,全文索引关联的停用词列表,全文索引的更新方式,以及跟文本关联的语言,全文索引列必须是文本字段,例如:

自增类型作为主键的好处:一是长度短,节省空间,尤其是在表中有很多二级索引的情况下;二是索引顺序和实际数据存放顺序一致,insert操作总是插入到索引的最后,和uuid相比,避免了大量的为新的行寻找合适位置插入以及频繁的页分裂操作。既节省空间又速度快。

create fulltext index 
on [dbo].[DatabaseLog]
(
[tsql] language 1033
)
key index ui_dbLogID
on (catalog_test,filegroup [primary]) 
with(change_tracking=off ,no population ,stoplist=system);

自增作为主键的坏处:对于高并发工作负载,瞬时插入大量数据,会造成明显的锁竞争,而uuid则不存在这样的问题。另外,在分布式架构中,采用自增作为主键有个主键全局唯一性问题(uuid也有极小概率会冲突)。

1,语言(language)

4、使用索引扫描来做排序的条件

选项 language
是可选的,用于指定列级别的语言,该选项的值可以是语言的名称或LCID,如果没有指定language选项,那么使用SQL
Server实例的默认语言。从系统视图 sys.fulltext_languages
(Transact-SQL).aspx)中查看系统支持的语言及其对应的LCID
和名称。

1)只有当索引列顺序和order
by子句的顺序完全一致,并且所有列的排序方向都一样时;

2,全文目录(fulltext_catalog)

2)如果查询需要关联多张表,则只有当order
by子句引用的字段全部为第一张表时;

选项fulltext_catalog_name 用于指定全文索引的分组,

3)需要满足索引的最左前缀的要求,除非前导列为常量。

3,文件组(filegroup)

比如:

选项 filegroup filegroup_name
用于指定全文索引存储的文件组,如果没有指定文件组,那么全文索引和基础表存储在相同的文件组中。由于更新全文索引是IO密集型操作,因此,为了更快的更新全文索引,最好把全文索引存储在不同于基础表的的物理硬盘或文件组上,以达到最大的IO并发。

发表评论

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