2023年11月29日发(作者：)

sql索引碎⽚产⽣的原理解决碎⽚的办法（sql碎⽚整理）

本⽂需要你对索引和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字节加上⼀些内部开销，可以容纳在⼀个页⾯中:

当我们随意更新某⾏中的col3字段后，造成页内⽆法容纳下新的数据，从⽽造成分页:

分页后的⽰意图:

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

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

外部碎⽚对于性能的影响上⾯说过，主要是在于需要进⾏更多的跨区扫描，从⽽造成更多的IO操作.

⽽内部碎⽚会造成数据⾏分布在更多的页中，从⽽加重了扫描的页树，也会降低查询性能.

下⾯通过⼀个例⼦看⼀下,我们⼈为的为刚才那个表插⼊⼀些数据造成内部碎⽚:

通过查看碎⽚，我们发现这时碎⽚已经达到了⼀个⽐较⾼的程度:

通过查看对碎⽚整理之前和之后的IO，我们可以看出，IO⼤⼤下降了:

对于碎⽚的解决办法

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

1.删除索引并重建

这种⽅式并不好.在删除索引期间，索引不可⽤.会导致阻塞发⽣。⽽对于删除聚集索引，则会导致对应的⾮聚集索引重建

两次(删除时重建，建⽴时再重建).虽然这种⽅法并不好，但是对于索引的整理最为有效

2.使⽤DROP_EXISTING语句重建索引

为了避免重建两次索引，使⽤DROP_EXISTING语句重建索引，因为这个语句是原⼦性的，不会导致⾮聚集索引重建两

次，但同样的，这种⽅式也会造成阻塞

3.如前⾯⽂章所⽰，使⽤ALTER INDEX REBUILD语句重建索引

使⽤这个语句同样也是重建索引，但是通过动态重建索引⽽不需要卸载并重建索引.是优于前两种⽅法的，但依旧会造成阻

塞。可以通过ONLINE关键字减少锁，但会造成重建时间加长.

4.使⽤ALTER INDEX REORGANIZE

这种⽅式不会重建索引，也不会⽣成新的页，仅仅是整理，当遇到加锁的页时跳过，所以不会造成阻塞。但同时，整理效

果会差于前三种.

理解填充因⼦

重建索引固然可以解决碎⽚的问题.但是重建索引的代价不仅仅是⿇烦，还会造成阻塞。影响使⽤.⽽对于数据⽐较少的情况

下，重建索引代价并不⼤。⽽当索引本⾝超过百兆的时候。重建索引的时间将会很让⼈蛋疼.

填充因⼦的作⽤正是如此。对于默认值来说，填充因⼦为0（0和100表⽰的是⼀个概念）,则表⽰页⾯可以100%使⽤。所

以会遇到前⾯update或insert时，空间不⾜导致分页.通过设置填充因⼦，可以设置页⾯的使⽤程度:

下⾯来看⼀个例⼦:

还是上⾯那个表.我插⼊31条数据，则占4页:

通过设置填充因⼦，页被设置到了5页上:

这时我再插⼊⼀页，不会造成分页:

上⾯的概念可以如下图来解释:

可以看出，使⽤填充因⼦会减少更新或者插⼊时的分页次数，但由于需要更多的页，则会对应的损失查找性能.

如何设置填充因⼦的值

如何设置填充因⼦的值并没有⼀个公式或者理念可以准确的设置。使⽤填充因⼦虽然可以减少更新或者插⼊时的分页，但同

时因为需要更多的页，所以降低了查询的性能和占⽤更多的磁盘空间.如何设置这个值进⾏trade-off需要根据具体的情况来看.

具体情况要根据对于表的读写⽐例来看,我这⾥给出我认为⽐较合适的值:

1.当读写⽐例⼤于100：1时，不要设置填充因⼦，100%填充

2.当写的次数⼤于读的次数时，设置50%-70%填充

3.当读写⽐例位于两者之间时80%-90%填充

上⾯的数据仅仅是我的看法，具体设置的数据还要根据具体情况进⾏测试才能找到最优.