索引阐述系列一索引概述,索引参数与碎片

-- 创建聚集索引
create table [dbo].[pub_stocktest] add  constraint [pk_pub_stocktest] primary key clustered 
(
[sid] asc
)with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, 
online = off, allow_row_locks = on, allow_page_locks = on) on [primary]

-- 创建非聚集索引
 create nonclustered index [ix_model] on [dbo].[pub_stocktest]
(
    [model] asc
)
include (     [name]) with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, drop_existing = off, 
online = off, allow_row_locks = on, allow_page_locks = on, FILLFACTOR = 85) on [primary]

一.概述

    索引填充因子作用:提供填充因子选项是为着优化索引数据存储和性质。 当创立或另行生成索引时,填充因子的值可规定每一种叶级页上要填写数据的长空百分比,以便在每一页上保存部分剩余存储空间作为将来扩大索引的可用空间,例如:指定填充因子的值为
80 表示逐个叶级页旅长有 20%
的上空保留为空,以便随着向基础表中添加多少而为伸张索引提供空间。

  填充因子的值是 1 到 100
之间的百分比,服务器范围的暗中同意值为
0,这表示将完全填充叶级页。

 1.1
页拆分现象

   依照数据的查询和修改的比例,正确抉择填充因子值,可提供丰裕的上空,以便随着向基础表中添加多少而伸张索引,从而降低页拆分的可能。要是向已满的索引页添加新行(新行位置依照键排序规则,可以是页中任意行地方), 数据库引擎将把大概一半的行移到新页中,以便为该新行腾出空间。 这种组合称为页拆分。页拆分可为新记录腾出空间,但是实施页拆分大概要求开销一定的时间,此操作会消耗多量资源。 其余,它还只怕造成碎片,从而造成 I/O
操作扩展。 若是平时爆发页拆分(大概过sys.dm_db_index_physical_stats
来查看页拆分景况),可经过应用新的或现有的填充因子值来再一次生成索引,从而再一次分发数据。

  填充值设置过低: 优点是
插入或改动时下降页的拆分次数。缺点是
会使索引须求越来越多的储存空间,并且会下落读取品质。

  填充值设置过高: 优点是
如若每种索引页数据都全体填满,此时select功效最高。缺点是
插入或修改时必要活动后边所有页,效能低。

一 . dm_db_index_physical_stats 紧要字段表明

  1.1 内部碎片:是avg_page_space_used_ca88手机版,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表下的目录

ca88手机版 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: 碎片数。

一. 索引概述

  关于介绍索引,有一种“小说长史,挥毫万字,一饮千钟”的磅礴感觉,因为索引要求讲的知识点太多。在各样关系型数据库里都会作为主要介绍,因为索引关系着数据库的完整质量,
它在数据库质量优化里占用至关主要地方。由于索引关联面广,我想通过一多重来把索引尽量讲演清楚,差不多包含索引存储单元、堆介绍、聚集索引与非聚集索引介绍、索引参数(填充因子,包蕴列,约束等)、索引的使用,索引维护管理,索引总括音信、索引访问方法、索引存储与文件组、索引视图、索引数据修改内部机制、索引的解析调优排查等。尽量争取把索引的知识点讲到讲精晓,借鉴一些资料和经历,整理输出理论,实践列出案例。

  索引可以提供了对数码的神速访问。似乎一本书的目录,一个好的目录可以极大的压缩查询时间,索引使数据以一种特定的章程社团起来,使查询操作具有最佳质量。当表变得愈加大,索引就变得可怜醒目,可以运用索引飞速满意where条件的数据行。某些意况还足以行使索引援助对数码举办排序,组合,分组,筛选。

  在sqlserver里索引类型包罗:堆,聚集索引,非聚集索引,列存储索引,特殊索引(如全文索引),别的索引如分区索引,过滤索引等。

  1.
 堆:堆不是索引,但讲索引时会讲到堆,两者有紧凑联系,堆结构在多少插入,没有改观时是有囤积顺序的,但一改动如修改删除,结构就会暴发变化。没有聚集索引的表称为堆表。

  2.
聚集索引:对于聚集索引,数据实际上是按顺序存储的是B-Tree结构,B树是代表平衡的树,在搜寻记录时都只需等量的资源,获取速度总是一样的,因为根索引到叶索引都具有同样的深度,
就如一本书把装有目录编撰一样,一旦找到所要的数量,就完事了本次搜索,当查问利用到了目录时,sqlserver优化器可以快速稳定,最少I/O次数获取所需的数码。

  3. 非聚集索引:非聚集索引也是B-Tree结构,在sql server 08可中多达999个。它是截然独立于数据我协会的,也等于说它存储的是键值,有指针指向数据我的地点。

  4. 列存储索引:它是sql server 2012早先引入的一种索引类型,,首要用以对天意据量的查询操作,与观念的索引行存储差距,通过列存储的缩减情势,在一些场景大大提升索引成效。

1.1 Filefactor参数

二. 碎片与填充因子案例

   上边分析在生产环境下,对长日子一个表的ix_1索引举行剖析。

-- 有一个PUB_Search_ResultVersions2表长期有增删改操作, 在很长一段时间运行后,查看碎片如下
dbcc SHOWCONTIG (PUB_Search_ResultVersions2,'ix_1')

  ca88手机版 2

    通过上图可以精晓到平均页密度是29.74%,约等于其中碎片太多,现四个页的数据存储量才是常规一个页的存储量。扫描的页数是703页,涉及到了192个区。上面重新维护索引

--重建索引
ALTER INDEX ix_1 ON dbo.PUB_Search_ResultVersions2 REBUILD

  ca88手机版 3

     通过上图可以观望,扫描页数唯有了248页(原来是703页)
用了36区(原来是192个区),现等于一页的实际数据是前边三页的总量,
查询将会让利扣了大气的I/O扫描。

  假如频仍的增删改,最好设置填充因子,暗中认可是0,约等于100%,
若是有新索引键排序后,挤入到一个已填满8060字节的页中时,就会生出页拆分,爆发碎片,那里本身动用图形界面来安装填充因子为85%(最好通过t-sql来安装,做运维自动爱惜),再重建下索引使设置生效。

  ca88手机版 4

  下图可以观望平均页密度是85%,填充因子设置生效。可以在通过sys.dm_db_index_physical_stats重新查看该索引页使用数据。

ca88手机版 5

二. 消除碎片方法

-------------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 )

二. 索引元数据   

  元数据是对应各个功效的局地描述与天性,那里的元数据是索引相关描述,后边查询分析还会动用到这一个元数据,具体领悟使用可以先查看msdn,
索引常用相关元数据如下:

  sys.indexes
 它提供索引名,索引类型(堆或索引),聚集与非聚集类型,索引填充因子,索引过滤等音信。

  sys.index_columns 它提供了目录包蕴的列音讯,可经过与sys.indexes关联获得索引列定义。

SELECT i.name AS index_name  
    ,COL_NAME(ic.object_id,ic.column_id) AS column_name  
    ,ic.index_column_id  
    ,ic.key_ordinal  
,ic.is_included_column  
FROM sys.indexes AS i  
INNER JOIN sys.index_columns AS ic   
    ON i.object_id = ic.object_id AND i.index_id = ic.index_id  
WHERE i.object_id = OBJECT_ID('表名xx');      

  如下图所示:ca88手机版 6

 

 sys.columns_store_dictionaries和sys.columns_store_segments:用于描述列存储消息。

 sys.xml_indexes:与sys.indexes类似
紧借使用来xml索引。

 sys.spatial_indexes:也与sys.indexes类似
紧假若用于spatial索引。

 sys.dm_db_index_physical_stats:它讲述了目录的轻重缓急和散装音讯,代替了DBCC
SHOWCONTIG。有二种得到总括消息扫描碎片方式:LIMITED,SAMPLED,DETAILED
那三种顺序描述需求的时间是更进一步多。

 sys.dm_db_index_operational_stats:用来跟踪索引
I/O、 锁定、
闩锁、访问方法。索引访问方式(叶级插入累计数,叶级删除累计数,叶级更新累积数)。
索引或堆上闩锁争用次数时间,lock锁定数量时间,以及索引载入内存 I/O
数。

 sys.objects:用户自定义对象(如:表,视图..)的标识号,可以透过索引的objectid找到有关表名或视图名。

 sys.PARTITIONS:描述索引在每种分区中各对应一行,表和目录都至少含有一个分区(在表内部结构里,顶层是表,中间层是分区,分区上边再是数额和目录)。

 sys.dm_db_index_usage_stats:描述分歧类型索引操作的计数(如:全表描述次数、走索引次数,书签查找次数等)以及对应各操作时间。每一回查询索引,所进行的每一种独立的追寻、扫描、查找或更新都被计为对该索引的一遍使用,并使此视图中的相应计数器递增。

 sys.dm_db_missing_index_groups:索引组中包涵的缺失索引音讯。

 sys.dm_db_missing_index_details:描述有关缺失索引的详细消息。

 sys.dm_db_missing_index_group_stats:描述缺失索引组中带有的缺失索引。

  如下图是多个元数据整合,分析出缺失的目录

SELECT  DB_NAME(database_id) AS database_name ,
        OBJECT_NAME(object_id, database_id) AS table_name ,
        mid.equality_columns ,
        mid.inequality_columns ,
        mid.included_columns ,
        ( migs.user_seeks + migs.user_scans ) * migs.avg_user_impact AS Impact ,
        migs.avg_total_user_cost * ( migs.avg_user_impact / 100.0 )
        * ( migs.user_seeks + migs.user_scans ) AS Score ,
        migs.user_seeks ,
        migs.user_scans
FROM    sys.dm_db_missing_index_details mid
        INNER JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
        INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
ORDER BY migs.avg_total_user_cost * ( migs.avg_user_impact / 100.0 )
        * ( migs.user_seeks + migs.user_scans ) DESC

 sys.dm_db_missing_index_columns:紧缺索引列的关于的新闻。

  使用Filefactor可以对索引的种种叶子分页存储保留部分空中。对于聚集索引,叶级别包括了数额,使用Filefactor来控制表的保留空间,通过预留的长空,幸免了新的数目按顺序插入时,需腾出空位而开展分页分隔。
  Filefactor设置生效注意,唯有在开立索引时才会基于现已存在的数目控制留下的空中尺寸,如里必要可以alter
index重建索引一碗水端平置原来指定的Filefactor值。
  在创制索引时,如若不指定Filefactor,就使用暗中同意值0
也等于填充满,可因此sp_configure
来布局全局实例。Filefactor也只就用于叶子级分页上。尽管要在当中层控制索引分页,可以因而点名pad_index选取来完结.该拔取会打招呼到目录上具有层次使用相同的Filefactor。Pad_index也只有索引在新建或重建时有用。

1.2 Drop_existing 参数

  删除或重建一个指定的目录作为单个事务来处理。该项在重建聚集索引时卓殊有用,当删除一个聚集索引时,sqlserver会重建各种非聚集索引以便将书签从聚集索引键改为RID。如若再新建可能重建聚集索引,Sql
server会再两遍重建总体的非聚集索引,倘若再新建或重建的聚集索引键值相同,可以安装Drop_existing=ON。

admin

网站地图xml地图