索引的使用

索引的简单使用说明：

一.常用扫描方式：
.全表扫描
 全表扫描就是顺序地访问表中每条记录.
.索引扫描　　
索引唯一扫描(index unique scan)
索引范围扫描(index range scan)
索引全扫描(index full scan)
索引快速扫描(index fast full scan) 

1.索引唯一扫描(index unique scan)
通过唯一索引查找一个数值经常返回单个ROWID。如果该唯一索引有多个列组成(即组合索引)，则至少要有组合索引的引导列参与到该查询中，如创建索引：create index index_test on building(name,status)。
如：语句：
select id from building where name='国际科技大厦'
此语句可以使用该索引。如果该语句只返回一行，则存取方法称为索引唯一扫描。
而语句：
select name from building where status ='1'
因为where条件没有用到索引的引导列。所以索引不生效。

2.索引范围扫描(index range scan)
使用一个索引存取多行数据，同上面一样，如果索引是组合索引
如:select id from building where name like 'H%' and status='1' 语句返回多行数据，此时的存取方法称为索引范围扫描。
使用index rang scan的3种情况：
(a) 在唯一索引列上使用了range操作符(> < <> >= <= between)
(b) 在组合索引上，只使用部分列进行查询，导致查询出多行
(c) 对非唯一索引列上进行的任何查询。

3. 全索引扫描(index full scan)
SQL请求的全部列（column）必须驻留在索引树中；也就是说，SELECT和WHERE字句中的所有数据列必须存在于索引中。并且排序返回。

4.索引快速扫描(index fast full scan)
扫描索引中的所有的数据块，与 index full scan很类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是以排序顺序被返回。

 

二.什么情况下应该建立索引
1.表的主关键字(建立唯一索引)
2.表的字段唯一约束
3.直接条件查询的字段,在SQL中用于条件约束的字段
4.查询中与其它表关联的字段,字段常常建立了外键关系
5.查询中排序的字段
6.查询中统计或分组统计的字段

三.什么情况下应不建或少建索引
1.表的记录很少
2.对一些经常处理的业务表应在查询允许的情况下尽量减少索引
3.数据重复且分布平均的表字段
假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度

四.索引应用注意事项
1. IS NULL 与 IS NOT NULL
不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。 任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

2.带通配符（%）的like语句
如： select * from building where name like '%中心%'
name为building表的索引，则此索引就不会生效，这里由于通配符（%）在搜寻词首出现，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引
select * from building where name like '&中心%'

3. Order by语句
　　ORDER BY语句决定了如何将返回的查询结果排序,任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。order by后面的排序字段尽量使用索引字段。

4.'<>'的用法
例如：
select * from building where id<>5000;
select * from building where id>5000 or id<5000
结果一样，但是第二句id索引生效。

5.可以使用外部连接优化not in子句，例如：
select name from building where fk_building in (select deptid from test where name='test' and ifdeleted=0);
　　
改为：
select name from building a,test b where a.fk_building=b.id and b.name='test' and ifdeleted=0;

6.索引列是否函数的参数。如是，索引在查询时用不上。

7.主从表关联方式
如果：building.id上有一索引
在district.id上有索引
如：
select contract_code from building a,district b where a.id=b.id
building.id上的索引就会生效
反之同理。

8.是否存在潜在的数据类型转换。如将字符型数据与数值型数据比较，ORACLE会自动将字符型用to_number()函数进行转换，从而导致上一种现象的发生。

9.索引应用的优先级；

(1).唯一性索引的等级高于非唯一性索引. 这个规则只有当WHERE子句中索引列和常量比较才有效.如果索引列和其他表的索引类相比较. 这种子句在优化器中的等级是非常低的.

(2).如果多个索引在一个查询中都可应用，那么如果条件中应用索引和常量进行比较，那么这个索引先被利用。

10.语句的执行计划中有不良索引时，可以人为地屏蔽该索引，方法：
　　
　　。数值型：在索引字段上加0，例如
　　select * from building where id+0 = 0;
　　
　　。字符型：在索引字段上加‘’，例如
　　select * from building where name||’’='test';