来自 金沙国际登录 2019-10-03 12:17 的文章
当前位置: 金沙国际登录 > 金沙国际登录 > 正文

存储数据是为了查找数据,存储结构影响数据查

一 . dm_db_index_physical_stats 重要字段说明

  1.1 内部碎片:是avg_page_space_used_in_percent字段。是指页的填充度,为了使磁盘使用状况达到最优,对于没有很多随机插入的索引,此值应接近 100%。 但是,对于具有很多随机插入且页很满的索引,其页拆分数将不断增加。 这将导致更多的碎片。 因此,为了减少页拆分,此值应小于 100%。

  1.2 外部碎片:也叫逻辑碎片是avg_fragmentation_in_percent字段。是分页的逻辑顺序和物理顺序不匹配或者索引拥有的扩展不连续时产生。当对表中定义的索引进行数据修改(INSERT、UPDATE 和 DELETE 语句)的整个过程中都会出现碎片。 由于这些修改通常并不在表和索引的行中平均分布,所以每页的填充度会随时间而改变。 对于扫描表的部分或全部索引的查询,这种碎片会导致额外的页读取。 这会妨碍数据的并行扫描。

  1.3 使用查看dm_db_index_physical_stats索引碎片 (SQL server 2005以上)。

SELECT OBJECT_NAME(sys.indexes.OBJECT_ID) AS tableName,
 sys.indexes.name,   
 page_count,
 (page_count*8.0)AS 'IndexSizeKB',
 avg_page_space_used_in_percent,
 avg_fragmentation_in_percent,
 record_count,avg_record_size_in_bytes,
index_type_desc,
fragment_count 
from sys.dm_db_index_physical_stats(db_id('dbname'),object_id('tablename'), null,null,'sampled') 
 JOIN sys.indexes  ON   sys.indexes.index_id = sys.dm_db_index_physical_stats.index_id
 AND sys.indexes.object_id = sys.dm_db_index_physical_stats.object_id

    下面还是接着上一篇查询PUB_StockCollect表下的索引

金沙集团送58元彩金 1

  (1) avg_fragmentation_in_percent(外部碎片也叫逻辑碎片):最重要的列,索引碎片百分比。
    val >10% and val<= 30% -------------索引重组(碎片整理) alter index reorganize )
    val >30% --------------------------索引重建 alter index rebulid with (online=on)
    avg_fragmentation_in_percent:大规模的碎片(当碎片大于40%),可能要求索引重建
  (2) page_count:索引或数据页的总数。
  (3) avg_page_space_used_in_percent(内部碎片):最重要列:页面平均使用率也叫存储空间的平均百分比, 值越高(以80%填充度为参考点) 页存储数据就越多,内部碎片越少。
  (4) avg_record_金沙国际登录 ,size_in_bytes:平均记录大小(字节)。
  (5) index_type_desc列:索引类型-聚集索引或者非聚集索引等。
  (6) record_count:总记录数,相当于行数。
  (7) fragment_count: 碎片数。

存储数据是为了查找数据,存储结构影响数据查找的性能。对无序数据进行查找,最快的查找算法是哈希查找;对有序数据进行查找,最快的查找算法是平衡树查找。在传统的关系型数据库中,聚集索引和非聚集索引都是平衡树(B-Tree)类型的存储结构,用于顺序存储数据,便于实现数据的快速查找。除了提升数据查找的性能之外,索引还能减少硬盘IO和内存消耗。通常情况下,硬盘IO是查找性能的瓶颈,由于索引是数据表的列的子集,这意味着,索引只存储部分列的数据,占用的硬盘空间比全部列少了很多,因此,数据库引擎只需要消耗相对较少的硬盘IO和内存buffer,就能把索引数据加载到内存中。

存储数据是为了查找数据,存储结构影响数据查找的性能。对无序数据进行查找,最快的查找算法是哈希查找;对有序数据进行查找,最快的查找算法是平衡树查找。在传统的关系型数据库中,聚集索引和非聚集索引都是平衡树(B-Tree)类型的存储结构,用于顺序存储数据,便于实现数据的快速查找。除了提升数据查找的性能之外,索引还能减少硬盘IO和内存消耗。通常情况下,硬盘IO是查找性能的瓶颈,由于索引是数据表的列的子集,这意味着,索引只存储部分列的数据,占用的硬盘空间比全部列少了很多,因此,数据库引擎只需要消耗相对较少的硬盘IO和内存buffer,就能把索引数据加载到内存中。

二. 解决碎片方法

-------------sqlserver 2000 碎片解决--------------
-- 索引重建 充填因子80
dbcc dbreindex(PUB_StockCategory,'PK_PUB_StockCategory',80)
-- 索引重组
DBCC INDEXDEFRAG(dbname,PUB_StockCategory,'PK_PUB_StockCategory')

 

------------sqlserver 2005以上碎片解决--------
-- 重新组织表中单个索引 
 ALTER INDEX ix_pub_stock_2 ON dbo.PUB_Stock REORGANIZE  
 -- 重新组织表中的所有索引
 ALTER INDEX ALL ON dbo.PUB_Stock REORGANIZE  
 -- 重新生成表中单个索引 (重点:重建索引用)
 ALTER INDEX ix_pub_stock_2 ON dbo.PUB_Stock REBUILD
 -- 重新生成表中的所有索引 
 ALTER INDEX ALL  ON dbo.PUB_Stock  
 REBUILD  WITH(FILLFACTOR=80, SORT_IN_TEMPDB=ON ,STATISTICS_NORECOMPUTE = ON )

索引以B-Tree结构存储在数据文件中,分为叶子节点和非叶子节点,叶子节点用于存储数据,而非叶子节点(中间节点和根节点)用于存储索引键,节点数据按照索引键排序。理论上,一旦数据集确定下来,索引查找的时间消耗就只跟索引结构的层次有关系,层次越多,查找数据所消耗的时间越多。碎片会影响索引的层次结构,但是,碎片并不总是破坏者,碎片有利于数据的更新。

索引以B-Tree结构存储在数据文件中,分为叶子节点和非叶子节点,叶子节点用于存储数据,而非叶子节点(中间节点和根节点)用于存储索引键,节点数据按照索引键排序。理论上,一旦数据集确定下来,索引查找的时间消耗就只跟索引结构的层次有关系,层次越多,查找数据所消耗的时间越多。碎片会影响索引的层次结构,但是,碎片并不总是破坏者,碎片有利于数据的更新。

在数据的物理存储上,索引和数据存储在硬盘上的数据文件中,数据文件以页(Page)为最小单位分割,每一个Page是8KB,物理位置上连续的8个Page叫做一个区(Extent),每一个区是64KB。区是空间分配的基本单位,而页是数据存储的基本单位。

在数据的物理存储上,索引和数据存储在硬盘上的数据文件中,数据文件以页(Page)为最小单位分割,每一个Page是8KB,物理位置上连续的8个Page叫做一个区(Extent),每一个区是64KB。区是空间分配的基本单位,而页是数据存储的基本单位。

从物理存储上来看,索引是由一系列的分段(Fragment)构成的,每个分段是由连续的数据页(Page)构成的。理想情况下,数据存储的物理顺序和索引键定义的逻辑顺序保持一致,这有利于数据的范围查询,因为机械硬盘不需要移动磁头就可以获取到所需数据。数据的更新(Insert,Update或Delete)有时会更新索引键,组成索引键的字段的Size增加,以至于原来的Page不能容纳该行数据,导致页拆分,致使数据的物理顺序和逻辑顺序不再匹配,产生索引外部碎片。因此,预留少量的页内碎片能够容纳数据行Size的有限增加,减少页拆分(page split)发生的次数,提高数据更新的性能。通常情况下,大量的索引碎片总是十分有害的,应该把索引碎片控制在一定百分比以下,微软推荐,30%。

从物理存储上来看,索引是由一系列的分段(Fragment)构成的,每个分段是由连续的数据页(Page)构成的。理想情况下,数据存储的物理顺序和索引键定义的逻辑顺序保持一致,这有利于数据的范围查询,因为机械硬盘不需要移动磁头就可以获取到所需数据。数据的更新(Insert,Update或Delete)有时会更新索引键,组成索引键的字段的Size增加,以至于原来的Page不能容纳该行数据,导致页拆分,致使数据的物理顺序和逻辑顺序不再匹配,产生索引外部碎片。因此,预留少量的页内碎片能够容纳数据行Size的有限增加,减少页拆分(page split)发生的次数,提高数据更新的性能。通常情况下,大量的索引碎片总是十分有害的,应该把索引碎片控制在一定百分比以下,微软推荐,30%。

数据更新和数据查找是此消彼长的关系,在索引页中预留空闲空间会增加索引的Size,然而,额外占用的硬盘空间需要额外的硬盘IO加载到内存中,这不利于数据的查找,然而,当发生数据更新时,预留的空间能够容纳数据行Size的增加,减少页拆分发生的次数,这有利于数据的更新,因此,在频繁更新的数据库系统中,为了减少页拆分的次数,需要人为增加索引的内部碎片:

数据更新和数据查找是此消彼长的关系,在索引页中预留空闲空间会增加索引的Size,然而,额外占用的硬盘空间需要额外的硬盘IO加载到内存中,这不利于数据的查找,然而,当发生数据更新时,预留的空间能够容纳数据行Size的增加,减少页拆分发生的次数,这有利于数据的更新,因此,在频繁更新的数据库系统中,为了减少页拆分的次数,需要人为增加索引的内部碎片:

  • FILLFACTOR = fillfactor
  • PAD_INDEX = { ON | OFF }
  • FILLFACTOR = fillfactor
  • PAD_INDEX = { ON | OFF }

在创建索引时,需要权衡数据更新和数据查找对系统的影响,在实际产品环境中,需要设置合适的填充因子,预留索引内部碎片;及时整理索引碎片,消除索引外部碎片,以使数据库达到最优状态。

金沙集团送58元彩金 ,在创建索引时,需要权衡数据更新和数据查找对系统的影响,在实际产品环境中,需要设置合适的填充因子,预留索引内部碎片;及时整理索引碎片,消除索引外部碎片,以使数据库达到最优状态。

一,索引碎片

一,索引碎片

索引碎片分为内部碎片(Internal Fragmentation)和外部碎片(External Fragmentation),内部碎片是指索引页内部的碎片,在索引页内部存在没有使用的空间,部分空间被闲置,这意味索引页存在空间的浪费,数据实际占用的空间多于需要的空间,因此,当存储相同的数据集时,如果索引的碎片越多,索引结构占用的硬盘空间越多;在处理数据时,数据库引擎需要读取的索引页越多,加载到内存消耗的缓存页(Buffer)越多。内部碎片会出现在索引结构的叶子节点或中间节点,叶子节点中的碎片会导致数据密度降低,而中间节点中的碎片会导致索引键的密度降低。

索引碎片分为内部碎片(Internal Fragmentation)和外部碎片(External Fragmentation),内部碎片是指索引页内部的碎片,在索引页内部存在没有使用的空间,部分空间被闲置,这意味索引页存在空间的浪费,数据实际占用的空间多于需要的空间,因此,当存储相同的数据集时,如果索引的碎片越多,索引结构占用的硬盘空间越多;在处理数据时,数据库引擎需要读取的索引页越多,加载到内存消耗的缓存页(Buffer)越多。内部碎片会出现在索引结构的叶子节点或中间节点,叶子节点中的碎片会导致数据密度降低,而中间节点中的碎片会导致索引键的密度降低。

外部碎片是指存储数据的页或区(Extent)的逻辑顺序和物理顺序不一致,逻辑顺序(Logical Order)是由索引键定义的,物理顺序(Physical Order)是在硬盘文件中,用于存储数据的页或区的顺序,也就是索引的叶子节点占用的页或区在硬盘上的物理存储的顺序。如果在逻辑上连续的Page或Extent在物理上也是连续的,那么就不存在外部碎片。最有效的顺序是:逻辑顺序上相邻的数据页,在物理顺序上也相邻。

外部碎片是指存储数据的页或区(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.

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的空间利用率越高。

可以通过内置函数: 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,扫描模式

1,扫描模式

本文由金沙国际登录发布于金沙国际登录,转载请注明出处:存储数据是为了查找数据,存储结构影响数据查

关键词: