索引碎片

碎片的概念

碎片可以定义为:任何情况下,在访问一个表时,造成比适量更多的磁盘IO操作或更长的磁盘IO操作。SELECT查询的最佳操作发生在,表的数据页是尽可能连续的,而且页是尽可能完整打包(fully packed)的。碎片会破坏这个规则,降低查询的性能。碎片可以发生在2个级别。一个是文件系统级,称为逻辑/物理磁盘碎片,还有一个是索引级碎片。在下面会分别介绍。

逻辑/物理磁盘碎片

逻辑碎片是在文件系统里,数据库文件的碎片,这和其他任何文件一样。这在文件系统不能分配给数据库文件连续空间时产生。这会造成磁头从数据库文件读时需要来回移动。SQL Server对此毫不知情,也不能用任何脚本去检查逻辑磁盘碎片。逻辑磁盘碎片可以因下列原因产生:

   1.数据库文件与其它文件(系统文件和其他应用程序文件)放在同个磁盘;

   2.数据库文件以小块大小频繁增长

移除逻辑碎片我们可以使用系统碎片整理工具,但是注意,在进行碎片整理时,我们需要停止SQL Server,不然的话碎片整理工具会跳过数据库文件,因为它正被SQL Server使用。

避免逻辑碎片的最佳方式是:

   1.将数据库文件放在独立的硬盘,与其他应用程序和日志文件分开。

2.创建新数据库的时候,估计下数据库文件的大小并分配足够的空间避免数据库       文件的频繁增长。

   3.指定数据库文件增长选项为大块而不是频繁的小块增长。

需要你对索引和SQL中数据的存储方式有一定了解

在SQL Server中,存储数据的最小单位是页,每一页所能容纳的数据为8060字节.而页的组织方式是通过B树结构(表上没有聚集索引则为堆结构,不在本文讨论之列)如下图:

   

    在聚集索引B树中,只有叶子节点实际存储数据,而其他根节点和中间节点仅仅用于存放查找叶子节点的数据.

    每一个叶子节点为一页,每页是不可分割的. 而SQL Server向每个页内存储数据的最小单位是表的行(Row).当叶子节点中新插入的行或更新的行使得叶子节点无法容纳当前更新或者插入的行时,分页就产生了.在分页的过程中,就会产生碎片.

理解外部碎片

    首先,理解外部碎片的这个“外”是相对页面来说的。外部碎片指的是由于分页而产生的碎片.比如,我想在现有的聚集索引中插入一行,这行正好导致现有的页空间无法满足容纳新的行。从而导致了分页:

  

     因为在SQL SERVER中,新的页是随着数据的增长不断产生的,而聚集索引要求行之间连续,所以很多情况下分页后和原来的页在磁盘上并不连续.

     这就是所谓的外部碎片.

     由于分页会导致数据在页之间的移动,所以如果插入更新等操作经常需要导致分页,则会大大提升IO消耗,造成性能下降.

     而对于查找来说,在有特定搜索条件,比如where子句有很细的限制或者返回无序结果集时,外部碎片并不会对性能产生影响。但如果要返回扫描聚集索引而查找连续页面时,外部碎片就会产生性能上的影响.

     在SQL Server中,比页更大的单位是区(Extent).一个区可以容纳8个页.区作为磁盘分配的物理单元.所以当页分割如果跨区后,需要多次切区。需要更多的扫描.因为读取连续数据时会不能预读,从而造成额外的物理读,增加磁盘IO.

 

理解内部碎片

    和外部碎片一样,内部碎片的”内”也是相对页来说的.下面我们来看一个例子:

    我们创建一个表,这个表每个行由int(4字节),char(999字节)和varchar(0字节组成),所以每行为1003个字节,则8行占用空间1003*8=8024字节加上一些内部开销,可以容纳在一个页面中:

   

   分页后的示意图:

  

    而当分页时如果新的页和当前页物理上不连续,则还会造成外部碎片

内部碎片和外部碎片对于查询性能的影响

    外部碎片对于性能的影响上面说过,主要是在于需要进行更多的跨区扫描,从而造成更多的IO操作.

    而内部碎片会造成数据行分布在更多的页中,从而加重了扫描的页树,也会降低查询性能.

    下面通过一个例子看一下,我们人为的为刚才那个表插入一些数据造成内部碎片:

    通过查看碎片,我们发现这时碎片已经达到了一个比较高的程度:

    通过查看对碎片整理之前和之后的IO,我们可以看出,IO大大下降了:

对于碎片的解决办法

基本上所有解决办法都是基于对索引的重建和整理,只是方式不同

1.删除索引并重建

这种方式并不好.在删除索引期间,索引不可用.会导致阻塞发生。而对于删除聚集索引,则会导致对应的非聚集索引重建两次(删除时重建,建立时再重建).虽然这种方法并不好,但是对于索引的整理最为有效

2.使用DROP_EXISTING语句重建索引

为了避免重建两次索引,使用DROP_EXISTING语句重建索引,因为这个语句是原子性的,不会导致非聚集索引重建两次,但同样的,这种方式也会造成阻塞

3.如前面文章所示,使用ALTER INDEX REBUILD语句重建索引

使用这个语句同样也是重建索引,但是通过动态重建索引而不需要卸载并重建索引.是优于前两种方法的,但依旧会造成阻塞。可以通过ONLINE关键字减少锁,但会造成重建时间加长.

4.使用ALTER INDEX REORGANIZE

这种方式不会重建索引,也不会生成新的页,仅仅是整理,当遇到加锁的页时跳过,所以不会造成阻塞。但同时,整理效果会差于前三种.

 

posted @ 2017-12-06 10:48  hissql  阅读(292)  评论(0编辑  收藏  举报