您好、欢迎来到现金彩票网!
当前位置:斗牛 > 无死锁性 >

缺少索引会带来三个问题限制并发性、影响性能、还有可能造成死锁

发布时间:2019-06-12 23:11 来源:未知 编辑:admin

  在ORACLE数据库中,定义外键约束时,ORACLE是不会自动创建对应索引的,必须手动在外键约束相关的列上创建索引。如果有必要的话,巡检时,如何找出外键字段上没有创建索引的相关表,并生成对应的索引的脚本呢?

  外键列上缺少索引会带来三个问题,限制并发性、影响性能、还有可能造成死锁。所以对于绝大部分场景,我们应该尽量考虑在外键上面创建索引

  1. 影响性能。 如果子表外键没有创建索引,那么当父表查询关联子表时,子表将进行全表扫描。影响表连接方式。

  2. 影响并发。 无论是更新父表主键,或者删除一个父记录,都会在子表中加一个表锁(在这条语句完成前,不允许对子表做任何修改)。这就会不必要

  我们先来看看一个简单的例子,看看当外键缺失索引时,子表是否进行全表扫描,如下所示,表EMP与DEPT存在主外键关系:

  如上所示,当外键字段没有索引时,父表与子表关联时,子表会进行全表扫描,下面,我在外键字段创建索引后,就能避免子表表扫描了。

  当然这两个表的数据量实在是太少了,性能上差别不大,当数据量增长上去后,这个性能差异就会比较明显了。如下例子所示,我们构造一个数据量相对较大的父表与子表:

  上面脚本构造了测试用的例子和数据, 那么我们对比看看外键有无索引的区别:

  创建索引后,我们再来看看其执行计划,注意对比创建索引前后,执行计划的差异,如下所示:

  接下来,我们再来看看外键缺失索引影响并发,以及造成死锁的情况,如下所示,创建表dead_lock_parent与dead_lock_foreign,两者存在主外键关系,分布插入两条测试数据:

  如果你在外键字段上创建索引,那么这种情况下的操作就不会出现死锁。在这里就不再赘述。有兴趣可以测试一下.

  虽然增加索引,可能会带来一些额外的性能开销(DML操作开销增加)和磁盘空间方面的开销,但是相比其带来的性能改善而言,这些额外的开销其实完全可以忽略。如果没有其他特殊情况,建议所有的外键字段都加上索引。在Oracle Oracle Database 9i/10g/11g编程艺术这本书中介绍了在什么时候不需要对外键加索引. 必须满足下面三个条件:

  3: 不会从父表联结到子表, 或者更通俗的讲,外键列不支持子表的一个重要访问路径,而且你在谓词中没有使用这些外键累从子表中选择数据。

  我们首先可以通过下面脚本,找到整个数据库中那些表有主外键关系,并列出主外键约束.

  如果是ORACLE 11g或以上版本,数据库有分析函数LISTAGG的话,可以使用下面脚本

  上面的这些脚本已经能找出那些外键字段已经建立或未建立索引,此时如果对外键字段缺少索引的表手工创建索引的话,如果数量很多的话,那么工作量也非常大,下面可以用这个脚本自动生成缺失的索引

  --脚本使用分析函数LISTAGG, 适用于ORACLE 11g以及以上版本,如果数据库版本是Oracle 11g及以上,就可以使用此脚本替代上面脚本。

http://pointadesign.com/wusisuoxing/130.html
锟斤拷锟斤拷锟斤拷QQ微锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷微锟斤拷
关于我们|联系我们|版权声明|网站地图|
Copyright © 2002-2019 现金彩票 版权所有