ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL 优化相关概念

SQL 优化相关概念

原创 Linux操作系统 作者:gaopengtttt 时间:2009-04-28 11:28:53 0 删除 编辑

(我的学习笔记)

 

1、  基础概念及理论

(1)       优化器:我们公司现在用的ORACLE 版本是ORACLE 10G,此版本用到的优化器是CBO,也就是通常所说的基于成本的优化器,所谓的成本的就是ORACLE在处理一条具体的语句时候CPU成本,I/O成本,网络成本的总和,当然ORACLE具体的如何算的比较复杂,此处只给出概念。

(2)       执行计划:执行计划对于DBA或者AD来说就像财务报表基于财务一样重要,执行计划给出了当前SQL运行的轨迹。给出了SQL的运行时每步所消耗的代价(COST),并且给出了ORACLE在遇到JOIN(连接),索引(INDEX)等操作的时候如何处理的,处理了输入了多少条记录,输入记录的字节数。

(3)       什么是优化:优化是选择最有效的执行计划来执行SQL语句的过程,这是在处理任何数据的语句(SELECT,INSERT,UPDATEDELETE)中的一个重要步骤

(4)       何时停止优化:当优化效果达到我们预先制定的目标的时候,优化就应该结束,否则优化将无止境。

(5)       为何添加索引会加快查询:索引是以一种B-TREE的结构来存储数据,并且在块上记录的是键值的数据,以及此行的ROWID,(ROWID是一种伪劣,在ORACLEROWID是最快对用到行的方式,因为每行只有唯一ROWID),比如我们的语句如下:SELECT * FROM TEST WHERE TEST.ID=111。如果ID列上建立有索引,ORACLE优化器一般会,首先进行INDEX 唯一或者范围扫描,找出ID=111的行然后通过ROWID对应到相应的表中的行,完成此次查询。

试验:

SQL> select * from test

  2  where it=100;

 

        IT

----------

       100

 

 

执行计划

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

   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=1 Card=1 Bytes=13)

   1    0   INDEX (RANGE SCAN) OF 'TEST_INDEX' (INDEX) (Cost=1 Card=1

 

 

2、  如何看懂执行计划

 一般说来执行计划遵循至右至上的原则(ORACLE 10G中)下面是一个复杂点的执行计划。

 

第一步:首先进行全表扫描TEST2,返回4

第二步:对TEST3进行全表扫描返回5

第三步:对TEST3TEST2进行HASH JION也就是语句中的(and test3.id=test2.id

返回5

第四步:进行INDEX RANGE SCAN,因为表TEST存在着索引,优化器会选择索引扫描而不会进行全表扫描

第五步:以HASH JION返回的5行做驱动表,INDEX RANGE SCAN作为外部表进行NESTED LOOPS JION也就是语句中的(where test.it=test2.id返回了72

最后进行语句的总的总结:消耗代价7 返回72 作用行的大小4680字节。

如何理解(Cost=1 Card=1 Bytes=13:

这里说明代价为1 ,返回行1 ,此行的大小13字节.

3、  索引种类

 (1) 索引唯一扫描(index unique scan)

通过唯一索引查找一个数值经常返回单个ROWID。如果该唯一索引有多个列组成(即组合索引),则至少要有组合索引的引导列参与到该查询中,如创建一个索引:create index idx_test on emp(ename, deptno, loc)。则select ename from emp where ename = JACK and deptno = DEV’语句可以使用该索引。如果该语句只返回一行,则存取方法称为索引唯一扫描。而select ename from emp where deptno = DEV’语句则不会使用该索引,因为where子句种没有引导列。如果存在UNIQUE PRIMARY KEY 约束(它保证了语句只存取单行)的话,Oracle经常实现唯一性扫描。

 (2) 索引范围扫描(index range scan)

使用一个索引存取多行数据,同上面一样,如果索引是组合索引,如(1)所示,而且select ename from emp where ename = JACK and deptno = DEV’语句返回多行数据,虽然该语句还是使用该组合索引进行查询,可此时的存取方法称为索引范围扫描。在唯一索引上使用索引范围扫描的典型情况下是在谓词(where限制条件)中使用了范围操作符(><<>>=<=between)

使用index rang scan3种情况:

(a) 在唯一索引列上使用了range操作符(> < <> >= <= between)

(b) 在组合索引上,只使用部分列进行查询,导致查询出多行。

(c) 对非唯一索引列上进行的任何查询。

(3) 索引全扫描(index full scan)

与全表扫描对应,也有相应的全索引扫描。在某些情况下,可能进行全索引扫描而不是范围扫描,需要注意的是全索引扫描只在CBO模式下才有效。CBO根据统计数值得知进行全索引扫描比进行全表扫描更有效时,才进行全索引扫描,而且此时查询出的数据都必须从索引中可以直接得到。

 (4) 索引快速扫描(index fast full scan)

扫描索引中的所有的数据块,与 index full scan很类似,但是一个显著的区别就是它不对查询出的数据进行排序,即数据不是以排序顺序被返回。在这种存取方法中,可以使用多块读功能,也可以使用并行读入,以便获得最大吞吐量与缩短执行时间。

4、  JOIN 类型

1)排序 - - 合并连接(Sort Merge Join, SMJ)

内部连接过程:

1.首先生成row source1需要的数据,然后对这些数据按照连接操作关联列(A.col3)进行排序。

2. 随后生成row source2需要的数据,然后对这些数据按照与sort source1对应的连接操作关联列(B.col4)进行排序。

3. 最后两边已排序的行被放在一起执行合并操作,即将2row source按照连接条件连接起来。

如果row source已经在连接关联列上被排序,则该连接操作就不需要再进行sort操作,这样可以大大提高这种连接操作的连接速度,因为排序是个极其费资源的操作,特别是对于较大的表。 预先排序的row source包括已经被索引的列(a.col3b.col4上有索引)row source已经在前面的步骤中被排序了。尽管合并两个row source的过程是串行的,但是可以并行访问这两个row source(如并行读入数据,并行排序)

SMJ连接的例子:

SQL> explain plan for

select /*+ ordered */ e.deptno, d.deptno

from emp e, dept d

where e.deptno = d.deptno

order by e.deptno, d.deptno;

 

Query Plan

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

SELECT STATEMENT [CHOOSE] Cost=17

  MERGE JOIN

    SORT JOIN

      TABLE Access FULL EMP [ANALYZED]

    SORT JOIN

      TABLE ACCESS FULL DEPT [ANALYZED]

排序是一个费时、费资源的操作,特别对于大表。基于这个原因,SMJ经常不是一个特别有效的连接方法,但是如果2row source都已经预先排序,则这种连接方法的效率也是蛮高的。

2)嵌套循环(Nested Loops, NL)

这个连接方法有驱动(外部表)的概念。其实,该连接过程就是一个2层嵌套循环,所以外层循环的次数越少越好,这也就是我们为什么将小表或返回较小row source的表作为驱动表(用于外层循环)的理论依据。但是这个理论只是一般指导原则,因为遵循这个理论并不能总保证使语句产生的I/O次数最少。有时不遵守这个理论依据,反而会获得更好的效率。如果使用这种方法,决定使用哪个表作为驱动表很重要。有时如果驱动表选择不正确,将会导致语句的性能很差、很差。

内部连接过程:

Row source1Row 1 --------------      

-- Probe ->       Row source 2

Row source1Row 2 --------------      

-- Probe ->       Row source 2

Row source1Row 3 --------------      

-- Probe ->       Row source 2

…….

Row source1Row n --------------      

-- Probe ->       Row source 2

从内部连接过程来看,需要用row source1中的每一行,去匹配row source2中的所有行,所以此时保持row source1尽可能的小与高效的访问row source2(一般通过索引实现)是影响这个连接效率的关键问题。这只是理论指导原则,目的是使整个连接操作产生最少的物理I/O次数,而且如果遵守这个原则,一般也会使总的物理I/O数最少。但是如果不遵从这个指导原则,反而能用更少的物理I/O实现连接操作,那尽管违反指导原则吧!因为最少的物理I/O次数才是我们应该遵从的真正的指导原则,在后面的具体案例分析中就给出这样的例子。

在上面的连接过程中,我们称Row source1为驱动表或外部表。Row Source2被称为被探查表或内部表。

NESTED LOOPS连接中,Oracle读取row source1中的每一行,然后在row sourc2中检查是否有匹配的行,所有被匹配的行都被放到结果集中,然后处理row source1中的下一行。这个过程一直继续,直到row source1中的所有行都被处理。这是从连接操作中可以得到第一个匹配行的最快的方法之一,这种类型的连接可以用在需要快速响应的语句中,以响应速度为主要目标。

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

如果不使用并行操作,最好的驱动表是那些应用了where 限制条件后,可以返回较少行数据的的表,所以大表也可能称为驱动表,关键看限制条件。对于并行查询,我们经常选择大表作为驱动表,因为大表可以充分利用并行功能。当然,有时对查询使用并行操作并不一定会比查询不使用并行操作效率高,因为最后可能每个表只有很少的行符合限制条件,而且还要看你的硬件配置是否可以支持并行(如是否有多个CPU,多个硬盘控制器),所以要具体问题具体对待。

NL连接的例子:

SQL> explain plan for

select a.dname,b.sql

from dept a,emp b

where a.deptno = b.deptno;

 

Query Plan

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

SELECT STATEMENT [CHOOSE] Cost=5

NESTED LOOPS

TABLE Access FULL DEPT [ANALYZED]

TABLE ACCESS FULL EMP [ANALYZED]

3)哈希连接(Hash Join, HJ)

这种连接是在Oracle 7.3以后引入的,从理论上来说比NLSMJ更高效,而且只用在CBO优化器中。

较小的row source被用来构建hash tablebitmap,第2row source被用来被hansed,并与第一个row source生成的hash table进行匹配,以便进行进一步的连接。Bitmap被用来作为一种比较快的查找方法,来检查在hash table中是否有匹配的行。特别的,当hash table比较大而不能全部容纳在内存中时,这种查找方法更为有用。这种连接方法也有NL连接中所谓的驱动表的概念,被构建为hash tablebitmap的表为驱动表,当被构建的hash tablebitmap能被容纳在内存中时,这种连接方式的效率极高。

HASH连接的例子:

SQL> explain plan for

select /*+ use_hash(emp) */ empno

from emp, dept

where emp.deptno = dept.deptno;

 

Query Plan

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

SELECT STATEMENT  [CHOOSE] Cost=3

  HASH JOIN

    TABLE Access FULL DEPT

    TABLE ACCESS FULL EMP

5、 公司团险具体语句优化。

上周团险的CIRC功能出现问题,然后对数据库进行检测发现主要问题如下

SQL ordered by Reads

  • Total Disk Reads: 1,997,604
  • Captured SQL account for 99.9% of Total

Physical Reads

Executions

Reads per Exec

%Total

CPU Time (s)

Elapsed Time (s)

SQL Id

SQL Module

SQL Text

1,995,633

11

181,421.18

99.90

845.14

2737.80

gn32k8dc7uwav

 

select distinct policy.polic...

8

2

4.00

0.00

0.65

2.05

d92h3rjp0y217

 

begin prvt_hdm.auto_execute( :...

0

10

0.00

0.00

0.00

0.00

04p9rnacfgfsn

 

INSERT INTO sys.wri$_adv_objec...

0

2

0.00

0.00

0.00

0.03

0cn2wm9d7zq8d

 

SELECT decode(value, 'FATAL', ...

0

2

0.00

0.00

0.04

0.07

0dwr2vd6vbqzs

 

SELECT count(*) over () as tot...

0

1

0.00

0.00

24.05

24.05

0j6fybptk35uh

 

select rownumx, t2_12404754155...

0

1,459

0.00

0.00

0.17

0.17

0kkrykkpjntnq

 

SELECT A.PRODUCT_ID, ...

0

14

0.00

0.00

0.00

0.00

0sxrub8cckxb7

 

INSERT INTO sys.wri$_adv_actio...

0

4

0.00

0.00

0.02

0.02

10xj8nynmpqtq

 

SELECT dbin.db_name, dbin.ins...

0

36

0.00

0.00

0.01

0.01

1167u92k662fn

 

SELECT I.ORG_ID AS id, ...

SQL ordered by Elapsed Time

  • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
  • % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100

Elapsed Time (s)

CPU Time (s)

Executions

Elap per Exec (s)

% Total DB Time

SQL Id

SQL Module

SQL Text

2,738

845

11

248.89

93.40

gn32k8dc7uwav

 

select distinct policy.polic...

74

74

3

24.71

2.53

dgqkkpq1a12pn

 

select count(*) from ( ...

41

41

1

40.52

1.38

2hngjtk6dh693

 

select rownumx, t2_12404754970...

40

40

1

39.72

1.35

dg0trvz782248

 

select rownumx, t2_12404755745...

24

24

1

24.05

0.82

0j6fybptk35uh

 

select rownumx, t2_12404754155...

3

3

2

1.63

0.11

bunssq950snhf

 

insert into wrh$_sga_target_ad...

2

2

1

2.16

0.07

dpvw61car1d6f

sqlplus@D0-LNXDB30 (TNS V1-V3)

select sql_text from v$sort_us...

2

1

2

1.03

0.07

d92h3rjp0y217

 

begin prvt_hdm.auto_execute( :...

1

1

1

0.71

0.02

bbvn0ku6s5f4g

 

select count(*) from ( selec...

1

1

1

0.67

0.02

6mcppf5qkbfsj

 

select rownumx, t2_12404756207...

 

语句gn32k8dc7uwav出现了大量的物理读,时间消耗也基本消耗此语句上,同时此期间数据库的物理写也是非常大的,当然通过其他一些语句也抓出了这条语句,可以确定是他的问题,现语句如下:

SQL> select distinct policy.policy_id policyId,
  2                  policy.policy_code PolNo,
  3                  policy.policy_code CertNo,
  4                  '' Pol_Prt_Code,
  5                  policy.proposal_code Prpl_No,
  6                  '' Prpl_Prt_Code,
  7                  hoa.post_code Post_Code,
  8                  io.org_code Branch_Code,
  9                  'G' GP_Type,
10                  ho.hold_org_name App_Name,
11                  '' App_Idcard_Type,
12                  '' App_Idcard_No,
13                  '' App_Income,
14                  la.life_assured_name InsName,
15                  it.TYPE_ID Ins_Idcard_Type,
16                  la.id_code Ins_Idcard_No,
17                  '' App_Ins_Relation,
18                  b.beneficiary_name BeneName,
19                  '' Sum_Ins,
20                  '' Sum_Ins_Death,
21                  policy.proposal_date App_Date,
22                  policy.insert_date Input_Date,
23                  uci.uw_end_date Underwr_date,
24                  policy.commencement_date Eff_Date,
25                  '' Recp_date,
26                  '' Revisit_date,
27                  policy.risk_cessation_date Matu_Date,
28                  ho.linkman_name Conta_Name,
29                  ho.linkman_tel Conta_Tel,
30                  cgc.code_circ BUSI_SRC_TYPE,
31                  agency.agency_code AGT_CODE,
32                  '' Salesman_No,
33                  '' Banc_Speci_No,
34                  policy.issue_agent Staff_No,
35                  policy.assumpsit Extra_Memo,
36                  '' Reins_Type
37    from t_policy policy
38    left join t_holder_org ho on policy.company_holder = ho.party_id
39    left join t_address hoa on ho.address_id = hoa.address_id
40    left join t_insurance_org io on io.org_id = policy.ISSUE_AGENT_ORG
41    left join t_life_assured la on policy.policy_id = la.policy_id
42    left join t_id_type it on la.id_type_id = it.type_id
43    left join t_certificate c on policy.policy_id = c.policy_id
44    left join t_beneficiary b on b.certificate_id = c.certificate_id
45    left join t_uw_case_info uci on policy.proposal_code = uci.proposal_code
46    left join t_channel_gims_circ cgc on policy.sales_channel = cgc.channel_id_gims
47    left join t_agency agency on policy.issue_agency = agency.agency_id
48   where policy.policy_id = 352;

查看执行计划如下:

Execution Plan

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

0

SELECT STATEMENT

 

 

 

198 (100)

 

1

   HASH UNIQUE

 

42

15708

198 (3)

00:00:03

2

     HASH JOIN OUTER

 

42

15708

197 (3)

00:00:03

3

       HASH JOIN OUTER

 

42

15162

155 (2)

00:00:02

4

         NESTED LOOPS OUTER

 

1

352

71 (3)

00:00:01

5

           HASH JOIN OUTER

 

1

341

70 (3)

00:00:01

6

             NESTED LOOPS OUTER

 

1

331

67 (3)

00:00:01

7

               HASH JOIN OUTER

 

1

328

67 (3)

00:00:01

8

                 HASH JOIN OUTER

 

1

293

11 (10)

00:00:01

9

                   NESTED LOOPS OUTER

 

1

216

7 (0)

00:00:01

10

                     NESTED LOOPS OUTER

 

1

192

4 (0)

00:00:01

11

                       NESTED LOOPS OUTER

 

1

183

3 (0)

00:00:01

12

                         TABLE ACCESS BY INDEX ROWID

T_POLICY

1

175

2 (0)

00:00:01

13

                           INDEX UNIQUE SCAN

T_POLICY_POLICY_SEQ_ID_PK

1

 

1 (0)

00:00:01

14

                         TABLE ACCESS BY INDEX ROWID

T_AGENCY

4

32

1 (0)

00:00:01

15

                           INDEX UNIQUE SCAN

T_AGENCY_AGENCY_ID_FK

1

 

0 (0)

 

16

                       TABLE ACCESS BY INDEX ROWID

T_INSURANCE_ORG

2

18

1 (0)

00:00:01

17

                         INDEX UNIQUE SCAN

PK_T_INSURANCE_ORG

1

 

0 (0)

 

18

                     TABLE ACCESS FULL

T_UW_CASE_INFO

1

24

3 (0)

00:00:01

19

                   TABLE ACCESS FULL

T_HOLDER_ORG

165

12705

3 (0)

00:00:01

20

                 TABLE ACCESS FULL

T_LIFE_ASSURED

1

35

56 (2)

00:00:01

21

               INDEX UNIQUE SCAN

PK_T_ID_TYPE

1

3

0 (0)

 

22

             TABLE ACCESS FULL

T_CHANNEL_GIMS_CIRC

6

60

3 (0)

00:00:01

23

           TABLE ACCESS BY INDEX ROWID

T_ADDRESS

1

11

1 (0)

00:00:01

24

             INDEX UNIQUE SCAN

PK_T_ADDRESS

1

 

0 (0)

 

25

         TABLE ACCESS FULL

T_CERTIFICATE

42

378

84 (2)

00:00:02

26

       TABLE ACCESS FULL

T_BENEFICIARY

17938

227K

41 (0)

00:00:01

粗略的看一看此执行计划很多全表扫描,这是用问题的,然后分析其步骤也可以得出相应的结论,他会扫描过多的行,然后决定添加索引如下:

t_holder_org(party_id)                (b-tree索引)
t_life_assured(policy_id)              (
反键索引)
t_uw_case_info(proposal_code)       (b-tree
索引)
t_certificate(policy_id)                (BITMAP
索引)

t_beneficiary(certificate_id)           (b-tree索引)

建立什么养的索引要根据索引的特点和数据的特点来确定。

建立索引后也就是现在的团险生产库执行计划如下:

 

如果看总的消耗COST 以前为198 而现在仅仅为66刚好为以前的1/3,然后测试应用程序速度也提高了3倍左右,当然不是说他们成正比,但是确实提高了不好,另外此条语句是会循环进行的,我所做的优化会被循环次数所放大(自我感觉),就此语句其实还可以进一部缩减COST但是由于目的已经达到,所以优化可以停止了。

clip_image002.jpg

clip_image002.jpg

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7728585/viewspace-592060/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
wxh gp_22389860 <<深入理解MySQL主从原理专栏>> 发布 可加WX了解

注册时间:2008-10-13

  • 博文量
    671
  • 访问量
    2917769