sql+优化讲义Word文档下载推荐.docx
《sql+优化讲义Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《sql+优化讲义Word文档下载推荐.docx（7页珍藏版）》请在冰豆网上搜索。
1、关键字段建立索引。
2、使用存储过程，它使SQL变得更加灵活和高效。
3、备份数据库和清除垃圾数据。
4、SQL语句语法的优化。
5、清理删除日志。
三．SQL语句优化的原则：
不要以为只有SELECT语句是查询。
实际上，带有任何WHERE条件的DML（INSERT、UPDATE、DELETE）语句中都包含查询要求，在后面的文章中，当说到查询时，不一定只是指SELECT语句，也有可能指DML语句中的查询部分。
我们知道，SQL语句同其它语言（如C语言）的语句不一样，它是非过程化（non-procedural）的语句，即当你要取数据时，不需要告诉数据库通过何种途径去取数据，如到底是通过索引取数据，还是应该将表中的每行数据都取出来，然后再通过一一比较的方式取数据（即全表扫描）。
为了实现一个查询，内核必须为每个查询定制一个查询策略，或为取出符合条件的数据生成一个执行计划（executionplan）。
典型的，对于同一个查询，可能有几个执行计划都符合要求，都能得到符合条件的数据。
例如，参与连接的表可以有多种不同的连接方法，这取决于连接条件和优化器采用的连接方法。
为了在多个执行计划中选择最优的执行计划，优化器必须使用一些实际的指标来衡量每个执行计划使用的资源（I/0次数、CPU等），这些资源也就是我们所说的代价（cost）。
如果一个执行计划使用的资源多，我们就说使用执行计划的代价大。
以执行计划的代价大小作为衡量标准，优化器选择代价最小的执行计划作为真正执行该查询的执行计划，并抛弃其它的执行计划。
3.1选择最有效率的表名顺序
在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.首先,扫描第一个表（FROM子句中最后的那个表）并对记录进行派序,然后扫描第二个表（FROM子句中最后第二个表）,最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.
基础表（DrivingTable）是指被最先访问的表（通常以全表扫描的方式被访问）.
例如:
表TAB116,384条记录
表TAB21条记录
选择TAB2作为基础表（最好的方法）
selectcount（*）fromtab1,tab2执行时间0.96秒oracle测试标准
选择TAB2作为基础表（不佳的方法）
selectcount（*）fromtab2,tab1执行时间26.09秒
如果有3个以上的表连接查询,那就需要选择交叉表（intersectiontable）作为基础表,交叉表是指那个被其他表所引用的表.
EMP表描述了LOCATION表和CATEGORY表的交集.
SELECT*
FROMLOCATIONL,
CATEGORYC,
EMPE
WHEREE.EMP_NOBETWEEN1000AND2000
ANDE.CAT_NO=C.CAT_NO
ANDE.LOCN=L.LOCN
将比下列SQL更有效率
FROMEMPE,
LOCATIONL,
CATEGORYC
WHEREE.CAT_NO=C.CAT_NO
ANDE.EMP_NOBETWEEN1000AND2000
3.3WHERE子句中的连接顺序
采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
同时在链接的表中能过滤的就应该先进行过滤。
（低效,执行时间156.3秒）
SELECT…
FROMEMPE
WHERESAL>
50000
ANDJOB=‘MANAGER’
AND25<
（SELECTCOUNT（*）FROMEMP
WHEREMGR=E.EMPNO）;
（高效,执行时间10.6秒）
WHERE25<
WHEREMGR=E.EMPNO）
ANDSAL>
ANDJOB=‘MANAGER’;
3.2SELECT子句中避免使用‘*‘
当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用‘*’是一个方便的方法.但这是一个非常低效的方法.实际上,在解析的过程中,会将’*’依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间.
3.3尽量多使用COMMIT
只要有可能,在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
COMMIT所释放的资源:
a.回滚段上用于恢复数据的信息.
b.被程序语句获得的锁
c.redologbuffer中的空间
3.4计算记录条数
和一般的观点相反,count（*）比count
（1）稍快,当然如果可以通过索引检索,对索引列的计数仍旧是最快的.例如COUNT（EMPNO）
3.5用Where子句替换HAVING子句
避免使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.
低效:
SELECTREGION，AVG（LOG_SIZE）
FROMLOCATION
GROUPBYREGION
HAVINGREGIONREGION!
=‘SYDNEY’
ANDREGION!
=‘PERTH’
高效
WHEREREGIONREGION!
GROUPBYREGION；
HAVING中的条件一般用于对一些集合函数的比较,如COUNT（）等等.除此而外,一般的条件应该写在WHERE子句中
3.6通过内部函数提高SQL效率
3.7使用表的别名（Alias）
当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.
3.8用EXISTS替代IN
3.9用NOTEXISTS替代NOTIN
3.10用表连接替换EXISTS
通常来说,采用表连接的方式比EXISTS更有效率
SELECTENAME
WHEREEXISTS（SELECT‘X’
FROMDEPT
WHEREDEPT_NO=E.DEPT_NO
ANDDEPT_CAT=‘A’）;
（更高效）
FROMDEPTD,EMPE
WHEREE.DEPT_NO=D.DEPT_NO
ANDDEPT_CAT=‘A’;
3.11用索引提高效率
索引是表的一个概念部分,用来提高检索数据的效率.
虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价.索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列被修改时,索引本身也会被修改.这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5次的磁盘I/O.因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.
3.12避免在索引列上使用计算
WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描
举例:
低效：
SELECT…
WHERESAL*12>
25000;
高效:
25000/12;
非常实用的规则，请务必牢记
3.13自动选择索引
如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性．
在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯一性索引．
FROMEMP
WHEREEMPNO=2326
ANDDEPTNO=20;
这里，只有EMPNO上的索引是唯一性的，所以EMPNO索引将用来检索记录．
TABLEACCESSBYROWIDONEMP
INDEXUNIQUESCANONEMP_NO_IDX
3.14避免在索引列上使用NOT
通常， 我们要避免在索引列上使用NOT,NOT会产生在和在索引列上使用函数相同的
影响.
3.15用>
=替代>
如果DEPTNO上有一个索引,
SELECT*
WHEREDEPTNO>
=4
3
两者的区别在于,前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.
3.16用UNION替换OR（适用于索引列）
通常情况下,用UNION替换WHERE子句中的OR将会起到较好的效果.对索引列使用OR将造成全表扫描.注意,以上规则只针对多个索引列有效.如果有column没有被索引,查询效率可能会因为你没有选择OR而降低.
在下面的例子中,LOC_ID和REGION上都建有索引.
SELECTLOC_ID,LOC_DESC,REGION
WHERELOC_ID=10
UNION
WHEREREGION=“MELBOURNE”
WHERELOC_ID=10ORREGION=“MELBOURNE”
如果你坚持要用OR,那就需要返回记录最少的索引列写在最前面.
3.17用IN来替换OR
SELECT….
ORLOC_ID=20
ORLOC_ID=30
SELECT…
WHERELOC_ININ（10,20,30）;
3.18避免在索引列上使用ISNULL和ISNOTNULL
避免在索引中使用任何可以为空的列，
3.19总是使用索引的第一个列
如果索引是建立在多个列上,只有在它的第一个列（leadingcolumn）被where子句引用时,优化器才会选择使用该索引.
3.20用UNION-ALL替换UNION（如果有可能的话）
当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并,然后在输出最终结果前进行排序.
如果用UNIONALL替代UNION,这样排序就不是必要了.效率就会因此得到提高.
UNION将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存
union:
如果查询出来的结果中有重复记录，那么就去重，英文称之为"
distinct"
unionall:
就显示所有的符合条件的记录，重复也保留
3.21ORDERBY子句只在两种严格的条件下使用索引.
ORDERBY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.
ORDERBY中所有的列必须定义为非空.
3.22避免改变索引列的类型.
当比较不同数据类型的数据时,自动对列进行简单的类型转换.
假设EMPNO是一个数值类型的索引列.
WHEREEMPNO=‘123’
实际上,经过ORACLE类型转换,语句转化为:
WHEREEMPNO=TO_NUMBER（‘123’）
幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.
现在,假设EMP_TYPE是一个字符类型的索引列.
WHEREEMP_TYPE=123
这个语句被ORACLE转换为:
WHERETO_NUMBER（EMP_TYPE）=123
因为内部发生的类型转换,这个索引将不会被用到!
为了避免ORACLE对你的SQL进行隐式的类型转换,最好把类型转换用显式表现出来.注意当字符和数值比较时,ORACLE会优先转换字符类型到数字类型.
3.23需要当心的WHERE子句
某些SELECT语句中的WHERE子句不使用索引.这里有一些例子.
在下面的例子里,‘!
=’将不使用索引.记住,索引只能告诉你什么存在于表中,而不能告诉你什么不存在于表中.‘+’是数学函数.就象其他数学函数那样,停用了索引
3.24避免使用耗费资源的操作
带有DISTINCT,UNION,MINUS,INTERSECT,ORDERBY的SQL语句会启动SQL引擎
执行耗费资源的排序（SORT）功能.DISTINCT需要一次排序操作,而其他的至少需要执行两次排序.
升级会员 