oracle 索引问题梳理

索引基础知识

索引概念

• 索引是为了加快数据的查找而创建的可选数据库对象
• 索引是建立在表的一列或多列上且进行排序的一种结构
• 通过指针快速定位数据行的方法，减少CPU和磁盘的I/O
• 索引是由Oracle自动使用和维护的，数据更新会自动传播到所有相关的索引
• 索引存在双面性，表面存在过多的索引会影响到DML语句的性能

索引类型

• 唯一索引

保证在定义索引的列中没有重复值
Oracle自动在表的主键列上创建唯一索引
唯一索引对应的字段上可以插入null值

语法：

create unique index idx_name on tab_name (col_name);

• 倒序索引

键值在索引上是倒叙存放的（由大到小）

语法：

create index idx_name on tab_name (col_name desc);

• 反向健索引

反转索引列键值的每个字节，比如：1234 1235 反转后：4321 5321
通常建立在值是连续增长的列上，防止索引在最右侧的叶子块争用

语法：

create index idx_name on tab_name (col_name) reverse;

• 函数索引

基于一个或多个列上的函数或者表达式创建的索引
通常会有额外的一些开销，比如空间、性能，但总体可控。

语法：

create index idx_name on tab_name (to_char(col_name,’yyyy-mm-dd’));

• 位图索引

一个键值存储指向多行数据的指针，可以存储null值
适合创建在低基数（cardinality）列上，但不适合频繁修改的列、多个会话并发修改的表

语法：

create bitmap index idx_name on tab_name (col_name);

获取sql执行计划

• 常用方法

PL/SQL Developer工具F5键
explain plan for
set autotrace on
set autotrace traceonly

索引范围扫描：INDEX RANGE SCAN
索引唯一扫描：INDEX UNIQUE SCAN
索引跳跃扫描：INDEX SKIP SCAN
索引全扫描：  INDEX FULL SCAN

索引失效场景

以下场景可能导致使用不上索引

字段不是索引的前导列
字段使用like且%放在前面
字段上存在函数或者数值运算
查询谓词条件使用 is null 或 <>
索引导致执行结果不正确
查询返回大量数据
统计信息不正确

例子

前提：
1.a、b、c都经常要被查询，并且有经常a、b、c组合查询的可能性
2.a列区分度不大，b和c都有区分度

思路：
1.分别建单列索引（a）（b）（c），但在大表把所有字段都列为索引显然不合理，且Oracle限制索引数32个
2.（a、b、c）组合索引，a作为先导列区分度不大，组合索引的第一列非常重要，区分度越高越靠前。
3.（b、c、a）组合索引，区分度较小的a只会徒增索引的开销
4.（b、c）组合索引，可以覆盖ab组合查询，bc组合查询，但ac组合因为c不是组合索引的先导列不能走索引
5.最终（b、c）组合索引+c单列索引是最优解

NULL值查询优化

查询语句

select * from test_tab where status is null;

数据分布

select count(*),status from test_tab group by status;
count(*)   status
----------  ---------
1995000  Y
500

索引设计

create index test_tab_idx1 on test_tab(status) online parallel 4;
alter index test_tab_idx1 noparallel;

表结构

OBJECT_ID   NOT NULL  NUMBER
OWNER                          VARCHAR2(20)
STATUS                          VARCHAR2(10)
CREATED                       DATE

索引重新设计

create index test_tab_idex2 on test_tab(status,object_id) online parallel 4;
alter index test_tab_idx2 noparallel;

索引热块争用问题

最右单调增长引起的叶子块争用问题

索引的特点是从小到大排好序，因此最右边的叶子块存的是最大的值
对于单调增长的字段，新插入的一定是最大的值
新插入的最大值必定需要插入最右边的叶子块
当多个进程并发插入时，则最右边的叶子块成为热点块
常见的单向增长字段有序列（sequence）、时间（sysdate）

解决方案

哈希分区表
哈希分区索引
反向键索引

最大值最小值查询

查询SQL

select max(object_id) from test_tab where created=to_date('2021-12-12','yyyy-mm-dd');

索引设计

create index test_tab_idx1 on test_tab(create,object_id) online parallel 4;
alter index test_tab_idx1 noparallel;

查询SQL

select max(object_id) as max_id,min(object_id) as min_id from test_tab;

object_id 为主键索引

优化SQL

select (select min(object_id) from test_tab) min_id,(select max(object_id) from test_tab) max_id from dual;

优化实例

在日均交易量较平均的情况下，wkdt具有较强区分度

当扩大日期范围，日期的区分度不能体现

使用常用且比较由区分度的字段payeracct和payeeacct做组合索引