注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oracle、MySQL资料及经验

.

 
 
 

日志

 
 

基于案例学SQL优化 第一周,从案例中推导sql优化的总体思路和误区  

2013-11-16 20:30:20|  分类: 笔记 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
 

notepad++

 

基于案例学SQL优化   第一周,从案例中推导sql优化的总体思路和误区 - 熊猫兔 - Oracle资料及经验

 

 

一. 缺乏对讹传的辨知力。

 

1. 说说你从第1节中围绕涉及到count(*)和count(列)的描述中,你学到了什么。

在没有索引的情况下,count(*)和count(列)一样快。

在有索引的情况下,如果该列允许为空,则count(列)比count(*)快,如果该列不允许为空,count(*)和count(列)一样快。

count(*)和count(列)不等价,count(列)统计的是该列不为空的记录数,count(*)则只要该行有一列不为空则被统计出来。

SCOTT@orcl> select count(comm) from emp;

COUNT(COMM)

-----------

    4

count(列)中列的偏移量决定性能,列越靠后,访问的开销越大,count(*)的算法与列偏移量无关,所以count(*)最快,count(最后一列)最慢。

 

表连接顺序和条件顺序是RBO时代的事情了,在CBO时代不需要注意。

 

10g中not in 中指定连接列非空,则跟not exists的执行计划一致,执行会变快。(ANTI是针对什么不是什么推出的算法,比filter高效)

在11g中not in和not exists执行计划一致,无需关心大小表关系,无需把not in再转成not exists。In和exists呢?

SCOTT@orcl> create table t1 as select * from dba_objects;

SCOTT@orcl> create table t2 as select * from dba_objects where object_id<=10000;

SCOTT@orcl> create table t3 as select * from dba_objects where object_id<=10;

SCOTT@orcl> set autot trace exp stat

SCOTT@orcl> select * from t1 where object_id in (select object_id from t2);

 

9709 rows selected.

 

Elapsed: 00:00:00.34

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1275841967

 

-----------------------------------------------------------------------------

| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |      |  8293 |  1781K|   327   (1)| 00:00:04 |

|*  1 |  HASH JOIN RIGHT SEMI|      |  8293 |  1781K|   327   (1)| 00:00:04 |

|   2 |   TABLE ACCESS FULL  | T2   |  8294 |   105K|    39   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL  | T1   | 89185 |    17M|   287   (1)| 00:00:04 |

-----------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - access("OBJECT_ID"="OBJECT_ID")

 

Note

-----

   - dynamic sampling used for this statement (level=2)

 

 

Statistics

----------------------------------------------------------

          0  recursive calls   --递归调用

          0  db block gets

       1799  consistent gets

       1025  physical reads

          0  redo size

     436316  bytes sent via SQL*Net to client

       7532  bytes received via SQL*Net from client

        649  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       9709  rows processed

 

SCOTT@orcl> select * from t1 where exists (select object_id from t2 where t1.object_id=t2.object_id);

 

9709 rows selected.

 

Elapsed: 00:00:00.36

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1275841967

 

-----------------------------------------------------------------------------

| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |      |  8293 |  1781K|   327   (1)| 00:00:04 |

|*  1 |  HASH JOIN RIGHT SEMI|      |  8293 |  1781K|   327   (1)| 00:00:04 |

|   2 |   TABLE ACCESS FULL  | T2   |  8294 |   105K|    39   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL  | T1   | 89185 |    17M|   287   (1)| 00:00:04 |

-----------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

 

Note

-----

   - dynamic sampling used for this statement (level=2)

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       1799  consistent gets

       1025  physical reads

          0  redo size

     436316  bytes sent via SQL*Net to client

       7532  bytes received via SQL*Net from client

        649  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       9709  rows processed

--以上看以看出对于大表,in和exists效率和消耗一致。

 

SCOTT@orcl> select * from t1 where object_id in (select object_id from t3);                         

 

9 rows selected.

 

Elapsed: 00:00:00.03

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2868643349

 

-----------------------------------------------------------------------------

| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |      |     9 |  1980 |   291   (1)| 00:00:04 |

|*  1 |  HASH JOIN RIGHT SEMI|      |     9 |  1980 |   291   (1)| 00:00:04 |

|   2 |   TABLE ACCESS FULL  | T3   |     9 |   117 |     3   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL  | T1   | 89185 |    17M|   287   (1)| 00:00:04 |

-----------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - access("OBJECT_ID"="OBJECT_ID")

 

Note

-----

   - dynamic sampling used for this statement (level=2)

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       1032  consistent gets

       1025  physical reads

          0  redo size

       1690  bytes sent via SQL*Net to client

        415  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          9  rows processed

SCOTT@orcl> select * from t1 where exists (select object_id from t3 where t1.object_id=t3.object_id);

 

9 rows selected.

 

Elapsed: 00:00:00.04

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2868643349

 

-----------------------------------------------------------------------------

| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |      |     9 |  1980 |   291   (1)| 00:00:04 |

|*  1 |  HASH JOIN RIGHT SEMI|      |     9 |  1980 |   291   (1)| 00:00:04 |

|   2 |   TABLE ACCESS FULL  | T3   |     9 |   117 |     3   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL  | T1   | 89185 |    17M|   287   (1)| 00:00:04 |

-----------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - access("T1"."OBJECT_ID"="T3"."OBJECT_ID")

 

Note

-----

   - dynamic sampling used for this statement (level=2)

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       1032  consistent gets

       1025  physical reads

          0  redo size

       1690  bytes sent via SQL*Net to client

        415  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          9  rows processed

对于小表,in和exists效率一致。

 

 

 

 

二、具备少做事的意识

2. 通过对尽量少做事这个小节的学习和案例的体会,罗列出这一小节涉及到的那些知识。

同时往两表中插入数据:

insert all

   into  ljb_tmp_transaction

   into  ljb_tmp_session

select * from dba_objects;

 

设计中少做事:

①. 利用临时表自动清除的特性避免中间表的delete和truncate操作。构造基于SESSION的全局临时表(退出session该表记录就会自动清空)、构造基于事务的全局临时表(commit提交后,不等退出session,在该表记录就会自动清空)

②利用truncate分区做分区数据清理,delete部分数据改为了truncate 分区

③分区消除带来的性能提升,访问数据落在某分区时性能快。

④分区表用于历史数据转移

 

开发中少做事:

①避免sql中嵌入函数调用,会产生很多递归调用,可用表连接避免函数调用

②减少sql的递归函数调用(where条件中紧跟着rownum<=10)

select name from (select rownum rn ,f_deal2(t1.object_name) name from t1) where rn<=12;

改为:select f_deal2(t1.object_name) name from t1 where rownum<=12;

③集合写法能给性能提升 insert into t select rownum from dual connect by level<=10000;

begin

    for i in 1 .. 100000

    loop

     insert into t values (i);  

    end loop;

  commit;

end;

/   --for循环中是一条一条的插入,而下面是一次插入

insert into t select rownum from dual connect by level<=100000;  --插入1到10000到t表,比for循环快很多

commit;

④访问视图时,只取所需要的列可能会更快,因为所需要的列只落在一个表上,不需多表连接

⑤只取所需要的列,索引无需回表,因为避免了回表,索引中含有部分列的数据

⑥只取所需要的列,表连接提速,因为只需要访问索引,避免过多列带入运算中,下面执行计划一致,但性能差别大。

基于案例学SQL优化   第一周,从案例中推导sql优化的总体思路和误区 - 熊猫兔 - Oracle资料及经验 
基于案例学SQL优化   第一周,从案例中推导sql优化的总体思路和误区 - 熊猫兔 - Oracle资料及经验

 ⑦代码超长慢sql,尽量不要超过100行(通过case when方式合并重复查表)

⑧改写sql(group by里也可以写case when)

⑨一模一样的sql部分可以用with语句来替代

with t as (sql)

select* from t1,t2,t where ...

 

 

开发设计少做事

数据迁移时,清收回收站,确认哪些大表不需要导出来,只把需要的数据导出来,根据cpu数并行。

顺便把该次实施中其他的事情也做了,或者根本不需要做

 

 

 

3. 执行体会PPT中所涉及到的所有脚本(本题全在自觉,检验方式就是,说明一下你认为最有趣的脚本是什么,贴出一个即可。

 

 

三、依据场景选择技术

①确认当前系统是自己用还是大家用,忙还是闲(忙时不能并行,cache内存会导致其他用户所用内存减少)

系统是读多还是写多,访问量多少。读多则该索引多,写多则该索引少。

sql返回的记录数少,则可以加索引优化,信心爆棚。

 

②索引的坏处

索引越多,insert大量数据会越慢。实验。

insert大量数据时,先把索引失效,然后把索引生效。

生产库和查询库的例子:用ogg同步数据,生产库上不建索引,查询库上建很多索引。

建索引会引发锁表,通过online参数避免锁表。

分区表如果不用到分区列,会比普通表更慢,所以在用到分区表时加上分区条件。

select * from part_tab where col2=8 ; --虽然col2上有分区索引,但索引高度较低,有多少分区就有多少分区索引

select * from norm_tab where col2=8 ; --普通表读了一个索引,而分区表读了分区表个索引

 

select * from part_tab where col2=8 and id=2;  --使用id列做的分区,用到了分区条件,为上面的等价改写,性能大提升

select * from norm_tab where col2=8 and id=2;

绑定变量会提升性能,也会引发故障

 

③count性能比拼

count(*)走全表扫描最慢,如有非空索引列,则走非空索引,如果有些列上适合建位图索引,则更快,因为位图索引比普通索引更小。

带查询重写的物化视图比位图索引更快:

create  materialized view  mv_count_t

                    build immediate

                    refresh on commit

                    enable query rewrite

                    as

                    select count(*) FROM T;

 

set autotrace traceonly

set linesize 1000

select COUNT(*) FROM T;

 

结果缓存

drop table t purge;

create table t as  select * from dba_objects;

select count(*) from t;

set linesize 1000

set autotrace traceonly

select /*+ result_cache */ count(*) from t;  --第一次执行时缓存了结果,之后执行就直接读取结果

不要在频繁的表上缓存结果集、物化视图。

 

最快的count(*):

select count(*) from t where rownum=1;   --不管t表多大都只取一条,判断是否有记录,如果记录则。。。

 

④单车到飞船的调优之旅

execute immediate 'insert into t values ( '||i||')';  --过程中dml无需使用动态sql

execute immediate 'insert into t values ( :x )' using i ;   --动态sql用绑定变量

insert into t select rownum from dual connect by level<=100000;  --集合写法,集合写法是批量的操作,而之前的循环写法是单次操作

create table t nologging parallel 64 as select rownum x from dual connect by level<=100000;

 

 

四. 考虑将需求最小化

将复杂的需求抽象简单化

需求有没有多余的,如需要去重、排序、某功能还在用吗?

了解该语句的需求是什么,经了解,需求如下:

DROP TABLE T PURGE;

CREATE TABLE T ( ID1 NUMBER,ID2 NUMBER,VALUE1 VARCHAR2(20),VALUE2 VARCHAR2(20));

INSERT INTO T VALUES (1,2,'A','B');

INSERT INTO T VALUES (1,2,'C','D');

INSERT INTO T VALUES (1,2,'E','F');

INSERT INTO T VALUES (1,2,'G','H');

INSERT INTO T VALUES (3,8,'I','J');

INSERT INTO T VALUES (3,8,'K','L');

INSERT INTO T VALUES (3,8,'M','N');

INSERT INTO T VALUES (8,9,'O','P');

INSERT INTO T VALUES (8,9,'Q','R');

INSERT INTO T VALUES (11,12,'S','T');

COMMIT;

SQL> SELECT * FROM T;

       ID1        ID2 VALUE1               VALUE2

---------- ---------- -------------------- --------------------

         1          2 A                    B

         1          2 C                    D

         1          2 E                    F

         1          2 G                    H

         3          8 I                    J

         3          8 K                    L

         3          8 M                    N

         8          9 O                    P

         8          9 Q                    R

        11         12 S                    T

10 rows selected

要求为(行列转换,超过3个的只取3个,不足3个的用空格来补列)

ID1        ID2 VALUE1               VALUE2  VALUE3               VALUE4  VALUE5               VALUE6

---------- ---------- -------------------- -------------------------------------------------------------

1           2    A                    B      C                    D        E                   F

3           8    I                    J      K                    L        M                   N

8           9    O                    P      Q                    R        NULL                NULL

11         12    S                    T      NULL                 NULL     NULL                NULL

 

 

我们可以通过MAX+分析函数实现如下:

SELECT ID1,ID2

      ,MAX(DECODE(RN,1,VALUE1))

      ,MAX(DECODE(RN,1,VALUE2))

      ,MAX(DECODE(RN,2,VALUE1))

      ,MAX(DECODE(RN,2,VALUE2))

      ,MAX(DECODE(RN,3,VALUE1))

      ,MAX(DECODE(RN,3,VALUE2))

  FROM (SELECT T.*, ROW_NUMBER() OVER(PARTITION BY ID1,ID2 ORDER BY VALUE1,VALUE2) RN FROM T) T

WHERE RN<=3

GROUP BY ID1,ID2;

 

 

五. 忽略sql改造的等价性,不能因为性能而丢了功能

insert all

into t1

into t2

select * t3;   不等价分别插两表

 

select max(),min() from t;比分开写要慢,不等价;等价为select * from(select max() from t,select min() from t);

>=20 and <=21 跟in(20,21)不等价,但结果一致

 

 

需求看透,什么才是真正的需求。

begin

select count(*) into v_cnt from t1 ;

if v_cnt>0

then  …A逻辑….

else

then  …B逻辑…..

End;

 

我来翻译一下这段需求:

      获取t1 表的记录数,判断是否大于0,如果大于0走A逻辑,否则就走B逻辑。

       因此代码就如上所示来实现了。真正的需求是这样吗?

      其实应该是这样的:只要T1表有记录就走A逻辑,否则走B逻辑。

两者有区别吗?其实区别还是很大的,前者可是强调获取记录数,我们是不是一定要遍历整个表得出一个记录数才知道是否大于0?

 

真正需求的理解可以让我们这样实现,只要从T1表中成功获取到第一条记录,就可以停止检索了,表示该表有记录了,难道事实不是这样?

 

因此原先的SQL1 从Select count(*) from t1; 被改造为:

Select count(*) from t1 where rownum=1;

 

begin

select count(*) into v_cnt from t1 where rownum=1;

if v_cnt=1

then  …A逻辑….

else

then  …B逻辑…..

End;

 

 

六. 需求才是顶级优化

基于案例学SQL优化   第一周,从案例中推导sql优化的总体思路和误区 - 熊猫兔 - Oracle资料及经验

 

异步载入,先执行前面的,然后慢慢执行后面的。

这个需求真的需要吗?

  评论这张
 
阅读(1395)| 评论(2)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018