常用的SQL优化原则

最新推荐文章于 2020-10-15 10:08:19 发布

川川Jason

最新推荐文章于 2020-10-15 10:08:19 发布

阅读量198

点赞数

分类专栏： Database 文章标签： SQL 优化

本文链接：https://blog.csdn.net/bansan/article/details/84413600

版权

Database 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

本文提供了Oracle SQL优化的实用建议，包括分析SQL执行计划、合理使用索引、避免隐式类型转换、使用UNION ALL代替OR提高查询效率、正确选择连接方法等。还介绍了如何根据具体情况调整优化器参数。

摘要由CSDN通过智能技术生成

同事写的，我整理了一下

1）通过分析SQL执行计划来优化SQL，这是最直接、最有效的方式。

2）Oracle SQL优化的目标主要有如下三点：降低执行SQL语句所需要的工作负载；均衡执行SQL语句所需要的工作负载；并行化执行SQL语句所需要的工作负载。

3）SQL的优化原则不能一概而论，与很多因素有关。比如你的系统的种类，是OLTP还是OLAP?系统数据量的大小；数据的分布；表和索引是否分析；表的一些存在严重倾斜的列上是否存在直方图；你用hint了吗？

optimizer_mode的值（Oracle 10g中的选项有first_rows/all_rows/first_rows_n，9i中还有rule/choose）是什么；

db_file_multiblock_read_count的值(影响Oracle在执行全表扫描时一次读取的block数量， 10gR2后数据库会根据系统的情况自动调整)是多少；_optim_peek_user_binds的值（当数据倾斜时是否对执行计划纠偏，可选值为true/false，默认为true）是什么；

optimizer_index_cost_adj 的值(索引扫描与全表扫描的成本比较，值范围为1~10000，百分比，缺省为100，表示两者等价)是多少等等。

总之，调优非常灵活，没有固定的模式，应该具体问题具体分析。

4）索引不一定比全表扫描快。

5）不要在where中使用function，如果要用，可以考虑建函数索引。

6）建联合索引时把离散程度最高的列放在最前面，这适用于主键索引或者需要高选择性的index range scan的索引。

7）不要让SQL执行时产生隐式转换。

8）如果有可能，就用union all 代替or吧。

通常情况下, 用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”

9）用exists不一定比用in好！如果子查询中的select有很好的选择性，那就用in吧！即in适合于外表大而内表小的情况；exists适合于外表小而内表大的情况。这里要注意not in中的null问题。

10）使用count(*)而不是count(columnname)，count(columnname)当碰到所查的列的值有null的时候，结果就不准了。

11）null值不入普通的B树索引，所以对B树索引而言，纯粹的null/not null条件是不会使用该索引的。

12）不要在重复值过多（即离散程度很小）的列上建索引

13）在基数小的字段上要善于使用位图索引。位图索引会记录相关的NULL值列信息。

14）CBO并不总是正确的！当你发现CBO产生的执行计划有问题时，先仔细分析一下为什么会这样：表和索引分析了吗？数据是否倾斜？或者你可以做一个10053或者10046的trace，分析一下原因。

15）不要对长度太长的VARCHAR2字段建索引。如果确实有这样的需求，可以考虑使用oracle text。
16）Nested Loops(NL)连接时要选择正确的连接顺序，选取的原则通常是尽量选择返回较少数据的表作为驱动表。

17）对于Nested Loops(NL)而言，如果driving row source(外部表)比较小，并且在inner row source(内部表)上有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到很好的效率；NL有其它连接方法没有的一个优点：NL可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速响应。

18）Sort Merge Join(SMJ)对于非等值连接，这种连接方式的效率是比较高的；如果在关联的列上都有索引且两个row source差不多大，可以考虑使用SMJ；SMJ通常并不适合于OLTP系统，本质原因是因为对于OLTP系统而言，排序是非常昂贵的操作。除非你能避免排序操作，比如关联的列上都有索引。

19）Hash Join(HJ)适合于小表与大表连接并且返回大型结果集的连接，其效率好于SMJ与NL，但是这种连接只能用于等值连接且必须用CBO。若要让HJ取得较好的效率，如下几点要注意：确认小表是驱动表；确认涉及到的表和连接键都分析过了；如果在连接键上数据不均匀的话，一定要收集直方图；如果可以，调大pga_aggregate_target的值。

如何看执行计划

1）从缩进度最大的行读取，它是最先被执行的步骤

2）当缩进一样时，最上面的最先被执行

其他资料：

优化器模式 http://blog.sina.com.cn/s/blog_492c0acd0100d0u0.html

表扫描方式 http://blog.csdn.net/doupeihua/article/details/6822591

Oracle 连接和半连接 http://blog.sina.com.cn/s/blog_7c5a82970101ix2l.html