Oracle SQL优化总结

文章目录
  1. 1. 基本优化总结
    1. 1.1. 执行连接查询时使用完全限定的列引用
    2. 1.2. 使用CASE来实现表列的的多种计算,而不是多个查询
    3. 1.3. 增加表索引
    4. 1.4. 分组查询时使用WHERE而不是HAVING
    5. 1.5. 在对结果进行合并时使用UNION ALL而不是UNION
    6. 1.6. 判断结果在某个列表中时使用EXISTS而不是IN
    7. 1.7. 去重时使用EXISTS而不是DISTINCT
    8. 1.8. 传递查询条件时使用绑定变量
  2. 2. 比较执行查询成本

基本优化总结

执行连接查询时使用完全限定的列引用

在两张表连接查询时,需要明确指定显示的列具体所属哪张表。比如:

1
2
SELECT a.name, age FROM A a, B b WHERE a.id = b.id;
SELECT a.name, b.age FROM A a, B b WHERE a.id = b.id;

使用①SQL去查询时,由于age没有指定所属于那张表,在显示age时,会从A、B两张表中去查找存不存在age列

而使用②SQL查询时,明确指定了age列属于b,name会从a中查找,age从b中查找,效率明显高于①SQL。

使用CASE来实现表列的的多种计算,而不是多个查询

当需要对表的相同行执行多种计算时,使用CASE表达式而不是多个查询。如:

1
2
3
SELECT COUNT(CASE WHEN T.PRICE < 13 THEN 1 ELSE NULL END) LOW,
COUNT(CASE WHEN T.PRICE BETWEEN 13 AND 15 THEN 1 ELSE NULL END) MED,
COUNT(CASE WHEN T.PRICE > 15 THEN 1 ELSE NULL END) HIGH FROM PRODUCTS T;

增加表索引

查询某个表时,如果经常使用其他的几个列当做查询条件,这个时候就可以考虑为这几个列新建索引。需要注意的是:like模糊匹配是不走索引的

并不是所有查询都可以建立索引,有一个基本准则:当任何单个查询要检索的行少于或等于整个表行数的10%时,就应当创建索引。其次,由于性能方面的原因,通常应该将索引与表存储到不同的表空间中。

Oracle索引可以分为两类:B-树索引和位图索引。

B-树索引:索引的选列应该是用来存储很大范围的值的列。Oracle数据库会为表的主键以及包含在唯一约束中的列自动创建B-树索引。

位图索引:包含小范围值的列

分组查询时使用WHERE而不是HAVING

WHERE子句用于过滤行,HAVING子句用于过滤行组。因为行被分组之后(需要花费时间),HAVING才可以过滤行组,所以应该尽量使用WHERE子句过滤行。这样首先避免了花费时间去分组应该被过滤的行。

在对结果进行合并时使用UNION ALL而不是UNION

UNION ALL用于获得两个查询检索到的所有行,包括重复行;

UNION用于获得查询检索到的所有不重复的行。

因为UNION要删除重复行(这需要时间),所以尽量使用UNION ALL。

判断结果在某个列表中时使用EXISTS而不是IN

IN用于检查一个值是否包含在列表中。EXISTS用于检查子查询返回行的存在性。

EXISTS与IN不同:EXISTS只检查行的存在性,而IN检查实际的值。EXISTS提供的性能通常比带有子查询的IN提供的性能要好。

因此,应该尽量使用EXISTS而不是IN。

去重时使用EXISTS而不是DISTINCT

DISTINCT用于禁止重复行的显示,EXISTS用于检查子查询返回航的存在性。应该尽量使用EXISTS而不是DISTINCT,因为DISTINCT在排除重复行之前要对检索到的行排序。

传递查询条件时使用绑定变量

Oracle数据库会缓存执行过的SQL,如果以后执行相同的语句,就重用缓存的SQL。重用SQL会缩短执行时间。但是需要遵守以下规则:要重用的缓存语句,SQL必须完全相同。这意味着:

  • SQL语句中的所有字符必须相同(不忽略大小写)
  • SQL语句中的所有空格必须相同

所以在SQL中需要提供不同的列值时,可以使用绑定变量,而不是字面量列值,可以提高SQL执行效率。

Oracle中,变量通常使用冒号加变量名来定义。由此我们可以联想到,在Java代码中,编写SQL时,为什么会尽可能的不要直接拼接SQL,而是使用占位符。

比较执行查询成本

Oracle数据库软件使用称为优化器的子系统,生成访问表中存储数据的最有效路径。优化器生成的路径称为执行计划。Oracle Database 10g及以上版本自动收集表和索引中数据的统计信息,从而生成最优执行计划;这称为基于成本的优化。

比较优化器生成的执行计划可以判断一条SQL语句和另一条SQL语句的相对成本,并可以根据结果提高SQL语句的性能。


关注我的微信公众号:FramePower
我会不定期发布相关技术积累,欢迎对技术有追求、志同道合的朋友加入,一起学习成长!


微信公众号1

如果文章对你有帮助,欢迎点击上方按钮打赏作者