现在有一系统,数据库中的其中几个表会被很频繁地INSERT,有几个表会被很频繁地UPDATE和INSERT,
这些表的数据量非常大(INSERT的表每秒有几千条记录,UPDATE和INSERT的表每秒的记录稍小一点),
我现在采用的办法是,做INSERT的表没有建索引,是按天分区且只保留最近15天的记录,做UPDATE和INSERT的表建有索引(个数为1-4个),每天用作业将表的数据SWITCH到另一个分区表中。我的难题是:建有索引的表,每10多分钟就会产生15%-90%不等的索引碎片,这会导致WEB查询超时和写数据超时,不建索引又不行,因为在做UPDATE时,会先SELECT,然后做判断,有则UPDATE,无则INSERT。邹兄和大家有没有遇到类似的情况?有没有什么好的解决办法?为什么会有这么频繁的索引碎片?
这些表的数据量非常大(INSERT的表每秒有几千条记录,UPDATE和INSERT的表每秒的记录稍小一点),
我现在采用的办法是,做INSERT的表没有建索引,是按天分区且只保留最近15天的记录,做UPDATE和INSERT的表建有索引(个数为1-4个),每天用作业将表的数据SWITCH到另一个分区表中。我的难题是:建有索引的表,每10多分钟就会产生15%-90%不等的索引碎片,这会导致WEB查询超时和写数据超时,不建索引又不行,因为在做UPDATE时,会先SELECT,然后做判断,有则UPDATE,无则INSERT。邹兄和大家有没有遇到类似的情况?有没有什么好的解决办法?为什么会有这么频繁的索引碎片?
大概原理就是SQL会保持现有一组索引,同时并行再建第二组索引,等第二组索引建好之后,自动把第一组索引换掉。
这个过程中整个数据库一直是可用的。如果不能使用2005企业版的话,另外,还有一个办法是使用 DBCC INDEXDEFRAG,
这个命令是2000和2005的其他版本里面唯一能在线清除索引碎片的命令。
使用这个命令时,表还是可访问的。
但这个命令没有完全重建索引(DBCC DBREINDEX)那么彻底,它不能更新表的统计信息。LZ可以考虑试试 DBCC INDEXDEFRAG
感觉是你的结构流程设计肯定有问题。
20000条数据要1分钟插入时用sqlbulkcopy
更新时先删除在插入
优化索引。。
http://topic.csdn.net/u/20070329/17/38398e78-adac-4d7e-a8b6-f2d319d283e8.html
就几千条数据而言,采用先放入缓存里,等数据量达到5万后在一次性INSERT进去,估计会好点。
-----------------------------------------------------------------------
可能还要仔细考虑一下每个索引的填充因子(fillfactor)的值,一般默认是90%,
你可以试试减小这个值到80%甚至75%,看看会不会好一些,但索引文件的体积会增加一些。
----------------------------------------------------------------------------------------------------------------如果这个是 UPDATE 造成的, 则一般在设计上应该做一些调整
从理论上来讲, UPDATE 导致索引碎片主要是由于被 UPDATE 的记录的索引页存储不下 UPDATE 的结果, 导致页拆分而产生碎片
因此, 除了如楼上所说, 应该适当降低索引的 FILLFACTOR 外
还应该考虑修改表结构, 为列预留空间
例如, 如果你的某个列存储的数据在 0-50 个字符之间, 从节约空间的角度来考虑, 一般设置会采用 varchar/nvarchar
但如果这个列被频繁更新, 则可能会经常导致页拆分(旧的数据比新的数据短)
在这种情况下, 应该考虑将列设置为 char/nchar, 这样空间是定长的, 无论新旧数据的实际长度如何, 它都是标准的 50 字符, 这样能够大大避免页拆分, 从而达到减少索引碎片的目的(在一定程度上对更新速度也有提高, 因为更新数据页的时候, 也同时避免了数据页的拆分)
^^^^^ 更换
还有一个问题我也很奇怪,我的数据库中有一个表,我是按天分区的,共15个分区,此表我建了3个分区索引,而该表只进行BLUK INSERT操作,每次BLUK INSERT后,分区索引的碎片也非常大,而且15个分区的3个索引都有程度不等的碎片。这些碎片产生是不是和我上面的有些类似呢???
--------------------------------
三种基本操作:insert,update, delete中
INSERT和UPDATE都会造成索引碎片,
而且INSERT造成碎片的可能性更大,
UPDATE的字段如果没有在索引中则不会影响索引,
DELETE基本不会,但会造成索引空间利用不足。