关键词:
存储数据是为了查找数据,存储结构影响数据查找的性能。对无序数据进行查找,最快的查找算法是哈希查找;对有序数据进行查找,最快的查找算法是平衡树查找。在传统的关系型数据库中,聚集索引和非聚集索引都是平衡树(B-Tree)类型的存储结构,用于顺序存储数据,便于实现数据的快速查找。除了提升数据查找的性能之外,索引还能减少硬盘IO和内存消耗。通常情况下,硬盘IO是查找性能的瓶颈,由于索引是数据表的列的子集,这意味着,索引只存储部分列的数据,占用的硬盘空间比全部列少了很多,因此,数据库引擎只需要消耗相对较少的硬盘IO和内存buffer,就能把索引数据加载到内存中。
索引以B-Tree结构存储在数据文件中,分为叶子节点和非叶子节点,叶子节点用于存储数据,而非叶子节点(中间节点和根节点)用于存储索引键,节点数据按照索引键排序。理论上,一旦数据集确定下来,索引查找的时间消耗就只跟索引结构的层次有关系,层次越多,查找数据所消耗的时间越多。碎片会影响索引的层次结构,但是,碎片并不总是破坏者,碎片有利于数据的更新。
在数据的物理存储上,索引和数据存储在硬盘上的数据文件中,数据文件以页(Page)为最小单位分割,每一个Page是8KB,物理位置上连续的8个Page叫做一个区(Extent),每一个区是64KB。区是空间分配的基本单位,而页是数据存储的基本单位。
从物理存储上来看,索引是由一系列的分段(Fragment)构成的,每个分段是由连续的数据页(Page)构成的。理想情况下,数据存储的物理顺序和索引键定义的逻辑顺序保持一致,这有利于数据的范围查询,因为机械硬盘不需要移动磁头就可以获取到所需数据。数据的更新(Insert,Update或Delete)有时会更新索引键,组成索引键的字段的Size增加,以至于原来的Page不能容纳该行数据,导致页拆分,致使数据的物理顺序和逻辑顺序不再匹配,产生索引外部碎片。因此,预留少量的页内碎片能够容纳数据行Size的有限增加,减少页拆分(page split)发生的次数,提高数据更新的性能。通常情况下,大量的索引碎片总是十分有害的,应该把索引碎片控制在一定百分比以下,微软推荐,30%。
数据更新和数据查找是此消彼长的关系,在索引页中预留空闲空间会增加索引的Size,然而,额外占用的硬盘空间需要额外的硬盘IO加载到内存中,这不利于数据的查找,然而,当发生数据更新时,预留的空间能够容纳数据行Size的增加,减少页拆分发生的次数,这有利于数据的更新,因此,在频繁更新的数据库系统中,为了减少页拆分的次数,需要人为增加索引的内部碎片:
- FILLFACTOR = fillfactor
- PAD_INDEX = { ON | OFF }
在创建索引时,需要权衡数据更新和数据查找对系统的影响,在实际产品环境中,需要设置合适的填充因子,预留索引内部碎片;及时整理索引碎片,消除索引外部碎片,以使数据库达到最优状态。
一,索引碎片
索引碎片分为内部碎片(Internal Fragmentation)和外部碎片(External Fragmentation),内部碎片是指索引页内部的碎片,在索引页内部存在没有使用的空间,部分空间被闲置,这意味索引页存在空间的浪费,数据实际占用的空间多于需要的空间,因此,当存储相同的数据集时,如果索引的碎片越多,索引结构占用的硬盘空间越多;在处理数据时,数据库引擎需要读取的索引页越多,加载到内存消耗的缓存页(Buffer)越多。内部碎片会出现在索引结构的叶子节点或中间节点,叶子节点中的碎片会导致数据密度降低,而中间节点中的碎片会导致索引键的密度降低。
外部碎片是指存储数据的页或区(Extent)的逻辑顺序和物理顺序不一致,逻辑顺序(Logical Order)是由索引键定义的,物理顺序(Physical Order)是在硬盘文件中,用于存储数据的页或区的顺序,也就是索引的叶子节点占用的页或区在硬盘上的物理存储的顺序。如果在逻辑上连续的Page或Extent在物理上也是连续的,那么就不存在外部碎片。最有效的顺序是:逻辑顺序上相邻的数据页,在物理顺序上也相邻。
The most efficient order is where the logical order of the pages and extents(as defined by the index keys, following the next-page pointers from the page headers) is the same as the physical order of the pages and extents with the data files. In other words, the index leaf-lelvel page that has the row with the next index key is also the next physical contiguous page int the data file.
二,检测索引碎片
可以通过内置函数: sys.dm_db_index_physical_stats,查看索引的外部碎片,字段 avg_fragmentation_in_percent 用于表示外部碎片的程度,对于索引,以Page为单位统计碎片;对于堆(Heap),以Extent为单位统计碎片,这是因为Heap结构的页(Page)是没有顺序的。在堆(Heap)的 Page Header中,字段 next_page 和 Pre_page pointer是null。字段 avg_page_space_used_in_percent 用于表示内部碎片的程度,百分比越高,说明单个Page的空间利用率越高。
1,扫描模式
检测索引的碎片,需要对索引进行扫描,参数mode指定为了获取碎片数据,数据库引擎必须执行的扫描模式,共有三种模式:LIMITED, SAMPLED, or DETAILED,默认值是LIMITED。
- Limited 模式是最快的,只扫描最小数据量的Page,Limited模式不会扫描数据页(Data Page),对于索引,扫描叶子节点的直接父节点;对于Heap,扫描堆表对应的IAM 和 PFS系统页。
- 在Sampled模式下,数据库引擎从索引或堆表中抽取1%的Page作为样本数据,根据样本数据来估计碎片的程度。
- Detailed 模式扫描所有的数据页,耗时最久,返回的信息最详细。
2,分段和碎片
分段(Fragment),也叫片段,是指在硬盘文件中,数据的物理存储的集中/分散程度。一个片段是由在物理位置上连续的索引页组成的,Fragment的Size 越大,说明页的物理位置越集中,读取相同数量的Page所需的IO越少,范围读取性能越好。
碎片(Fragmentation)用于描述数据更新对索引结构产生的副作用。页内碎片是指Page 内部存在空闲空间,外部碎片是指Page 或 extent 的物理顺序和所以键定义的逻辑顺序不一致。
- avg_fragmentation_in_percent:碎片百分比,合理的比例是在10左右,比例越大,索引碎片越多,读取性能越差;
- fragment_count:分段的数量,理论上,分段(Fragment)数量越少越好,间接说明索引的物理顺序和逻辑顺序越匹配;
- avg_fragment_size_in_pages:每个分段平均包含的Page数量,Fragment的Size 越大,读取相同数量的Pages所需的IO越少,读取性能越好;
- avg_page_space_used_in_percent:Page空间的平均利用率,值越大,页内碎片越小;
3,检测碎片的脚本
通过执行函数,检测索引的碎片:
select ps.database_id, ps.object_id, ps.index_id, ps.partition_number, ps.index_type_desc, ps.alloc_unit_type_desc, ps.index_depth, ps.index_level, ps.avg_fragmentation_in_percent, ps.fragment_count, ps.avg_fragment_size_in_pages, ps.page_count, ps.avg_page_space_used_in_percent, ps.record_count, ps.ghost_record_count, ps.version_ghost_record_count, ps.min_record_size_in_bytes, ps.max_record_size_in_bytes, ps.avg_record_size_in_bytes, ps.forwarded_record_count, ps.compressed_page_count from sys.dm_db_index_physical_stats(database_id,object_id,index_id,partition_number,'detailed') as ps order by ps.index_level
字段avg_fragmentation_in_percent 表示索引碎片的密度,可以接受的百分比是从0到10%,根据碎片的百分比,选择重新组织索引或重新创建索引,以整理碎片。
返回的字段分析:
- Index_level=0,表示是索引结构的深度,0表示叶子级别;
- avg_fragmentation_in_percent:碎片的百分比,表示物理顺序不连续的pages所占的百分比;如果基础表是BTree, 碎片的计量单位是Page,avg_fragmentation_in_percent和page_count 的乘积就是物理顺序和逻辑顺序不一致的pages的总数量。
- fragment_count:片段的数量
- page_count:page 的数量
- avg_fragment_size_in_pages:每个Index 片段平均使用的Pages,是Page_Count和Fragment_Count的比值。
- avg_page_space_used_in_percent:每个Page内空间的平均使用程度
三,碎片整理
碎片整理有两种方式:重新组织索引和重新创建索引,重建索引是指在一个事务中,删除旧的索引,并重建新的索引,这种方式会回收原有索引的硬盘空间,并分配新的存储空间,以创建索引结构。重组索引是指不分配新的存储空间,在原有的空间基础上,重新组织索引结构的叶子节点,使数据页的逻辑顺序和物理顺序保持一致,并释放索引中多余的空间,这就是说,重组索引是为了减少叶子节点的外部碎片。
使用函数 sys.dm_db_index_physical_stats 检测碎片的程度,字段 avg_fragmentation_in_percent 返回的逻辑碎片的百分比,一般情况下,微软推荐以30%为阈值:
- avg_fragmentation_in_percent >5% and <=30%: 重组索引(ALTER INDEX REORGANIZE);
- avg_fragmentation_in_percent >30%: 重建索引(ALTER INDEX REBUILD);
以下脚本使用游标(Cusor)逐个整理索引碎片,在重建索引(Rebuild Index)时,使用的索引选项是:FILLFACTOR = 95, ONLINE = OFF, DATA_COMPRESSION = PAGE
DECLARE @SchemeName NVARCHAR(MAX)=N''; DECLARE @TableName NVARCHAR(MAX)=N''; DECLARE @IndexName NVARCHAR(MAX)=N''; DECLARE @avg_fragmentation_in_percent FLOAT=0; DECLARE @SQL NVARCHAR(MAX)=N''; DECLARE cur_index CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY FOR SELECT '['+s.name+']' AS SchemeName, '['+o.name+']' AS TableName, '['+i.name+']' AS IndexName, MAX(ps.avg_fragmentation_in_percent) AS avg_fragmentation_in_percent FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N'DETAILED') AS ps ON ps.object_id = i.object_id AND ps.index_id = i.index_id WHERE ps.avg_fragmentation_in_percent >= 10 AND i.type IN (1, 2) --1: CLUSTERED, 2: NONCLUSTERED AND o.type = N'U' --U: USER_TABLE AND ps.index_level = 0 --Index leaf-level GROUP BY s.name, o.name, i.name ORDER BY avg_fragmentation_in_percent DESC; OPEN cur_index; FETCH NEXT FROM cur_index INTO @SchemeName, @TableName, @IndexName, @avg_fragmentation_in_percent; WHILE(@@FETCH_STATUS=0) BEGIN IF (@avg_fragmentation_in_percent>30) BEGIN SELECT @SQL = N'ALTER INDEX ' + @IndexName + N' ON ' + @SchemeName + N'.' + @TableName + N' REBUILD PARTITION=ALL WITH (FILLFACTOR = 95, ONLINE = OFF, DATA_COMPRESSION = PAGE );' END ELSE --@avg_fragmentation_in_percent between 10 and 30 BEGIN SELECT @SQL = N'ALTER INDEX ' + @IndexName + N' ON ' + @SchemeName + N'.' + @TableName + N' REORGANIZE PARTITION=ALL;' END EXEC (@SQL) FETCH NEXT FROM cur_index INTO @SchemeName, @TableName, @IndexName, @avg_fragmentation_in_percent; END CLOSE cur_index; DEALLOCATE cur_index;
这个阈值,可以根据产品环境数据更新和查找的实际情况,适度调整。
参考文档:
索引碎片的检测和整理
...查找算法是平衡树查找。在传统的关系型数据库中,聚集索引和非聚集索引都是平衡树(B-Tree)类型的存储结构,用于顺序存储数据,便于实现数据的快速查找。除了提升数据查找的性能之外,索引还能减少硬盘IO和内存消耗。... 查看详情
如何快速检测和解决数据库的 SQL Server 索引碎片?
】如何快速检测和解决数据库的SQLServer索引碎片?【英文标题】:HowcanIquicklydetectandresolveSQLServerIndexfragmentationforadatabase?【发布时间】:2017-09-2618:42:39【问题描述】:我遇到过这样一种情况,即我对许多SQLServer数据库表的数据库... 查看详情
如何在 PostgreSQL 中找出碎片索引并对其进行碎片整理?
】如何在PostgreSQL中找出碎片索引并对其进行碎片整理?【英文标题】:HowtofindoutfragmentedindexesanddefragmenttheminPostgreSQL?【发布时间】:2019-02-2522:24:19【问题描述】:我已经找到了如何在SQLServerhere中解决这个问题-但是我如何在Postgre... 查看详情
二级索引已损坏。必须对数据库进行碎片整理
】二级索引已损坏。必须对数据库进行碎片整理【英文标题】:Secondaryindexiscorrupt.Thedatabasemustbedefragmented【发布时间】:2012-06-0313:25:27【问题描述】:数据库还原后出现问题。如何修复数据库?【问题讨论】:【参考方案1】:更... 查看详情
id索引更改怎么重新抽取索引内容
...插入、更新或删除操作,SQLServer数据库引擎都会自动维护索引。随着时间的推移,这些修改可能会导致索引中的信息分散在数据库中(含有碎片)。当索引包含的页中的逻辑排序(基于键值)与数据文件中的物理排序不匹配时,... 查看详情
b树b-树b+树b*树介绍,和b+树更适合做文件索引的原因
今天看数据库,书中提到:由于索引是采用B树结构存储的,所以对应的索引项并不会被删除,经过一段时间的增删改操作后,数据库中就会出现大量的存储碎片,这和磁盘碎片、内存碎片产生原理是类似的,这些存储碎片不仅... 查看详情
b树b-树b+树b*树都是什么
今天看数据库,书中提到:由于索引是采用B树结构存储的,所以对应的索引项并不会被删除,经过一段时间的增删改操作后,数据库中就会出现大量的存储碎片,这和磁盘碎片、内存碎片产生原理是类似的,这些存储碎片不仅... 查看详情
用于重建和重新索引碎片索引的脚本?
】用于重建和重新索引碎片索引的脚本?【英文标题】:Scriptforrebuildingandreindexingthefragmentedindex?【发布时间】:2010-11-0809:05:49【问题描述】:当\'avg_fragmentation_in_percent\'超过某些限制(如果不使用游标更好)时,任何人都可以提... 查看详情
磁盘碎片整理有啥用
参考技术A磁盘碎片整理有什么用 参考阅读资料一: 硬盘是计算机的一个重要组成部分,它的数据读取速度直接影响磁盘的读写效率,如果碎片太多还会造成系统崩溃、数据丢失,不仅仅会影响计算机的运行速度,还会... 查看详情
查看索引碎片和维护
SELECT a.index_id, b.name, a.avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(‘‘),   查看详情
小白学习mysql-表空间碎片整理方法
...放的问题。碰巧看到姚老师这篇文章,《MySQL表空间碎片整理方法》,学习一下。MySQL数据库中的表在进行了多次delete、update和insert后,表空间会出现碎片。定期进行表空间整理,消除碎片可以提高访问表空间的 查看详情
sql用于管理sqlserver重建和重组索引碎片的脚本(代码片段)
为啥linux不需要磁盘碎片整理–
参考技术A因为linux的文件系统存储方式和Windows的文件系统存储方式不同相对来说linux下存储文件更安全更快所以不会产生碎片具体如何不同就要了解extntfs的区别这个自己去百度搜 查看详情
如何在我的 C++ 程序中检测和估计堆碎片?
】如何在我的C++程序中检测和估计堆碎片?【英文标题】:HowtodetectandestimateheapfragmentationinmyC++program?【发布时间】:2010-12-0800:25:10【问题描述】:我正在开发一个VC++NT服务,该服务旨在连续运行数月。它大量使用VC++运行时堆。... 查看详情
译索引进阶:sqlserver中的索引碎片下篇(代码片段)
为了讨论碎片产生的原因,以及避免和移除索引碎片的技术,我们必须从本进阶系列后续将介绍的两个章节借用一些知识点:创建/更新索引的知识,以及向一个索引表插入数据行的相关知识。当我们讲解这些信息的时候,记住... 查看详情
go语言碎片整理之文件操作(代码片段)
文件是什么?计算机中的文件是存储在外部介质(通常是磁盘)上的数据集合,文件分为文本文件和二进制文件。打开和关闭文件os.Open()函数能够打开一个文件,返回一个*File和一个error。对得到的文件实例调用close()方法能够关... 查看详情
收缩数据库是不是有风险
...间,这样不会对系统造成太大的影响2:频繁做收缩,那索引碎片会越来越多,查询会越来越慢追问能整理碎片吗?追答整理碎片就是索引重整DBCCINDEXDEFRAG(数据库名,表名,索引键)追问整理这个数据库呢?参考技术A没有风险,放心... 查看详情
golang碎片整理之指针(代码片段)
golang中保留了C中的值和指针的区别,但对于指针的繁琐用法进行了简化,引入了"引用"的概念,所以在go语言中,你不用担心因为直接操作内存而引起各式各样的错误。运算符只有&和,一个是取地址一个是取值(解析... 查看详情