数据库索引优化 oracle

摘要：请把数据表想象成一本书，索引就是书的目录。那么索引过多会导致插入更新操作性能下降。哪些查询会无法使用索引优化中要注意这些情况，调整以便使用索引模糊查询在字符串开端使用通配符，会忽略索引。

请把数据表想象成一本书， 索引就是书的目录。
这里只讨论写ORACLE SQL中优化时常遇到的索引，一般有以下几类，
1. normal 顺序的btree 索引，字段值可以为空，
2. unique 唯一的顺序索引，也就是说书的目录不能出现重复项，这样索引建立时，字段是不允许有空值的
3. 组合索引 多个字段放到一起建立的 normal索引，类似于书的目录是多级的
4. 函数索引
5. 位图索引 适合于某列的取值情况较少， 例如性别，只有男女两个值
更复杂的索引， 不作讨论

索引的优点
检索速度快，根据目录去找自己需要的东西， 显然比整个书翻一遍（全表扫描）要快很多
索引的缺点：
1. 需要多带带的存储空间去保存索引， 如果一张表建了很多索引， 那么索引的空间会更大
2. 如果一张表高并发操作主要是插入、更新。那么索引过多会导致插入更新操作性能下降。
类似书的内容总在变，目录自然也要同时跟着变化。维护目录会增加额外的消耗

在书目录查找的时候， 当我们找到需要的章节时，会记住这些章节的页码， 再翻到相应的页码去寻找我们需要的内容。
这里有两次查找: 1. 从目录找页码 2. 根据页码在内容中查找自己需要的
oracle 也是这样， 根据索引筛选可能需要的记录 rowid（rowid 是oracle每一行记录的实际物理地址，类似页码，精确到了每一行），利用rowid 读取行记录， 逐行读取记录的时候再比较这些记录是否符合其他where条件。最后返回符合条件的行。
注意：一次不可分割的子查询中中， 不论表建了多少索引，只会使用一条索引。例如

select * from users where userid=3 and username like "json%"。

假设userid跟username字段都建立了索引，如果同时使用两条索引，userid返回了等于3的一个rowid（这里是假设，实际情况一般不知一个，例如用户登录记录表）， username 返回json开头的5个rowid。 通过两组rowid 我们还是无法得知，哪些rowid同时符合两个条件。

oracle 有自己的分析规则，还是上面的例子， 利用userid查找起来 更快，一方面因为它只返回了一个rowid， 后续的查找只需遍历一个结果集，判断这个结果集是否 like "json%"，另一方面like操作更耗时。哪条索引查出来的rowid少自然就更快。
我们可以用索引提示的方式，来指定oracle使用哪条索引，使用这样的格式 /*+index(表名或表别名 索引名)*/

select /*+index(users users_idx1)*/ * from users where userid=3 and username like "json%"

实际情况中， 可能更复杂，例如:

select * from users where userid=3 and login_time=to_date("yyyy-mm-dd hh24:mi:ss", "2014-02-14 03:00:00")

这里可能userid 跟 login_time 都是独立的normal索引，而且查询效率都较高，需要根据表的实际情况去选择。如果 用户量很大，查询的时间只是到天，可能使用userid字段的索引更快，如果查询的时间精确到了秒， 可能时间索引更快

如果需要查看 oracle 使用了哪条索引，查看执行计划便可,在pl/sql工具中，只需按F5或explain sql

类似书的目录有多级目录一样， 组合索引是建立在多个字段的， 不同的字段顺序不同的组合索引，类似多级目录，调换了上下级，就是新的目录。
基本上书籍都是多级目录， 一般从我们一级目录开始查找。
组合索引也遵循这样的规则，示例如下：

create index user_idx1 on user_log(userid, username, log_time)
// 以下方式，查询条件中使用了组合索引第一列（前导列）都可以使用 组合索引
select * from user_log where userid=3 and  username like "json%" and log_time=to_date("yyyy-mm-dd hh24", "2014-02-14 03");
select * from user_log where userid=3 and log_time=to_date("yyyy-mm-dd hh24", "2014-02-14 03");
select * from user_log where userid=3;
// 跳过 第一列的情况， 将不再使用使用组合索引（oracle 9i以上版本会使用跳跃扫描）
selec * from user_log where log_time=to_date("yyyy-mm-dd hh24", "2014-02-14 03");

查找中对字段使用函数时，将会忽略直接创建于字段上的索引。

select * from users where upper(username) = "JSON"

这样的查询， 需要创建函数索引

create index users_upper_idx on users(upper(username))

理解起来也很简单， 当对字段使用了函数的时候，查询已经不是字段的值了，原来基于字段值建立的索引，自然失效。

sql优化中要注意这些情况，调整sql以便使用索引
模糊查询在字符串开端使用通配符，会忽略索引。如 like "%json" 或 like "%json%"
对非函数索引的字段， 使用了函数
对于index索引 不会存储 null类型，is null 是不会使用该类索引的
索引本身是利用合理的数据结构，根据值的比较，来达到快速检索的目的，以上列举的几种情况，显然都无法通过比较值来查找到最终结果