SQL优化最佳实践

东窗未白
385 阅读12分钟

SQL优化最佳实践

1.查看执行计划

有多种方式，这种比较好用：

调用dbms_xplan.display函数

explain plan for select ...
select * from table(dbms_xplan.display)

2. SQL优化相关的对象

2.1表

2.2字段

1.做表设计时，将访问频繁的字段放在最前面；

2.字段类型的影响：

  • 隐式数据类型转换
  • 错误数据类型带来的成本估算异常

2.3索引

2.4视图

2.5函数

2.6函数

2.7数据链（DB_LINK）

create public database link linkName connect to userName identified by pwd 
using 'ip:port/xe';

3.特有 SQL

3.1 MERGE

MERGE操作在一个语句中实现了两部分的功能，当记录匹配时，执行某个操作，当记录不匹配时，执行另外的操作。

当然也支持只执行一类的操作。

merge into empbak b
using emp e
on (b.id=e.id)
when matched then update set b.name=b.name||e.name
when not matched then insert (b.id,b.name) values(e.id,e.name);

3.2 INSERT ALL

INSERT ALL 即复合表插入，可以将一个查询结果行同时插入多个表中的功能。

使用 INSERT ALL 带来的好处是，通过读取一次原表就可以插入多张目标表，减少了重复读取的开销。

示例：

insert all
   when ottl<1000 then into small_orders values(oid,ottl,sid,cid)
   when ottl>1000 and ottl<2000 then into medium_orders values(oid,ottl,sid,cid)
   ...
select * from ...

3.4 WITH

WITH 即定义语句块，它出现在select语句中时，将一个查询语句定义为某个名称，并可在后续的查询块中引用。

WITH 语句可以定义多个查询，中间用逗号分隔。

示例：

with
dept_cost as 
(select * from dept),
emp_cost as
(select * from emp)
select * from dept_cost,emp_cost where ...

3.5 CONNECT BY/START WITH

适用于层次查询。

示例：

select emp_id,emp_name,manager_id,level
   from emp
   start with emp_id=1
   connect by prior emp_id=manager_id
   order siblings by emp_id;

4. 优化技巧

4.1 查询转换

4.2 访问路径

  1. 全表扫描

    使用全表扫描的场景：

    • 大范围数据读取的情况

      大范围是个相对的概念，一般来说，如果访问表中的大部分数据，用表扫描效率较高；如果访问表中的小部分数据，则使用索引访问的效率较高。这里涉及一个损益点的概念，当小于损益点时，索引访问效率较高；当高于损益点时，表访问效率高。

    • 从小数据表中读取数据的情况

      如果访问的数据规模较小，则优化器倾向于通过全表扫描的方式访问整个表。因为全表扫描使用了多块读的机制，往往效率是很高的。

    • 按照并行处理方式读取数据的情况

      在并行处理的情况下，全表扫描的执行速度会在更大程度上得到提高。

    • 使用FULL提示的情况

      这个提示告诉优化器，使用全表扫描访问表。

  2. ROWID扫描

    ROWID是指某一行所在数据文件、数据块以及行在该块中的位置。rowid扫描是指按照ROWID来访问数据，这种方式可以快速定位到目标数据上，也是Oracle访问单行数据最快的方法。

  3. 采样扫描

    采样扫描是一类特殊的扫描方式，它是从表中读取用户指定比例且满足条件的数据。

    采样扫描的语法如下：

    select ...
  from table_name sample {BLOCK option} (sample percent)
  where ...

    采样扫描支持两种方式，一种是按照指定块的采样，一种是指定记录的采样。

    • 指定块的采样

      扫描指定比例的数据块。

    • 指定记录的采样

      扫描多块，然后按比例返回记录。

  4. B树索引访问路径

    B树索引是以一种平衡树（Balance Tree）的形式保存数据的。结构上分为根节点（Root Node）、分支节点（Branch Node）、叶子节点（Leaf Node）。

    树中的最底层的块（也就是叶子节点）包含了每个索引键值和一个指定表中记录行的ROWID。查询数据时根据索引值，取得ROWID，再由ROWID取出数据。

    B树索引的扫描方式：

    • 索引唯一扫描

      如果存在UNIQUE或者PRIMARY KEY 约束，Oracle 经常实现唯一性扫描。

      为了进行索引唯一扫描而必须基于主键来创建索引或者创建唯一索引，且在SQL语句中必须为索引列使用=比较运算符。否则即使基于具有唯一值的列创建了索引，在执行时优化器也不可能选择索引唯一扫描，而会选择范围扫描。

      除了使用=外，IN一个单值也是可以的，如：

      select * from t_objects where object_id in (10);

    • 索引范围扫描

      索引范围扫描是索引最普遍的数据读取方式，常见的以下三种：

      • 在唯一索引列上使用了范围操作符（> < <> >= <= between）
      • 在组合索引上，只使用部分列进行查询，导致查询出多行
      • 在非唯一索引列上进行的任何查询

    • 索引全扫描

      索引全扫描不是扫描索引的全部节点块，而是指索引的全部叶块。

      索引全扫描的发生条件：

      • 至少有一个索引列被赋予了查询条件，有可能使用索引全扫描，也就是说赋予查询条件的索引列不一定是索引的第一列（即前导列）

      • 没有为索引赋予查询条件，但是具备以下两个条件

        • 查询语句所涉及的所有列都在索引中

        • 索引列中至少存在一个 NOT NULL 列

          如果在索引列中连一个 NOT NULL 列都不存在，在最坏的情况下，某个索引列的所有值全部为NULL,则此时索引列有可能没有被存储在索引中，这就使得索引中的行数与表中的行数不一致，从而导致无法从索引中读取数据。

      ==非常经典的问题----MAX/MIN==

      select max(object_id),min(object_id) from t_objects t;

      此时优化器给出的执行计划是全表扫描，显然这不是一个优质的执行计划，从简单分析可知，最大值、最小值时间对应的就是索引叶子节点的双向链表中左右端的数据，但优化器没有智能判断出来。

      改进方法：

      select max(object_id) from t_objects t union all select min(object_id) from t_objects;

      此时的执行计划为 INDEX FULL SCAN(MIN/MAX)，这是一种特殊的索引全扫描，它按照全扫描的方式扫描部分数据，快速得到结果。

    • 索引快速全扫描

      索引快速全扫描，一次读取多个数据块，读取对象也不限于叶子块，还包含分支块（也包括根节点），但是在处理中会忽略这些块。

      由于是一次读取多个块，且没按照索引顺序读取，因此查询后的结果是无序的。这种方式可以使用多块读的功能，也可以使用并行读。

      ==索引快速全扫描和索引全扫描的区别：==

      • 索引快速全扫描不会从树的根节点开始读取，而是直接扫描所有叶子节点；

      • 一次读取 db_file_mutiblock_read_count 个数据块，而不是一个数据块。

        如果db_file_mutiblock_read_count 值过小、强制使用索引扫描时，会发生INDEX FULL SCAN,反而会走索引全扫描。

    • 索引跳跃扫描

      其原理相当于执行了多次范围扫描，并将结果合并返回。

      发生条件：

      在索引中有多个列，且第一个列重复值不多，此时如果只访问第二列，则Oracle可能会采取索引跳跃扫描。

4.3 表间关联

4.3.1 表间关联关系类型：

  1. 内连接

    --标准写法
select t1.*,t2.* from t1 inner join t2 on t1.a=t2.a;
--专有写法
select t1.*,t2.* from t1,t2 where t1.a=t2.a;

  2. 左连接

    --标准写法
select t1.*,t2.* from t1 left join t2 on t1.a=t2.a;
--专有写法
select t1.*,t2.* from t1,t2 where t1.a=t2.a(+);

  3. 右连接

    --标准写法
select t1.*,t2.* from t1 right join t2 on t1.a=t2.a;
--专有写法
select t1.*,t2.* from t1,t2 where t1.a(+)=t2.a;

  4. 全连接

    --标准写法
select t1.*,t2.* from t1 full join t2 on t1.a=t2.a;
--专有写法
select t1.*,t2.* from t1,t2 where t1.a(+)=t2.a(+);

  5. 笛卡尔积

    --标准写法
select t1.*,t2.* from t1 cross join t2;
--专有写法
select t1.*,t2.* from t1,t2;

对于上面的两种写法，在某些场合下性能会有差异，==一般建议使用专有写法==。

4.3.2 表关联的实现方法：

  1. 嵌套循环

    • 如果外部表比较小，并且在内部表上有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较高的效率；
    • 嵌套循环有其他连接方法没有的一个优点：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。

  2. 排序合并连接

    分别将关联的两个表按照他们各自要连接的列排序，然后将已经排序的两个源表合并。如果找到匹配的数据，就放到结果集。

    引入这种连接方式的目的是缩减在嵌套循环连接中发生的随机读取量，但由于排序的存在，增加了连接的代价。

    • 对于非等值连接，这种连接方式的效率比较高；
    • 如果在关联的列上都有索引，效果更好；
    • 对于将两个较大的行源做连接，该连接方法比嵌套循环连接要好一些。但是如果排序合并返回的数据量过大，则又会导致使用过多的ROWID，在表中查询数据时，数据库性能下降，因为I/O过多。

  3. 哈希连接

    引入较晚，目的主要是解决嵌套循环中大量随机读取的问题，又要解决排序合并连接中排序代价过大的问题。

    • 一般来说，其效率应该好于其他两种连接，但是这种连接只能用于CBO优化器中，而且需要设置合适的hash_area_size参数，才能取得较好的性能；
    • 在两个较大的行源之间连接时会取得相对较好的效率，在一个行源较小时则能取得更好的效率；
    • 只能用于等值连接中。

4.4 半连接与反连接

4.4.1 半连接

常见的有使用：

IN，EXISTS 关键字。

==注意：==

  • IN 是比较运算符，主查询和子查询之间的连接列清晰可见；

  • EXISTS 并不是比较运算符，而是判断满足与否的布尔函数。在该过程中不需要对满足查询条件的全部数据进行判断，只需要利用一个证据实现对整行数据以及某个列值的所有相同值的判定。

4.4.2 反连接

常见的有使用：

NOT IN、NOT EXISTS、OUTER JOIN 关键字。

==注意：==

当使用 NOT IN 时，如果子查询中返回的列表中包含空值，可能会带来一些异常的情况，如查询不到数据，常见的策略是在子查询部分加上非空条件。

4.5 排序

排序操作是数据库中比较消耗资源的一类操作，也是优化的重点。

4.5.1 引发排序的操作

可能引发排序的操作很多，并不是简单的 ORDER BY 会引发排序。

  1. 创建索引

    索引是个有序的结构，创建索引肯定需要将索引值排序后才能建立索引。

    如果在创建索引时就知道某个索引列是升序的（预先排过序或递增插入），则可以在索引建立时使用 NOSORT 字句，消除索引建立的排序动作。

  2. 某些 SQL

    带有如下子句的查询：

    DISTINCT、ORDER BY、GROUP BY、UNION、MINUS、INTERSET、CONNECT BY、CONNECT BY ROLLUP 。

    其中对于 ORDER BY、CONNECT BY、GROUP BY、CONNECT BY ROLLUP 子句，服务器进程要对子句中指定的值或条件进行排序。

    对包括 DISTINCT、UNION、INTERSET、MINUS 的查询，服务器进程要清除重复值。

    ==优化点：==

    • 有些不必要的需求，可以转换写法，例如对结果集不需要去重的话，就可以使用 UNION ALL 代替 UNION ；

    • 对于类似 ORDER BY 的部分，可以利用索引的有序结构，避免排序；

    • 对于主键字段的访问，也不需要 DISTINCT、UNIQUE，因为其本身就是唯一的。

  3. 排序合并连接

    如果查询两个或多个表的等值连接请求时没有发现索引，则服务器进程会进行排序合并连接。如果优化器选择排序合并，则服务器进程要对每个表进行全表扫描，按连接列的值分别排序每个表，然后根据条件中的值合并表。

    ==优化点：==

    如果执行关联查询的表的连接列是存在索引的，则可不需要排序环节，这样可大幅度提升性能。

  4. 收集统计信息

  5. 其他情况

4.5.2 优化方法

  1. NOSORT 索引

    如果要检索的列值是升序（预先排序或者递增插入），则可以在索引建立时使用 NOSORT 子句，消除索引建立的排序阶段。

  2. UNION ALL 而不是 UNION

    UNION ALL 子句并不消除重复项，因此查询阶段不需要排序。

  3. 表连接使用索引访问

    排序合并连接，需要全表扫描和一个排序合并结果集。

    基于索引访问的嵌套循环连接，可以减少全表扫描和消除排序，从而提高性能。

  4. 对 ORDER BY 子句引用的列使用索引

    对经常在 ORDER BY 子句中引用 的列生成索引，这样 Oracle 用索引提供顺序，而不是进行排序。

  5. 主键选择

    主键的 select，不需要 distinct 和 unique 子句，因为主键本身唯一。

  6. 选择要分析的特定列

  7. 用 estimate 而不是 compute

4.5.3 优化示例

  1. 使用索引避免排序

    create table t_nosort(id number primary key,name varchar2(30) not null);
create index ind_t_nosort_name on t_nosort(name);
insert into t_nosort select rownum,table_name from dba_tables;

select id,name from t_nosort order by name;

image.png

此时有排序动作

select /*+index(t_nosort ind_t_nosort_name) */ id,name from t_nosort order by name

image.png

利用了索引全扫描，可以避免排序。

如果是倒排序（order by desc），可以使用 index_desc 提示利用索引扫描避免排序。

  1. 通过包含连接的查询避免排序

    create table t1(id number primary key,name varchar2(30) not null);
create table t2(id number,name varchar2(200));
create index ind_t1_name on t1(name);

insert into t1 select rownum,table_name from dba_tables;
insert into t2 select rownum,object_name from dba_objects;

    • 不排序执行计划：

image.png

  • 普通排序执行计划：

image.png

有 SORT ORDER BY 的操作。

  • 使用 MERGE JOIN 避免排序

image.png

注意：

  • MERGE JOIN 只能对连接列排序
  • 排序操作只能是升序，对于降序 MERGE JOIN 却无能为力
4.5.4 执行计划中的 Sort

  1. Sort Aggregate

    Sort Aggregate 不一定涉及排序，它通常发生在使用一些聚合函数的时候，如sum、avg、max、count等。

  2. Sort Unique

    排序去重，往往发生在用户指定distinct语法或者下一步操作需要唯一值时，会导致Sort Unique。

  3. Sort Join

    在排序合并连接时将会发生Sort Join, Sort Join 发生在出现Merge Join的情况下，两张关联的表要各自做Sort，然后再Merge。

  4. Sort Group By

    当聚合用来计算不同组的数据时，将会使用Sort Group By，排序需要将行值分成不同的组。

    这个操作经常发生在Group By的时候，在10g R2以后，Sort Group By被 Hash Group By 所代替。

  5. Sort Order By

    这是最常见的一种情况，按照一个非索引列进行排序就会发生这种情况。

  6. Buffer Sort

    指在内存中进行的排序动作。有时 Oracle 会借助会话私有的内存区域（PGA）完成动作，但这个提示并不表明一定发生排序。

4.6 子查询

  1. OR 问题

    对含有 OR 的 半连接或反连接，注意有 FILTER 的情况，如果 FILTER 影响效率，可以通过改写为 UNION、UNION ALL、AND 等逻辑条件进行优化。

  2. [NOT] IN/EXISTS 问题

    • IN/EXISTS

      IN 操作是先进行子查询操作，再进行主查询操作。

      EXISTS操作是先进行主查询操作，再到子查询中进行过滤。

      当子查询表数据量巨大且索引情况不好（大量重复值等），则不宜使用 IN;

      当外部表数据量巨大且索引情况不好（大量重复值等），则不宜使用 EXISTS。

      如果限制性强的条件在子查询，一般建议用 IN 操作，反之，则用 EXISTS 操作。

    • NOT IN/EXISTS

      在子查询中， NOT IN 子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN 都是最低效的（因为它对子查询的表执行了一个全表遍历），为了避免使用 NOT IN，可以把它改成外连接或者 NOT EXISTS。

4.7并行

基本原理是将一个大的数据块分割成多个小的数据块，然后启动多个进程分别处理多个数据块，最后由一个进程整合结果返回给用户。

并行不是任何情况都适用，一般只有当系统有大量闲置资源，且 SQL 通过串行方式执行时间过长时，才考虑使用并行处理，但同时要考虑闲置并行使用的数量。

SQL语句并行提示：

select /*+parallel(t 2) */ count(1) from t;
avatar
文章
阅读
粉丝