控制索引的数量

索引可以加快查询的速度,很多人意识到这点后,就特别喜欢用索引,动不动就在表上建索引,有时为了自己非常特殊的冷门需求,也为表建某索引,用完后也不将索引清除掉。我这里要特别说明一下,随意建索引的态度是要不得的!
为什么这么说呢?这里要注意索引有一个不利的地方,就是索引一定会影响DML的性能,往不带索引表中插入值的速度和带索引的表中插入的速度相比,不带索引的表的插入速度一定更快,原因很明显,DML语句要进行维护索引的开销,所以会慢,这无须做实验就能证明了。另外随着索引的不断增大,维护的开销将越来越大,如果一张表的索引数量再一增多,开销将会进一步增大!
生产中要严格控制索引的数量,避免某些更新频繁的表更新的性能太低影响了系统的正常运行,如何控制索引的数量呢?

1、首先可以执行如下命令获取需要监控的索引的语句,然后执行提取出来的语句

select 'alter index '||owner||'.'||index_name||' monitoring usage;'
from dba_indexes
where table_owner='BILL'

2、保证上面提取的语句执行过后,通过一段时间的观察,查看下列语句得出索引使用的情况,获取那些索引不常用的信息

select io.name index_name, t.name table_name,
       decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring,
       decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,
       ou.start_monitoring start_monitoring,
       ou.end_monitoring end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where io.owner# = (select user# from sys.user$ where name='&用户名')
  and i.obj# = ou.obj#
  and io.obj# = ou.obj#
  and t.obj# = i.bo#

3、观察第2步查询出来的结果,删除不常使用的索引,控制索引的数量。
对此大家应该掌握了如何观察和控制索引的方法,希望大家能熟悉并经常在工作中应用。
此外当索引的数量和大小达到某个临界值的时候,DML操作如插入的速度,可能会忽然慢到难以忍受的地步,可能从原先比插无索引表的速度慢3-5倍,到一下子慢到比无索引表的插入速度慢10多倍。此时考虑删除索引,等插入完成后再重建索引,也是一个好方法,也许会快很多!这个我很有体会!在计费环境组上班期间,我维护DSG数据库同步软件的时候发现,把生产的超级大表account_item和查询库的account_item做全同步时,同步表包含同步索引的操作方式让我等了8个多小时才同步好,而我选择单独同步表不同步索引,1小时内同步完成,接着我手动建索引,在1小时左右完成,这样加起来的速度比合并操作要快许多!从这里也说明了索引非常影响大表的DML性能!

总结:现实应用中,控制索引的数量是非常重要的,大家要有索引不能随便建的意识,如果为了某个特定的需求建立了索引,日后不常用,要及时删除这个索引,避免将来对DML操作造成严重的影响。另外要经常利用上面提供的三个步骤,及时维护生产中的索引,观察和控制索引的数量!有时必须进行大批量插入动作,某些索引又不得不建的情况下,可以考虑分开执行,索引先删除,等插完数据,再将索引重新建起来。
引申联想:使用EXP/IMP工具进行数据迁移时,可以考虑对索引进行两种方式控制以提高EXP/IMP的速度。1、就是不使用索引,等数据迁移过来后,在新的数据库中手动建立自己需要的索引,毕竟数据更重要,索引可以慢慢建,选择性的建。这样就在EXP的选项中设置INDEXES=N,这样加快了导出的速度也加快的导入的速度。2、在IMP的时候选择INDEXFILE参数,让索引先生成文件,先不要导入数据库中,等数据全部导入库后再建选择性的从INDEXFILE中建索引,即便把INDEXFILE的索引全部建到新库中,大数据迁移时,分步操作的速度也更快。这两点是我写本小节的引申联想。

Trackback

no comment untill now

Add your comment now

切换到手机版