Oracle SQL 优化干货总结

avatar
作者
筋斗云
阅读量:12

oracle 多表left join_oracle 多表left join_oracle 多表left join

随着数据库使用的时间长短,以及现在商业规模的扩大,数据量成级数增长;大数据,云技术等越来越多的海量数据,分析和检索成为数据使用的瓶颈。现在通常是增加硬件投入,增加,增加,增加。无论是工业化还是发电等带给人类的环境污染,还是数据使用的便捷,都值得人类反思。

再多的硬件投入,也无法弥补 低效能SQL的过失。

Oracle 数据库作为商业数据库的杰出代表。优化程序,减少硬件和电力的消耗才是出路。

优化......

优化......

优化......

oracle 多表left join_oracle 多表left join_oracle 多表left join

提升性能,就是减少CPU,I/O等性能消耗。

一 缩小检索范围

① 分区表尽量加分区条件;

执行计划区别:(PARTITION RANGE ALL 分区全扫) (PARTITION RANGE SINGLE 分区范围)

② 缩小范围 (全扫能否加过滤条件’最好是索引条件’)

③、是否存在没意义的关联条件

如 1:A.id in (Select id from a1)

① and A.id in (Select id from a1 where id=1)

② 此时可以注释掉①部分避免没必要的关联和查询。

如 2:业务功能类似,功能 1 需要范围 abcde5 张表,功能 2 范围 abc3 张表;

排除的表 / 字段都等需求都相同,开发可能为了省事改写,直接将功能 2 sql 直接使用功能 1 的 sql;

最终最外层将不需要字段筛选掉。

④、left join 确认能否改成 join

left join 关联方式 存在选错驱动表被驱动表, 某些时候可能还会存在量小应该走索引时不走索引。

且产生大量不必要的中间结果,产生大量物理 / 逻辑读;Join 可以过滤数据 (过滤条件)。

二 查看执行计划cost

cost值小,不一定运行效率好

cost值大,一定运行效率不好

①、小表 / 小分区 变大表 / 大分区,不统计引起的 执行计划错误问题;

②、特殊情况可加 hint (driving_site、full、no_index、use_hash ..)语句加 hint 需要和 dba 协商

数据库统计信息没问题时,大多数没特殊情况都是不需要加 hint 指定执行计划的;

③、善于用绑定变量;

④、使用标量子查询要量力而行;

⑤、能传入定值部分尽量传入定值

若某字段传一定值,凡是用到这个传定值字段的部分都尽量直接传值 (例如:关联条件,group by 等)

oracle 在生成执行计划时要通过关联条件等衡量 2 个表哪个驱动被驱动表,若直接写定值,

数据库会更准确的选择最好的执行计划去数据库中读取数据,group by 排序部分为了减少资源消耗。

执行计划出现如下情况需要严重注意的:

①、CARTESIAN,笛卡尔乘积,需要确认是否有落下的表没有和其他表做关联;

②、filter,驱动表量越大越会引起性能问题;

③、Nest loop 驱动表或被驱动表全扫时

最糟糕的情况:驱动表和被驱动表都是全扫,且随着量的增加后续会存在性能问题..

大表做循环 --> 加索引

选择性很差的索引循环 --> 删索引

-->2 大表做 nest loop 循环时,需要衡量使用频繁度且尽量和 dba 协商,来适当加索引;

-->nest loop 是选择性很差 (distinct key 很小) 的索引 (且执行时间较慢) 时,需要 dba 分析该索引;

若删除索引,建议做个统计确认这几个月是否真的没人用再进行删除;

三 一点经验

①、update 和 merge 的选择

如下说的不针对全部情况,具体慢的情况根据业务 / 数据情况做更改’

update:更改单表,小表关联时 比 merge 速度快,稳定好;

merge:相同表既做更改后字段也做过滤条件时 “update A set i=(select i from b where a.id=b.id) where i(select i from b where a.id=b.id)” 部分需要额外消耗,可以用 merge 避免;

②、merge into where 条件除更新字段要放到 on 里

字段 :where 条件 (主键 id=1) 不走索引,放在 on 会走主键索引;

子查询:where 包含子查询没有放到 on 里可能存在执行计划没出现子查询关联表的情况,从而导致跑不出结果的情况。

③、不要使用 (id = 1 or in 子查询) 形式

原因是:执行计划会选择 filter,驱动表过滤后条目很多的话,

被驱动表会产生热点块。

④、exists 和 in 的选择 (避免 filter)

exists 比 in 产生 filter 执行计划几率大,所以当 sql 慢,且按 F5 发现执行计划中有 filter 可以试着改成 in,

若出现 in 子查询里表全扫或者还存在慢的情况,可以考虑将 in (子查询) A 与外层 B 做 join 关联,

若还存在问题,确认是否存在本文提到的情况,针对整体衡量去优化。

补充:[not] exists 子查询里不存在与外层关联的条件,最终结果有无数据的现象

⑤、分页:rownum 和 row_number 的选择

⑥、适当选择使用 rowid...

⑦、尽量不写 is null 等会引起不走索引的条件,“索引不记录空值”

若字段有 nvl 函数索引可以写 NVL(字段, 0))=0 来当做 is null 判断,可走函数索引

⑧、代码 If count >0.. 做判断时,若 rownum=1 能查出数据就满足 count>0

就不必全部取出再判断 count>0... 所以这样的判断可以统一加 rownum=1

全部做更改后 dba 需要跟踪 sql 确认是否有存在 rownum 缺点引起的问题’

MySQL 使用 limit 1,Oracle 使用 rownum=1 来取数据中一行。

⑨、 合理使用临时表解决大量下载功能

⑩、 临时用的表: 临时属性表?普通表?

临时表:不可统计,create/insert 大量后,再调用临时表可能存在选择索引情况引起性能问题可能;

普通表:create as select ... 不统计,再调用时,数据库会使用动态采样去产生正确的执行计划;若是 insert 需要及时统计,否则可能会存在和临时表的问题。

希望有小小的帮助。

oracle 多表left join_oracle 多表left join_oracle 多表left join

广告一刻

为您即时展示最新活动产品广告消息,让您随时掌握产品活动新动态!