1. 首页
  2. IT资讯

外键加索引问题

根据9I/10G 编程艺术所说,如果不加索引
1、主建删除比如DELETE TEST WHERE ID=1 CASCADE,会导致外键表全表扫描,影响性能
2、主键在更改期间,外键表加锁,DML操作完成后释放(不是COMMIT,是DML操作完成)
对于第一点,我觉得有些小问题,我认为DELETE TEST WHERE ID=1这样的语句同样会导致全表扫描,因为为了保证主表记录可以删除,必须去全表扫描外键表(因为没有索引),来确定没有记录和其匹配
如果匹配当然就报错不能删除,这一点可以通过TKPROF SQL TRACE看到。
如下测试:
SQL> select count(*) from test_b;
 
  COUNT(*)
———-
   4000005
 
SQL> select count(*) from test_a;
 
  COUNT(*)
———-
   4000000 
 
 a表主键,B表外键。我们删除a表中的一行数据进行SQL_TRACE看看,删除过程中我明显感觉到一点点延时,正常来说应该非常快,因为只涉及到TEST_A表的主键索引的唯一扫描,看看TRACE
 delete test_a 
where
 it=10000000
call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.00          0          3          7           1
Fetch        0      0.00       0.00          0          0          0           0
——- ——  ——– ———- ———- ———- ———-  ———-
total        2      0.01       0.00          0          3          7           1
select /*+ all_rows */ count(1)    ———–这里看到了实际他统计了到底是否有IT=1000000的行,如果行数为0即可删除。不为0则报错
from
 “SYS”.”TEST_B” where “IT” = :1
call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.06       0.07          0       6074          0           1  —此处为逻辑读取。
——- ——  ——– ———- ———- ———- ———-  ———-
total        3      0.06       0.07          0       6074          0           1
所以可以看到,但是UPDATE却没有出现,但是UPDATE主键列应该也是同样的。
 
 然后测试第二点,对TEST_A表进行删除如下
 delete test_a where it>1000;
ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
—————- —————- ———- —- ———- ———- ———- ———- ———- ———-
000007FF28CDB180 000007FF28CDB1A0        165 XR            4          0          1          0       7471          0
000007FF28CDB218 000007FF28CDB238        165 CF            0          0          2          0       7471          0
000007FF28CDB348 000007FF28CDB368        165 RS           25          1          2          0       7467          0
000007FF28CDB478 000007FF28CDB498        166 RT            1          0          6          0       7467          0
000007FF28CDB510 000007FF28CDB530        164 TS            3          1          3          0       7455          0
000007FF28CDB6D8 000007FF28CDB6F8        167 MR            1          0          4          0       7464          0
000007FF28CDB770 000007FF28CDB790        167 MR            2          0          4          0       7464          0
000007FF28CDB808 000007FF28CDB828        167 MR            3          0          4          0       7464          0
000007FF28CDB8A0 000007FF28CDB8C0        167 MR            4          0          4          0       7464          0
000007FF28CDB938 000007FF28CDB958        167 MR          201          0          4          0       7464          0
000007FF27E8DB40 000007FF27E8DB68        158 TM        51907          0          3          0        214          0
000007FF27E8DC40 000007FF27E8DC68        158 TM        51914          0          4          0          0          0 –偶尔的访问时才需要,如果外键表没加索引时间就很长,如果有索引就更快一些
000007FF27F1F618 000007FF27F1F650        158 TX       262161        327          6          0        214          0
在执行期间偶尔对表TEST_B加MODE 4 S的锁。这种情况下肯定堵塞DML SX 和S不兼容,这样死锁得可能性大大增加,
如果其他的绘画进行修改TEST_B外键表比如DELETE,可以成功,但是一旦更改主表的需要获得mode4的锁那就堵塞这里就是堵塞158
另外开一个会话
delete test_b;
这时就堵塞了第一个会话,如果这个会话再
DELETE TEST_A;那么死锁出现
000007FF27E8DB40 000007FF27E8DB68        158 TM        51907          0          3          0        908          0
000007FF27E8DC40 000007FF27E8DC68        138 TM        51907          0          3          0          3          0
000007FF27E8DD40 000007FF27E8DD68        138 TM        51914          0          3          0          3          1
000007FF27E8DE40 000007FF27E8DE68        158 TM        51914          0          0          4        208          0
000007FF27F1F618 000007FF27F1F650        158 TX       262161        327          6          0        908          1
000007FF27F31E78 000007FF27F31EB0        138 TX       196646        324          6          0        208          0
这就是138 158互相堵塞的时候
000007FF27E8DB40 000007FF27E8DB68        158 TM        51907          0          3          0        914          0
000007FF27E8DC40 000007FF27E8DC68        138 TM        51907          0          3          0          9          0
000007FF27E8DD40 000007FF27E8DD68        138 TM        51914          0          3          0          9          0
000007FF27F1F618 000007FF27F1F650        158 TX       262161        327          6          0        914          1
000007FF27F31E78 000007FF27F31EB0        138 TX       196646        324          6          0        214          0
随后ORACLE自动释放
在我测试期间就模拟出一次死锁
Thu Oct 17 11:40:02 2013
ORA-00060: Deadlock detected. More info in file c:oracleproduct10.2.0adminbendiudumpbendi_ora_6964.trc.
不管怎么样还是应该对外键加索引,性能提高是明显的,特别是当外键表非常庞大的时候,同时速度快了锁定时间也就降低了。
不仅如此外键列(更改非外键字段也会)DELETE insert UPDATE也会导致主表锁定(TM加锁mode 3 sx),这种情况下主表的任何UPDATE,DELETE将被堵塞 因为他需要MODE 4 s的锁,但是INSERT 不会,insert加锁
mode 2 ss的锁,
另外我同样认为修改增加任何一行外键的数据,因为进行一次对主键索引的扫描,来判断这个记录是否在主键中,如果在则成功,如果不在则失败。
这一点在TRACE中任然没有找到但是可以借用如下的办法
select object_name,STATISTIC_NAME,value from v$segment_statistics  where object_name in (‘PK_PRI’,’TEST_A’) order by value;
可以看到如下:
记录开始时候的
TEST_A                         logical reads                                                       4166096
PK_PRI                         logical reads                                                      27876832
记录完成后
TEST_A                         logical reads                                                       4166096
PK_PRI                         logical reads                                                      39887152
可以看到确定访问过OK_PRI这个索引。
主外键的关系比较复杂,需要有好好的理解。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7728585/viewspace-774524/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/183318.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code