索引的概念
在使用 oracle 的过程中 , 我们就不能不考虑性能和 SQL 优化 , 而正确的使用索引在优化过程中是很关键的 .
索引是建立在表的一列或多列上的辅助对象 , 它有助于快速访问该表中的数据 . 索引由于其内在的结构 , 具有某些内在的开销 , 这些开销依赖于为了检索由索引中 ROWID 指定的行所访问的表中的块数 , 需要特别注意的是 : 这个开销可能会超过进行顺序全表扫描的成本 .
Oracle 使用 B* 树存储索引 ( 包括位图索引 ). 索引的顶点称为根节点 , 第二级节点称为分支节点 , 最低级的节点是叶节点 . 上级索引块 ( 分支节点 ) 包含了指向下级索引块的索引数据 . 最低级索引块 ( 叶节点 ) 包含每个值的索引数据和一个相对应的用来确定该实际行位置的 ROWID. 叶节点本身使用双向链表连接 , 允许叶节点双向切换 .
二 , 索引的文件存储
索引文件在存储器上分为两个区：索引区和数据区。索引区存放索引表，数据区存放主文件。建立索引文件的过程：
（ 1 ） 按输入记录的先后次序建立数据区和索引表。其中索引表中关键字是无序的
（ 2 ） 待全部记录输入完毕后对索引表进行排序，排序后的索引表和主文件一起就形成了索引文件。
【例】对于表 10.2 的数据文件，主关键字是职工号，排序前的索引表如表 10.3 所示，排序后的索引表见表 10.4 ，表 10.2 和表 10.4 一起形成了一个索引文件。
drop table t_index_test;
create table T_index_test(
f1 integer ,
f2 integer ,
f3 varchar2 ( 400 )
);
insert into t_index_test
select rownum , mod ( rownum , 100 ),lpad( rownum , 300 , '-' )
from dba_objects, dba_tab_cols
where rownum <= 10000 ;
commit ;
create index ind_index_test_1 on t_index_test(f1);
analyze index ind_index_test_1 validate structure ;
select * from index_stats where name = upper( 'ind_index_test_1' );
| 字段名称 | 字段描述 | 字段内容 |
| HEIGHT | 索引树高度 | 2 |
| BLOCKS | 分配给索引的块数 | 32 |
| NAME |
| IND_INDEX_TEST_1 |
| PARTITION_NAME |
|
|
| LF_ROWS | 索引叶子节点个数 | 10000 |
| LF_BLKS | 叶子节点块数 | 21 |
| LF_ROWS_LEN | 叶子节点总长度 | 149801 |
| LF_BLK_LEN | 平均每个叶子块的大小 | 7980 |
| BR_ROWS | 根节点指针个数, 就是说根节点中有20 个指针指向叶子节点 | 20 |
| BR_BLKS | 根节点个数 | 1 |
| BR_ROWS_LEN | 根节点总长度 | 220 |
| BR_BLK_LEN |
| 8012 |
| DEL_LF_ROWS | 删除的叶子节点行数 | 0 |
| DEL_LF_ROWS_LEN |
| 0 |
| DISTINCT_KEYS | 不同值总数 | 10000 |
| MOST_REPEATED_KEY |
| 1 |
| BTREE_SPACE | 分配给索引的字节数 | 175592 |
| USED_SPACE | 索引已经使用的字节数 | 150021 |
| PCT_USED |
| 86 |
| ROWS_PER_KEY | 每个字段的平均个数 | 1 |
| BLKS_GETS_PER_ACCESS |
| 3 |
| PRE_ROWS |
| 0 |
| PRE_ROWS_LEN |
| 0 |
| OPT_CMPR_COUNT |
| 0 |
| OPT_CMPR_PCTSAVE |
| 0 |
可以看到，该所引高度为 2 ，只有 1 个 branch 块，同时也是 root 根节点，同时有 21 个 leaf 块。
select extent_id,file_id,block_id,blocks from dba_extents where segment_name=upper( 'ind_index_test_1' )
| EXTENT_ID | FILE_ID | BLOCK_ID | BLOCKS |
| 0 | 33 | 12073 | 8 |
| 1 | 33 | 12081 | 8 |
| 2 | 33 | 12089 | 8 |
| 3 | 33 | 12097 | 8 |
如何建立最佳索引
何时使用索引
假定索引的唯一目的是减少 IO 操作 , 如果一个查询使用索引时相对于全表扫描执行了更多的 IO 操作 , 则使用索引的意义会明显降低 .
例如 , 假设有一个拥有 1000000 行的表存储在 5000 个块中 , 某个给定的查询需要的结果分布在其中 4000 个数据块中 , 这种情况下 , 建立和使用这一列上的索引肯定不是最佳的 .
如果一个拥有 1000 行的表经历了大量的重复插入和删除操作后 , 表的高水位标记线将升高 , 因为 delete 操作不能收回已经使用的数据块 . 如果高水位标记线为 1000, 而实际记录存储在其中 100 个数据块中 , 这时使用索引是有意义的 . 因为被访问的数据块的数量和执行 IO 操作的数量明显少于执行全表扫描的数量 .
什么是最佳索引
较好的索引 ( 数据按照索引组织 , 在索引中顺序的内容在表中也相邻存储 . 这样之需要读取较少的数据块就可以完成检索任务 )
A---------7
A---------8
B---------8
B---------8
C----------8
C----------9
较差的索引 ( 索引中相邻的数据在表中存储位置相隔较远 , 导致每次读取了多余的重复数据块 )
A---------1357
A---------2
B---------9878
B---------38
C----------1008
C----------9
最佳索引的参数 (CF)
| 什么是 Clustering Factor | |
|
问题和答案
1, 什么是 Index clustering Facotr(CF).
Index CF 是一个 CBO 的统计值 , 这个值标示表中两行记录的距离与索引中两行记录的距离的比值 . 可以大致理解为 ( rowid(row1) – rowid(row2))/(rowed(index1) – rowed(index2)).
2, 为什么 Index CF 值越小越好 .
根据上面的定义描述 , 我们知道 , 这个值越小 , 索引中两个相邻值在表中存储的位置越接近 , 这样 oracle 在根据根据索引范围得到存储记录的位置的范围越小 . 所需要读取的数据块数就越少 , 所以索引的性能就越高 .
3, 使用 exp/imp 或者 table/index move 可以帮助减少 Index CF 值吗 ?
答案是否定的 , 这两种方式都对 index CF 没有改变 .
Ok, 那么我们就可以理解为 , table/index move 虽然可以收回没有记录的数据块 , 但这个过程并不对数据记录排序后重新存储 , 而只是简单地将几个相邻的空闲块中的内容写入新块中 .
4, 怎么做才能减少 index CF
只有对结果记录排序后重新 reload 到表中才能减少这个值 .
5, 如果表中的索引不止一个 , 怎么办 ?
如果表的索引不止一个 , 我们不可能同时让所有的 index CF 值减少 , 而只能通过排序 reload 减少某一个或者几个索引的 index CF 值 .
6, 有没有什么办法可以避免产生高的 index CF values?
可以将表放在 keep pool 中 .
…
7, 减少 index CF values 的方式 .
使用外部排序特性 , 对表数据按照索引排序后重新读入 .
或者使用 create table as select from table order by 的方式 .
有效使用索引的几个问题
以下问题的答案有助于建立最佳索引 .
1, 与全表扫描相比 , 索引扫描需要执行多少块 IO 操作 .
如果知道这个问题的答案 , 就会立即知道建立和使用一个索引是否具有性能意义 .
2, 用于特定表中的数据访问的最常用列组合是什么 ?
研究应用程序代码 , 如果程序代码不容易看懂 , 则查看 V$SQLAREA 或 V$SQLTEXT, 并分析最常用的 SQL 语句 . 查找在 V$SQLAREA 中具有较高执行次数的语句 , 并查找它们的 where 子句的成分 .
3, 对打算在其上建立索引的一组给定的列 , 其选择性是什么 ?
如果一些列始终有值并且相对唯一 , 则他们应该是索引的前导列 . 为建立索引 , 按照可能具有唯一值的概率 , 对这些列进行降序排列 .
4, 在 where 子句中引用的所有列都需要进行索引吗 ?
具有很低的数据基数 , 或者可能具有空值的列不适合做索引列 .
5, 索引所基于的表主要用于事务处理还是查询 ?
如果它是事务处理的表 , 则需要确定由于给出附加索引对事务处理的潜在负面影响 .
关于建立最佳索引的答案不是轻易得来的 ; 它们相当复杂 , 但是 , 上述的问题将指出正确的路径 . 从性能的立场上 , 应该了解并朝着它努力的一件事情是如何使使用索引的开销最小化 , 使得索引真正对性能有帮助而不是阻碍性能 .
四 , 导致索引无效的情况
限制索引是一些没有经验的开发人员经常犯的错误之一 . 在 SQL 中有很多陷阱使一些索引无法使用 .
1, 使用不等于操作符 (<>, !=)
下面的查询即使在 cust_rating 列有一个索引，查询语句仍然执行一次全表扫描。
select cust_Id,cust_name
from customers
where cust_rating <> 'aa';
把上面的语句改成如下的查询语句，这样，在采用基于规则的
优化器而不是基于代价的优化器（更智能）时，将会使用索引。
select cust_Id,cust_name
from customers
where cust_rating < 'aa' or cust_rating > 'aa';
特别注意：通过把不等于操作符改成 OR 条件，就可以使用索引，以避免全表扫描。
2, 使用 is null 或者 is not null
使用 IS NULL 或 IS NOT NULL 同样会限制索引的使用。因为 NULL 值并没有被定义。在 SQL 语句中使用 NULL 会有很多的麻烦。因此建议开发人员在建表时，把需要索引的列设成 NOT NULL 。如果被索引的列在某些行中存在 NULL 值，就不会使用这个索引（除非索引是一个位图索引，关于位图索引在稍后在详细讨论）。
3, 比较不匹配的数据类型
比较不匹配的数据类型也是比较难于发现的性能问题之一。
注意下面查询的例子， account_number 是一个 VARCHAR2 类型，
在 account_number 字段上有索引。下面的语句将执行全表扫描。
select bank_name,address,city,state,zip
from banks
where account_number = 990354;
Oracle 可以自动把 where 子句变成 to_number(account_number)=990354 ，这样就限制了索引的使用 , 改成下面的查询就可以使用索引：
select bank_name,address,city,state,zip
from banks
where account_number ='990354';
特别注意：不匹配的数据类型之间比较会让 Oracle 自动限制索引的使用，即便对这个查询执行 Explain Plan 也不能让您明白为什么做了一次 “ 全表扫描 ” 。
五 , 创建索引选择合适的可选项
如为了大批量导入数据，我们往往会先取消索引其以提高插入的速度。然后等数据导入完毕后再重新创建索引。在这个过程中如果能够采用一些可选项，则可以缩短索引创建的时间。在 Oracle 数据库中提供了丰富的可选项。我们常用的可选项主要有以下这些。
可选项一：NOSORT ，记录排序可选项。
默认情况下，在表中创建索引的时候，会对表中的记录进行排序，排序成功后再创建索引。但是当记录比较多的是，这个排序作业会占用比较多的时间，这也就增加了索引建立的时间( 排序作业是在索引创建作业中完成) 。有时候，我们导入数据的时候，如采用insert into 语句插入数据过程中同时采用Order by 子句对索引字段进行了排序。此时如果在索引创建过程中再进行排序的话，就有点脱裤子放屁，多此一举了。为此在重新创建索引时，如果表中的数据已经排好序了( 按索引字段排序) ，那么在创建索引时就不需要为此重新排序。此时在创建索引时，数据库管理员就可以使用NOSORT 可选项，告诉数据库系统不需要对表中当记录进行重新排序了。
采用了这个选项之后，如果表中的记录已经按顺序排列，那么在重新创建索引的时候，就不会重新排序，可以提高索引创建的时间，节省内存中的排序缓存空间。相反，如果表中的记录是不按索引关键字排序的话，那么此时采用NOSORT 关键字的话，系统就会提示错误信息，并拒绝创建索引。所以在使用NOSORT 可选项的时候，数据库管理员尽管放心大胆的使用。因为其实在不能够使用这个选项的时候，数据库也会明确的告知。为此其副作用就比较少，数据库管理员只需要把这个可选项去掉然后重新执行一次即可。不过这里需要注意的是，如果表中的记录比较少的话，那么使用NOSORT 选项的效果并不是很明显。当采用insert into 批量导入数据，并在这个过程中采用了Order by 子句对索引关键字进行了排序的话，则此时采用NOSORT 选项的话，往往能够起到比较好的效果。
可选项二：NOLOGGING ，是否需要记录日志信息。
在创建索引的时候，系统会把相关的信息 存储 到日志信息中去。如果表中的记录比较多，则需要一一的把这些信息记录到日志文件中，这显然会让数据库增加很大的工作量。从而增加索引创建的时间。为此在创建索引的过程中，如果有必要时，我们可以采用NOLOGGING 选项，让数据库在创建索引的过程中，不产生任何重做日志信息。此时当表中的记录比较多时，就可以明显提高速度。
但是默认情况下，数据库在在创建索引时，是不采用这个选项的，即会把相关的信息保存到重做日志中去。这虽然降低了索引创建的效率，但是如果遇到什么意外的话，却可以利用重做日志来进行恢复。所以，此时数据库管理员就比较难以抉择了。一方面是数据的 安全 ，另一方面是索引创建的速度。根据笔者的经验，只要数据库 服务器 比较稳定，而数据库中约束机制又比较完善的话，那么在创建索引的过程中一般不会出现问题，可以放心大胆的使用这个可选项。
但是如果 数据库已经使用了好几年了。后来因为某种原因需要重建索引。在这种情况下，由于数据库使用过程中很多因素数据库管理员无法控制。此时为这种类型的数据库创 建索引时，为了保险起见还是不要采用这个选项好。因为此时遇到错误的几率相对来说会搞一点。为此此时牺牲一下索引创建的速率，而提高数据的 安全 性还是有必要的。万一遇到什么问题时，可以通过重做日志来及时的恢复数据，为企业用户减少损失。
可选项三：COMPUTE STATISTICS ，是否生成统计信息。
如果管理员在创建索引时采用了这个选项，则数据库将在创建索引的过程中以非常小的代价直接生成关于索引的相关统计信息，然后把这些信息 存储 在数据字典中。这就可以避免以后对索引进行分析统计，而且优化器在优化SQL 语句的时候可以随机使用这些统计信息，以确定是否生成使用该索引的执行计划。通常情况下，在生成索引的过程中统计索引的相关信息，其所花的代价是最小的。无论从时间上，还是从硬件资源的耗费上，都是非常小的。所以，在创建索引的过程中统计相关的索引信息是非常有用的。
但是默认情况下，数据库是不采用这个选 项的。这主要是因为一些事物处理系统，索引的信息是经常需要发生变化的。如果在索引创建的过程中统计了相关信息。这些信息随着索引的调整等等原因会很快的 过时。所以说，其在默认情况下没有采用这个选项。可见这个选项并不是在任何情况下都能够起到效果。但是如果这个数据库系统是一个决策支持系统。其数据、索 引等等在一段时间内基本上是稳定不变的。此时在创建索引时可以使用这个选项。如此的话，在生成索引时可以以最小的代价生成这些统计信息，方便优化器使用。 笔者在部署数据库应用的时候，对于事务型的数据库系统，一般不会启用这个选项。但是对于一些决策性的数据库系统或者数据仓库中，创建索引时则笔者喜欢采用 这个选项。这有助于提高数据库的性能。因为优化器在生成执行计划时，可以直接采用这个统计信息。所以，数据库能够在最短的时间内确定需要采用的执行计划。 而且在执行计划制定中参考了这个索引统计信息，为此所生成的执行计划在同等条件下可能更加的合理。
可选项四：ONLINE ，DML 操作与创建索引操作是否可以同时进行。
默认情况下，数据库系统是不允许DML 操作与创建索引的操作同时进行的。也就是说，在创建索引的过程中，是不允许其他用户对其所涉及的表进行任何的DML 操作。这主要是因为对基础表进行DML 操作时，会对基础表进行加锁。所以在基础表上的DDL 事务没有递交之前，即没有对基础表进行解锁之前，是无法对这基础表创建索引的。反之亦然。显然此时数据库没有采用这个ONLIE 选项，继之DML 操作与创建索引操作同时进行，主要是从创建索引的效率出发的。防止因为两个作业相互冲突，从而延长某个作业的运行时间。
但是有时会我们必须允许他们进行同时操作。如用户可能一刻都不能够离开数据库系统，需要时时刻刻对数据库基础表进行DML 操作。而此时由于某些原因，数据库管理员又需要重新建立索引时，那么不得不在创建索引的语句中加入这个ONLINE 选项。让他们同时运行。此时虽然可能会延长索引创建作业的时间，但是可以保障用户DML 操作能够正常进行。有时候牺牲这个代价是值得的。用户是不能够等的，而我们数据库管理员则可以勉强的等一会儿。
当然，如果用户对于这个DML 操作及时性没有这么高。如数据库管理员在晚上员工没有使用数据库时创建索引时，则可以不带这个选项。在限制用户对基础表进行DML 操作的同时，提高数据库创建索引的效率。
可选项五：PARALLEL ，多服务进程创建索引。
默认情况下，Oracle 数据库系统不采用这个选项。这并不是说这个选项不可用，而是因为大多数情况下企业部署Oracle 数据库时所采用的数据库 服务器 往往只有单个CPU 。此时数据库系统是用一个服务进程来创建索引的。
如果企业的服务器有多个CPU 的话，则可以在创建索引时采用这个选项。因为只要采用了这个选项，则数据库就会使用多个服务进程来并行的创建索引，以提高索引创建的速度。为此，在同等条件下，多服务并行创建进索引并单服务创建索引速度要快的多。所以如果服务器中有多个CPU ，而且需要创建的索引比较多或者基础表中记录比较多的话，则采用这个选项能够大幅度的提高索引的创建效率。
故笔者建议，如果采用多CPU 的服务器时，最好在创建索引时使用这个选项。不能够浪费了服务器的CPU 呀。不然的话，多CPU 服务器的优势就体现不出来了。为此采用这个选项，也是物尽其用。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
