SQL 优化原则和技巧.docx
《SQL 优化原则和技巧.docx》由会员分享,可在线阅读,更多相关《SQL 优化原则和技巧.docx(21页珍藏版)》请在冰点文库上搜索。
SQL优化原则和技巧
SQL编写及其优化培训
温有飘
2005年4月28日
软脑软件(北京)公司
目录
本培训包含的内容如下:
4
1.表、索引相关知识及其在数据库内部相关的物理存储4
1.1.数据库中最小的物理存储单位:
块(block)4
1.2.表数据在块中的存储以及RowId信息4
1.3.索引4
1.4.Bitmap索引介绍6
2.数据库查询操作的内部处理过程6
2.1.数据库的数据读取是以块为单位的6
2.2.查询操作内部过程以及索引的作用6
2.3.执行计划概念7
2.4.排序处理过程以及各种引起排序的操作7
2.5.多表join操作的内部过程7
3.写SQL语句时在性能方面的目标8
3.1.select列表中减少不需要的数据8
3.2.from列表中去除不需要的表8
3.3.where条件中应该考虑到索引的使用,避免一些写法8
3.3.1.避免过滤字段中套用函数,如果必须,则考虑函数索引9
3.3.2.避免把列放入表达式中去比较9
3.4.避免不需要的排序9
3.5.避免数据类型的隐式转换9
3.6.程序代码中的注意点10
3.6.1.由多个SQL语句完成的一个操作尽可能写成一个SQL就完成,避免分解10
3.6.2.使用数据库提供的约束来判断某些错误,如唯一性11
4.SQL语句知识11
4.1.SQL语句串讲11
4.1.1.selectf1fromA11
4.1.2.selectf1fromAwheref2=v_111
4.1.3.selectf1fromAorderbyf211
4.1.4.selectsum(f1)fromA12
4.1.5.selectf2,sum(f1)fromAgroupbyf212
4.1.6.selecta_f2,b_f2fromA,BwhereA.f1=B.f112
4.1.7.Select*fromreportwhereSALES_CODEin(selectSALES_CODEfromankenwhere…);14
4.1.8.Select*fromreportwhereSALES_CODEexists(select1fromankenwhere…);14
4.1.9.in,exists的选择14
4.1.10.selectyadfwast1,kopeewast2FromAunionallselectuufgerast1,poeprvcxast2fromB14
4.2.几种常见操作或函数15
4.2.1.比较符like15
4.2.2.isnull,isnotnull15
4.2.3.to_char,to_date,to_number15
4.2.4.nvl16
4.2.5.其他16
5.表设计的一些建议16
参考文献16
本培训包含的内容如下:
⏹表、索引相关知识及其在数据库内部相关的物理存储;
⏹数据库查询操作的内部处理过程;
⏹写SQL语句时在性能方面的目标;
⏹SQL语句知识;
⏹表设计的一些建议
下面阐述的内容以Oracle为例来讲述的,在排版方面可能有不妥当的地方,或有些内容前后重复,但各种知识应该比较容易理解和接受;
1.表、索引相关知识及其在数据库内部相关的物理存储
1.1.数据库中最小的物理存储单位:
块(block)
数据库最小的物理存储单位是块(9i默认是8k),每个块只能属于一个表;一行数据插入时会占用块中的一些存储,当块不能再容纳新数据时,则将启用新的块存储数据;
1.2.表数据在块中的存储以及RowId信息
表的字段除了设计者设计的字段外,还有个字段Rowid;RowId是oracle为每个表自动增加的一个字段。
当表中插入一行记录时,此记录在块就有一个唯一的物理位置,这个位置信息就保存在此行记录的RowId字段;
1.3.索引
表中记录的存储顺序是以先后插入顺序存储的,在一个数据量很大的表中,如果不引入其他的手段,每次查找小部分记录都是从第一条扫描到最后一条,这样,系统将慢得不能使用;使用索引可以有效解决问题;如图:
Report表(100万条记录)
RowId
Session_code
Sales_code
Employee_code
…
AAANslAAQAAA7YTAAh
10006
10000
17
AAANslAAYAAAAmWAAX
10007
10001
5
。
。
。
…
…
…
索引
Employee_code
RowId
5
AAANslAAYAAAAmWAAX
17
AAANslAAQAAA7YTAAh
…
…
索引的本质用途是通过它使读取进程在扫描源表时的数据块范围大大减小了,因此性能大幅度提高;
一般情况下,当检索的记录与所有记录数比较<=4%时,索引是很有效的,当比例更大时,索引反而有可能降低性能,因为即使使用了索引,读取进程还是扫描了大部分的表中的块,如果这样,倒不如不用索引直接扫描源表,因为减少了读取索引的块的开销;
当索引建好后,oracle优化器在执行sql时会选择是否使用索引,所以设计表者不用担心此索引会影响检索性能;
创建索引的标准是那一列是否经常在where条件中出现,否则不应该建立,因为,每次表记录的增加、删除以及修改那个字段值时,还要维护索引,增加了开销;
此节讲的索引叫B_tree索引,在内部存储中类似树状结构,有枝和叶,枝是oracle内部存储的一些连接数据,叶才存储实际的值,如图:
表中每条记录只要那个列是非NULL值,在索引中都有一个条目(entry)来存储(如上图),类似于表在块中一条条记录存储;
1.4.Bitmap索引介绍
当表中某列的distinct值比较少时,使用B_tree索引效率就不高了,因为以此列来做where条件过滤的话,结果集很可能超过总记录数的4%,正如前面说过的,超过4%记录使用B_tree索引,效率反而下降。
使用Bitmap位图索引可以解决这个问题;
Bitmap索引使用一个位图来记录数据情况,举例:
商品表product,有一个字段color存储颜色值,在所有商品中最多就10种不同的颜色,创建的位图图示如下:
其中,StartROWID是表中的第一条记录的物理地址,endROWID是表中最后一条记录的物理地址,bitmap是一张位图,存储一连串的0或1;
列值为blue的bitmap中,每个bit的位置在源表中都能找到某个位置与它一一对应,它们在各自对象中的相对位置一样。
bit值为1时,则源表对应位置那个rowid所在的记录的color字段值为‘blue’,为0时,则非‘blue’值;类似的,Green,Red,Yellow都有各自的bitmap;
试想,当where条件中有过滤条件AandB,条件A可以使用B_tree索引,条件B可以使用bitmap索引,那么从A条件中可以得到一个RowId的集合,从B条件也可以得到RowId结合,这样,只要从第一个rowid集中去除第二个rowid集中的值,得到结果集Rowid,我们就可以从源表得到数据了;
Bitmap使用了压缩技术,节约了存储,并且在一个bitmap上对某个位bit的值是1还是0时速度是非常快的;
2.数据库查询操作的内部处理过程
2.1.数据库的数据读取是以块为单位的
Oracle读取表中的数据时,是以整个块为单位的,有可能一次读多个块;从索引中得到rowid的物理位置时,也要把含rowid这个位置的块从磁盘中读出,然后才处理个别行;
2.2.查询操作内部过程以及索引的作用
举例:
select*frompersonal_functionwhereemployee_code=-200;
上述查询,如果employee_code上没有索引,则会把personal_function表数据的块全部读入内存中,同时从第一条记录查到最后一条记录,对符合条件的记录返回给用户;
如果employee_code有索引,则使用索引检索出含符合条件的Rowid的块读入内存,然后定位到rowid指示的位置上把记录返回给用户;
2.3.执行计划概念
2.2节中,如果employee_code=-200的记录数在占总记录数超过4%,则有可能就不会用索引查找,而是直接全表扫描,这是oracle的优化器经过各种检测后会自动选择的;
Oracle对已存在数据统计特征会选择不同的执行路径(或者全表,或者使用索引等),这就产生了不同的执行计划,使能达到最大的性能。
执行计划选择的模式有RBO和CBO方式,采用CBO方式时(这是oracle公司强烈建议使用的),sql语句中from后表的先后顺序以及where条件中各个条件的先后顺序变得不是很重要了,如果RBO方式则不然;
CBO全称Cost-basedOptimizer,基于代价的优化。
2.2节提到的SQL语句,Oracle是否使用索引,其内部会做各种比较,然后自动做出选择;为了使Oracle做出更精确的比较,我们应该周期性地对表进行统计,使Oracle了解到真实情况后做出更准确的判断。
因为要统计,所以称之为基于代价;
RBO全称Rule-basedOptimizer,基于规则,就是说Oracle定义的一套执行先后顺序,如,有索引则一定会用索引,就如2.2节的SQL语句,但这样有时并不是最好的执行选择。
2.4.排序处理过程以及各种引起排序的操作
举例:
select*frompersonal_functionwhereemployee_code=-200orderbyfunc_name;
由2.2节可知,如果没有orderby语句时,oracle只要检索到一条符合条件的记录就立刻返回给用户,直到所有数据返回完毕。
当有orderby时,情况就不一样了,oracle会把检索到的每一条记录先保存在一个用于排序的内存中,当所有符合条件的记录在那个区域完成排序后,再返回给用户;
通过排序这个操作,oracle中间多处理了一个步骤。
数据量越大,排序时间则更长,当数据量达到所找的内存区域无法容纳时,将使用磁盘做为临时排序区,此时,性能会大大降低,用户将等待更长的时间才能得到返回的结果;
因此,在一个SQL语句中,如果对返回的记录集没有顺序要求时,应该去除引起排序的语句;
引起排序的操作有:
orderby,distinct,union,groupby
2.5.多表join操作的内部过程
举例说明:
Esm系统中的3张表结构如下:
Employee:
EMPLOYEE_CODE
职员代码
EMPLOYEE_NAME
职员名称
EMPLOYEE_KANA
职员全称
Emp_detial
EMPLOYEE_CODE
职员代码
DEPART_CODE
职员部门代码
…
…
Depart
Depart_CODE
部门代码
DEPART_NAME
部门名称
…
…
现在要求列出所有职员的名称和其对应的部门名称,SQL语句如下:
selectaa.employee_name,cc.depart_name
fromemployeeaa,emp_detailbb,departcc
whereaa.employee_code=bb.employee_codeandbb.depart_code=cc.depart_code;
执行时,oracle可以选择如下的一个执行计划(可参考4.1.6节的流程图):
Employe表数据emp_detaildepart
00001
张三
…
00002
100
…
200
开发2部
…
00002
李四
…
00003
200
…
300
开发3部
…
00003
王五
…
00001
300
…
100
开发1部
…
1)读取employee的第一条记录,得到职员代码A;
2)再在emp_detail查找职员代码为A的第一条记录,得到部门代码B;
3)在表depart中查找部门代码为B的记录;
4)在各个表的记录中中取出需要的信息返回给用户;
5)。
。
。
2步和1步是个循环操作,2步嵌套于1步中,直到所有信息返回给用户;
Oracle如果有其他的执行记录更好的话,会选择其他的;
3.写SQL语句时在性能方面的目标
3.1.select列表中减少不需要的数据
select列表需要的信息应该按需索取,不能因为图方便用‘*’把所有字段内容取到客户端,这样,既增加服务器的负荷,又增加网络流量;
3.2.from列表中去除不需要的表
出现在from后的表,oracle都会对它检索并与其他表进行join操作,如果把不需要的表不经意间放在了from后面,可能会增加几倍甚至几十倍的负荷;如果where条件中也没有加上进行join的条件,oracle将对它进行笛卡儿乘积的join,这种负荷可想而知;
3.3.where条件中应该考虑到索引的使用,避免一些写法
3.3.1.避免过滤字段中套用函数,如果必须,则考虑函数索引
举例:
统计在某天的日报登记个数,其中,某天这个值V_date是从程序外面以参数的形式传递进去的字符串,格式如yyyy-mm-dd;
比较两种写法:
第一种:
Selectcount(*)fromreportwhereto_char(day,’yyyy-mm-dd’)=V_date
第二种:
Selectcount(*)fromreportwhereday=to_date(V_date,‘yyyy-mm-dd’);
必须选择第二种,因为如果day上有索引,而day作为函数to_char的参数,oracle将不使用它的索引,因而进行全表扫描,这样的后果是,也许2秒内就能统计出来的结果,却可能要花几十分钟甚至几小时;report表数据量越大,对比结果就更明显;
有些情况确实需要使用函数,如:
取出职员名称为V_name的职员代码,而V_name是从程序外面传进去的参数;
由于在最初增加职员记录时,每个存入的职员名称没有做大小写转化,比如,最初登陆了一个‘ZhanSan’的职员,此时V_name传进来的是zhansan,如果用如下语句查询将得不到记录:
Selectemployee_codefromemployeewhereemployee_name=V_name;
可使用如下语句
Selectemployee_codefromemployeewhereUPPER(employee_name)=UPPER(V_name);
此时由于套用了函数UPPER而列employee_code上的索引将不被使用,可以考虑创建函数索引,就是把‘UPPER(employee_name)’当成一个字段去创建索引,当查询时,则会使用此函数索引;
3.3.2.避免把列放入表达式中去比较
请看下面2条语句:
Select*fromAwheref1/5>300;
Select*fromAwheref1>300*5;
必须使用第二种,否则,如果f1有索引,第一种情况将不使用;
3.4.避免不需要的排序
2.4节中已说明排序需要额外的负荷,所以,当不需要排序时,应该避免;
3.5.避免数据类型的隐式转换
举例:
表A,有一字段code,varchar2类型,此字段存储的值都是由数字组成的串;
SQL语句:
Select*fromAwherecode=v_number;其中,v_number是由程序外面传入的数值;
因为v_number是数值,而code是varchar2类型,所以,Oracle会进行类型的隐式转换,把此语句转化成如下形式执行:
Select*fromAwhereto_number(code)=v_number;
把列嵌套在函数里(索引不能使用),这个是我们不期望的,所以正确的做法是我们应该进行显式的转换:
Select*fromAwherecode=to_char(v_number);
3.6.程序代码中的注意点
3.6.1.由多个SQL语句完成的一个操作尽可能写成一个SQL就完成,避免分解
举个例子:
需要得到2004年中,每个季度(1-3月,4-6月,7-9月,10-12月)的日报个数;
第一种方法:
比较容易想到的,用4个SQL语句分别去得到各个季度的统计:
Selectcount(*)fromreportwhereday>=to_date(‘2004-01-01’,’yyyy-mm-dd’)andday<=to_date(‘2004-03-3123:
59:
59’,’yyyy-mm-ddhh24:
mi:
ss’);
Selectcount(*)fromreportwhereday>=to_date(‘2004-04-01’,’yyyy-mm-dd’)andday<=to_date(‘2004-06-3023:
59:
59’,’yyyy-mm-ddhh24:
mi:
ss’);
Selectcount(*)fromreportwhereday>=to_date(‘2004-07-01’,’yyyy-mm-dd’)andday<=to_date(‘2004-09-3023:
59:
59’,’yyyy-mm-ddhh24:
mi:
ss’);
Selectcount(*)fromreportwhereday>=to_date(‘2004-10-01’,’yyyy-mm-dd’)andday<=to_date(‘2004-12-3123:
59:
59’,’yyyy-mm-ddhh24:
mi:
ss’);
第二种方法:
一个SQL语句完成
Select
Count(casewhenday>=to_date(‘2004-01-01’,’yyyy-mm-dd’)andday<=to_date(‘2004-03-3123:
59:
59’,’yyyy-mm-ddhh24:
mi:
ss’))asone,
Count(casewhenday>=to_date(‘2004-04-01’,’yyyy-mm-dd’)andday<=to_date(‘2004-06-3023:
59:
59’,’yyyy-mm-ddhh24:
mi:
ss’))assecond,
Count(casewhenday>=to_date(‘2004-07-01’,’yyyy-mm-dd’)andday<=to_date(‘2004-09-3023:
59:
59’,’yyyy-mm-ddhh24:
mi:
ss’))asthird,
Count(casewhenday>=to_date(‘2004-10-01’,’yyyy-mm-dd’)andday<=to_date(‘2004-12-3123:
59:
59’,’yyyy-mm-ddhh24:
mi:
ss’))asforth,
Fromreport;
显然,第二种方法可以提高4倍的性能;
要达到这种转化,编码人员需要对数据库提供的SQL函数有充分的认识;
3.6.2.使用数据库提供的约束来判断某些错误,如唯一性
有种情况,应用程序中经常出现,举个例子:
有张全国居民身份表resident,其中有个字段身份证号码ID_card,是主键,在登记一个居民时,为了避免发生主键冲突,应用程序往往保存之前用一个SqL语句去查此ID_card号是否已存在,存在则提示ID_card冲突。
上述情况完全可以避免使用写SQL语句的方法去查,因为oracle的每个错误都有一个唯一的错误号,只要在程序中去捕捉违例(exception),然后进行对比是否是主键冲突即可;
有些时候非主键的字段也要保持唯一性,则可以使用唯一索引。
当发生唯一性冲突时,也有一个错误号;
Oracle在自身版本升级的时候,错误号的含义是向后兼容的;
4.SQL语句知识
4.1.SQL语句串讲
4.1.1.selectf1fromA
这是一个最简单的SqL语句,想象一下A表的块读入内存,然后从第1条记录扫描到最后一条记录且每次立刻把f1字段值返回给用户,这个过程是全表扫描;
Sql语句中,有列别名和表别名,对很长表名或列名可以简化操作,如:
表别名:
Selectaa.STATUSfromREGULAR_REPORT_CONTENTSaa;
表别名前不应该使用‘as’,SQLSERVER支持,但Oracle里不支持;
列别名:
SelectMODIFIED_STATUS_CODE_OUTasmodfromTIMEBOOK_LOG;
程序代码中引用的时候就可以使用mod,而非长串;
如果在连接表中有同名表的时候,别名是必须的;
4.1.2.selectf1fromAwheref2=v_1
如果f2无索引,则第4.1.1节中每次扫描到一条记录时会根据条件进行过滤,符合条件则立即返回给用户,否则扫描下一条;
F2有索引且使用时,则从索引开始扫描,扫描时不象表一样从第一条目扫描到最后一条,oracle会根据内部算法很快定位到符合条件的条目,读取Rowid后根据物理地址定位到源表的记录上立即返回给用户,然后再在索引中定位到第二个符合条件的条目,直到全部;
4.1.3.selectf1fromAorderbyf2
在4.1.1节中讲到的每扫描的记录立即返回给用户,而当有排序时,则先发送到一个排序区中进行排序,等所有记录扫描完后,再把排序区中的数据集返回给用户;
当select后带distinct时,内部处理也有排序操作,更靠近distinct的字段就更先排序,同时在排序过程发现有各个字段信息完全相同的行时,只留一条记录,废除其他相同的;排序完成后,再把排序区中的数据集返回给用户;
4.1.4.selectsum(f1)fromA
oracle处理时类似这样:
使用一个变量V,初始值0,每次扫描一个记录时,v=v+f1,直到扫描到最后,把v返回给用户;
类似的,函数avg,max,min,count原理差不多,如果f1有索引时,可能会扫描索引而不扫描表,因为索引的块更少,而且包含了需要的全部信息;
4.1.5.selectf2,sum(f1)fromAgroupbyf2
上节中是把f1值加如到一个变量中,这里多了一个字段,可以这样理解:
每扫描一条记录时,如果f2值未出现过,则定义一个数组变量A,A[1]=f1,A[2]=f2;如果f2值以前出现过,则把当前f1值增加到对应的数组变量中,这样,直到表扫描完毕,按f2字段分组的各个统计值都记录在了一堆的数组中,最后,oracle把数组的各个值