Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1788511
  • 博文数量: 310
  • 博客积分: 6853
  • 博客等级: 准将
  • 技术积分: 2833
  • 用 户 组: 普通用户
  • 注册时间: 2005-08-04 16:41
文章分类

全部博文(310)

文章存档

2013年(17)

2012年(42)

2011年(76)

2010年(71)

2009年(99)

2007年(2)

2006年(1)

2005年(2)

分类: 数据库开发技术

2011-10-09 13:27:18


你可能已经创建好了索引,并且所有索引都在工作,但性能却仍然不好,那很可能是产生了索引碎片,你需要进行索引碎片整理。  什么是索引碎片?
  由于表上有过度地插入、修改和删除操作,索引页被分成多块就形成了索引碎片,如果索引碎片严重,那扫描索引的时间就会变长,甚至导致索引不可用,因此数据检索操作就慢下来了。
  有两种类型的索引碎片:内部碎片和外部碎片。
  内部碎片:为了有效的利用内存,使内存产生更少的碎片,要对内存分页,内存以页为单位来使用,最后一页往往装不满,于是形成了内部碎片。
  外部碎片:为了共享要分段,在段的换入换出时形成外部碎片,比如5K的段换出后,有一个4k的段进来放到原来5k的地方,于是形成1k的外部碎片。
  如何知道是否发生了索引碎片?
  执行下面的SQL语句就知道了(下面的语句可以在SQL Server 2005及后续版本中运行,用你的数据库名替换掉这里的AdventureWorks):

SELECT
object_name(dt.object_id) Tablename,si.name

  IndexName,dt.avg_fragmentation_in_percent 
AS

  ExternalFragmentation,dt.avg_page_space_used_in_percent 
AS

  InternalFragmentation

  
FROM

  (

  
SELECT
object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent

  
FROM sys.dm_db_index_physical_stats (db_id('AdventureWorks'),null,null,null,'DETAILED'

  )

  
WHERE index_id <>
0AS dt INNER
JOIN sys.indexes si ON si.object_id=dt.object_id
AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10
AND dt.avg_page_space_used_in_percent<75
ORDER
BY avg_fragmentation_in_percent DESC

  执行后显示AdventureWorks数据库的索引碎片信息。


  图 3 索引碎片信息
  使用下面的规则分析结果,你就可以找出哪里发生了索引碎片:
  1)ExternalFragmentation的值>10表示对应的索引发生了外部碎片;
  2)InternalFragmentation的值<75表示对应的索引发生了内部碎片。
  如何整理索引碎片?
  有两种整理索引碎片的方法:
  1)重组有碎片的索引:执行下面的命令
  ALTER INDEX ALL ON TableName REORGANIZE
  2)重建索引:执行下面的命令
  ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR=90,ONLINE=ON)
  也可以使用索引名代替这里的“ALL”关键字重组或重建单个索引,也可以使用SQL Server管理工作台进行索引碎片的整理。


  图 4 使用SQL Server管理工作台整理索引碎片
  什么时候用重组,什么时候用重建呢?
  当对应索引的外部碎片值介于10-15之间,内部碎片值介于60-75之间时使用重组,其它情况就应该使用重建。
  值得注意的是重建索引时,索引对应的表会被锁定,但重组不会锁表,因此在生产系统中,对大表重建索引要慎重,因为在大表上创建索引可能会花几个小时,幸运的是,从SQL Server 2005开始,微软提出了一个解决办法,在重建索引时,将ONLINE选项设置为ON,这样可以保证重建索引时表仍然可以正常使用。
  虽然索引可以提高查询速度,但如果你的数据库是一个事务型数据库,大多数时候都是更新操作,更新数据也就意味着要更新索引,这个时候就要兼顾查询和更新操作了,因为在OLTP数据库表上创建过多的索引会降低整体数据库性能。
  我给大家一个建议:如果你的数据库是事务型的,平均每个表上不能超过5个索引,如果你的数据库是数据仓库型,平均每个表可以创建10个索引都没问题。

摘自:http://tech.it168.com/a2009/1125/814/000000814758_2.shtml
阅读(3337) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~