推荐给好友 上一篇 | 下一篇

SQL 语句性能调优

BSD爱好者乐园:X|'w.Z_M5pJ!O

经常听到有做应用的朋友抱怨数据库的性能问题,比如非常低的并发,令人崩溃的响应时间,长时间的锁等待,锁升级 , 甚至是死锁,等等。在解决这些问题的过程中,DBA 经常发现应用开发人员对数据库的“误用”。包括 , 返回过多不必要的数据 , 不必要和不适当加锁,对隔离级别的误用和对存储过程的误用等等。但是,面对浩如烟海的数据库知识 , 要求完全掌握 , 对应用开发人员来说也确实枯燥艰深 . 因此,笔者特别提炼对应用开发人员有帮助的 SQL 书写部分,以期望能对数据库开发人员有所帮助。

"Xx2udS;U!P _'f IA"zGBSD爱好者乐园$}.yBhf"Q |$y,c

“根据我们的经验(由很多业界专家证明),在 SQL Server 上取得的性能提高有 80% 来自对 SQL 编码的改进,而不是来自于对于配置或系统性能的调整。”
/R uk ^#_f3Ul—凯文 克莱恩等,Transact-SQL Programming 作者

0wD,\+hrG,s"j;J

{`L'q9rNR“经验表明 80%-90% 的性能调优是在应用级做的,而不是在数据库级”
d:p2n.} Ve+a—托马斯 白特,Expert One on One: Oracle 作者

Z0z TNVe

$e/c0\P;wU8c本文将主要讨论基于语法的优化以及简简单的查询条件。基于语法的优化指的是为不考虑任何的非语法因素(例如,索引,表大小和存储等),仅考虑在 SQL 语句中对于词语的选择以及书写的顺序。BSD爱好者乐园$|$H4{a-IiXBJ*tF

BSD爱好者乐园%`1K6q,B?B2d_A o

一般规则BSD爱好者乐园/GkZ;`'Fn{6]

BSD爱好者乐园3U2FJ#Ll$kC

这一部分,将看一下一些在书写简单查询语时需要注意的通用的规则。

1V6j/J^)g8J,@i_BSD爱好者乐园gZb W\\ }"H

根据权值来优化查询条件

h ME5Hir4CBf

RI-GpjB最好的查询语句是将简单的比较操作作用于最少的行上。以下两张表,表 1 和表 2 以由好到差的顺序列出了典型查询条件操作符并赋与权值。BSD爱好者乐园[J{W_D

BSD爱好者乐园 vE;F o%Q,}
表 1. 查询条件中操作符的权值
&J,u2our2nP2W_
操作符权值
=10
>5
>=5
<5
<=5
LIKE3
<>0
BSD爱好者乐园 Z|p9hE4v
BSD爱好者乐园!N5gH/Y A?
表 2. 查询条件中操作数的权值
)?v o7b:}1~5BD
操作数权值
仅常量字符10
仅有列名5
仅有参数5
多操作数表达式3
精确数值类型2
其它数值类型1
时间数据类型1
字符数据类型0
NULL0

1G6f5d6b~$kmP^cnBSD爱好者乐园j*Y8UyqHf0SR

根据表 1 和表 2 中分配的权值,可以看出最好的查询条件应该是像下面这样的:

~ oN#O|Z{~
… WHERE smallint_column = 789

6M#Wx_e2Q"IX"F&PBSD爱好者乐园^!m7\t k$m'J]

这个例子得到 27 分,计算如下:BSD爱好者乐园Db7lN[b|

  • 左侧只有列名(smallint_column)得 5 分
  • 操作数为精确数据类型(smallint_column)得 2 分
  • 等号(=)操作符得 10 分
  • 右侧是文字字符(789)得 10 分

S%E+Ll n._1OA下面是另外一个例子

*\x qD6FN
… WHERE char_column >= varchar_column || ‘ x ’
BSD爱好者乐园~ d/Pi ]5Fh
BSD爱好者乐园y6Gzi\Yu.eRIb

这种类型的查询权值得分就很低,只有 13 分

}/tQI ^X.}_
  • 左侧只有列名(char_column)得 5 分
  • CHAR 类型的操作数得 0 分
  • 大于等于操作符得 5 分
  • 左侧是多操作数表达示得 3 分
  • VARCHAR 类型的操作数得 0 分

i2u5A-H.\(uO,b上面表格中的权值数可能在不同类型的数据库系统中会有所不同,所以记住这些具体数值是没有意义的,只需要了解它们的排序即可。用时越少的比较条件,得分也就越高,这样的比较条件通常是那些操作的行数少或者易于比较的。BSD爱好者乐园TT.QU7X7hS"GZM

BSD爱好者乐园7F;za9IY,t

传递法则BSD爱好者乐园 co6C5C%z

#`1v m6H-a~传递法则是这样定义的:BSD爱好者乐园$KW TA~"}9?

IF 
 
 (A <comparison operator> B) IS TRUE 
 AND (B <comparison operator> C) IS TRUE 
 
 THEN 
 
 (A <comparison operator> C) IS TRUE 
 AND NOT (A <comparison operator> C) IS FALSE
BSD爱好者乐园v][pTq

d)w+w)Al,IA(D.SS3d比较运算符包括:=、>、>=、<、<、+, 但不包括:<>、LIKE。BSD爱好者乐园1\3H'r9\ l$d'B3P

BSD爱好者乐园1g Rx-X@5xr

通过传递法则,我们可以看出,可以用 C 来替换 B,而不使表达式的意思发生变化。BSD爱好者乐园 w8?hK1`m+n-M1~B_

BSD爱好者乐园)@'] q(Q l

下面的两个例子表达了同样的含义,但是第二个表达示要比第一个表达式执行的快。BSD爱好者乐园'D;}&} {4|

K{ wUi,c表达式一:BSD爱好者乐园E8P PI2L~P k%[]

... WHERE column1 < column2 

 AND column2 = column3 

 AND column1 = 5

(m4\ ?UXBSD爱好者乐园,P(B:v6] fz8O6r"uNg

表达式二:BSD爱好者乐园 ab Q K_2k8e;E

... WHERE 5 < column2 

 AND column2 = column3 

 AND column1 = 5

#Y\ix+y3gWm%bqBSD爱好者乐园(S2[!c#b'c$_d`

大多数的数据库管理系统都会自动的做这样的调整,但是当表达式中含有括号时,它们就不会自动调整了。例如一个如下的 SELECT 语句:BSD爱好者乐园'_N#x#gB"]_

SELECT * FROM Table1 

 WHERE column1 = 5 AND 

 NOT (column3 = 7 OR column1 = column2)
BSD爱好者乐园1o d#\zq,TI)W&b

[.Jg&v'f]k p如果进行转化的话,会得到如下的语句:

H^}"~ a7E
SELECT * FROM Table1 

 WHERE column1 = 5 

 AND column3 <> 7 

 AND column2 <> 5

,^tC/sR+i

2t+u#Oi'K[ pGP进行这样变化后的语句会比第一个执行的更快。BSD爱好者乐园wW3{9ZY$|0X.qip

]tdMs5{L]1. Sargability

sSu$u G0]fFBSD爱好者乐园mD WP+_6B.M f

理想的 SQL 表达式应该采用下面这种通用的格式:BSD爱好者乐园 [,?4q5\ b*Y.O

<column> <comparison operator> <literal>

/]{)z?3JF-|.Z#Y

2m bA5\e早些时候,IBM 研究人员将这种查询条件语名命名为”sargable predicates”,因为 SARG 是 Search ARGument 的组合。BSD爱好者乐园 PK([a v-g

(],paheR_根据这一规则,查询条件的左侧应该是一个列名;右侧应该是一个很容易进行查找的值。

p1b`cD8bI

^#\+@,G1MM遵循这一规则,所有的数据库系统都会将如下的表达式:

6Bua!AlJl@
5 = column1
BSD爱好者乐园&]+B SpS&cQ;Ldw&w
BSD爱好者乐园!qKJ*CP

转换成:BSD爱好者乐园gJ4gY%S+b(SG4T

column1 = 5
BSD爱好者乐园(fxX3T]8Y?r@1_
BSD爱好者乐园%AK Z#n,e)U1b(\

但是当查询条件中包含算术表达式时,只有部分的数据库系统进行转换。

^q[JDp"w2LK"o4?BSD爱好者乐园+~5mp8Z0Y~

例如:BSD爱好者乐园7V)Ta[6| rs!l

... WHERE column1 - 3 = -column2

U!J7u]"z

!b)lKgv转换成:BSD爱好者乐园 F@4i%{3yP3wY

... WHERE column1 = -column2 + 3

d Kc:mn7F&F`BSD爱好者乐园)_*a?!VLe&C

还是可以带来查询性能的优化的。BSD爱好者乐园 \"X#L6R8?%tn

针对专门操作符的调优

aE'I-Oc前面,讲的是关于查询条件的一般规则,在这一节中,将讨论如何使用专门的操作符来改进 SQL 代码的性能。BSD爱好者乐园hL d}.FM3~0t

BSD爱好者乐园Tv'bD2psBN|

与 (AND) 

K3v9ByV%V:J

p_hch1d7oNHq数据库系统按着从左到右的顺序来解析一个系列由 AND 连接的表达式,但是 Oracle 却是个例外,它是从右向左地解析表达式。可以利用数据库系统的这一特性,来将概率小的表达示放在前面,或者是如果两个表达式可能性相同,那么可将相对不复杂的表达式放在前面。这样做的话,如果第一个表达式为假的话,那么数据库系统就不必再费力去解析第二个表达式了。例如,可以这样转换:BSD爱好者乐园 p6H#[ k| Y%K%`A

... WHERE column1 = 'A' AND column2 = 'B'

5F4Djm-wTl

s/r@,x]*nt,QH'{转换成:BSD爱好者乐园5r,Ja#Y y3jK{ G

... WHERE column2 = 'B' AND column1 = 'A'
BSD爱好者乐园 PY;Np0`

qAnfAg\这里假设 column2 = 'B'的概率较低,如果是 Oracle 数据库的话,只需将规则反过来用即可。BSD爱好者乐园i1YuX x

wR3f&v}z^或 (OR)

_4n5[yvn A9@"n!GBSD爱好者乐园 jx5?3U't%Mi(J

和与 (AND) 操作符相反,在用或 (OR) 操作符写 SQL 语句时,就应该将概率大的表达示放在左面,因为如果第一个表达示为假的话,OR 操作符意味着需要进行下一个表达示的解析。

R c2Xiu,Y?y+d(@BSD爱好者乐园8qA3Q8I%G_;q

与 + 或

P%zr5aT[ d/FwBSD爱好者乐园Lu,cUb8J

按照集合的展开法则,

R!e1B2^UG JnA
A AND (B OR C) 与 (A AND B) OR (A AND C) 是等价表达示。
BSD爱好者乐园BymVJ
BSD爱好者乐园&P(b5tnOE|V*k

假设有如表 3 所示的一张表,要执行一个 AND 操作符在前的表达示BSD爱好者乐园o g!S{uXDS

SELECT * FROM Table1 
 
 WHERE (column1 = 1 AND column2 = 'A') 
 
 OR (column1 = 1 AND column2 = 'B')
BSD爱好者乐园!jgq;s!g

t@C*]SV(M!\ C表 3. AND+OR 查询BSD爱好者乐园{Ur2A1k8C7E0]p
Row#Colmun1Column2
13A
22B
31C

F `YR^J x/BBSD爱好者乐园I)[,`,^2s/F @~

当数据库系统按照查询语进行搜索时,它按照下面的步骤执行:

J-E{'N#z3|3^
  • 索引查找 column1 = 1, 结果集 = {row 3}
  • 索引查找 column2 = ‘ A ’ , 结果集 = {row1}
  • AND 合并结果集,结果集 = {}
  • 索引查找 column 1 = 1, 结果集 = {row 3}
  • 索引查找 column 2 = ‘ B ’ , 结果集 = {row2}
  • AND 合并结果集,结果集 = {}
  • OR 合并结集,结果集 = {}

1j^*S`9w k t1n现在根据集合的展开法则,对上面的语句进行转换:

*{7f*^V$}&z
SELECT * FROM Table1 

 WHERE column1 = 1 

 AND (column2 = 'A' OR column2 = 'B')

O;u1E7A;U z

"]d2eQ7s3^v w U按照新的顺序进行查搜索时,它按照下面的步骤执行:BSD爱好者乐园p j KD] H'M#A

  • 索引查找 column2 = ‘ A ’ , 结果集 = {row1}
  • 索引查找 column 2 = ‘ B ’ , 结果集 = {row2}
  • OR 合并结集,结果集 = {}
  • 索引查找 column1 = 1, 结果集 = {row 3}
  • AND 合并结果集,结果集 = {}
BSD爱好者乐园'Z'|&y3@(v}@kB4Zp

由此可见搜索次数少了一次。虽然一些数据库操作系统会自动的进行这样的转换,但是对于简单的查询来说,这样的转换还是有好处的。

_^'z-N1N]({

0Nv)| zG非 (NOT)

}v%V!EU6sE n+e;Uh

IOkgV!E让非 (NOT) 表达示转换成更易读的形式。简单的条件能通过将比较操作符进行反转来达到转换的目的,例如:

8Hc2Lg&k2H[
... WHERE NOT (column1 > 5)
BSD爱好者乐园r,FQ*Z9}&GEx-n
BSD爱好者乐园L:wY:i7|"@ x

转换成:BSD爱好者乐园2w+K|m!X%oT

... WHERE column1 <= 5
BSD爱好者乐园5o War+l,b*dP

,wb#h7kt:r#j比较复杂的情况,根据集合的摩根定理:

_6oL.h%{tr7Y~
NOT (A AND B) = (NOT A) OR (NOT B) 和 NOT (A OR B) = (NOT A) AND (NOT B)
BSD爱好者乐园&Hy et3{v9L/h
BSD爱好者乐园P8` YHzu!M

根据这一定理,可以看出它可以至少二次的搜索有可能减少为一次。如下的查询条件:

dhFXD2dS*L
... WHERE NOT (column1 > 5 OR column2 = 7)
BSD爱好者乐园-g&F O4fP ZR
BSD爱好者乐园h&bAk9r)r,z!^U$O

可以转换成:BSD爱好者乐园8e-p9x A j;kNJ

... WHERE column1 <= 5 

 AND column2 <> 7

/_3{9m!^ j6tU;Tu5}

@ q:ms*b CA8h但是,当转换成后的表达示中有不等操作符 <>,那么性能就会下降,毕竟,在一个值平均分布的集合中,不等的值的个数要远远大于相等的值的个数,正因为如此,一些数据库系统不会对非比较进行索引搜索,但是他们会为大于或小于进行索引搜索,所以可以将下面的查询进行如下转换:BSD爱好者乐园;~MaI#Q-]

... WHERE NOT (column1 = 0)

Z0[]/Ic\$BBSD爱好者乐园aF+rG K+EZ7U2p

转换成:BSD爱好者乐园ku}VV/i0S)f+x

... WHERE column <0 

 OR column > 0

V7V\axBSD爱好者乐园 \2dM5M4o(r#?&]`9M

INBSD爱好者乐园'aBeiJ kT

BSD爱好者乐园:M ap7B5s`Z

很多人认为如下的两个查询条件没有什么差别,因为它们返回的结果集是相同的:BSD爱好者乐园1QW7wc7l3g| D't

BSD爱好者乐园R7p4N8z FD Gb b%c?S

条件 1:

BSq/Hg
... WHERE column1 = 5 

 OR column1 = 6
BSD爱好者乐园8Qf ^x#F!Ny+c q9x

t|%Y6M u)j^"ir?1L条件 2:BSD爱好者乐园qC!x7O\.W

... WHERE column1 IN (5, 6)

)]#G1Hh;Z3J

9@(W)r l%U:UP6`j这样的想法并不完全正确,对于大多数的数据库操作系统来说,IN 要比 OR 执行的快。所以如果可以的话,要将 OR 换成 INBSD爱好者乐园0?7?d8`P_l)r$B.r

@ s&PiwGNn8}'t当 IN 操作符,是一系列密集的整型数字时,最好是查找哪些值不符合条件,而不是查找哪些值符合条件,因此,如下的查询条件就应该进行如下的转换:BSD爱好者乐园 D0n6G3nk;d

... WHERE column1 IN (1, 3, 4, 5)
BSD爱好者乐园h"T-nRI'KEi
BSD爱好者乐园8x'IJ*Q0GC

转换成:BSD爱好者乐园(k#yw,`)Q%Qf2aJ

... WHERE column1 BETWEEN 1 AND 5 
 AND column1 <> 2

|9W(|V8yg EBSD爱好者乐园,i6e3@4t;^

当一系列的离散的值转换成算数表达示时,也可获得同样的性能提高。

^a*T7E)an6q${y1v/A u

-OS+Z1KwUNION

UDnLW

4OQ.` VN"MoV P N在 SQL 中,两个表的 UNION 就是两个表中不重复的值的集合,即 UNION 操作符返返回的两个或多个查询结果中不重复行的集合。这是一个很好的合并数据的方法,但是这并不是最好的方法。BSD爱好者乐园@)UR?"t{F VF

BSD爱好者乐园w-\P zf5a

查询 1:

^+F5Xe%c^;q
SELECT * FROM Table1 

 WHERE column1 = 5 

 UNION 

 SELECT * FROM Table1 

 WHERE column2 = 5
BSD爱好者乐园%JR%x6]7q4Q Zzf O

A6y3Y+rj;a5x%~!Na查询 2:BSD爱好者乐园O2f+n*e6LG#wc

SELECT DISTINCT * FROM Table1 

 WHERE column1 = 5 

 OR column2 = 5
BSD爱好者乐园DQ5b.l#qqS[n
BSD爱好者乐园4pNm%_,BP n-o

在上面的例子中,column1 和 column2 都没有索引。如果查询 2 总是比查询 1 执行的快的话,那么就可以建议总是将查询 1 转换成查询 2,但是有一种情况,这样做在一些数据库系统中可能会带来性能变差,这是由于两个优化缺陷所造成的。BSD爱好者乐园g3y'gjh4b

%z2n(@%I0@?Z3yf第一个优化缺陷就是很多优化器只优化一个 SELECT 语句中一个 WHERE 语句,所以查询 1 的两个 SELECT 语句都被执行。首先优化器根据查询条件 column1 = 5 为真来查找所有符合条件的所有行,然后据查询条件 column2 = 5 为真来查找所有符合条件的所有行,即两次表扫描,因此,如果 column1 = 5 没有索引的话,查询 1 将需要 2 倍于查询 2 所需的时间。如果 column1 = 5 有索引的话,仍然需要二次扫描,但是只有在某些数据库系统存在一个不常见的优化缺陷却将第一个优化缺陷给弥补了。当一些优化器发现查询中存在 OR 操作符时,就不使用索引查询,所以在这种情况下,并且只有在这种情况下,UNION 才比 OR 性能更高。这种情况很少见,所以仍然建议大家当待查询的列没有索引时使用 OR 来代替 UNION。

$U5b \E_%Cxw#k
总结
BSD爱好者乐园S:Ck)W{W^

以上是作者对如何提高 SQL 性能的一些总结,这些规则并一定在所有的数据库系统上都能带来性能的提高,但是它们一定不会对数据库的性能带来下降,所以掌握并使用这些规则可以对数据库应用程序的开发有所帮助。本文总结的是一些 SQL 性能调优的比较初级的方面,SQL 调优还包括 Order by,Group by 以及 Index 等等,作者将来后续的文章中进行讲解。

}a{T2\:bE
[重要提醒]对本篇资料有疑问,请到论坛讨论,尽量使文章准确无误>>>
[版权声明]BSD爱好者乐园站内文章,如来源不是互联网,则均系原创或翻译之作,可随意转载,或以此为基础进行演译,但务必以链接形式注明原始出处和作者信息,否则属于侵权行为。另对本站转载他处文章,俱有说明,如有侵权请联系本人,本人将会在第一时间删除侵权文章。
TAG: sql SQL 优化
 

评分:0

我来说两句

seccode