MSSQL中的索引碎片

0    106    3

Tags:

👉 本文共约11258个字,系统预计阅读时间或需43分钟。

简介

在SQL Server中,索引碎片指的是索引存储结构中的空隙和未被充分利用的空间,这可能会导致数据库性能下降。索引是用于加速数据库查询操作的数据结构,它们可以按照特定的列对数据库表进行排序和组织,从而提高检索效率。当数据库中的数据发生变化(例如插入、更新、删除操作)时,索引可能会出现碎片化。

  • 当索引包含的页中,索引中的逻辑排序(基于索引中的键值)与索引页中的物理排序不匹配时,就存在碎片

  • 无论何时对基础数据执行插入、更新或删除操作,数据库引擎 都会自动修改索引。例如,在表中添加行可能会导致拆分行存储索引中的现有页,以腾出空间来插入新键值。随着时间的推移,这些修改可能会导致索引中的信息分散在数据库中(含有碎片)。当索引包含的页中的逻辑排序(基于键值)与数据文件中的物理排序不匹配时,就存在碎片。

  • 大量碎片式索引可能会降低查询性能,因为需要额外 I/O 来查找索引指向的数据。较多的 I/O 会导致应用程序响应缓慢,特别是在涉及扫描操作时。

索引是数据库引擎中针对表(有时候也针对视图)建立的特别数据结构,用来帮助查找和整理数据,它的重要性体现在能够使数据库引擎快速返回查询结果。当对索引所在的基础数据表进行增删改时,若存储的数据进行了不适当的跨页(SQL Server中存储的最小单位是页,页是不可再分的),就会导致索引碎片的产生。随着索引碎片的不断增多,查询响应时间就会变慢,性能也因此而下降。要解决这个问题,可以通过重新生成或重新组织索引来解决。

索引碎片分类

一般我们可以把碎片分为外部碎片内部碎片。外部碎片,可分为逻辑碎片(索引)和区碎片(堆)。内部碎片主要有页填充密度衡量。

  • 逻辑碎片,这是索引的叶级页中出错页所占的百分比。对于出错页,分配给索引的下一个物理页不是当前叶级页中的下一页 指针所指向的页。 这种碎片发生在索引的逻辑结构上,即索引中的页顺序与相应表中的实际行顺序不匹配。这可能是由于数据的插入、更新和删除操作导致的。逻辑碎片可以导致查询性能下降,因为数据库引擎需要更多的I/O操作来读取和处理这些碎片。

  • 物理碎片: 这种碎片发生在索引所在的物理存储结构上,即索引的页不是顺序存储的,而是分散在磁盘上。物理碎片通常是由于数据页的分配和释放操作引起的。物理碎片同样会导致磁盘I/O的增加,从而影响性能。

  • 区碎片,这是堆的叶级页中出错区所占的百分比。出错区是指:包含堆当前页的区在物理上不是包含前一页的区后的下一个区。

  • 内部碎片,指示索引页的平均填充率(以百分比表示)。100% 表示索引页完全填充,没有碎片。不过这种理想状态很难存在。

内部碎片

先说说内部碎片,右键索引属性下可以查看其页填充度。

MSSQL中的索引碎片

该索引的页填充度为 97.90%,说明还有一部分空间未使用,有点浪费了。当经过多次DML操作后(如下图),页内有较多的空间空闲出来了,而实际存储的数据只是很少一部分。所以当我们查询部分数据时,需要查询更多的数据页才能获取完整数据,这样增加了IO。

MSSQL中的索引碎片

外部碎片

那外部碎片又是什么样子呢?什么样的数据页是物理不连续呢?

我们创建一个数据文件,数据文件将划分成数据页来存储数据。假设我们预先给数据文件分配一定的空间,数据页分配理应是按顺序在磁盘文件上分配空间(如下图)。当新的数据插入表后,页内空间不足以填充新的行数据,因此产生了页拆分。由于索引键的顺序性,逻辑上要保证其页是顺序的,但是物理上已经出现了跨多个页的情况。这种就是外部碎片。同理,堆表的区碎片也类似。

MSSQL中的索引碎片

如何查看索引碎片,可通过系统函数sys.dm_db_index_physical_stats 查看。

说明
avg_fragmentation_in_percent堆的区碎片/逻辑碎片(索引中的无序页)的百分比
avg_page_space_used_in_percent平均页密度

既然索引碎片产生了更多的空间和磁盘IO,定期清理仍是有必要的。参考以上的字段 avg_fragmentation_in_percent ,进行索引重组或者索引重建。

avg_fragmentation_in_percent处理方法
> 5% 且 < = 30%ALTER INDEX REORGANIZE
> 30%ALTER INDEX REBUILD WITH (ONLINE = ON)

查看碎片也可以通过SSMS工具来查看:

MSSQL中的索引碎片

碎片总计要越小越好,而页填充度要越高越好!!!

重新组织索引(重组索引)使用的系统资源最少,并且是联机操作。也就是说,不保留长期阻塞性表锁,且对基础表的查询或更新可以在 ALTER INDEX REORGANIZE 事务处理期间继续进行。数据库引擎 通过以物理方式重新排序叶级别页,以匹配叶节点逻辑顺序(从左到右),从而对表和视图中的聚集索引和非聚集索引的叶级别进行碎片整理。重新组织还会根据索引的填充因子值压缩索引页。

重新生成索引(重建索引)将会删除并重新创建索引。重新生成操作可以联机或脱机执行,具体取决于索引类型和 数据库引擎 版本。对于行存储索引,重新生成操作会:删除碎片;根据指定或现有的填充因子设置来压缩页,从而回收磁盘空间;还会在连续页中重新排序索引行。如果指定 ALL,将删除表中的所有索引,然后在一个事务中重新生成。不必预先删除外键约束。重新生成具有 128 个区或更多区的索引时,数据库引擎延迟实际的页释放及其关联的锁,直到事务提交。

不过也有注意以下事项:

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!
MSSQL中的索引碎片后续精彩内容已被小麦苗无情隐藏,请输入验证码解锁本站所有文章!
验证码:
请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

嘿,我是小麦,需要帮助随时找我哦。
  • 18509239930
  • 个人微信

  • DB宝
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部