外键加索引问题

根据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:\oracle\product\10.2.0\admin\bendi\udump\bendi_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://blog.itpub.net/7728585/viewspace-774524/