1、如果检索数据量超过30%的表中记录数,使用索引将没有显著的效率提高
2、在特定情况下,使用索引也许会比全表扫描慢,但这是同一个数量级上的差距;而通常情况下,使用索引比全表扫描要快几倍乃至几千倍!
用索引提高效率
索引是表的一个概念部分,用来提高检索数据的效率。实际上,ORACLE使用了一个复杂的自平衡B-tree结构。 通常,通过索引查询数据比全表扫描要快。当ORACLE找出执行查询和Update语句的最佳路径时,ORACLE优化器将使用索引。同样在联结多个表时使用索引也可以提高效率。另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证。 除了那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列。通常, 在大型表中使用索引特别有效。当然,在扫描小表时,使用索引同样能提高效率。
虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价。索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列被修改时, 索引本身也会被修改。 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O。因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢,所以索引不能盲目的建立。
建立索引
表的主键、外键必须有索引;
经常与其他表进行连接的表,在连接字段上应该建立索引,多表进行多字段连接,建议适当建立复合索引;
索引应该建立在查询条件中进行比较的字段上,而不是建立在我们要找出来并且显示的字段上;
在条件表达式中经常用到的不同值较多的列上建立检索,在不同值少的列上不要建立索引;<段落>比如在雇员表的“性别”列上只有“男”与“女”两个不同值,因此就无必要建立索引。如果建立索引不但不会提高查询效率,反而会严重降低更新速度。<段落>组合索引要尽量使关键查询形成索引覆盖,其前导列一定是使用最频繁的列。
经常与其他表进行连接的表,在连接字段上应该建立索引,多表进行多字段连接,建议适当建立复合索引;
索引应该建立在查询条件中进行比较的字段上,而不是建立在我们要找出来并且显示的字段上;
在条件表达式中经常用到的不同值较多的列上建立检索,在不同值少的列上不要建立索引;<段落>比如在雇员表的“性别”列上只有“男”与“女”两个不同值,因此就无必要建立索引。如果建立索引不但不会提高查询效率,反而会严重降低更新速度。<段落>组合索引要尽量使关键查询形成索引覆盖,其前导列一定是使用最频繁的列。
索引失效
1、IN、OR子句常会使用工作表
使索引失效。如果不产生大量重复值,可以考虑把子句拆开。拆开的子句中应该包含索引。例子如下:<段落>如果在fields1和fields2上同时建立了索引,fields1为主索引 以下sql会用到索引 select * from tablename1 where fields1=’value1’ and fields2=’value2’ 以下sql不会用到索引 select * from tablename1 where fields1=’value1’ or fields2=’value2’
使索引失效。如果不产生大量重复值,可以考虑把子句拆开。拆开的子句中应该包含索引。例子如下:<段落>如果在fields1和fields2上同时建立了索引,fields1为主索引 以下sql会用到索引 select * from tablename1 where fields1=’value1’ and fields2=’value2’ 以下sql不会用到索引 select * from tablename1 where fields1=’value1’ or fields2=’value2’
2、使用IS NULL 或IS NOT NULL
使用IS NULL 或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被索引定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时,把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值,就不会使用这个索引。也可以使用特定的值来代替NULL。
请避免在索引中使用任何可以为空的列,oracle将无法使用该索引。
对于单列索引,如果列包含空值,索引中将不存在此记录;
对于复合索引,如果每个列都为空,索引中同样不存在此记录。如果至少有一个列不为空,则记录存在于索引中
因为空值不存在于索引列中,所以where子句中对索引列进行空值比较将使oracle停用该索引
(索引失效)
使用IS NULL 或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被索引定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时,把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值,就不会使用这个索引。也可以使用特定的值来代替NULL。
请避免在索引中使用任何可以为空的列,oracle将无法使用该索引。
对于单列索引,如果列包含空值,索引中将不存在此记录;
对于复合索引,如果每个列都为空,索引中同样不存在此记录。如果至少有一个列不为空,则记录存在于索引中
因为空值不存在于索引列中,所以where子句中对索引列进行空值比较将使oracle停用该索引
(索引失效)
select …
from department
where dept_code is not null
3、条件字段使用函数和表达式
如果不使用基于函数的索引,那么在SQL语句的WHERE子句中对存在索引的列使用函数时,会使优化器忽略掉这些索引。下面的查询不会使用索引(只要它不是基于函数的索引)
如果不使用基于函数的索引,那么在SQL语句的WHERE子句中对存在索引的列使用函数时,会使优化器忽略掉这些索引。下面的查询不会使用索引(只要它不是基于函数的索引)
select empno,ename,deptno
from emp
where trunc(hiredate)='01-MAY-81';
把上面的语句改成下面的语句,这样就可以通过索引进行查找。
select empno, ename, deptno
from emp
where hiredate < (to_date('01-MAY-81') + 0.9999);
请务必注意,检索中不要对索引列进行处理,如:trim,to_date,类型转换等操作,破坏索引,使用全表扫描,影响sql执行效率<段落>4、避免在索引列上使用计算
假如索引列是函数的一部分,优化器将不使用索引而使用全表扫描。
假如索引列是函数的一部分,优化器将不使用索引而使用全表扫描。
低效:
SELECT … FROM DEPT WHERE SAL * 12 > 25000;
高效:
SELECT … FROM DEPT WHERE SAL > 25000/12;<段落>5、比较不匹配的数据类型
比较不匹配的数据类型也是比较难于发现的性能问题之一。注意下面查询的例子,account_number是一个VARCHAR2类型,在account_number字段上有索引。下面的语句将执行全表扫描。
比较不匹配的数据类型也是比较难于发现的性能问题之一。注意下面查询的例子,account_number是一个VARCHAR2类型,在account_number字段上有索引。下面的语句将执行全表扫描。
select bank_name, address, city, state, zip
from banks
where account_number = 990354;
Oracle可以自动把where子句变成to_number(account_number)=990354,这样就限制了索引的使用,改成下面的查询就可以使用索引:
select bank_name, address, city, state, zip
from banks
where account_number = '990354';
特别注意:不匹配的数据类型之间比较会让Oracle自动限制索引的使用,即便对这个查询执行Explain Plan也不能让您明白为什么做了一次“全表扫描”。
6、带通配符(%)的like语句
要求在职工表中查询名字中包含cliton的人。可以采用如下的查询SQL语句:
要求在职工表中查询名字中包含cliton的人。可以采用如下的查询SQL语句:
select * from employee where last_name like '%cliton%';
这里由于通配符(%)在搜寻词首出现,所以Oracle系统不使用last_name的索引。在很多情况下可能无法避免这种情况,但是一定要心中有底,通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时,优化器就能利用索引。
在下面的查询中索引得到了使用:
select * from employee where last_name like 'c%';
7、Order by语句
ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制,也可以将函数加入列中(象联接或者附加等)。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。仔细检查order by语句以找出非索引项或者表达式,它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引,也可以为所使用的列建立另外一个索引,同时应绝对避免在order by子句中使用表达式。
ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制,也可以将函数加入列中(象联接或者附加等)。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。仔细检查order by语句以找出非索引项或者表达式,它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引,也可以为所使用的列建立另外一个索引,同时应绝对避免在order by子句中使用表达式。
8、大于或小于操作符
大于或小于操作符一般情况下是不用调整的,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化。 如一个表有100万记录,一个数值型字段A,30万记录的A=0,30万记录的A=1,39万记录的A=2,1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了,因为A>2时ORACLE会先找出为2的记录索引再进行比较,而A>=3时ORACLE则直接找到=3的记录索引。
大于或小于操作符一般情况下是不用调整的,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化。 如一个表有100万记录,一个数值型字段A,30万记录的A=0,30万记录的A=1,39万记录的A=2,1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了,因为A>2时ORACLE会先找出为2的记录索引再进行比较,而A>=3时ORACLE则直接找到=3的记录索引。