Oracle 建立索引及SQL优化

SQL学习 专栏收录该内容
5 篇文章 0 订阅

 

一、建立数据库索引:

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

建设原则:

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

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

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

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

   2、当一个表的索引达到4个以上时,ORACLE的性能可能还是改善不了,因为OLTP系统每表超过5个索引即会降低性能,而且在一个sql 中, Oracle 从不能使用超过 5个索引

   3、索引可能产生碎片,因为记录从表中删除时,相应也从表的索引中删除.表释放的空间可以再用,而索引释放的空间却不能再用.频繁进行删除操作的被索引的表,应当阶段性地重建索引,以避免在索引中造成空间碎片,影响性能.在许可的条件下,也可以阶段性地truncate表,truncate命令删除表中所有记录,也删除索引碎片. (建立索引影响了删除和更新操作)

创建索引:
 单一索引:Create Index <Index-Name> On <Table_Name>(Column_Name);

 复合索引:Create Index <Index-Name> 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 列(索引列的后面列) 则将不会走索引,将会进行全表扫描。

      同时在Oracle里用PL/SQL的F5可以对整个SQL查询来判断没加索引前和加完索引后的用时。

 

索引失效的情况:
 ① 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 。

 

二、关于SQL 性能优化

 

(一)ORACLE 性能优化主要方法
⑴硬件升级(CPU、内存、硬盘):

     CPU:在任何机器中CPU的数据处理能力往往是衡量计算机性能的一个标志,并且ORACLE是一个提供并行能力的数据库系统,如果运行队列数目超过了CPU处理的数目,性能就会下降;
     内存:衡量机器性能的另外一个指标就是内存的多少了,在ORACLE中内存和我们在建数据库中的交换区进行数据的交换,读数据时,磁盘I/O必须等待物理I/O操作完成,在出现ORACLE的内存瓶颈时,我们第一个要考虑的是增加内存,由于I/O的响应时间是影响ORACLE性能的主要参数;
     网络条件:NET*SQL负责数据在网络上的来往,大量的SQL会令网络速度变慢。比如10M的网卡和100的网卡就对NET*SQL有非常明显的影响,还有交换机、集线器等等网络设备的性能对网络的影响很明显,建议在任何网络中不要试图用3个集线器来将网段互联。
 

⑵版本及参数设置


⑶应用程序设计(框架、调用方式---源代码)

     程序设计中的一个著名定律是20%的代码用去了80%的时间;
     两种方式优化:源代码的优化和SQL语句的优化。源代码的优化在时间成本和风险上代价很高;另一方面,源代码的优化对数据库系统性能的提升收效有限。
     DBMS处理查询计划的过程是这样的:在做完查询语句的词法、语法检查之后,将语句提交给DBMS的查询优化器,优化器做完代数优化和存取路径的优化之后,由预编译模块对语句进行处理并生成查询规划,然后在合适的时间提交给系统处理执行,最后将执行结果返回给用户。 


⑷SQL 语句优化:

 

当Oracle数据库拿到SQL语句时,其会根据查询优化器分析该语句,并根据分析结果生成查询执行计划。
也就是说,数据库是执行的查询计划,而不是Sql语句。
查询优化器有rule-based-optimizer(基于规则的查询优化器) 和Cost-Based-optimizer(基于成本的查询优化器)。
其中基于规则的查询优化器在10g版本中消失。

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


先执行From ->Where ->Group By->Order By,所以尽量避免全表扫。

执行From 字句是从右往左进行执行。因此必须选择记录条数最少的表放在右边。  

对于Where字句其执行顺序是从后向前执行、因此可以过滤最大数量记录的条件必须写在Where子句的末尾,而对于多表之间的连接,则写在之前。因为这样进行连接时,可以去掉大多不重复的项。  

④SELECT子句中避免使用(*)ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.但是在count(*)和count(1)的执行中不需要遵守上述内容,速度经过我测试是相同的。

用UNION替换OR(适用于索引列)

  union:是将两个查询的结果集进行追加在一起,它不会引起列的变化。 由于是追加操作,需要两个结果集的列数应该是相关的,并且相应列的数据类型也应该相当的。union 返回两个结果集,同时将两个结果集重复的项进行消除。 如果不进行消除,用UNOIN ALL.

   通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 
   如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引.

  高效:
  SELECT ID , NAME
  FROM LOCATION
  WHERE ID = 1
  UNION
  SELECT ID , NAME
  FROM LOCATION
  WHERE NAME = “SUQI356”

  低效:
  SELECT ID , NAME 
  FROM LOCATION
  WHERE ID = 1 OR NAME = “SUQI356”
  如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.

⑥用EXISTS替代IN、用NOT EXISTS替代NOT IN和用(+)比用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’)

⑦ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表 。

⑧避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤,这个处理需要排序、总计等操作; 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.

  ⑨尽可能使用varchar代替char,因为变长字段存储空间小,在一个相对较小的字段内搜索效率要高。

  ⑩使用临时表来存储

  • 11
    点赞
  • 1
    评论
  • 62
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值