1.
测试方法
在
测试环境下，创建两张表，
PK1和FK1，通过外键PK_ID进行关联，
检查外键无索引和有索引情况下对数据库的影响
2.
测试环境
如下所示，建立两张表，主表为
PK1,
子表为
FK1
，主表主键为
ID
列，子表主键也为
ID
列，外键约束为
PK_ID,references
为
PK1
的
ID
列。
主表键
子表键
主表列
子表列
主表和子表的数据
3.
测试结果汇总
|
|
|
|
|
外键无索引 |
a. 子表有删改操作。
b. |
a. 子表有删改操作。
b |
|
外键有索引 |
a. 子表有删改操作。
b. |
未出现主表 |
(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 /*+ all_rows */ 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
********************************************************************************
