`

常用的SQL优化原则

阅读更多

    同事写的,我整理了一下酷

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

 

分享到:
评论

相关推荐

    SQL 优化常用原则

    SQL语句 优化常用原则。 1. 选用适合的ORACLE优化器

    SQL优化原则

    ### SQL优化原则及注意事项 #### 一、问题的提出与背景 在软件开发过程中,特别是在初期阶段,由于数据库中的数据量相对较少,开发者往往难以直观感受到不同SQL语句编写方式所带来的性能差异。然而,随着互联网...

    oraclesql优化_Oracle中优化SQL的原则.pdf

    oraclesql优化_Oracle中优化SQL的原则.pdf

    sql语句优化原则

    SQL语句优化原则是数据库管理和应用开发中的关键环节,它涉及到如何提高查询效率,减少资源消耗,提升系统整体性能。以下是一些针对SQL语句优化的重要原则和方法: 1. **利用索引**:索引是提高查询速度的关键。...

    基于Oracle数据库的SQL优化原则和方法.pdf

    【SQL优化原则】 1. **减少I/O操作**:I/O操作是数据库性能的瓶颈之一。优化的目标是尽量减少磁盘读写,避免不必要的数据传输,以减轻CPU的负担。 2. **优化排序操作**:频繁的排序操作会导致额外的计算开销,应当...

    基于案例学习SQL优化

    在“基于案例学习SQL优化”的课程中,我们主要探讨如何提升数据库性能,特别是针对SQL查询的优化技巧。DBA(数据库管理员)作为关键角色,需要掌握这些技能来确保系统的高效运行。以下是根据课程标题和描述提炼出的...

    SQL 优化原则

    (转)SQL 优化原则 一、问题的提出  在应用系统开发初期,由于开发数据库数据比较少,对于查询SQL语句,复杂视图的的编写等体会不出SQL语句各种写法的性能优劣,但是如果将应用系统提交实际应用后,随着数据库中...

    SQL语句优化原则.pdf

    具体来说,有几个重要的SQL语句优化原则需要掌握和理解: 1. 判断空值(NULL)与索引的使用。在数据库中,包含NULL值的列不会被索引。因此,在where子句中使用ISNULL或ISNOTNULL条件时,优化器将不会使用索引,而是...

    sql优化的几种方法

    ### SQL优化的关键方法 #### 一、选择最有效的表名顺序 在构建SQL查询时,尤其是在涉及多个表的连接查询中,表名的顺序对于查询性能有着重要影响。Oracle数据库的解析器是从右向左处理FROM子句中的表名。为了优化...

    《基于Oracle的SQL优化》PDF版本下载.txt

    ### SQL优化的一般原则 1. **选择合适的索引**:索引是提高查询速度的有效手段。合理的索引设计可以显著提高查询效率。例如,在经常用于条件筛选的列上建立索引。 2. **避免全表扫描**:全表扫描会大大增加查询...

    sql优化书籍大全

    首先,我们要明白SQL优化的基本原则:减少查询次数、减小数据量、合理设计索引以及优化查询语句结构。这四个原则贯穿于整个SQL优化过程,是提升查询效率的基础。 1. 减少查询次数:通过联合查询、子查询优化和存储...

    ORACLE数据库SQL优化原则.pdf

    ORACLE数据库SQL优化原则.pdf

    关于SQL优化的电子书

    尽管给定描述并未提供具体的信息,但从标题“关于SQL优化的电子书”及标签“sql优化”,结合部分内容可以看出,此电子书聚焦于SQL应用的优化技术。以下将深入解析与SQL优化相关的专业知识点: ### SQL优化的核心...

    Oracle中优化SQL的原则

    在Oracle数据库中,SQL优化是提升系统性能的关键环节。以下是一些优化SQL的原则和策略: 1. **完全匹配已检验的语句**:确保执行的SQL语句与已经在共享池中的语句完全一致,这有助于避免不必要的解析和计划生成,...

    SQL Server SQL优化

    #### 一、SQL优化的重要性与原则 在SQL Server的实际运行过程中,随着数据量的增长和用户访问频率的提升,数据库性能问题逐渐显现。根据所谓的“二八法则”,即大约20%的慢查询消耗了系统80%的资源,这表明对SQL...

    SQL_Server_性能优化的原则

    ### SQL Server 性能优化的原则 #### 一、SQL Server 自调整特性 SQL Server 的版本迭代不断优化其自我管理和自我调整的能力。特别是从 SQL Server 7.0 开始,这一趋势更加明显。此版本提供了更高级别的自配置与自...

    SQL语句的优化及SQL 性能调整原则

    SQL语句的优化是提升数据库系统性能的关键环节。在开发初期,由于数据量较小,可能无法明显察觉到SQL语句的性能差异,但随着数据的...SQL优化是一个持续的过程,需要不断学习和实践,以便适应不断变化的数据和需求。

    大道相通,得鱼忘筌 - 从Oracle的SQL优化到MySQL的SQL优化.pdf

    在数据库管理与优化领域,SQL优化是至关重要的一环,它涉及到数据库性能的提升以及系统资源的有效利用。在不同数据库管理系统(DBMS)之间,如从Oracle迁移到MySQL,虽然两个系统有着各自的特性,但是优化的核心思想...

Global site tag (gtag.js) - Google Analytics