君不知

索引碎片

电脑版发表于:2020/3/17 17:23

索引碎片

一、什么是索引碎片?


由于表上有过度地插入、修改和删除操作,索引页被分成多抉就形成了索引碎片,如果 索引碎片严重,那扫描索引的时间就会变长,甚至导致索引不可用,因此数据检索操作就慢 来, 索引碎片有两种类型的索引碎片:内部碎片和外部碎片。

二、索引碎片分类


1、内部碎片

为了有效地使用内存并使内存产生更少的片段,有必要对内存进行分页。内存用在页面中。最后一页通常不填充,因此形成内部片段。


2、外部碎片

为了共享片段,在片段交换时形成外部片段。例如,在5K段被调出后,一个4K段进入并放在原来的5K位置,从而形成1K外部片段。


三、碎片的产生

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


--创建一个表
CREATE TABLE TableToTest
(
Id int PRIMARY KEY,
Name char(999),
Description nvarchar(500)
)
--插入数据
DECLARE @count int
SET @count=1
WHILE(@count<9)
BEGIN
INSERT TableToTest VALUES(@count,'xxx','')
SET @count +=1
END


修改其中的一条数据


UPDATE TableToTest SET Description='1234567890' where Id=8


得到结果:




DBCC showcontig 查看索引碎片

DBCC showcontig('bsscost'): --显示指定表的所有索引的碎片信息。


扫描页数:如果你知道行的近似尺寸和表或索引里的行数,那么你可以估计出索引里的页数。看看扫描页数(如果明显比估计的页数要高,说明存在内部碎片)。

扫描区数:用扫描页数除以8,四舍五入到下一个最高值。该值应该和DBCC SHOWCONTIG返回的扫描扩展盘区数一致

    如果DBCC SHOWCONTIG返回的数高,说明存在外部碎片。碎片的严重程度依赖于刚才显示的值比估计值高多少(扫描区数=扫描页数/8 如果过高说明有外部碎片)

区切换次数:该数应该等于扫描扩展盘区数减1。高了则说明有外部碎片(理论值=扫描区数-1)。

每个扩展盘区上的平均页数:该数是扫描页数除以扫描扩展盘区数(一般是8。小于8说明有外部碎片)

扫描密度[最佳值:实际值]:DBCC SHOWCONTIG返回最有用的一个百分比。这是扩展盘区的最佳值和实际值的比率。该百分比应该尽可能靠近100%。低了则说明有外部碎片(百分比 越高越好)。

逻辑扫描碎片:无序页的百分比。该百分比应该在0%到10%之间,高了则说明有外部碎片(越低越好)。

扩展盘区扫描碎片:无序扩展盘区在扫描索引叶级页中所占的百分比。该百分比应该是0%,高了则说明有外部碎片(越低越好)。

每页上的平均可用字节数:所扫描的页上的平均可用字节数。越高说明有内部碎片,不过在你用这个数字决定是否有内部碎片之前,应该考虑fill factor(填充因子)(越低越好)。

平均页密度(完整):每页上的平均可用字节数的百分比的相反数。低的百分比说明有内部碎片(越高越好)。

sql 查看索引碎片

--查看碎片
SELECT
page_count 页数,
avg_page_space_used_in_percent 索引内部碎片的百分比,
record_count 记录计数,
avg_record_size_in_bytes 分段的大小,
avg_fragment_size_in_pages 分段的大小,
avg_fragmentation_in_percent 索引内部碎片的百分比
FROM sys.dm_db_index_physical_stats(DB_ID('TestServer'),OBJECT_ID('TableToTest'),NULL,NULL,'sampled')


当前存在了内部碎片!

注:当修改了 第一页的数据之后,第一页的数据大于8060字节,存储不下 页进行分裂了,产生了内部索引碎片。

并且 不会将修改的数据单独分到第二页,索引B+树存储,会让存储尽量平衡,以减少检索层级  示意图


且一般情况下SQL Server数据库默认设置有20%的填充因子(可设置),既新建页80%存数据,20%为update和insert预留。

另外,在插入1~8之后 9之前,很可能数据库在这段时间有N多新增数据,也就是在物理结构上 页1 和 页2 无法连续。这就无法避免的产生了外部碎片

四、内部碎片和外部碎片的影响

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

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

五、 索引碎片的理解

这里的索引碎片可以理解为索引数据页中的一些空隙,这应该如何理解呢?假如某一个页里面是满的,比如是8K,如果存在25%的空隙,那么真正有效的数据只有75%,举个简单的例子比如某个表格的索引数据有100个页,但是碎片率是25%,所以这100个换页面里面只有75个页面的数据是有效的。所以在索引的碎片率非常高的情况下,索引的效率就会非常低,因为其IO的使用率也会非常低。 索引碎片既指索引文件页中的空白空间;又指被Page Split的索引页;还指索引失序的数据页。前面两种我们称之为索引内部碎片,后面一种我们叫着索引外部碎片。这些操作带来的后果是:更新操作可能导致失序(out of order);删除操作导致空白条目(empty space);插入操作导致分页(page split)。结果就是最终形成电话簿(类似于索引)的碎片外部碎片和内部碎片。由于SQL Server读取数据的最小单位是数据页,而不是单条记录,所以,相同的查询语句需要SQL Server读取更多的磁盘宽度,加之索引碎片会浪费更多的内存资源来存放读取到的数据。因此,碎片化程度越高意味着更高的内存使用浪费和更低的查询性能。微软建议索引碎片率在5%到30%之间,做索引重组;碎片率超过30%,做索引重建工作。


内部碎片:就好比2居室就住了一个人,空余一间居室。行分布在更多的页中,内部碎片会造成数据行分布在更多的页中,从而加重了扫描的页树,也会降低查询性能.

外部碎片:就好比我有2间居室,但不在一个屋子里。外部碎片多,则需要进行更多的跨区扫描,从而造成更多的IO操作


解决索引碎片的方法其实很简单,也就是进行一个Rebuild Indexes的操作,做完这个操作之后统计信息会被更新,相应的执行计划中的缓存信息也会被清空,

当相同的语句再过来的时候,SQL Server就会重新进行执行计划的评估和选择,并获得更好的执行计划。

*生产环境重建索引注意

在产品环境中重建索引需要十分小心,原因是:

  • 重建索引会消耗大量的系统I/O读写资源。

  • 重建索引会导致查询进程的死锁或者锁等待,尤其是非企业版SQL Server(企业版可以使用ONLINE选项来最大限度规避这个问题)。

  • 重建索引会导致数据库日志文件暴涨,而因此会给Database Mirroring、Log Shipping和Backup带来压力。

  • 因为Rebuild Indexes是一个IO密集型的操作,所以会非常消耗IO,所以,请选择业务低谷期进行索引碎片重整的操作。

六、 对于碎片的解决办法

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

1.删除索引并重建

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

2.使用DROP_EXISTING语句重建索引

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

3.使用ALTER INDEX REBUILD语句重建索引(DBCC DBREINDEX)

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

4.使用ALTER INDEX REORGANIZE(DBCC INDEXDEFRAG)

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


avg_fragmentation_in_percent:索引碎片百分比,如果碎片小于10%~20%,碎片不太可能会成为问题,如果索引碎片在20%~40%,碎片可能成为问题,但是可以通过索引重组(DROP_EXISTING)来消除索引解决,大规模的碎片(当碎片大于40%),可能要求索引重建(DBCC DBREINDEX)。


七、填充因子

用来设置页的使用情况,值:0-100 以避免页拆分。使用填充因子会减少更新或者插入时的分页次数,但由于需要更多的页,则会对应的损失查找性能 ,填充因子的概念(预留一定的空间存放插入和更新新增加的数据,以避免页拆分)重建索引固然可以解决碎片的问题.但是重建索引的代价不仅仅是麻烦,还会造成阻塞。影响使用.而对于数据比较少的情况下,重建索引代价并不大。而当索引本身超过百兆的时候。重建索引的时间将会很让人蛋疼.填充因子的作用正是如此。对于默认值来说,填充因子为0(0和100表示的是一个概念),则表示页面可以100%使用。所以会遇到前面update或insert时,空间不足导致分页.通过设置填充因子,可以设置页面的使用程度:使用填充因子会减少更新或者插入时的分页次数,但由于需要更多的页,则会对应的损失查找性能.

如何设置填充因子的值

 如何设置填充因子的值并没有一个公式或者理念可以准确的设置。使用填充因子虽然可以减少更新或者插入时的分页,但同时因为需要更多的页,所以降低了查询的性能和占用更多的磁盘空间.如何设置这个值进行trade-off需要根据具体的情况来看.

 具体情况要根据对于表的读写比例来看,此处列出比较合适的值:

   1.当读写比例大于100:1时,不要设置填充因子,100%填充

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

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

性能.


关于TNBLOG
TNBLOG,技术分享。技术交流:群号677373950
ICP备案 :渝ICP备18016597号-1
App store Android
精彩评论
{{item.replyName}}
{{item.content}}
{{item.time}}
{{subpj.replyName}}
@{{subpj.beReplyName}}{{subpj.content}}
{{subpj.time}}
猜你喜欢