Oracle数据库索引原理分析

1、相关概念介绍

统计信息：描述数据库中表，索引的大小，规模，数据分布状况等的一类信息。
执行计划：一条查询语句在Oracle中的执行过程或访问路径的描述，即执行查询语句的逻辑或方式
HINT：开发人员控制执行计划的接口，可以给sql语句执行具体的执行计划

2、详细介绍

Oracle在执行查询语句的时候，会根据统计信息对sql进行评估，选择出最有的执行计划去执行sql语句。所以是否选择使用索引，和统计信息有关。

2.1、统计信息

1、统计信息在工作日22:00-02:00会更新，双休日可能全天统计
2、执行查询时，数据库会根据统计信息评估，选择出相应的执行计划

2.2、执行计划

2.3、索引（B树）

索引建立后，会生成一个索引表，使索引和表中数据形成映射；若表中的数据有更新而影响到了索引，那么索引表则要重新生成，所以索引不利于插入、变更频繁的表。

INDEX RANGE SCAN 范围扫描

首先定位初始位置，然后向后遍历，查询出所有附和条件的索引

INDEX FULL SCAN 索引全扫描

一般在统计表数据总条数时使用

注意点：
单列索引不存储null,
多列索引允许存储null，但是不可以全部为null

2.4、表连接算法

2.4.1、Next Loop

缺点：无索引的情况下，表过大，不可用
适用于OLTP类型的交易，返回处理少量的数据

算法结构：

For I in (    语句1)
Do
     语句2
done

2.4.2、Hash Join

适用于OLAP型交易，返回或处理大量数据

缺点：第一张表的关联值过多的相同，消耗CPU

原理步骤：
1、先访问table1，按表关联字段进行hash算法
       例如：取余（id为连接字段）
          若mod(table1.id, 1000) = 1,放在第1个桶中
          若mod(table1.id, 1000) = 2,放在第2个桶中
         若mod(table1.id, 1000) = 3,放在第3个桶中
              …………………………

2、访问table2，按照同样的规则，与对应桶中的记录进行比较，相同，即关联上

2.5、简单案例

场景介绍：
第一天：创建table，插入200W条数据，查询正常，20:00使用delete删除表中的所有记录
第二天：插入200W条数据，查询时，速度相较于第一天很慢（数量级上的差别）

分析：
第一天：由于表刚创建，所以table的统计信息为null,所以评估执行计划时，没有依据统计信息
当天的22:00-02:00，更新table的统计信息，所以对应的统计信息变为数据行数0

第二天，插入200W条数据后，执行查询时，数据库进行评估时，认为table中的行数为0，所以选择出了较为低效率的执行计划，才会出现上述情况

3、Oracle结构（补充）

图解：
Database: 数据库

Tablespace：表空间是一个逻辑容器，它和数据文件关联起来，一个表空间至少有一个数据文件与之关联。一个表空间可以有多个段，一个段只能属于一个表空间。
Segment：段，是一种在数据库中消耗物理存储空间的任何实体（一个段可能存在于多个数据文件中， 因为物理的数据文件 是组成逻辑表空间的基本物理存储单位）如果一个表不进行分区，那么一个表就是在一个segment中。如果一个表进行多个分区，那么每一个分区就在一个segment。

Block:块，Oracle存储的最基本单位，由DB_BLOCK_SIZE制定，通常为8KB，也可以定义为2KB,4KB,16KB,32KB,64KB等，磁盘最小存储单位是sector（512BYTE 操作系统概念），Oracle数据块由连续的sector组成，Oracle读写单位是数据块，应尽量设置BLOCK大小为磁盘数据块大小的整数倍，避免IO浪费

Extent：区又叫盘区，是数据文件中一个连续的分配空间，它比块要大，由块组成。有些对象分配空间时可能至少需要两个盘区，比如回滚段，而这两个盘区不一定要求相连。区的大小从一个块到2GB不等

oracle中存储的层次结构总结如下：

1、数据库由一个或多个表空间组成
2、Tablespace可以理解为segments的逻辑容器(包含多个段)，由一个或多个数据文件组成
3、Segment是db对象占有空间的最小单位，一个对象可以包含0个、1个或多个segment,段由一个或多个区组成
4、Extent是数据文件中一个连续的分配空间，由一个或多个块组成。Extent是空间分配的最小单位，oracle都是以extent为单位为对象分配空间的
5、Block块是数据库中最小、最基本的空间单位，是数据库使用的最小的I/O单元，数据的读取都是以block的整数倍进行的，通常一个db block通常是一个os block的整数倍大小
6、Schema可以理解为用户下的所有对象的集合，每个用户都有一个对应的方案