从索引技术谈数据库查询索引建立和查询条件书写

       索引的优势当然是提高检索速度,但并不是说数据库建立了索引就真的会提高检索速度.为什么呢?

        我们知道,索引本身是有序的,索引查找的时候一般是多分查找,(当然在内存用数组实现的索引则可以做到随机查找,但数据库一般很少会采用这种方式组织,一般都是利用B+树),所以索引的查找一般不会是常数级,由于索引本身数据量问题,也不是一次就能将所有索引数据加载在内存里,所以也可能会引起多次磁盘读,加上定位到目标索引后还需要常数级的具体数据块磁盘读写,因此一次索引定位需要的磁盘读写可以控制在常数级别.因此索引查找的速度会在对数级别.但这并不等同于数据库查询时具体的查询速度,下面来分析一下:

1)只有建立索引的字段作为条件才会启用索引查询,提高速度;
2)如果索引字段的条件和其它非索引字段的条件是or关系,也会启用索引,但不会提高速度,因为这是的检索速度取决于慢条件;
3)索引字段在等值查询时效率最高(等条件),大于,小于等带范围的查询条件的速度是否能提高要看数据库索引本身的实现技术,因此数据库一般也不会采用B树而采用类似B+树的原因,因为B+树的卫星数据都在叶子节点上,可以实现范围读,提高范围查询的效率;
4)对于模糊查询,要看具体的数据库,一般是不会启用索引(Oracle 会做一定的优化,会用索引,具体可看后面的测试数据);
5)IS NULL,IS NOT NUL等条件也是一样.

因此,在数据库索引实际应用时要根据实际需要进行:
1)如果某个字段,或者某几个字段频繁单独作为条件查询时,可以建立索引;
2)如果一般字段多使用模糊查询,则不要建立索引;
3)索引字段条件和非索引字段只有在逻辑关系为与的情况下,索引字段条件才会真正有意义,否则还是全表扫描;

下面是Oracle的索引是否有效的一些条件:
1) 一般比较都会启用索引,In,between也会启用索引.
2)Like比较特殊,实际上Oracle会对第1个模糊匹配符号前面的部分串作索引定位匹配,具体的可参见后面的测试数据;
3) is null,is not null不会启用索引;
4)索引字段与常量表达式时Or关系时,常量表达式不会影响结果,但变量和参数化就会全表扫描;

下面是我测试的结果:数据量进2亿,服务器就是普通的PC机,Cards字段是索引字段,batchno字段是非索引字段,下面是结果:

select count(*) from cards;-- >52s
select count(1) from cards;-- >52s
select * from cards where cards='1';--不存在,毫秒
select * from cards where cards='994595942';--存在,毫秒级
select * from cards where cards>='999999999';--毫秒级,但也与返回数据量大小有关.索引有效.
select * from cards where cards>='9999' and  cards<='9999';--毫秒级,但也与返回数据量大小有关.索引有效.
select * from cards where cards in('994595942','999906236');--毫秒秒,索引有效.
select * from cards where cards in( select '994595942' from dual);--毫秒级索引有效.
select * from cards where cards in('999906236');--0.156秒,索引有效.

select * from cards where cards > '999999999';--毫秒级,索引有效.
select * from cards where cards like '999999%';-->毫秒级,索引有效.但也与返回数据量大小有关
select * from cards where cards like '_999999_';--分钟级,索引无效
select * from cards where cards like '999999_';--毫秒级,索引有效.但也与返回数据量大小有关
select * from cards where cards like '_999999';--分钟级索引无效
select * from cards where cards like '999_999';--分钟级索引无效

select * from cards where cards is null;--分钟级索引无效
select * from cards where cards='994595942' or 1>1;--毫秒级,索引有效
update cards set batchno=batchno  where cards='994595942';--毫秒级,索引有效.
update cards set batchno=batchno  where cards='994595942' or i>1;--分钟级索引无效 i是变量
update cards set batchno=batchno  where cards='994595942' or :V >1;--分钟级索引无效 v是占位符号,其实就是一般参数化查询.

select * from cards where cards between '994595942' and '994595942';--毫秒级索引有效.
select * from cards where cards='994595942' or batchno='222';--索引有效,但没意义,还是全表扫描;
select * from cards where cards='994595942' and batchno='222';--索引有效,毫秒级
select * from cards where  batchno='222' and cards='994595942';--索引有效,毫秒级,说明Oracle有先进行索引字段处理的优化.

select * from cards where (batchno,cards) in (select '994595942','994595942' from dual);--毫秒级,索引有效.

PS:从上面的测试来看,Oracle的优化,特别是Like的优化非常到位,因为我原来还认为数据库不会对模糊查询利用索引.当然从上述测试也可以反推出一些Oracle索引存放的一些技术。