oracle索引使用案例,Oracle开发建议系列第四期 ——索引使用不佳典型案例

索引不佳的表现

对于联机交易系统，主要交易查询都必须走索引。索引不佳或未创建索引时，通常会出现SQL执行计划有全表扫描， INDEX SKIP SCAN，执行计划改变，SQL逻辑读高，SQL物理读等相关表现，造成Oracle内部资源争用，产生Oracle等待事件，引发CPU，内存等系统资源增高，最终会导致业务响应缓慢。

案例大放送

结合上期分享索引使用的建议，下面与大家分享一下日常遇到一些索引使用不佳的案例。

(1) 索引缺失造成全表扫描，物理读高，响应时间慢

sql_id 27qn1ubzucqvp 物理读高，执行时间长。

BP_AND_PR_REL_HIST_JRNL表大小17G，表在列JRNL_CRT_TM 没有索引，全表扫描，造成物理读高，SQL性能差。经查看，过滤条件JRNL_CRT_TM的distinct值较大，如下图，该列应创建索引。

说明：这种情况在执行次数少或表中数据少时不会表现出异常，上线后当业务量增加，表中数据增加后就会造成业务影响。对大表查询的SQL语句执行计划走全表扫描最容易导致业务缓慢，同时也和并发量相关。所以，需要我们开发人员未雨绸缪，在开发过程中要有创建索引的意识。

(2) 复合索引组合字段不合理，选择性不高，逻辑读高

sql_id g95h505hhxb89语句逻辑读很高，查看索引IDX_M_SBSTRCVPY_BTCH_INF_HST_3如下：

该索引对应单列ETRUNT_ID，语句中过滤条件字段为etrunt_jrnl_no和ETRUNT_ID，查看列信息如下：

可以发现建立etrunt_jrnl_no和ETRUNT_ID的复合索引其唯一值更高，选择性会更好，能有效降低逻辑读。

(3) 复合索引组合字段顺序不合理，执行计划有INDEX SKIP SCAN，逻辑读高

如上图，某系统一条主要SQL，逻辑读高，执行计划中有INDEX SKIP SCAN，索引如下：

查看列上统计信息，发现列MRCH_ID选择性明显好于BACK_TRAD_DATE，BACK_TRAD_DATE distinct值太低，不适合做前导列。

这种情况下，调整复合索引为(MRCH_ID，BACK_TRAD_DATE)。

(4) 存在冗余索引

IND_AUTO_TSK_SPINF_TBL_N1索引字段(CCSTTRID),IND AUTO_TSK_SPINF_TBL_N2索引字段(CCSTTR_ID, PD_CMPT_NO，APL _BTCH_NO)，IND_AUTO_TSK_SPINF_TBL_N1为冗余索引，针对上述insert语句，需要维护索引，冗余索引影响SQL性能。

(5) 繁忙表上索引未分区或分区不合理

繁忙表索引应进行HASH分区，从而避免热点，减少索引分裂的大量出现。 应合理选择分区字段，避免HASH不均。

例如某系统数据库突然有几分钟响应时间长，经查出数据库的主要等待事件：

分析可以发现：索引分裂引发数据库性能问题，该系统时常出现该现象，进一步分析定位到索引THDPTY_DBDF_LIST_PartC。这个索引是为HASH分区索引，，以("TRAD_DATE")为分区建，导致数据分布不均匀，查看每个分区记录数如下。因为并发是在同一时间发生的，针对每天操作不可能均匀散到8个分区，HASH失去意义，需要调整分区建。

(6) 外键字段未建索引

某系统时常报dead lock，影响业务。通过alert log和trace文件，获得如下信息：

判定跟表INFO_APP_COMP有关。获得该表建表语句，发现表INFO_APP_COM的INFO_PLATFORM_ID列上有外键，其对应父表为INFO_PLATFORM。

查看INFO_APP_COM外键列上没有索引。

所以，对于父表INFO_PLATFORM的删除或更新操作，会锁定INFO_APP_COMP上所有记录，当INFO_APP_COMP表上发生修改或更新操作时就容易发生死锁。这种情况必须在外键列上创建索引。

总结语

希望通过上述具体案例，让大家更好理解上一期索引使用的一些建议要求，对Oracle开发工作起到帮助