关于Oracle 数据块、B树索引和5种索引扫描

python与大数据分析

发布于 2022-03-11 16:10:36

8950

发布于 2022-03-11 16:10:36

Oracle中有80%的性能问题，是由20%的优化技术所决定。索引策略，执行路径，就是绝大多数性能问题的那20%的关键因素。所以对Oracle数据表和索引的了解是至关重要的。

数据块是Oracle最小的存储单位，Oracle数据存放在块中，一个块占用一定的磁盘空间。数据块的大小一般是操作系统块大小的整数倍，缺省是8K。同样对数据表而言，空间管理的最小单位也是数据块。Oracle每次执行I/O的时候，都是以数据块为单位的，而不是以行为单位。“块”是Oracle读写数据的最小单位或者最基本的单位。

数据块由数据块头（header/Common and Variable),表目录区（Table Directory）,行目录区（Row Directory）,可用空间区（Free Space）和行数据（Row Data）五部分组成，如下图所示：

数据块头（包括标准内容和可变内容）：数据块头（header）中包含了此数据块的概要信息，例如块地址（block address）及此数据块所属的段（segment）的类型（例如，表或索引）。

表目录区：如果一个数据表在此数据块中储存了数据行，那么数据表的信息将被记录在数据块的表目录区中。

行目录区：此区域包含数据块中存储的数据行的信息在行数据区中的地址）。当一个数据块的行目录区空间被使用后，即使数据行被删除，行目录区空间也不会被回收。

可用空间区：在插入新数据行，或在更新数据行需要更多空间时，将使用可用空间区中的空间。。

行数据：数据块中行数据区包含了表或索引的实际数据。一个数据行可以跨多个数据块。

rowid也是oracle中一个比较重要的概念，rowid是oracle数据库的表中的每一行数据的唯一的标识符，在oracle内部通常就是使用它来访问数据的。rowid由10个字节或者是80个位二进制位构成。该值表明了该行在oracle数据库中的物理具体位置。可以在一个查询中使用rowid来表明查询结果中包含该值。

索引在表中的作用，相当于书的目录对书的作用。索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高查询效率而引入，是一个独立于表的对象，可以存放在与表不同的表空间中。索引记录中存有索引关键字和指向表中数据的指针（地址）。对索引进行的I/O操作比对表进行操作要少很多。索引一旦被建立就将被Oracle系统自动维护，查询语句中不用指定使用哪个索引。

索引是提高数据查询最有效的方法，也是最难全面掌握的技术，因为正确的索引可能使效率提高10000倍，而无效的索引可能是浪费了数据库空间，甚至大大降低查询性能。

索引的优点：

①可以加快数据的检索速度。

②可以加速表和表之间的连接。

③使用分组和排序子句进行数据检索时,可以减少查询中分组和排序的时间。

索引的缺点：

①创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

②索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。

③当对表中的数据进行增加、删除和修改的时候，索引需要动态的维护，这样就降低了数据的维护速度。

Oracle B树索引和表的关系

接下来模拟两张百万级的数据表，一张表不建任何索引，另一张表建七个索引，分别为唯一索引、普通索引，字段不允许为空的普通索引，高选择性索引，低选择性索引，组合索引，位图索引。看看两种表的插入速度情况。

代码示例

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as usera@ORCL

SQL> 
SQL> SET TIMEING ON;
Cannot SET TIMEING
SQL> DROP TABLE indextest;

Table dropped

Executed in 0.251 seconds

SQL> DROP TABLE indextestnoindex;

Table dropped

Executed in 0.019 seconds

SQL> CREATE TABLE indextestnoindex as
  2  SELECT a.*,rownum uniq_index,rownum popu_index,rownum popu_index_nonull,b.rn high_index,mod(rownum,100000) index100000,mod(rownum,10000) index10000, mod(rownum,1000) index1000,mod(rownum,2) low_index,mod(rownum,2) bitmap_index
  3    FROM dba_tables a,(SELECT level,ROWNUM rn FROM DUAL CONNECT BY ROWNUM<=500) b
  4  WHERE 1=0;

Table created

Executed in 0.101 seconds

SQL> CREATE TABLE indextest as
  2  SELECT a.*,rownum uniq_index,rownum popu_index,rownum popu_index_nonull,b.rn high_index,mod(rownum,100000) index100000,mod(rownum,10000) index10000, mod(rownum,1000) index1000,mod(rownum,2) low_index,mod(rownum,2) bitmap_index
  3    FROM dba_tables a,(SELECT level,ROWNUM rn FROM DUAL CONNECT BY ROWNUM<=500) b
  4   WHERE 1=0;

Table created

Executed in 0.102 seconds

SQL> INSERT INTO indextestnoindex
  2  SELECT a.*,rownum uniq_index,rownum popu_index,rownum popu_index_nonull,b.rn high_index,mod(rownum,100000) index100000,mod(rownum,10000) index10000, mod(rownum,1000) index1000,mod(rownum,2) low_index,mod(rownum,2) bitmap_index
  3    FROM dba_tables a,(SELECT level,ROWNUM rn FROM DUAL CONNECT BY ROWNUM<=500) b ;

1415500 rows inserted

Executed in 62.623 seconds

SQL> COMMIT;

Commit complete

Executed in 0.014 seconds

SQL> ALTER TABLE Indextest modify(popu_index_nonull not null);

Table altered


Executed in 0.027 seconds

SQL> CREATE UNIQUE INDEX indextest_uniq_index on indextest(uniq_index);

Index created

Executed in 0.008 seconds

SQL> CREATE INDEX indextest_popu_index on indextest(popu_index);

Index created

Executed in 0.003 seconds

SQL> CREATE INDEX indextest_popu_index_nonull on indextest(popu_index_nonull);

Index created

Executed in 0.003 seconds

SQL> CREATE INDEX indextest_high_index on indextest(high_index);

Index created

Executed in 0.004 seconds

SQL> CREATE INDEX indextest_low_index on indextest(low_index);

Index created

Executed in 0.004 seconds

SQL> CREATE INDEX indextest_muti_index on indextest(index1000,index10000,index100000);

Index created

Executed in 0.004 seconds

SQL> CREATE BITMAP INDEX indextest_bitmap_index on indextest(bitmap_index);

Index created

Executed in 0.006 seconds

SQL> INSERT INTO indextest
  2  SELECT a.*,rownum uniq_index,rownum popu_index,rownum popu_index_nonull,b.rn high_index,mod(rownum,100000) index100000,mod(rownum,10000) index10000, mod(rownum,1000) index1000,mod(rownum,2) low_index,mod(rownum,2) bitmap_index
  3    FROM dba_tables a,(SELECT level,ROWNUM rn FROM DUAL CONNECT BY ROWNUM<=500) b ;

1415500 rows inserted

Executed in 122.238 seconds

SQL> COMMIT;

Commit complete

Executed in 0.007 seconds

SQL> analyze table indextest estimate statistics sample 20 percent;

Table analyzed

Executed in 6.761 seconds

SQL>

大致可以看到有索引的情况下，数据的插入速度要比没索引的表慢一倍；如果是逐条提交的话，速度会更慢。

代码示例

SELECT a.table_name,a.blocks,a.empty_blocks,a.num_rows,a.avg_row_len,a.num_rows*a.avg_row_len/8/1024 need_blocks
  FROM dba_tables a 
 WHERE table_name='INDEXTEST';
SELECT a.segment_name,a.segment_type,a.file_id,a.block_id,a.bytes/1024/1024 as bytes,a.blocks ,a.*
  FROM dba_extents a
 WHERE a.segment_name ='INDEXTEST';
SELECT a.segment_name,a.segment_type,a.blocks,a.header_block,a.bytes/1024/1024 as bytes
  FROM dba_segments a 
 WHERE segment_name='INDEXTEST';
SELECT a.index_name,a.table_name,a.uniqueness,a.blevel,a.leaf_blocks,a.distinct_keys,a.avg_leaf_blocks_per_key albpk,a.avg_data_blocks_per_key adbpk,a.clustering_factor,a.num_rows,a.degree
 FROM DBA_INDEXES a 
 WHERE table_name='INDEXTEST';
SELECT a.index_name,a.table_name,a.column_name,a.column_position
  FROM dba_ind_columns a
 WHERE table_name='INDEXTEST';
SELECT a.table_name,a.column_name,a.data_type,a.data_length,a.num_distinct,a.low_value,a.high_value,a.density,a.num_nulls
  FROM dba_tab_columns a
 WHERE table_name='INDEXTEST';

这里最关键的表应该还是dba_indexes,

总结：

1.唯一索引和非唯一索引的root block和branch block结构大体基本一致。

2.唯一索引的root block和branch block不需要存储rowid或rowid前缀。

3.非唯一索引的索引条目=索引键值+rowid，而唯一索引的索引条目=索引键值,ROWID存储在DATA。

4.唯一索引的leaf block会比非唯一索引小一些，差不多是每条少一个字节。

再聊聊Oracle的索引扫描方式，这个是影响Oracle查询效率的最关键的因素，Oracle的索引扫描方式包括以下五种方式：

1、INDEX UNIQUE SCAN【唯一索引扫描】

仅适用于where条件是等值查询的SQL，因为扫描对像是唯一索引，所有扫描结果最多只返回一行记录

2、INDEX RANGE SCAN【索引范围扫描】

如果查询条件相同的情况下，索引范围扫描因需要确定扫描的终点，所以至少会比唯一索引扫描多1个逻辑读

3、INDEX FULL SCAN【索引全扫描】

默认情况下，索引全扫描要从左到右依次顺序扫描目标索引所有叶子块的所有索引行，这个需要注意的事，只是扫描所有叶子块而不是所有分支块，因为是从左到右依次扫描，所以索引扫描的执行结果也是有序的，并且是按照索引的键值排序的，这就意为着索引全扫描不存在排序操作，但同时缺点也很明显，结果的有序性就决定了索引全扫描是不能够并行执行的，并且通常情况下索引全扫描使用的是单块读，索引的全扫描有一个前提是目标索引至少有一个索引键值列的属性是not null

对于索引全扫描，会话会产生db file sequential reads事件。

具体情况分析，对于索引全扫描而言，限制比较多：

对于单一列建立的索引（单一索引），当该索引列有非空约束时，在具体检索中只检索该列全部数据，会使用“索引全扫描”。

对于单一列建立的索引（单一索引），当该索引列无非空约束时，在具体检索中只检索该列全部数据，且是对该列的统计（count）或者非空条件查询（is not null），会使用“索引全扫描”。

对于单一列建立的索引（单一索引），当该索引列无非空约束时，在具体检索中只检索该列全部数据，且是对该列的常规查询，不会使用“索引全扫描”。（这是因为对于oracle索引，对于列中存在的null值不记录在b-tree索引中）

对于多个列建立的索引（组合索引），当该索引列有非空约束时，在具体检索中只检索组合索引中涉及的全部列或者部分列的全部数据，会使用“索引全扫描”。

对于多个列建立的索引（组合索引），当该索引列无非空约束时，在具体检索中只检索组合索引中涉及的全部列或者部分列的全部数据，且是对这些相关列的统计（count）或者非空条件查询（is not null），会使用“索引全扫描”。

对于多个列建立的索引（组合索引），当该索引列无非空约束时，在具体检索中只检索组合索引中涉及的全部列或者部分列的全部数据，且是对该列的常规查询，不会使用“索引全扫描”。

4、INDEX FAST FULL SCAN【索引快速扫描】

基本的概念与索引全扫描相似，与索引全扫描的区别在于：

1、索引快速全扫描只适用于CBO

2、索引快速扫描可以使用多块读，也可以并行执行

3、索引快速全扫描的执行结果不一定是有序的

对于索引快速扫描，会话会产生db file scattered reads事件。

5、INDEX SKIP SCAN【索引跳跃式扫描】

如果表上有一个组合索引，如indextest_muti_index(index1000,index10000,index10000)，如果查询条件是index100000=1 and index10000=1/index10000=1/index100000=1，这种相当于跳过索引的前缀还能使用indextest_muti_index，因此称为索引跳跃扫描，其本质是ORACLE进行了改写，但这种有限制如下：前缀部分distinct的值数据较少

当进行index full scan的时候 oracle定位到索引的root block，然后到branch block（如果有的话），再定位到第一个leaf block, 然后根据leaf block的双向链表顺序读取。

它所读取的块都是有顺序的，也是经过排序的。而index fast full scan则不同，它是从段头开始，读取包含位图块，root block, 所有的branch block, leaf block，读取的顺序完全有物理存储位置决定，并采取多块读，每次读取db_file_multiblock_read_count个。

索引全扫描不得读取索引段头，而索引快速全扫描要读取索引段头。从效率上讲ndex fast full scan会高一些

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2020-12-05，如有侵权请联系 cloudcommunity@tencent.com 删除

oracle