Oracle初级性能优化总结

前言

  关于对Oracle数据库查询性能优化的一个简要的总结。 从来数据库优化都是一项艰巨的任务。对于大数据量,访问频繁的系统,优化工作显得尤为重要。由于Oracle系统的灵活性、复杂性、性能问题的原因多样性以及Oralce数据库的动态特性,优化成为Oracle数据库管理中最困难的领域。作为一个对数据库了解不多的程序猿,我也只能从最基本的开始着手,慢慢来学习掌握Oracle的基础吧。

示例

1、避免使用select *

  当你想在select字句中列出所有的column时,使用“select *”是一个方便的方法。不幸的是,这是一个低效的方法。实际上,Oracle在解析的过程中,会将‘*’依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间。

---糟糕的查询
select * from Table_Name1 
---较好的查询
select columnname1,columnname2,columnname3 from Table_Name1

2、使用表的别名 当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个column上。这样一来,就可以减少解析的时间并减少那些由column歧义引起的语法错误。 column歧义指的是由于SQL中不同的表具有相同的column名,当SQL语句中出现这个column时,SQL解析器无法判断这个column的归属。

---糟糕的查询
select columnname from Table_Name1 t1,Table_Name2
---较好的查询
select t1.columnname from Table_Name1 t1,Table_Name2

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

例子:从小卖部买东西(商品),假如有个库存表Table1,买东西出库表Table2.查找库存中的商品,是否有被卖出的,有的话就输出库存信息。

--糟糕的查询
SELECT column_name
FROM table_name1
WHERE column_name IN
( SELECT column_name
  FROM table_name2)
---较好的查询
SELECT column_name
FROM table_name1 outer
WHERE EXISTS
  (SELECT 1
  FROM table_name2 inner
  WHERE inner.column_name = outer.column_name)

4、用not exists 替代 not in 在子查询中,not in子句将执行一个内部的排序和合并。无论在那种情况下,not in 都是低效的(因为它对子查询中的表执行了一个全表遍历)。为了避免使用 not in ,我们可以把它改写成外连接(outer join)或 not exists。

---糟糕的查询
select columnname,columnname1 from Table_Name1 
where id not in(select id from Table_Name2 where name='A')
---较好的查询
select columnname,columnname1 from Table_Name1 t1,Table_Name2 t2 
where t1.id=t2.id
and t2.name<>'A'
---更好的查询
select columnname,columnname1 from Table_Name1 t1
where not exists(select 1 from Table_Name2 t2
where t2.id=t1.id
and t2.name='A')

5、用表连接替换Exists 通常来说,采用表连接的方式比Exists更有效率。 但是很多情况下我们无法将Exists改编为连接。

---糟糕的查询
select columnname,columnname1 from Table_Name1 t1
where not exists(select 1 from Table_Name2 t2
where t2.id=t1.id
and t2.name='A')
---较好的查询
select columnname,columnname1 
from Table_Name1 t1
join Table_Name2 t2 on t1.id=t2.id
where t2.name='A'

6、用exists替换distinct

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在select字句中使用distinct。一般可以考虑用Exists替换。Exists使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果。

例子:从小卖部买东西(商品),假如有个库存表Table_Name1,买东西出库表Table_Name2.查找库存中的商品,是否有被卖出的,有的话就输出库存信息。

--糟糕的查询
SELECT DISTINCT t1.column_name
FROM table_name1 t1, table_name2 t2
WHERE t1.column_name = t2.column_name;
---较好的查询
SELECT column_name
FROM table_name1 outer
WHERE EXISTS
  (SELECT 1
  FROM table_name2 inner
  WHERE inner.column_name = outer.column_name)

7、用>=替换> 如果id上有一个索引,则:

///糟糕的查询
select * from EMP where id>3;
//较好的查询
select * from EMP where id>=4;

两者的区别在于,后者将直接跳转到第一个id等于4的记录而前者将首先定位到id=3的记录并且向前扫描到第一个id大于3的记录。

8、用UNION替换OR 通常情况下,用UNION替换where字句中的OR将会起到较好的效果。对索引列使用OR将造成全表扫描。注意,以上规则只针对多个索引列有效。查询效率可能会因为没有选择OR而降低。

---糟糕的查询
select id,name,reg where Table_Name1 where id=10 or reg='A'
---较好的查询
 select id,name,reg from Table_Name1 t1 where t1.id=10
 union
 select id,name,reg from Table_Name2 t2 where t2.reg='A'

当然以上要基于id列和reg列都是索引列。

9、用UNION-ALL 替换UNION 当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并,然后在输出最终结果前进行排序,并将重复记录过滤掉。 如果用UNION ALL替代UNION,这样排序就不是必要了,效率会因此得到提高。 需要注意的是,UNION ALL将重复输出两个结果集合中相同记录,因此还是要从业务需求 分析使用UNION ALL的可行性。

---糟糕的查询,需要进行排序
 select id,name,reg from Table_Name1 t1 where t1.id=10
 union
 select id,name,reg from Table_Name2 t2 where t2.id=10
---较好的查询,不需要排序
 select id,name,reg from Table_Name1 t1 where t1.id=10
 union  all
 select id,name,reg from Table_Name2 t2 where t2.id=10

考虑的时候一定也要基于业务的需求进行取舍。

10、避免在索引列上使用IS NULL和IS NOT NULL 对于单列索引,如果列包含空值,索引中将不存在此记录。 对于复合索引,如果每个列都为空,索引中同样不存在此记录。如果至少有一个列不为空。则记录存在于索引中。 因为空值不存在于索引列中,所以where子句中对索引列进行空值比较将使Oracle停用该索引。

---糟糕的查询
 select id,name,reg from Table_Name1 t1 where t1.id is not null
---较好的查询
select id,name,reg from Table_Name2 t2 where t2.id>=10

前提还是id列是索引列

总结

   本节暂时总结到这里,之后继续进行总结,感觉还是很有用的,然后在日常的工作中加以实践,应该对自己的能力有所改善。上面总结的都是常规的做法,当然具体优化还要根据具体的环境进行处理,处理方式复杂多变,但万变不离其宗。如有错误,请及时通知加以更正,谢谢。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Albert陈凯

理解SQL原理SQL调优你必须知道的10条铁律

原文地址: http://www.nowamagic.net/librarys/veda/detail/1502 我们做软件开发的,大部分人都离不开跟数据库打...

3895
来自专栏眯眯眼猫头鹰的小树杈

猫头鹰的深夜翻译:如何优化MYSQL查询

索引除了能够确保唯一的标记一条记录,还能是MySQL服务器更快的从数据库中获取结果。索引在排序中的作用也非常大。

922
来自专栏Pythonista

Mysql基础

数据库(database,DB)是指长期存储在计算机内的,有组织,可共享的数据的集合。数据库中的数据按一定的数学模型组织、描述和存储,具有较小的冗余,较高的数据...

2382
来自专栏数据和云

又见程序媛 | 从索引的创建角度分析热门“面试题”

关于周一 Eygle 在文章《千头万绪:从一道面试题看数据库性能和安全的方方面面》讲到的 SELECT* FROM girls WHERE age BETWEE...

1644
来自专栏乐沙弥的世界

MySQL EXPLAIN SQL 输出信息描述

1102
来自专栏java达人

mysql explain详解

对于经常使用mysql的兄弟们,对explain一定不会陌生。当你在一条SELECT语句前放上关键词EXPLAIN,MySQL解释它将如何处理SELECT,提供...

19310
来自专栏chenssy

MySQL的索引是什么?怎么优化?

索引类似大学图书馆建书目索引,可以提高数据检索的效率,降低数据库的IO成本。MySQL在300万条记录左右性能开始逐渐下降,虽然官方文档说500~800w记录,...

993
来自专栏逍遥剑客的游戏开发

汉字转成拼音首字母

4686
来自专栏Java帮帮-微信公众号-技术文章全总结

Oracle应用实战五——SQL查询

Oracle SQL SQL学习是重点,请仔细阅读。 ? O Oracle 结构化查询语言(Structured Query Language)简称SQL(...

3464
来自专栏自由而无用的灵魂的碎碎念

分享:Oracle sql语句优化

最近做查询时,写的一条查询语句用了两个IN,导致tuexdo服务积压了不少,用户没骂就不错了。最后经过技术经理的点拨,sql语句性能提升了大约10倍,主要用了表...

1771

扫码关注云+社区

领取腾讯云代金券