SQL索引

什么是索引

索引是建立在表的一列或多个列上的辅助对象， 目的是加快访问表中的数据。

oracle存储索引的数据结构是B树，位图索引（Bitmap索引）也是如此，只不过是叶子节点不同B树索引。

Bitmap索引：索引由根节点，分支节点和叶子节点组成，上级索引块包含下级索引快的索引数据，叶节点包含索引数据和确定行实际位置的rowid.

oracle中最常用的索引就俩种：B树索引和位图索引。

B树索引在oracle中是一个通用的索引，在创建索引时他就是默认的类型，最多可以包含32列。

一般情况下，大多数用户都只创建TYPE为NORMAL的B树索引，所以对于较低基数的列我们都是不创建索引的，

因为B-树索引对查询速度提升不一定会有改善，甚至会增加INSERT,UPDATE,DELETE 命令所消耗的时间。

位图索引为oracle每一个键创建一个位图，然后把与键值所关联的ROWID保存为位图，最多可以包含30列。 

位图索引在加载表（插入操作）时，通常要比B树索引做得好。通常，位图索引要比一个低基数（很少不同值）上的B树索引要快3~4倍。

但如果新增的值占插入行的70%以上时，B树索引通常会更快一些。当每条记录都增加一个新值时，B树索引要比位图索引快3倍。

建议

建议不要在一些联机事务处理（OLTP）应用程序中使用位图索引。B树索引的索引值包含ROWID，这样oracle就可以在行级别上锁定索引。

位图索引被存储为压缩的索引值，其中包含了一个范围的ROWID，因此ROWID必须对一个给定值锁定所有范围内的ROWID。

这种锁定可能会使某些DML语句中造成死锁。SELECT语句不会受到这种锁定问题的影响。 

位图索引有很多限制

1 基于规则的优化器不会考虑位图索引

2 当执行ALTER TABLE语句，并修改包含有位图索引的列时，会使位图索引失效

3 位图索引在索引块中存储了索引键的值，然而，他们并不能完成用户任何类型的检查 

使用索引的目的

加快查询速度，减少I/O操作

I/O操作：设备与cpu连接的接口电路的操作。

索引的种类

非唯一索引，唯一索引，位图索引，局部有前缀分区索引，局部无前缀分区索引，

全局有前缀分区索引，散列分区索引，基于函数的索引

管理索引的准则

在表中插入数据后创建准则

在用SQL*LOADER(工具)或import工具插入或装载数据后，建立索引比较有效

索引正确的表和列

列中的值相对比较唯一

取值范围（大：B树索引，小：位图索引）

Date型列一般适合基于函数的索引

列中有许多空值，不适合建立索引

为性能而安排索引列

经常一起使用多个字段检索记录，组合索引比单索引更有效。

把最常用的列放在最前面，例：dx_groupid_serv_id(groupid,serv_id)，在where条件中使用groupid或serv_id,

查询将使用索引，若仅用到serv_id字段，则索引无效。

合并/拆分不必要的索引 

限制每个表索引的数量

一个表可以有几百个索引，但是对于频繁的插入和更新表，索引越多系统CPU,I/O负担就越重。

建议每张表不超过5个索引。

删除不再需要的索引。

索引无效，集中表现在该使用基于函数的索引或位图索引，而使用了B树索引。

应用中的查询不使用索引。

重建索引之前必须先删除索引，若用alter index ...rebuild 重建索引，则不必删除索引。

索引数据块空间使用

创建索引时指定表空间，特别是在建立主键时，应明确指定表空间。

合理设定pctfress，注意：不能给索引指定pctused。

pctfress：为一个块保留的空间百分比，表示数据块在什么情况下可以被insert，默认是10，

表示当数据块的可用空间低于10%后，就不可以被insert了，只能被用于update。

即当使用一个bolck时，在达到pctfree之前，该block是一直可以被插入的，这个时候处在上升期。

pctused：是指当块里的数据低于多少百分比时，又可以重新被insert，一般默认是40，即40%，

即当数据低于40%时，又可以写入新的数据，这个时候处在下降期。

考虑并行创建索引

对大表可以采取并行创建索引，在并行创建索引时，存储参数被每个查询服务器进程分别使用，

例如：initial为1M，并行度为8，则创建索引期间至少要消耗8M空间。

考虑用nologging创建索引

对大表创建索引时可以用弄logging来减少重做日志。

节省重做日志文的空间。

缩短创建索引的时间。

改善了并行创建大索引时的性能。

怎样建立最佳索引

明确的创建索引

create index index_name on table_name (field_name)

tablespace tablespace_name

pctfree 5

initrans 2

maxtrans 255

storge

(

minextents 1

maxextents 16382

pctincrease 0

 )

initrans：每个块都有一个块首部。事务表中会建立一些条目来描述哪些事务将块上的哪些行/元素锁定。

这个事务表的初始大小由对象initrans设定指定。 对于表，这个值默认为1，索引的initrans默认为2。

创建基于函数的索引

常用语UPPER,LOWER,TO_CHAR(date)等函数分类上， 例

create index idx_func on emp(UPPER(ename)) tablespace tablespace_name

创建位图索引

对于基数较小，且基数相对稳定的列建立索引时，首先应该考虑位图索引，例

create bitmap index idx_bitm on class(classno) tablespace tablespace_name

明确地创建唯一索引

唯一索引：一种索引，不允许具有索引值相同的，从而禁止重复的索引或键值。

系统在创建该索引时检查是否有重复的键值，并在每次使用insert和update语句添加数据时进行检查。

可以用create unique index 语句来创建唯一索引，例

create unique index dept_unique_idx on dept(dept_no) tablespace idx_1;

创建与约束相关的索引

可以用using index字句，为与unique和primary key约束相关的索引，例如：

alter table table_name

add constraint PK_primary_keyname primary key (field_name)

using index tablespace tablespace_name;

建立索引的代价

基础表维护时，系统要同时维护索引，不合理的索引将严重影响系统资源，主要表现在CPU和I/0上

插入，更新，删除数据产生大量db file sequential read锁等待

db file sequential read：？？db file sequential read等待事件有3个参数，file#，first block#，和block#数量。

在10g中，这等待事件受到用户I/O等级别的影响。当处理db file sequential read等待事件的时候，牢记以下关键想法。

oracle进程需要一个当前不在SGA中的块，等待数据块从磁盘读入到SGA中。

要看的俩个重要的数字是单独会话和TIME_WAITED和AVERAGE_WAIT

重要db file sequential read等待时间最可能是一个应用问题。

下面是一些常见的索引限制问题

使用不等于操作符（<>,!=）

即使在列dept_id有一个索引，查询语句扔然执行一次全表扫描

select * from dept where staff_num <> 1000;

但是开发中的确需要这样的查询，难道没有解决问题的办法了吗？

有的!

通过把用or语法替代不等号进行查询，就可以使用索引，以避免全表扫描，

上面的语句改成下面这样的，就可以使用索引了。

select * from dept where staff_num < 1000 or dept_id > 1000;

使用函数

如果没有使用基于函数的索引，那么where子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引，

下面的查询就不会使用索引

select * from staff where trunc（birthdate） = '01-MAY-82'

但是把函数应用在条件上，索引是可以生效的， 把上面的语句改成下面的语句，就可以通过索引进行查找。

select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999) 

下面的例子中，dept_id是一个varchar2型的字段， 在这个字段上有索引，但是下面的语句会执行全盘扫描。

select * from dept where dept_id = 900198

这是因为oracle 会自动把where 子句换成to_number(DEPT_ID) = 900198 , 就是限制了索引的使用。

把语句改成select * from dept where dept_id = '900198'   语句就会使用索引。

使用like子句查询时，数据需要把所有的记录都遍历来进行判断，索引不能发挥作用，这种情况也要尽量避免。

如果能不用到排序，则尽量避免排序。

用到排序的情况有集合操作。

union，minus，intersect，注意：union all是不排序的。

order by , group by, distinct , in 有的时候会用到排序

确实要排序的时候也尽量要排序小数据量 ，尽量让排序在内存中执行。