如何编写优化的sql语句1文档格式.docx
- 文档编号:22409361
- 上传时间:2023-02-03
- 格式:DOCX
- 页数:26
- 大小:33.47KB
如何编写优化的sql语句1文档格式.docx
《如何编写优化的sql语句1文档格式.docx》由会员分享,可在线阅读,更多相关《如何编写优化的sql语句1文档格式.docx(26页珍藏版)》请在冰豆网上搜索。
在图1中还给出了执行了何种操作(例如:
INDEX(FULLSCAN),COUNT(STOPKEY)等,具体的说明在下面进行解释),同时在最后还给出了执行的代价(COST)。
2.SQL解释方案介绍
2.1.操作说明
系统中的全部操作可分为行操作或集(SET)操作。
二者之间的比较可以对比如下:
行操作
集操作
一次执行一行
在行的结果集上执行
如果不涉及集操作将在FETCH级上操作
当光标打开时,在EXECUTE级上执行
在取最后一行之前,用户可以看到第一次的结果
用户不能看到第一次的结果,直到所有的行被取出和处理完
示例:
全表扫描
使用groupby子句的全表扫描
对于行和集操作的分类如下(暂时列到ORACLE8):
And-equal,Bitmap,Concatenation,Countstopkey,Filter,Forupdate,Indexfullscan,Indexrangescan,Indexuniquescan,Intersection,Mergejoin,Nestedloops,Outerjoin,Projection,Sequence,Tableaccessbyrowid,Tableaccesscluster,Tableaccessfull,Tableaccesshash,Union,Union-all
Connectby,Count,Hashjoin,Minus,Remote,Sortaggregate,Sortgroupby,Sortjoin,Sortorderby,Sortunique,View
2.2.具体操作解释
在上述的操作中有许多我们平时很少用到,因此就不一一介绍了,只介绍日常常用的一些操作:
2.2.1.约定
Table_name
Column_name
Index_type
Company
Company_id
U_ind
City
Ind
State
Parent_company_id
Competitor
Product_id
Sales
Period_id
2.2.2.AND-EQUAL
说明:
用来合并由索引返回的值的排序列表。
AND-EQUAL用于非唯一索引的合并和唯一索引的范围扫描。
例子:
selectname,city,statefromcompanywherecity=’Roanoke’andstate=’VA’
2.2.3.CONCATENATION
用来执行结果集的unionall操作
selectname,city,statefromcompanywherestate=’TX’andcityin(‘Houston’,’Austin’,’Dallas’);
该例子最终可以该写成:
selectname,city,statefromcompanywhere(state=’TX’andcity=’Houston’)or(state=’TX’andcity=’Austin’)or(state=’TX’andcity=’Dallas’);
备注:
有时候在语句比较复杂时,Oracle可能不会使用CONCATENATION操作,而是使用部分索引的范围扫描(RangeScan)。
如果要强制使用CONCATENATION操作,就把语句写成最终的样子。
2.2.4.COUNT
当使用伪列(Pseudo-column),并且指定RowNum的最大值时,执行Count(计数)。
Count从它的子操作接收行并且增大RowNum的计数。
selectname,state,rownumfromcompanywherecity>
’Roanoke’
2.2.5.COUNTSTOPKEY
当使用限定计数的RowNum伪列时,执行COUNTSTOPKEY。
它从先前的操作接收行并且增加计数,如果计数达到了临界值,就产生一个“NoMoreRows”条件,结束查询并把结果返回给用户。
selectname,city,state,rownumfromcompanywherecity>
’Roanoke’andrownum<
10;
2.2.6.FILETER
使用在当没有索引可以用来帮助评估时,FILTER执行一个where子句条件。
当FILTER在一个解释方案里显示时,通常显示的是索引丢失或者存在的索引不能用的结果。
2.2.7.FORUPDATE
为所有能从select语句回复的行级别(rowlevel)上加锁。
selectnamefromcompanywherecity>
’Roanoke’forupdateofname;
2.2.8.HASHJOIN
它是行操作和集操作的混合。
它在内存中创建其中一个表的位图,然后利用哈希(HASH)功能在第二个表里定位联结行,通过HASHJOIN(哈希联结)将表联结起来。
selectcompany.namefromcompany,saleswherepany_id=pany_idandsales.period_id=3andsales.sales_total>
1000;
注意:
在这个例子中,sales表作为联结表被读到内存中,然后同company表中的记录进行逐行比较。
能够使用到hashjoin的条件为在联结的表中,一张表比其他的联结表小的多,并且这些记录能够全部被读到内存中去,那么系统就会使用hashjoin而不是使用nestedloops来进行连接。
有时即使为联结提供了一个索引,hashjoin也许比nestedloops联结跟可取。
2.2.9.INDEXRANGESCAN
它从索引里选择一定范围的值,索引既可以是唯一的也可以是复合的。
当遇到下列条件时,使用它:
●使用范围操作(如>
or<
)
●使用between子句
●使用有通配符的查找字符串(如:
’b%’)
●只使用复合索引的一部分
selectname,city,statefromcompanywherecity>
它的效率与两个因素有关:
选择范围内的关键值的数目以及索引的条件,当关键数目越多,查找时间越长;
分段越多,查找时间越长。
INDEXUNIQUESCAN
它从唯一索引中选择,是从已知字段里选择一行的最有效的方法。
selectname,city,statefromcompanywherecompany_id=12345
2.2.10.MERGEJOIN
它通过合并每个表中已排序的记录列表去联结数据表。
它主要是针对大型批处理操作,但是对事务处理可能是无效的。
当ORACLE实施联结而又不能使用索引时,就使用mergejoin。
selectcompany.namefromcompany,saleswherepany_id+0=pany_id+0andsales.period_id=3andsales.sales_total>
1000
需要值得注意的是它是针对集操作。
对于它的执行计划,在执行完一个全表扫描之后还要进行一个sortjoin的排序操作,然后再进行mergejoin操作。
2.2.11.NESTEDLOOPS
在进行多表的联结时,如果有一个联结的列被索引过的时候,那么nestedloops将起作用。
selectcompany.namefromcompany,saleswherepany_id=pany_idandsales.period_id=3andsales.sales_total>
●在使用nestedloops的时候,查询的驱动表的选择是很重要的,关于这点,这下面会作专题讨论。
原则是如果是rule_based的情况下,驱动表将选择from语句的最后一个,如果是cost_based的情况下,驱动表的选择间考虑表的大小和索引的选择性。
●在进行nestedloops的操作时,首先作的是驱动表的全表扫描,然后才使用索引的扫描,然后才进行nestedloops操作。
2.2.12.OUTERJOIN
它是nestedloops、hashjoin、mergejoin操作的一个选项。
它死来自驱动表的行能够返回到调用查询,虽然在联结数据表里没有发现任何匹配的行。
selectcompany.namefromcompany,saleswherepany_id=pany_id(+)andsales.period_id=3andsales.sales_total>
2.2.13.SEQUENCE
当通过nextval和currval来访问sequence时,使用sequence操作。
selectseq_tbi_tm.nextvalfromdual;
2.2.14.SORTAGGREGATE
每当对数据集的组操作功能在sql语句中出现但是没有groupby子句时,SORTAGGREGATE(聚集排序)就用来给结果排序和聚集。
这些组函数包括:
max、min、count、sum、avg
selectsum(sales_total)fromsales;
2.2.15.SORTGROUPBY
在数据集中实现分组功能
selectzip,count(*)fromcompanygroupbyzip;
2.2.16.SORTJOIN
把用于MERGEJOIN操作的一个记录集排序,同mergejoin是同步出现的。
2.2.17.SORTORDERBY
它用于给结果集排序,但是不去除重复记录。
selectnamefromcompanyorderbyname;
2.2.18.SORTUNIQUE
在由minus、intersection和union操作处理前,它用于给结果集排序并将重复记录去除。
selectcompany_idfromcompanyminusselectcompany_idfromcompeitor;
2.2.19.TABLEACCESSBYROWID
基于所提供Rowid的操作,它从一个表返回一条记录。
这是从一个表返回记录的最快的方法。
selectnamefromcompanywherecompany_id=12345andactive_flag=’Y’;
2.2.20.TABLEACCESSFULL
当没有为行查找提供Rowid时,oracle将扫描给定表里的每一块,直到读完所有行。
select*fromcompany;
2.2.21.UNION
它从两个或者跟多个查询结果返回行的单个集。
为了执行这个操作,Oracle首先根据Select语句过滤所有的行,给它们排序,然后实行UnionMerge。
在Union操作期间,包含了Unionall操作。
selectcompany_idfromcompanyunionselectcompany_idfromcompetitor;
如果一个操作不考虑去除重复记录的话,请使用unionall,它的执行效率比union高。
三.编写高效的SQL:
在Oracle系统的性能提升过程中,SQL语句的优化的效果是最明显的。
因此,编写高效的SQL语句对整个系统性能的提高是应用中效果最明显的,同时也是代价最低的。
一个高效的SQL语句基本上都是要利用到索引的,因此我们在谈SQL语句的同时也介绍一下索引。
并且,任何语句都同回滚段都分不开,因此也会介绍一下回滚段的情况以及使用。
1.什么是索引
1.1.索引定义
索引本身也是一个数据库对象。
对于在表的索引列上的每一值将包含一项(ENTRY),为行提供直接的快速存取。
索引的存储参数的取值和使用同表的存储参数的使用一致。
1.2.索引的组织存储形式
在Oracle系统中,索引的数据存储是以B+树的形式存在的。
需要值得注意是,在索引的数据项发生删除的时候,释放出来的空闲空间无法再次使用。
如果对索引数据发生频繁的update或者删除的时候,会导致索引失去平衡,或者导致索引的层次增加,会导致索引停滞(IndexStagnation)现象的出现。
为解决该问题,可以考虑重建索引。
重建索引可以使用Alterindex….Rebulid语句来实现,该功能可以使索引再次平衡,同时也可以对索引的存储地点以及存储策略进行修改。
从Oracle8开始,索引的类型增加了两种,一为位图索引(BitmapIndex)主要针对重复值比较多的列而创建的索引,对它的逻辑操作的效果比较好;
一为反向索引(ReverseIndex)主要是为尽量保持索引的平衡而考虑创建,对该索引的查询只有使用“=”才起作用,而对于范围内的查询(RangeScan)不起作用。
对于数据比较多的表创建索引的时候,从考虑效率角度出发,在创建索引的时候使用NoLogging的参数来提高效率。
该参数在创建索引的时候不产生重作日志,可以有效的提高效率,加快索引的产生。
1.3.使用索引的优缺点
在下列情况下ORACLE可利用索引改进性能:
按指定的索引列的值查找行,按索引列的顺序存取表。
然而,索引虽然可以加快查询的速度,但是它可减慢INSERT、DELETE和UPDATE命令的速度,由于这些操作要影响索引列的值,ORACLE必须对索引数据和表数据进行维护。
1.4.创建索引的限制
一个索引最多可包含16列(到Oracle8开始为32列),索引项为每一列的数据值的连串,按指定的列的顺序连串。
这一顺序对ORACLE如何使用该索引非常重要。
ORACLE在一张表上可建立多个索引,索引的数目没有限制。
但是应该注意增加索引会增加维护表所需的处理时间。
建议只有当要索引的数据在表中所占的数据量,少于总数据量的15%时,使用索引才会提高查询的速度,否则对表的全表扫描的速度还比使用索引的速度快。
1.5.创建索引遵循的原则
对于索引列的选择遵从下列的准则:
◆经常用于WHERE子句中使用的列考虑作索引的列。
◆经常用于SQL语句中连结表的列考虑作为索引的列。
◆在Cost_based的优化方式下,对于复合索引的主列的选择显的尤为重要。
它的选择可以遵循以下原则:
在Where条件中频繁使用的列为主列;
对于数据最有选择性的列为主列。
◆一个索引列要具有好的选择性。
一个索引的选择性(Selectivity)是对具有相同值的表中的百分比。
一个索引的选择性好,就是很少行有相同值。
可以通过用具有不同索引值的数目除表中的行数来决定索引的可选择性。
可以用ANALYZE命令获取这些值。
用这种方法计算的可选择性应该解释为百分比。
◆不要将具有很少不同值的列作为索引列。
这样的列具有很差的选择性,并且不能优化性能,除非频繁选择的值比其他列值来更少出现。
例如:
在表中如果存在以YES或NO为值的列,那么尽量不要用该列作为索引列,因为用此列作为索引不会提高系统的性能,但是如果在表中YES的值很少出现,而在运用中又经常以YES值为查询,则以该列为索引可能会改进系统的性能。
从Oracle8开始可以考虑创建BitmapIndex来解决一些问题。
◆不要将频繁修改的列作为索引列。
因为修改索引列的UPDATE语句和修改索引表的INSERT和DELETE语句将比没有索引要用更多的时间。
这样的语句必须修改索引中的数据,还要修改表中的数据。
◆不要将只出现在带函数或操作符的WHERE子句中的列作为索引列。
使用带索引列的函数(不是MIN或MAX函数)或操作符的WHERE语句并不使用索引的存取路径。
◆在大量并发INSERT、UPDATE和DELETE语句存取父表和子表的情况下,考虑对参考完整性约束的外部键作索引。
也就是说对表的外键在可能的情况下创建索引以加快操作的速度,改进性能。
因为如果对于一张表而言,如果在它的外键上没有索引的话,那么在对它操作的同时,ORACLE将对此外键对应的主表加一个表级锁,以保证数据的完整性和一致性。
如果在外键上有索引的的话,那么它就可以通过索引找到主表的ROWID,只对主表加一个行级锁。
1.6.如何选择单列索引还是复合索引
1.6.1.索引选择性的测量
所谓索引选择性是指索引中有多少不同的值同表中记录数目的比值,不同的值越多,代表选择性越高,最高的选择性为1(例如唯一性索引)。
对于索引选择性的测量可以通过不同的方法来进行测量。
一种是通过手工的方式作,一种是通过自动的方式来作。
自动方式来作的是通过对表进行Analyze之后,然后通过查看相应数据字典的值来计算。
对索引的不同值可以通过USER_INDEXES.DISTINCT_KEYS查到,表的全部记录数可以通过USER_TABLES.NUM_ROWS查到。
在创建索引之前,可能需要查看一下列的不同值的数目,它可以通过USER_TAB_COLUMNS.NUM_DISTINCT查到。
手工方式,就是对相应的索引列进行DISTINCT操作,然后得到不同值的数目。
然后对表进行COUNT(*)函数操作,得到表的记录数目,把两者数据进行比较,就得到索引的选择性。
1.6.2.单列索引和复合索引的选择
对于复合索引而言,主列(第一列)的选择性从很大程度上决定了复合索引的选择性。
我们还是举前面的例子来说明如何选择索引的创建。
假设我们在Where条件中经常用City=’xxx’andstate=’xxx’为条件的话,这时有两种选择,一种是方案1,一种是方案3。
假设复合索引同单独索引的选择性差不多的话,那么选择方案3就会有一个AND_EQUAL的操作,会导致比方案1多将近2/3的索引操作,因此需要考虑方案1。
又假设在系统中有大量的基于State的Where语句操作,这时还是有两种方案一是方案2,一是方案3。
此时如果基于State列上的索引的数据量不大的话,而且相应的附加开销不多的话,基于同上面相同的理由,可以选择方案2。
不过如果单列索引同复合索引的选择性相差很大的话又另当别论了。
附:
方案
索引#1
索引#2
1
City,state
None
2
3
2.编写利用索引的SQL语句
在日常的应用中,索引是否引用是关系到该SQL语句效率的一个非常关键的因素。
在日常应用中应该注意到如果在SQL语句中出现下列语法,则系统无法利用到索引:
◆COLUMN1>
COLUMN2
◆COLUMN1<
=COLUMN2
其中COLUMN1和COLUMN2在同一张表中。
◆COLUMNISNULL
◆COLUMNISNOTNULL
◆COLUMNNOTIN
◆COLUMN!
=EXPR
◆COLUMNLIKE‘%ANYTHING’
在上述条件中不管COLUMN上是否有建索引,SQL语句都不会利用索引。
◆EXPR是一个表达式,它用运算符或函数操作在该列上,不管列上是否有索引,例如:
EXPR*COLUMN=ANYTHING,则不能利用索引。
◆NOTEXISTSSUBQUERY
◆不包含未被索引的列的任何条件
◆在LIKE表达式中如果模糊的列为数字或日期的话,那么该语句同样无法利用索引。
◆在ORACLE内部的本身的数据转换(DATACONVERSION)将引起ORACLE索引列的不被使用。
在日常SQL语句使用时还应该注意使用索引的原则:
◆在使用复合索引时,应该注意是否有利用到该索引的前导部分(LeadingPosition),在使用该复合索引时只有使用到该索引的前导部分,该索引才能被利用,否则就根本无法发挥索引作用。
用户在一张表的三个列(X,Y,Y)张建立一复合索引,该复合索引的顺序为XYZ。
在SQL语句的WHERE条件中使用XY,XYZ,XZ等都可利用到索引,但是如果使用YZ,Z等就无法利用到索引。
如果对索引的所有列都有引用的话,则无所谓对字段位置的使用了。
◆对于复合索引,对于主列的操作是否使用索引同样受上面的条件限制。
◆在SQL语句中尽量避免不要使用不等或者NULL。
3.回滚段的介绍:
介绍回滚段的原理,使用时要注意的事项,从它入手如何
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 如何 编写 优化 sql 语句