ITPub博客

首页 > 数据库 > Oracle > 外键无索引对数据库的影响以及增加索引后的效果

外键无索引对数据库的影响以及增加索引后的效果

原创 Oracle 作者:shadow_young 时间:2020-07-06 23:39:56 0 删除 编辑

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 /*+ 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

********************************************************************************


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

请登录后发表评论 登录
全部评论
oracle热爱者

注册时间:2020-07-06

  • 博文量
    9
  • 访问量
    8666