第一篇：OracleSQL语句的执行计划优化的总结
通过分析SQL语句的执行计划优化SQL(总结)
第一章、第2章 并不是很重要，是自己的一些想法，关于如何做一个稳定、高效的应用系统的一些想法。
第三章以后都是比较重要的。
附录的内容也是比较重要的。我常用该部分的内容。
前言
本文档主要介绍与SQL调整有关的内容，内容涉及多个方面：SQL语句执行的过程、ORACLE优化器，表之间的关联，如何得到SQL执行计划，如何分析执行计划等内容，从而由浅到深的方式了解SQL优化的过程，使大家逐步步入SQL调整之门，然后你将发现„„。
该文档的不当之处，敬请指出，以便进一步改正。请将其发往我的信箱：xu_yu_jin2000@sina.com。
如果引用本文的内容，请著名出处！
作者：徐玉金
MSN：sunny_xyj@hotmail.com
Email: xu_yu_jin2000@sina.com
日期：2005.12.12
活跃于:www.xiexiebang.com;
这样在分析时导致查询出的数据过多，不方便，所以用count(a.CHANNEL||B.user_class)来代替，而且count(a.CHANNEL||B.user_class)操作本身并不占用过多的时间，所以可以接受此种替代。
利用索引查询出SWD_BILLDETAIL表中所有记录的方法 SQL> select count(id)from SWD_BILLDETAIL;COUNT(ID)----------
53923574 Elapsed: 00:02:166.00 Execution Plan---------------------------0
SELECT STATEMENT Optimizer=CHOOSE(Cost=18051 Card=1)1
0
SORT(AGGREGATE)2
INDEX(FAST FULL SCAN)OF 'SYS_C001851'(UNIQUE)(Cost=18051 Card=54863946)
Statistics---------------------------
0 recursive calls
1952 db block gets
158776 consistent gets
158779 physical reads
1004 redo size
295 bytes sent via SQL*Net to client
421 bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts(memory)
0 sorts(disk)rows processed
利用全表扫描从SWD_BILLDETAIL表中取出全部数据的方法。SQL> select count(user_class)from swd_billdetail;COUNT(USER_CLASS)-----------------
53923574 Elapsed: 00:11:703.07 Execution Plan---------------------------0
SELECT STATEMENT Optimizer=CHOOSE(Cost=165412 Card=1 Bytes=2)1
0
SORT(AGGREGATE)2
TABLE ACCESS(FULL)OF 'SWD_BILLDETAIL'(Cost=165412 Card=54863946 Bytes=109727892)
Statistics---------------------------
0 recursive calls
8823 db block gets
1431070 consistent gets
1419520 physical reads
0 redo size
303 bytes sent via SQL*Net to client
421 bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts(memory)
0 sorts(disk)rows processed
select count(a.CHANNEL||B.user_class)from swd_billdetail B, SUPER_USER A where A.cn = B.cn;EXEC_ORDER PLANLINE------------------------
SELECT STATEMENT OPT_MODE:CHOOSE(COST=108968,CARD=1,BYTES=21)
SORT(AGGREGATE)(COST=,CARD=1,BYTES=21)
NESTED LOOPS
(COST=108968,CARD=1213745,BYTES=25488645)
TABLE ACCESS(FULL)OF 'SWORD.SUPER_USER'(COST=2,CARD=2794,BYTES=27940)
TABLE ACCESS(BY INDEX ROWID)OF 'SWORD.SWD_BILLDETAIL'(COST=39,CARD=54863946,BYTES=603503406)
INDEX(RANGE SCAN)OF 'SWORD.IDX_DETAIL_CN'(NON-UNIQUE)(COST=3,CARD=54863946,BYTES=)
这个查询耗费的时间很长，需要1个多小时。运行后的信息如下：
COUNT(A.CHANNEL||B.USER_CLASS)------------------------------
1186387
Elapsed: 01:107:6429.87
Execution Plan---------------------------
0
SELECT STATEMENT Optimizer=CHOOSE(Cost=108968 Card=1 Bytes=21)
0
SORT(AGGREGATE)
NESTED LOOPS(Cost=108968 Card=1213745 Bytes=25488645)
TABLE ACCESS(FULL)OF 'SUPER_USER'(Cost=2 Card=2794Bytes=27940)
TABLE ACCESS(BY INDEX ROWID)OF 'SWD_BILLDETAIL'(Cost=39 Card=54863946 Bytes=603503406)
INDEX(RANGE SCAN)OF 'IDX_DETAIL_CN'(NON-UNIQUE)(Cost=3 Card=54863946)Statistics---------------------------
0 recursive calls db block gets
1196954 consistent gets
1165726 physical reads
0 redo size
316 bytes sent via SQL*Net to client
421 bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts(memory)
0 sorts(disk)rows processed
将语句中加入hints，让oracle的优化器使用嵌套循环，并且大表作为驱动表，生成新的执行计划：
select /*+ ORDERED USE_NL(A)*/ count(a.CHANNEL||B.user_class)from swd_billdetail B, SUPER_USER A where A.cn = B.cn;
EXEC_ORDER PLANLINE------------------
SELECT STATEMENT OPT_MODE:CHOOSE(COST=109893304,CARD=1,BYTES=21)
SORT(AGGREGATE)(COST=,CARD=1,BYTES=21)
NESTED LOOPS
(COST=109893304,CARD=1213745,BYTES=25488645)
TABLE ACCESS(FULL)OF 'SWORD.SWD_BILLDETAIL'(COST=165412,CARD=54863946,BYTES=603503406)
TABLE ACCESS(BY INDEX ROWID)OF 'SWORD.SUPER_USER'(COST=2,CARD=2794,BYTES=27940)
INDEX(RANGE SCAN)OF 'SWORD.IDX_SUPER_USER_CN'(NON-UNIQUE)(COST=1,CARD=2794,BYTES=)
这个查询耗费的时间较短，才20分钟，性能比较好。运行后的信息如下：
COUNT(A.CHANNEL||B.USER_CLASS)------------------------------
1186387
Elapsed: 00:20:1208.87
Execution Plan---------------------------
0
SELECT STATEMENT Optimizer=CHOOSE(Cost=109893304 Card=1 Bytes=21)
0
SORT(AGGREGATE)
NESTED LOOPS(Cost=109893304 Card=1213745 Bytes=25488645)
TABLE ACCESS(FULL)OF 'SWD_BILLDETAIL'(Cost=165412 Card=54863946 Bytes=603503406)
TABLE ACCESS(BY INDEX ROWID)OF 'SUPER_USER'(Cost=2Card=2794 Bytes=27940)
INDEX(RANGE SCAN)OF 'IDX_SUPER_USER_CN'(NON-UNIQUE)(Cost=1 Card=2794)
Statistics---------------------------
0 recursive calls
8823 db block gets
56650250 consistent gets
1413250 physical reads
0 redo size
316 bytes sent via SQL*Net to client
421 bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts(memory)
0 sorts(disk)rows processed
总结：
因为上两个查询都是采用nested loop循环，这时采用哪个表作为driving table就很重要。在第一个sql中，小表(SUPER_USER)作为driving table，符合oracle优化的建议，但是由于SWD_BILLDETAIL表中cn列的值有很多重复的，这样对于SUPER_USER中的每一行，都会在SWD_BILLDETAIL中有很多行，利用索引查询出这些行的rowid很快，但是再利用这些rowid去查询SWD_BILLDETAIL表中的user_class列的值，就比较慢了。原因是这些rowid是随机的，而且该表比较大，不可能缓存到内存，所以几乎每次按照rowid查询都需要读物理磁盘，这就是该执行计划比较慢的真正原因。从结果可以得到验证：查询出1186387行，需要利用rowid从SWD_BILLDETAIL表中读取1186387次，而且大部分为从硬盘上读取。
反其道而行之，利用大表(SWD_BILLDETAIL)作为driving表，这样大表只需要做一次全表扫描(而且会使用多块读功能，每次物理I/O都会读取几个oracle数据块，从而一次读取很多行，加快了执行效率)，对于读出的每一行，都与SUPER_USER中的行进行匹配，因为SUPER_USER表很小，所以可以全部放到内存中，这样匹配操作就极快，所以该sql执行的时间与SWD_BILLDETAIL表全表扫描的时间差不多(SWD_BILLDETAIL全表用11分钟，而此查询用20分钟)。
另外：如果SWD_BILLDETAIL表中cn列的值唯一，则第一个sql执行计划执行的结果或许也会不错。如果SUPER_USER表也很大，如500万行，则第2个sql执行计划执行的结果反而又可能会差。其实，如果SUPER_USER表很小，则第2个sql语句的执行计划如果不利用SUPER_USER表的索引，查询或许会更快一些，我没有对此进行测试。
所以在进行性能调整时，具体问题要具体分析，没有一个统一的标准。
[center]第6章 其它注意事项[/center]
1.不要认为将optimizer_mode参数设为rule，就认为所有的语句都使用基于规则的优化器
不管optimizer_mode参数如何设置，只要满足下面3个条件，就一定使用CBO。
1)如果使用Index Only Tables(IOTs), 自动使用CBO.2)Oracle 7.3以后，如果表上的Paralle degree option设为>1，则自动使用CBO, 而不管是否用rule hints.3)除rlue以外的任何hints都将导致自动使用CBO来执行语句
总结一下，一个语句在运行时到底使用何种优化器可以从下面的表格中识别出来，从上到下看你的语句到底是否满足description列中描述的条件：
Description
对象是否被分析
优化器的类型
~~~~~~~~~~~
~~~~~~~~~~~~
~~~~~~~~~
Non-RBO Object(Eg:IOT)
n/a
#1
Parallelism > 1
n/a
#1
RULE hint
n/a
RULE
ALL_ROWS hint
n/a
ALL_ROWS
FIRST_ROWS hint
n/a
FIRST_ROWS
*Other Hint
n/a
#1
OPTIMIZER_GOAL=RULE
n/a
RULE
OPTIMIZER_GOAL=ALL_ROWS
n/a
ALL_ROWS
OPTIMIZER_GOAL=FIRST_ROWS
n/a
FIRST_ROWS
OPTIMIZER_GOAL=CHOOSE
NO
RULE
OPTIMIZER_GOAL=CHOOSE
YES
ALL_ROWS
#1 表示除非OPTIMIZER_GOAL 被设置为FIRST_ROWS，否则将使用ALL_ROWS。在PL/SQL中，则一直是使用ALL_ROWS
*Other Hint 表示是指除RULE、ALL_ROWS 和FIRST_ROWS以外的其它提示
2)当CBO选择了一个次优化的执行计划时, 不要同CBO过意不去, 先采取如下措施: a)检查是否在表与索引上又最新的统计数据
b)对所有的数据进行分析，而不是只分析一部分数据
c)检查是否引用的数据字典表，在oracle 10G之前，缺省情况下是不对数据字典表进行分析的。
d)试试RBO优化器，看语句执行的效率如何，有时RBO能比CBO产生的更好的执行计划
e)如果还不行，跟踪该语句的执行，生成trace信息，然后用tkprof格式化trace信息，这样可以得到全面的供优化的信息。
3)假如利用附录的方法对另一个会话进行trace，则该会话应该为专用连接
4)不要认为绑定变量(bind variables)的缺点只有书写麻烦，而优点多多，实际上使用绑定变量虽然避免了重复parse，但是它导致优化器不能使用数据库中的列统计，从而选择了较差的执行计划。而使用硬编码的SQL则可以使用列统计。当然随着CBO功能的越来越强，这种情况会得到改善。目前就已经实现了在第一次运行绑定变量的sql语句时，考虑列统计。
5)如果一个row source 超过10000行数据，则可以被认为大row source
6)有(+)的表不是driving table，注意：如果有外联接，而且order hint指定的顺序与外联结决定的顺序冲突，则忽略order hint
7)影响CBO选择execution plan的初始化参数:
这些参数会影响cost值 ALWAYS_ANTI_JOIN B_TREE_BITMAP_PLANS COMPLEX_VIEW_MERGING DB_FILE_MULTIBLOCK_READ_COUNT FAST_FULL_SCAN_ENABLED HASH_AREA_SIZE HASH_JOIN_ENABLED HASH_MULTIBLOCK_IO_COUNT OPTIMIZER_FEATURES_ENABLE OPTIMIZER_INDEX_CACHING OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_MODE> / GOAL OPTIMIZER_PERCENT_PARALLEL OPTIMIZER_SEARCH_LIMIT PARTITION_VIEW_ENABLED PUSH_JOIN_PREDICATE SORT_AREA_SIZE SORT_DIRECT_WRITES SORT_WRITE_BUFFER_SIZE STAR_TRANSFORMATION_ENABLED V733_PLANS_ENABLED CURSOR_SHARING
第二篇：通过分析SQL语句的执行计划优化SQL(总结)
通过分析SQL语句的执行计划优化SQL(总结)
做DBA快7年了，中间感悟很多。在DBA的日常工作中，调整个别性能较差的SQL语句时一项富有挑战性的工
作。其中的关键在于如何得到SQL语句的执行计划和如何从SQL语句的执行计划中发现问题。总是想将日常
经验的点点滴滴总结一下，但是直到最近才下定决心，总共花了3个周末时间，才将其整理成册，便于自
己日常工作。不好意思独享，所以将其贴出来。
修改日志：
2006.02.20：
根据网友反馈已做部分修改，但pdf文件没有做修改，修改部分在“如何产生执行计划”关于set
autotraceonly的介绍部分
第一章、第2章 并不是很重要，是自己的一些想法，关于如何做一个稳定、高效的应用系统的一些想法。
第三章以后都是比较重要的。
附录的内容也是比较重要的。我常用该部分的内容。
前言
本文档主要介绍与SQL调整有关的内容，内容涉及多个方面：SQL语句执行的过程、ORACLE优化器，表之间 的关联，如何得到SQL执行计划，如何分析执行计划等内容，从而由浅到深的方式了解SQL优化的过程，使
大家逐步步入SQL调整之门，然后你将发现„„。
该文档的不当之处，敬请指出，以便进一步改正。请将其发往我的信箱：xu_yu_jin2000@sina.com。
如果引用本文的内容，请著名出处
目录
第1章 性能调整综述 第2章 有效的应用设计
第3章 SQL语句处理的过程 第4章 ORACLE的优化器 第5章 ORACLE的执行计划 访问路径(方法)--access path 表之间的连接
如何产生执行计划 如何分析执行计划
如何干预执行计划-合并连接(Sort Merge Join(SMJ))嵌套循环(Nested Loops(NL))哈希连接(Hash Join)
排序-合并连接(Sort Merge Join, SMJ)：
a)对于非等值连接，这种连接方式的效率是比较高的。b)如果在关联的列上都有索引，效果更好。c)对于将2个较大的row source做连接，该连接方法比NL连接要好一些。d)但是如果sort merge返回的row source过大，则又会导致使用过多的rowid在表中查询数据时，数据库
性能下降，因为过多的I/O。
嵌套循环(Nested Loops, NL)：
a)如果driving row source(外部表)比较小，并且在inner row source(内部表)上 有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。b)NESTED LOOPS有其它连接方法没有的的一个优点是：可以先返回已经 连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。哈希连接(Hash Join, HJ)：
a)这种方法是在oracle7后来引入的，使用了比较先进的连接理论，一般来说，其效率应该好于其它2种连接，但是这种连接只能用在 CBO优化器中，而且需要设置合适的hash_area_size参数，才能取得较好的性能。
b)在2个较大的row source之间连接时会取得相对较好的效率，在一个 row source较小时则能取得更好的效率。c)只能用于等值连接中
笛卡儿乘积(Cartesian Product)当两个row source做连接，但是它们之间没有关联条件时，就会在两个row source中做笛卡儿乘积，这通
常由编写代码疏漏造成(即程序员忘了写关联条件)。笛卡尔乘积是一个表的每一行依次与另一个表中的所
有行匹配。在特殊情况下我们可以使用笛卡儿乘积，如在星形连接中，除此之外，我们要尽量使用笛卡儿
乘积，否则，自己想结果是什么吧！
注意在下面的语句中，在2个表之间没有连接。SQL> explain plan for select emp.deptno,dept,deptno from emp,dept
Query Plan------------------------------SLECT STATEMENT [CHOOSE] Cost=5 MERGE JOIN CARTESIAN TABLE ACCESS FULL DEPT SORT JOIN TABLE ACCESS FULL EMP
CARTESIAN关键字指出了在2个表之间做笛卡尔乘积。假如表emp有n行，dept表有m行，笛卡尔乘积的结果
就是得到n * m行结果。
7楼
06-01-12 17:48 [ 大 中 小 ]
SunnyXu 一般会员
注册日期: 2004 Nov 来自:
技术贴数:38 精华贴数:1 论坛积分:267 论坛排名:9743 论坛徽章:0
[center]如何产生执行计划[/center]
要为一个语句生成执行计划，可以有3种方法： 1)．最简单的办法
Sql> set autotrace on Sql> select * from dual;执行完语句后，会显示explain plan 与 统计信息。这个语句的优点就是它的缺点，这样在用该方法查看执行时间较长的sql语句时，需要等待该语句执行成
功后，才返回执行计划，使优化的周期大大增长。
如果想得到执行计划，而不想看到语句产生的数据，可以采用： Sql> set autotrace traceonly 这样还是会执行语句。它比set autotrace on的优点是：不会显示出查询的数据，但是还是会将数据输出
到客户端，这样当语句查询的数据比较多时，语句执行将会花费大量的时间，因为很大部分时间用在将数
据从数据库传到客户端上了。我一般不用这种方法。
Sql> set autotrace traceonly explain 如同用explain plan命令。对于select 语句，不会执行select语句，而只是产生执行计划。但是对于dml
语句，还是会执行语句，不同版本的数据库可能会有小的差别。这样在优化执行时间较长的select语句时，大大减少了优化时间，解决了“set autotrace on”与“set autotrace traceonly”命令优化时执行
时间长的问题，但同时带来的问题是：不会产生Statistics数据，而通过tatistics数据的物理I/O的次数，我们可以简单的判断语句执行效率的优劣。
如果执行该语句时遇到错误，解决方法为：(1）在要分析的用户下：
Sqlplus > @ ?rdbmsadminutlxplan.sql(2)用sys用户登陆
Sqlplus > @ ?sqlplusadminplustrce.sql Sqlplus > grant plustrace to user_name;-> A)--> C。如果数据库是基于代价的优化器，它会利用计
算出的代价来决定合适的驱动表与合适的连接顺序。一般来说，CBO都会选择正确的连接顺序，如果CBO选
择了比较差的连接顺序，我们还可以使用ORACLE提供的hints来让CBO采用正确的连接顺序。如下所示：
select /*+ ordered */ A.col4 from B,A,C where B.col3 = 10 and A.col1 = B.col1 and A.col2 = C.col2 and C.col3 = 5
既然选择正确的驱动表这么重要，那么让我们来看一下执行计划，到底各个表之间是如何关联的，从而得
到执行计划中哪个表应该为驱动表：
在执行计划中，需要知道哪个操作是先执行的，哪个操作是后执行的，这对于判断哪个表为驱动表有用处
。判断之前，如果对表的访问是通过rowid，且该rowid的值是从索引扫描中得来得，则将该索引扫描先从
执行计划中暂时去掉。然后在执行计划剩下的部分中，判断执行顺序的指导原则就是：最右、最上的操作
先执行。具体解释如下：
得到去除妨碍判断的索引扫描后的执行计划： Execution Plan---------------------------0 SELECT STATEMENT Optimizer=CHOOSE 1 0 MERGE JOIN 2 1 SORT(JOIN)3 2 NESTED LOOPS 4 3 TABLE ACCESS(FULL)OF 'B' 5 3 TABLE ACCESS(BY INDEX ROWID)OF 'A' 7 1 SORT(JOIN)8 7 TABLE ACCESS(FULL)OF 'C' 看执行计划的第3列，即字母部分，每列值的左面有空格作为缩进字符。在该列值左边的空格越多，说明
该列值的缩进越多，该列值也越靠右。如上面的执行计划所示：第一列值为6的行的缩进最多，即该行最
靠右；第一列值为4、5的行的缩进一样，其靠右的程度也一样，但是第一列值为4的行比第一列值为5的行
靠上；谈论上下关系时，只对连续的、缩进一致的行有效。
从这个图中我们可以看到，对于NESTED LOOPS部分，最右、最上的操作是TABLE ACCESS(FULL)OF 'B'，所以这一操作先执行，所以该操作对应的B表为第一个驱动表(外部表)，自然，A表就为内部表了。从图中
还可以看出，B与A表做嵌套循环后生成了新的row source，对该row source进行来排序后，与C表对应的
排序了的row source(应用了C.col3 = 5限制条件)进行MSJ连接操作。所以从上面可以得出如下事实：B表
先与A表做嵌套循环，然后将生成的row source与C表做排序—合并连接。
通过分析上面的执行计划，我们不能说C表一定在B、A表之后才被读取，事实上，B表有可能与C表同时被
读入内存，因为将表中的数据读入内存的操作可能为并行的。事实上许多操作可能为交叉进行的，因为
第三篇：网站SEO优化每天执行计划
个人执行计划
网站排名对于SEO人员来说重中之重，是SEO行业都为之关注的话题。对于企业站来说排名无非也就是所谓的外链与内容。执行力更是SEO中占据重要的步骤，有好的策略没有执行力也是空谈。今天在这里和大家分享个人在优化网站的执行计划，如果适合各位所优化网站，那么的就非常的高兴。反之，希望各位不要取笑。
一、网站内容：
每网站一篇原创文章。将今日写的文章依次收藏到社会化书签中，一是可以加快文章的收录，二是外链的一种。这里推荐大家几个社会化书签：百度收藏、好网角收藏夹、宝盒网、乐收网络收藏夹。
二、友情链接
每天坚持友链2-3个，最好是相关性的。友情链接的交换，看的不是收录多少，pr值多少。最关键是网站的目标关键词是否在首页，网站所带来的流量是多少。友情链接重点不是量，质才是我们追求的。
三、行业商铺：
行业商铺的权重很高，大家可以多搜集一些商铺，可以找一些行业性的商铺。商铺下我们基本可能做以下形式的链接：商铺友情链接、供求信息、求购信息。这里推荐大家一些好的商铺：行业中心、环球厨卫网、百业网、一呼百应等等。
四、论坛：
论坛是我们常见做的链接形式之一，推荐大家做论坛方法：行业相关论坛、权重较高的门户网站论坛广告发布区。推荐：搜房论坛、慧聪论坛、落伍者论坛、19楼等等。
五、分类信息与网站目录:
对于新上线站来说分类信息与网站目录是个不错的选择，可以让百度蜘蛛快速度收录网站，但有一些目录网站对于审核的要求过高，新站初期可以提交一些审核不严格的，后期提交那些权重高的目录站。我们常见的分类信息站有：58同城、赶集网、百姓网。权重高目录网站：启发网站目录、第一摘网站分类目录、中国开放网页目录.以上的执行方式比较适合企业网站的优化流程，当然了每个人执行方式都不一样。这里只是说说自己的看法，希望能帮助各位站长。也希望你们能分享自己的一些经验，加大一起相互学习。
/
第四篇：SQL语句性能优化
我也做了很长时间医疗软件，也写过不少sql优化，没有详细记录下来，个人感觉下面转载的更符合医院医疗软件实际业务，很认可大部分所写的原则，固转载过来，以作借鉴。软件的根本还是在于更细更精，在于从客户的实际使用考虑问题。
性能优化原则1:永远避免困境
利用缓存把字典数据取到中间服务器或是客户端替代直接sql查询,如,门诊医生站把字典下载到客户端,减少执行次数。
一次性取数据到客户端，然后再逐条处理，而不是分次取数据，处理好一条数据再取下一条再处理。例：门诊收费取hjcfmxk例子，原来是一张处方条明细都查询一次，查询后再处理，现改为一次把所有明细都取过来，然后一条条处理
尽量减少光标，看能不能用临时表
性能优化原则2:kiss原则
对于where 条件中的左边可以利用索引的字段Keep it simple stupid，左边尽量避免用函数(substring,isnull,upper,lower)，参加计算+,-*/
例子1：select * from ZY_BRFYMXK where substring(zxrq,1,8)='20081212‘
select * from ZY_BRFYMXK where zxrq between '2008121200' and '2008121224' 例子2：
select * from zy_detail_charge where SUBSTRING(patient_id,1,10)=
substring('000005090600',1,10)这句耗时30秒以上
select * from zy_detail_charge where patient_id like substring('000005090600',1,10)+'%' 这句耗时2秒以内
性能优化原则3:尽可能利用到索引
例：select * from ZY_BRFYMXK a(nolock),VW_LSYZK b(nolock)where a.syxh=3 and a.yzxh=b.xh and a.fylb=0
select * from ZY_BRFYMXK a(nolock),VW_LSYZK b(nolock)where a.syxh=3 and a.yzxh=b.xh and a.fylb=0 and b.syxh=3
性能优化原则4:or,避而远之
对于索引字段尽力避免用or，普通字段可以用or，解决要么分解成多个sql,要么用业务规则避免，例：declare @rq1 ut_rq16,@syxh ut_syxh
select @rq1='20081201'
select @syxh=157
性能优化原则5:避免大批量数据取到前台
例： select * from ZY_BRSYK cyrq between ‘20080901’ and ‘20081201‘,对于大医院每天100多人，90天是9000条数据
性能优化原则6:事务,尽可能的短吧
所有计算、对临时表的更新都应但放在事务外，事务中最好只有更新和插入正式表操作.因为事务中产生的锁只有在commit tran是才会释放。
性能优化原则7:热表，留在最后吧
热表是频繁调用的表。如：sf_mzcfk,zy_brfymxk,bq_fyqqk.对于热表尽量放在事务最后：这样锁的时间短。大家都坚持这样，死锁的可能性就小。如果都是热表各个存储过程更新表的顺序应当一样这样可以避免死锁
性能优化原则8:创建临时表一定要避免在事务中作
如create #tempXX(…)
Select * into #tempXX from …
因为创建临时表会锁tempdb的系统表
例：生成#temp1放在事务内外，用sp_lock2 ‘’观察结果
if object_id('tempdb..#temp1','U')is not null
drop table #temp1
begin tran
select * into #temp1 from ZY_BRSYK where ryrq>'20080901‘
select * from #temp1
waitfor delay '00:00:10'
commit
性能优化原则9:大的报表查询避免与正常业务碰撞
如果没有查询服务器，那要在存储过程中限制不能操作加上如：
declare @rq1 ut_rq16,@rq2 ut_rq16,@now ut_rq16
select @rq1=convert(varchar(8),getdate(),112)+'08:00:00'
select @rq1=convert(varchar(8),getdate(),112)+'11:30:00'
select @now=convert(char(8),getdate(),112)+convert(char(8),getdate(),8)
if @now>@rq1 and @now<@rq2
begin
select '上午繁忙时间段不能作此查询'
return
end
性能优化原则10:存储过程避免大的if…else…
这个常出项在业务相同表不同的存储过程中，因为这样常到if …else …原来医技接口中很多这种存储过程，当时把门诊住院业务放在一个存储过程中。这样最大的问题是sql server会根据sql语句来compile存储，这个过程会生成优化计划，决定用那个索引。如果存储过程用到门诊表compile一下，到用到住院表是发现不对，又会compile一下，这样不停compile.compile很号时间要1-2秒，而且一个存储过成在compile是，所有调用这个存储过程的进程都要在排队等候,因为他会独占锁这个存储过程
例：usp_yjjk_getwzxxm_old.sql,后改为：
usp_yjjk_getwzxxm.sql, usp_yjjk_getwzxxm_mz.sql,usp_yjjk_getwzxxm_zy.sql
性能优化原则11:进攻是最好的防守
在普通编程语句对于数据校验总是用防守办法先判断,后再作相应处理。而在sql中先处理再判断性能会好很多。
--更新药品库存。
If exists(select 1 from YK_YKZKC WHERE idm=100 and kcsl>50)
begin
update YK_YKZKC set kcsl=kcsl-50 where idm=100
End
Else begin
rollback tran
Select ‘F库存不够’
return
end
--改为
update YK_YKZKC set kcsl=kcsl-50 where idm=100 and kcsl>50
If @@rowcount<=0
Begin
Rollbakc tran
Select ‘F库存不够’
end
--取未执行的医技项目,日表没有数据就到年表中查找
if exists(select a.* from SF_MZCFK a(nolock),SF_CFMXK b(nolock)
begin
select a.* into #temp1 from SF_MZCFK a(nolock),SF_CFMXK b(nolock)
end
else begin
select a.* into #temp1 from SF_NMZCFK a(nolock),SF_NCFMXK b(nolock)
end
--改为
Insert into #temp1 select a.*
from SF_MZCFK a(nolock),SF_CFMXK b(nolock)
If @@rowcount=0
Begin
Insert into #temp1 select a.*
from SF_NMZCFK a(nolock),SF_NCFMXK b(nolock)
end
性能优化原则12:trig最后的手段
Trig（触发器）的处理的处理机制是满足条件时就会在源语句后面加上trig中的代码进行执行。
它有两个致命的弊端：（1）不清楚有trig的人会对于执行结果感到迷惑。如常有在插入一张表如果主键是indentity的值常取用select @@identity。但如是有trig,tring中有表插入操作，这时的@@identity可能就不是想要的值。（2）trig会束缚选择。如：有一套单据主表和明细表，当明细表的金额更新时，要同步主表的金额，当程序是一条条更新明细时用trig的作法是每当更新一条明细记录时都算一处所有明细表的总金额，再去更新主表的金额。这样有多少条明细就要算多少次，好的作法是不要trig,直接在sql语句中明细更新完明后，一次性算出总金额每条单据的总金额，再更新主表的金额。
对于trig如果有其他手段就一定要避免用trig.性能优化原则13:用户说好才是真的好
1）有时sql语句性能难以优化，但用户对于系统响应速度还是不满意。这时可以从业务分析处理。
如：我们退费模块录入发票号原来是用fph like ‘XXX%’。用户报怨慢，后来改为先用fph=‘XXX’来查，如查不到再fph like ‘XXX%’。这样在绝大部情况下速度都非常快，同时也满足小部分情况下模糊查询的需求。
如：我们的程序要查日表和年表。如果通过日表union表视图去查会非常慢，性能也难以优化。程序改为普通情况下不查年表，用户勾上年表标志时才查年表。
（2）查询统计很多数据时间比较长，就以查询完一部分数据后可以显示这部分数据或是用提示，这样用户清楚系统在作事情也知道大概进度。这样情绪上会好很多。
（3）查询模块常有一进入时也默认一个查询，如果性能好，查询又合用户心意，这种设计非常好，如果性能不好，那就不是好的设计。用户对于进入都困难的模块是没有好感的。
（4）有户的耐心与查询出的记录成正比。用户痛恨等待很久却没有查询出记录。
对于非常慢的查询，如果有些子查询非常快可以先作这样查询以避免查询很久却没有数据出来的情况。如：按病历号查在院病人所有费有明细，可以先查一下这个病历是不是有对应病人。
实战技巧1:用exists、in代替distinct
Distinct实际上是先收集再删除这样两步都耗资源。
Exists,in会隐式过滤掉重复的记录
例查自2009年以来有金额大于100的药品的病人
select distinct a.blh,a.hzxm from ZY_BRXXK a(nolock),ZY_BRSYK b(nolock),ZY_BRFYMXK c(nolock)where a.patid=b.patid and b.syxh=c.syxh and c.zxrq>'2009' and c.zje>100--改为
select a.blh,a.hzxm from ZY_BRXXK a where exists(select 1 from ZY_BRSYK
b(nolock),ZY_BRFYMXK c(nolock)where a.patid=b.patid and b.syxh=c.syxh and
c.zxrq>'2009'and c.zje>100)
实战技巧2:缩短union
select …from A,B,C,D,E1
where(E1的条件)
and(其他表联接条件)
union
select …from A,B,C,D,E2
where(E2的条件)
and(其他表接接条件)
改为
select …from A,B,C,D,(select...from E1where(E1条件)
union
select …from E2where(E2条件))E where(其他条件)
当涉及ABCD表部分耗资源而E1,E2不耗资源时是这样，如果反过来则改后的性能不一定好。查2009年4月后入院的在院病人在2905病区发生的所有费用明细
select a.hzxm,b.cyrq,d.ypmc,d.ypgg,c.ypsl/c.dwxs ypsl, c.ypdw
select a.hzxm,b.cyrq,d.ypmc,d.ypgg,c.ypsl/c.dwxs ypsl, c.ypdw
from ZY_BRXXK a(nolock),ZY_BRSYK b(nolock),ZY_BRFYMXK c(nolock),YK_YPCDMLK d where a.patid=b.patid and b.ryrq>'200904' and b.brzt not in(3,8,9)and b.syxh=c.syxh and c.bqdm='2905' and c.idm=d.idm
union all
select a.hzxm,b.cyrq,d.name,d.xmgg,c.ypsl/c.dwxs ypsl, c.ypdw
from ZY_BRXXK a(nolock),ZY_BRSYK b(nolock),ZY_BRFYMXK c(nolock),YY_SFXXMK d where a.patid=b.patid and b.ryrq>'200904' and b.brzt not in(3,8,9)and b.syxh=c.syxh and c.bqdm='2905' and c.ypdm=d.id and c.idm=0
--改为
select a.hzxm,b.cyrq,d.ypmc,d.ypgg,c.ypsl/c.dwxs ypsl, c.ypdw
from ZY_BRXXK a(nolock),ZY_BRSYK b(nolock),ZY_BRFYMXK c(nolock),(select ypmc,ypgg,ypdm,idm idm from YK_YPCDMLK union select name,xmgg,id,0 from YY_SFXXMK)d
where a.patid=b.patid and b.ryrq>'200904' and b.brzt not in(3,8,9)and b.syxh=c.syxh and c.bqdm='2905' and c.idm=d.idm and c.ypdm=d.ypdm
实战技巧3:合并sql
把表和where条件类似的两个或是多个sql合并为一个sql.--查2009年以后的普通、急诊、专家挂号人数
declare @ptghs int,@jzghs int,@zjghs int
select @ptghs=0,@jzghs=0,@zjghs=0
select @ptghs=count(*)from GH_GHZDK where ghrq>'2009' and ghlb=0
select @jzghs=count(*)from GH_GHZDK where ghrq>'2009' and ghlb=1
select @zjghs=count(*)from GH_GHZDK where ghrq>'2009' and ghlb=2
select @ptghs,@jzghs,@zjghs
--改为
select @ptghs=0,@jzghs=0,@zjghs=0
select @ptghs=sum(case when ghlb=0 then 1 else 0 end),@jzghs=sum(case when ghlb=1 then 1 else 0 end), @zjghs=sum(case when ghlb=2 then 1 else 0 end)
from GH_GHZDK where ghrq>'2009'
select @ptghs,@jzghs,@zjghs
实战技巧4:去掉游标
把游标当作编程语言的for,do---while的方式，很多情况下都可以去掉,如果光标中间sql语句只有一条一般都是可以去掉光标改为一句sql。
--查当天出院出院日期在2009年4月1到9日间病人的zfdj,zfje置为0
declare @syxh ut_syxh
declare cur1 cursor for select syxh from ZY_BRSYK where cyrq>='20090401' and cyrq<'20090410'
open cur1
fetch cur1 into @syxh
while @@fetch_status=0
begin
fetch cur1 into @syxh
end
close cur1
deallocate cur1
--改为
update ZY_BRFYMXK set zfdj=0,zfje=0
from ZY_BRFYMXK a,ZY_BRSYK b
where a.syxh=b.syxh and b.cyrq>='20090401' and b.cyrq<'20090410'
实战技巧5:取代count
利用内部函数代替
declare @count int
select * into #tmep1 from ZY_BRFYMXK WHERE zxrq>'200901'
select @count=@@rowcount—可以得到count值
select @count
select @count=count(*)from #tmep1—可以被取代
select @count
利用exists而不count判断有没有记录
declare @count int
Select @count=count(1)from ZY_BRFYMXK WHERE zxrq>'2009‘
If @count>0 … else ….--改为
If exists(Select 1 from ZY_BRFYMXK WHERE zxrq>'2009’)… else ….
第五篇：SQL语句的优化方法
SQL语句的优化方法
1.1注释使用
在语句中多写注释，注释不影响SQL语句的执行效率。增加代码的可读性。
1.2对于事务的使用
尽量使事务处理达到最短，如果事务太长最好按功能将事务分开执行(如：可以让用户在界面上多几步操作)。事务太长很容易造成数据库阻塞，用户操作速度变慢或死机情况。
1.3对于与数据库的交互
尽量减少与数据库的交互次数。如果在前端程序写有循球访问数据库操作，最好写成将数据一次读到前端再进行处理或者写成存储过程在数据库端直接处理。
1.4对于SELECT *这样的语句，不要使用SELECT *这样的语句，而应该使用SELECT table1.column1这样的语句，明确指出要查询的列减少数据的通讯量并且这样的代码可读性好，便于维护。
1.5尽量避免使用游标
它占用大量的资源。如果需要row-by-row地执行，尽量采用非光标技术,如：在客户端循环，用临时表，Table变量，用子查询，用Case语句等等。如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。
1.6尽量使用count(1)
count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率。
1.7IN和EXISTS
EXISTS要远比IN的效率高。里面关系到full table scan和range scan。几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。
1.8注意表之间连接的数据类型
避免不同类型数据之间的连接。
1.9尽量少用视图
对视图操作比直接对表操作慢,可以用stored procedure来代替她。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。我们看视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时，不要使用指向多个表的视图，直接从表检索或者仅仅包含这个表的视图上读，否则增加了不必要的开销,查询受到干扰。
1.10没有必要时不要用DISTINCT和ORDER BY
这些动作可以改在客户端执行，它们增加了额外的开销。
1.11避免相关子查询
一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。
1.1注意UNion和`UNion all 的区别
UNION all执行效率高。
1.1外键关联的列应该建立索引
(如子表id)主子表单据肯定要建视图，2个表的关联以2个表中的MainID为关系，所以，需要给子表的MainID单独建索引，这将很大地提高视图的速度。例如Gy_InOutSub中的InoutMainid增加索引。
点击咨询 