欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle建立索引及SQL优化教程

程序员文章站 2022-11-08 11:36:36
索引: 索引有单列索引 复合索引之说 如何某表的某个字段有主键约束和唯一性约束,则oracle 则会自动在相应的约束列上建议唯一索引。数据库索引主要进行提高访问速度。 建设原则: 1、索引...

索引:

索引有单列索引
复合索引之说

如何某表的某个字段有主键约束和唯一性约束,则oracle 则会自动在相应的约束列上建议唯一索引。数据库索引主要进行提高访问速度。

建设原则:

1、索引应该经常建在where 子句经常用到的列上。如果某个大表经常使用某个字段进行查询,并且检索行数小于总表行数的5%。则应该考虑。

2、对于两表连接的字段,应该建立索引。如果经常在某表的一个字段进行order by 则也经过进行索引。

3、不应该在小表上建设索引。

优缺点:

1、索引主要进行提高数据的查询速度。 当进行dml时,会更新索引。因此索引越多,则dml越慢,其需要维护索引。 因此在创建索引及dml需要权衡。

创建索引:

单一索引:create index  on (column_name);
 复合索引: create index i_deptno_job on emp(deptno,job); —>在emp表的deptno、job列建立索引。
  select * from emp where deptno=66 and job='sals' ->走索引。
  select * from emp where deptno=66 or job='sals' ->将进行全表扫描。不走索引
  select * from emp where deptno=66 ->走索引。
  select * from emp where job='sals' ->进行全表扫描、不走索引。

如果在where 子句中有or 操作符或单独引用job 列(索引列的后面列) 则将不会走索引,将会进行全表扫描。

sql 优化:

当oracle数据库拿到sql语句时,其会根据查询优化器分析该语句,并根据分析结果生成查询执行计划。

也就是说,数据库是执行的查询计划,而不是sql语句。

查询优化器有rule-based-optimizer(基于规则的查询优化器) 和cost-based-optimizer(基于成本的查询优化器)。

其中基于规则的查询优化器在10g版本中消失。

对于规则查询,其最后查询的是全表扫描。而cbo则会根据统计信息进行最后的选择。

1、先执行from ->where ->group by->order by

2、执行from 字句是从右往左进行执行。因此必须选择记录条数最少的表放在右边。这是为什么呢?  

3、对于where字句其执行顺序是从后向前执行、因此可以过滤最大数量记录的条件必须写在where子句的末尾,而对于多表之间的连接,则写在之前。

因为这样进行连接时,可以去掉大多不重复的项。  

4. select子句中避免使用()oracle在解析的过程中, 会将’’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间

5、索引失效的情况:

 ① not null/null 如果某列建立索引,当进行select * from emp where depto is not null/is null。 则会是索引失效。

 ② 索引列上不要使用函数,select col from tbl where substr(name ,1 ,3 ) = ‘abc’

或者select col from tbl where name like ‘%abc%’ 而select col from tbl where name like ‘abc%’ 会使用索引。

 ③ 索引列上不能进行计算select col from tbl where col / 10 > 10 则会使索引失效,应该改成

select col from tbl where col > 10 * 10

 ④ 索引列上不要使用not ( != 、 <> )如:select col from tbl where col ! = 10

应该 改成:select col from tbl where col > 10 or col < 10 。

6、用union替换or(适用于索引列)

  union:是将两个查询的结果集进行追加在一起,它不会引起列的变化。 由于是追加操作,需要两个结果集的列数应该是相关的,

并且相应列的数据类型也应该相当的。union 返回两个结果集,同时将两个结果集重复的项进行消除。 如果不进行消除,用unoin all.

通常情况下, 用union替换where子句中的or将会起到较好的效果. 对索引列使用or将造成全表扫描. 注意, 以上规则只针对多个索引列有效.

如果有column没有被索引, 查询效率可能会因为你没有选择or而降低. 在下面的例子中, loc_id 和region上都建有索引.

高效:

  select loc_id , loc_desc , region
  from location
  where loc_id = 10
  union
  select loc_id , loc_desc , region
  from location
  where region = “melbourne”
  低效:
  select loc_id , loc_desc , region
  from location
  where loc_id = 10 or region = “melbourne”

如果你坚持要用or, 那就需要返回记录最少的索引列写在最前面.

7. 用exists替代in、用not exists替代not in

在许多基于基础表的查询中, 为了满足一个条件, 往往需要对另一个表进行联接. 在这种情况下, 使用exists(或not exists)通常将提高查询的效率.

在子查询中, not in子句将执行一个内部的排序和合并. 无论在哪种情况下, not in都是最低效的(因为它对子查询中的表执行了一个全表遍历).

为了避免使用not in, 我们可以把它改写成外连接(outer joins)或not exists.

例子:

高效: 
select * from emp (基础表) where empno > 0 and exists (select ‘x’ from dept where dept.deptno = emp.deptno and loc = ‘melb’)
低效: select * from emp (基础表) where empno > 0 and deptno in(select deptno from dept where loc = ‘melb’)