Oracle学习（四）---索引说明和使用注意

索引：

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向/指针）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

索引的原理分析

通过上图可以看出，左边表中相邻的物理数据，在实际存储结构中是不一定相邻的（右图），所以索引能够提高数据查询的速度，因为他本身的二叉树查找结构，但是随之而来的是数据更新插入所带来的二叉树结构变化问题，这也就说明了为什么索引能提高查询速度，但是数据的更新插入会对索引进行重建（存储变化导致索引指向改变）

聚集索引
在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。（访问四度更快）

聚集索引特殊的方面是：聚集索引的叶级是实际的数据

索引使用注意事项

1.索引在以下情况中会放弃索引进行全局搜索

（1）.当sql语句中存在 not null ， is null 时（即索引变量为null）

select * from table where num is null （索引失效）

尽量将一些字段修改为数字判断 如 0 1

select * from table where num = 0 （有效）

（2）.语句中存在 not in ，in

select * from table where num in（select id from table_1） （索引失效）

（3）.语句中存在or 链接查询条件 ，导致引擎放弃使用索引而进行全表扫描

select * from table where num =0 or name=’lucy’ （索引事项）

（4）.like对索引进行模糊查询也会导致索引失效（前通配的情况下 如：’%ss’）

select * from table where name like ‘%a%’（失效）

select * from table where name like ‘a%’ （有效）

（5）.对索引字段进行表达式运算会造成索引失效

select * from table where num/2 = 100 （失效）

应该为：

select * from table where num = 100*2

（6）.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描

// oracle总有的是substr函数
select * from table where substring(name,1,3)=’abc’

//查过了确实没有datediff函数
select * from table where datediff(day,createdate,’2005-11-30’)=0

应改为:

select * from table where name like ‘abc%’

// oracle 中时间应该把char 转换成 date 如：
createdate >= to_date(‘2005-11-30’,’yyyy-mm-dd’)
select * from table where createdate>=’2005-11-30’ and createdate<’2005-12-1’

2.索引的建立注意

（1）.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用，所以索引一般是创建在能代表数据独一性的字段上

（2）.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要

（3）.新建索引一方面增加了数据库的存储空间，另一方面，在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。