第一篇：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.tmdps.cn;
這樣在分析時導致查詢出的數據過多，不方便，所以用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增加索引。
點擊咨詢 