1. 测试方法
在 测试环境下，创建两张表， PK1和FK1，通过外键PK_ID进行关联，
检查外键无索引和有索引情况下对数据库的影响
2. 测试环境
如下所示，建立两张表，主表为 PK1, 子表为 FK1 ，主表主键为 ID 列，子表主键也为 ID 列，外键约束为 PK_ID,references 为 PK1 的 ID 列。
主表键
子表键
主表列
子表列
主表和子表的数据
3. 测试结果汇总
|
测试大类 |
测试内容 |
测试结果 |
|
外键无索引 |
a. 子表有删改操作。 同时 主表有删操作，或者更新主键的操作。 b. 删除主表记录或主子表关联查询 |
a. 子表有删改操作。 同时 主表有删操作，或者更新主键的操作。 满足以上两个条件会出现主表操作hang状态。 b 外键不建索引，则删除主表记录或主子表关联查询，都会进行子表的全表扫描。 |
|
外键有索引 |
a. 子表有删改操作。 同时 主表有删操作，或者更新主键的操作。 b. 删除主表记录或主子表关联查询 |
未出现主表 hang和相关全表扫描 |
(1) 外键没有索引，会导致子表产生表锁：
a. 子表有删改操作。
b. 主表有删操作，或者更新主键的操作。
满足以上两个条件会出现主表操作hang 状态。
(2) 外键不建索引，则删除主表记录或主子表关联查询，都会进行子表的全表扫描。
(3) 只有外键创建索引， (1) 中的操作才不会出现锁或 hang 状态， (2) 中的操作才有可能使用索引。
4. 测试场景
4.1. 外键无索引情况
1. 更新子表时，主表无法删除列或者更新主键列。
2. 删除子表列时，主表无法删除列或者更新主键列
同时进行关联查询时，会用到全表扫描
删除主表数据的话，还会对子表进行全表扫描
SQL ID: 6j3nfuym9sr2q Plan Hash: 715003257
delete from pk1
where
id=8
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 1 7 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 7 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 90
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 DELETE PK1 (cr=8 pr=0 pw=0 time=2968 us)
1 1 1 INDEX UNIQUE SCAN PK_ID (cr=1 pr=0 pw=0 time=47 us cost=0 size=13 card=1)(object id 76853)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 4.08 4.08
*******************************************************************************
SQL ID: 6j3sg0cgf9vvf Plan Hash: 92442012
select count(1)
from
"TEST"."FK1" where "PK_ID" = :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.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 7 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=604 us)
0 0 0 TABLE ACCESS FULL FK1 (cr=7 pr=0 pw=0 time=523 us cost=3 size=13 card=1)
******************************************************************************* *
4.2. 外键有索引情况
子表建立索引后
对子表的删除或者更新操作后，主表更新主键或者删除列不会获取表锁，可以正常执行。
同时关联查询时也会用到索引
对主表的删除也不会进行全表扫描
SQL ID: 6j3nfuym9sr2q Plan Hash: 715003257
delete from pk1
where
id=8
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 1 6 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 6 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 90
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 DELETE PK1 (cr=1 pr=0 pw=0 time=874 us)
1 1 1 INDEX UNIQUE SCAN PK_ID (cr=1 pr=0 pw=0 time=47 us cost=0 size=13 card=1)(object id 76853)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 9.67 9.67
********************************************************************************
