盡量避免使用游標，因為游標的效率較差，如果游標操作的數(shù)據(jù)超過1萬行，那么就應該考慮改寫。
站在用戶的角度思考問題，與客戶深入溝通，找到淇濱網(wǎng)站設計與淇濱網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗，讓設計與互聯(lián)網(wǎng)技術結(jié)合，創(chuàng)造個性化、用戶體驗好的作品，建站類型包括：網(wǎng)站設計制作、成都網(wǎng)站建設、企業(yè)官網(wǎng)、英文網(wǎng)站、手機端網(wǎng)站、網(wǎng)站推廣、域名注冊、網(wǎng)站空間、企業(yè)郵箱。業(yè)務覆蓋淇濱地區(qū)。
使用基于游標的方法之前，應先尋找基于集的解決方案來解決問題，基于集的方法通常更有效。
最好的改進光標性能的技術就是：能避免時就避免使用游標
若有時無法避免使用游標，則可以用如下技巧來優(yōu)化游標的性能。
(1). 除非必要否則不要使用static/insensitive游標。打開static游標會造成所有的行都被拷貝到臨時表。這正是為什么它對變化不敏感的原因——它實際上是指向臨時數(shù)據(jù)庫表中的一個備份。很自然，結(jié)果集越大，聲明其上的static游標就會引起越多的臨時數(shù)據(jù)庫的資源爭奪問題。
(2). 除非必要否則不要使用keyset游標。和static游標一樣，打開keyset游標會創(chuàng)建臨時表。雖然這個表只包括基本表的一個關鍵字列(除非不存在唯一關鍵字)，但是當處理大結(jié)果集時還是會相當大的。
(3). 當處理單向的只讀結(jié)果集時，使用fast_forward代替forward_only。使用fast_forward定義一個forward_only，則read_only游標具有一定的內(nèi)部性能優(yōu)化。
(4). 使用read_only關鍵字定義只讀游標。這樣可以防止意外的修改，并且讓服務器了解游標移動時不會修改行。
(5). 小心事務處理中通過游標進行的大量行修改。根據(jù)事務隔離級別，這些行在事務完成或回滾前會保持鎖定，這可能造成服務器上的資源爭奪。
(6). 小心動態(tài)光標的修改，尤其是建在非唯一聚集索引鍵的表上的游標，因為他們會造成“Halloween”問題——對同一行或同一行的重復的錯誤的修改。因為SQL Server在內(nèi)部會把某行的關鍵字修改成一個已經(jīng)存在的值，并強迫服務器追加下標，使它以后可以再結(jié)果集中移動。當從結(jié)果集的剩余項中存取時，又會遇到那一行，然后程序會重復，結(jié)果造成死循環(huán)。
(7). 對于大結(jié)果集要考慮使用異步游標，盡可能地把控制權交給調(diào)用者。當返回相當大的結(jié)果集到可移動的表格時，異步游標特別有用，因為它們允許應用程序幾乎馬上就可以顯示行
一、建立索引
二、建立存儲過程
三、只查詢您所需要的數(shù)據(jù)，不要把所有數(shù)據(jù)都查詢出來，防止數(shù)據(jù)冗余。
四、對于大量及海量數(shù)據(jù)一般還要建立分區(qū)
首先應該確定是誰慢的，往往是程序處理方面的問題而不是數(shù)據(jù)庫的問題。
程序方面應該盡可能的減少數(shù)據(jù)查詢返回的內(nèi)容，減少IO壓力，磁盤IO和網(wǎng)絡IO是非常非常慢的。比如可以查詢返回ID，然后再根據(jù)ID一條一條的查詢具體內(nèi)容，看似慢了，在數(shù)據(jù)量大的時候快很多
對于數(shù)據(jù)可以參照下面幾點
1、優(yōu)化SQL語句，SQL語句對查詢速度影響最大的
2、對于經(jīng)常查詢的字段作索引。但是這樣會增加修改時的壓力
4、優(yōu)化SQLServer，比如給其分配固定的內(nèi)存，預先分配查詢內(nèi)存，調(diào)整CPU使用率等。SQL Server 可以占用幾乎所有Windows的內(nèi)存，但是申請內(nèi)存開銷很大。因此可以設定其使用固定大小內(nèi)存，比如啟動就分配1G以上內(nèi)存。
5、優(yōu)化硬件資源，比如使用更高的服務器或者硬盤，獨立安排數(shù)據(jù)庫的數(shù)據(jù)文件和索引文件，將數(shù)據(jù)文件分布于不同的物理硬盤上等等
6、考慮使用分布數(shù)據(jù)庫或者對大表進行拆分
我一不太會優(yōu)化，提供你一些優(yōu)化的方法吧
操作符優(yōu)化
in 操作符
用in寫出來的sql的優(yōu)點是比較容易寫及清晰易懂，這比較適合現(xiàn)代軟件開發(fā)的風格。
但是用in的sql性能總是比較低的，從oracle執(zhí)行的步驟來分析用in的sql與不用in的sql有以下區(qū)別：
oracle試圖將其轉(zhuǎn)換成多個表的連接，如果轉(zhuǎn)換不成功則先執(zhí)行in里面的子查詢，再查詢外層的表記錄，如果轉(zhuǎn)換成功則直接采用多個表的連接方式查詢。由此可見用in的sql至少多了一個轉(zhuǎn)換的過程。一般的sql都可以轉(zhuǎn)換成功，但對于含有分組統(tǒng)計等方面的sql就不能轉(zhuǎn)換了。
推薦方案：在業(yè)務密集的sql當中盡量不采用in操作符。
not in操作符
此操作是強列推薦不使用的，因為它不能應用表的索引。
推薦方案：用not exists 或（外連接+判斷為空）方案代替
操作符（不等于）
不等于操作符是永遠不會用到索引的，因此對它的處理只會產(chǎn)生全表掃描。
推薦方案：用其它相同功能的操作運算代替，如
a0 改為 a0 or a0
a’’ 改為 a’’
is null 或is not null操作（判斷字段是否為空）
判斷字段是否為空一般是不會應用索引的，因為b樹索引是不索引空值的。
推薦方案：用其它相同功能的操作運算代替，如
a is not null 改為 a0 或a’’等。
不允許字段為空，而用一個缺省值代替空值，如業(yè)擴申請中狀態(tài)字段不允許為空，缺省為申請。
建立位圖索引（有分區(qū)的表不能建，位圖索引比較難控制，如字段值太多索引會使性能下降，多人更新操作會增加數(shù)據(jù)塊鎖的現(xiàn)象）
及 操作符（大于或小于操作符）
大于或小于操作符一般情況下是不用調(diào)整的，因為它有索引就會采用索引查找，但有的情況下可以對它進行優(yōu)化，如一個表有100萬記錄，一個數(shù)值型字段a，30萬記錄的a=0，30萬記錄的a=1，39萬記錄的a=2，1萬記錄的a=3。那么執(zhí)行a2與a=3的效果就有很大的區(qū)別了，因為a2時oracle會先找出為2的記錄索引再進行比較，而a=3時oracle則直接找到=3的記錄索引。
like操作符
like操作符可以應用通配符查詢，里面的通配符組合可能達到幾乎是任意的查詢，但是如果用得不好則會產(chǎn)生性能上的問題，如like ‘%5400%’ 這種查詢不會引用索引，而like ‘x5400%’則會引用范圍索引。一個實際例子：用yw_yhjbqk表中營業(yè)編號后面的戶標識號可來查詢營業(yè)編號 yy_bh like ‘%5400%’ 這個條件會產(chǎn)生全表掃描，如果改成yy_bh like ’x5400%’ or yy_bh like ’b5400%’ 則會利用yy_bh的索引進行兩個范圍的查詢，性能肯定大大提高。
union操作符
union在進行表鏈接后會篩選掉重復的記錄，所以在表鏈接后會對所產(chǎn)生的結(jié)果集進行排序運算，刪除重復的記錄再返回結(jié)果。實際大部分應用中是不會產(chǎn)生重復的記錄，最常見的是過程表與歷史表union。如：
select * from gc_dfys
union
select * from ls_jg_dfys
這個sql在運行時先取出兩個表的結(jié)果，再用排序空間進行排序刪除重復的記錄，最后返回結(jié)果集，如果表數(shù)據(jù)量大的話可能會導致用磁盤進行排序。
推薦方案：采用union all操作符替代union，因為union all操作只是簡單的將兩個結(jié)果合并后就返回。
select * from gc_dfys
union all
select * from ls_jg_dfys
sql語句索引的利用
對條件字段的一些優(yōu)化
采用函數(shù)處理的字段不能利用索引，如：
substr(hbs_bh,1,4)=’5400’，優(yōu)化處理：hbs_bh like ‘5400%’
trunc(sk_rq)=trunc(sysdate)， 優(yōu)化處理：
sk_rq=trunc(sysdate) and sk_rq
進行了顯式或隱式的運算的字段不能進行索引，如：
ss_df+2050，優(yōu)化處理：ss_df30
‘x’||hbs_bh’x5400021452’，優(yōu)化處理：hbs_bh’5400021542’
sk_rq+5=sysdate，優(yōu)化處理：sk_rq=sysdate-5
hbs_bh=5401002554，優(yōu)化處理：hbs_bh=’ 5401002554’，注：此條件對hbs_bh 進行隱式的to_number轉(zhuǎn)換，因為hbs_bh字段是字符型。
條件內(nèi)包括了多個本表的字段運算時不能進行索引，如：
ys_dfcx_df，無法進行優(yōu)化
qc_bh||kh_bh=’5400250000’，優(yōu)化處理：qc_bh=’5400’ and kh_bh=’250000’
應用oracle的hint（提示）處理
提示處理是在oracle產(chǎn)生的sql分析執(zhí)行路徑不滿意的情況下要用到的。它可以對sql進行以下方面的提示
目標方面的提示：
cost（按成本優(yōu)化）
rule（按規(guī)則優(yōu)化）
choose（缺?。╫racle自動選擇成本或規(guī)則進行優(yōu)化）
all_rows（所有的行盡快返回）
first_rows（第一行數(shù)據(jù)盡快返回）
執(zhí)行方法的提示：
use_nl（使用nested loops方式聯(lián)合）
use_merge（使用merge join方式聯(lián)合）
use_hash（使用hash join方式聯(lián)合）
索引提示：
index（table index）（使用提示的表索引進行查詢）
其它高級提示（如并行處理等等）
oracle的提示功能是比較強的功能，也是比較復雜的應用，并且提示只是給oracle執(zhí)行的一個建議，有時如果出于成本方面的考慮oracle也可能不會按提示進行。根據(jù)實踐應用，一般不建議開發(fā)人員應用oracle提示，因為各個數(shù)據(jù)庫及服務器性能情況不一樣，很可能一個地方性能提升了，但另一個地方卻下降了，oracle在sql執(zhí)行分析方面已經(jīng)比較成熟，如果分析執(zhí)行的路徑不對首先應在數(shù)據(jù)庫結(jié)構(gòu)（主要是索引）、服務器當前性能（共享內(nèi)存、磁盤文件碎片）、數(shù)據(jù)庫對象（表、索引）統(tǒng)計信息是否正確這幾方面分析。
在良好的數(shù)據(jù)庫設計基礎上，能有效地使用索引是SQL Server取得高性能的基礎，SQL Server采用基于代價的優(yōu)化模型，它對每一個提交的有關表的查詢，決定是否使用索引或用哪一個索引。因為查詢執(zhí)行的大部分開銷是磁盤I/O，使用索引提高性能的一個主要目標是避免全表掃描，因為全表掃描需要從磁盤上讀表的每一個數(shù)據(jù)頁，如果有索引指向數(shù)據(jù)值，則查詢只需讀幾次磁盤就可以了。
所以如果建立了合理的索引，優(yōu)化器就能利用索引加速數(shù)據(jù)的查詢過程。但是，索引并不總是提高系統(tǒng)的性能，在增、刪、改操作中索引的存在會增加一定的工作量，因此，在適當?shù)牡胤皆黾舆m當?shù)乃饕牟缓侠淼牡胤絼h除次優(yōu)的索引，將有助于優(yōu)化那些性能較差的SQL Server應用。實踐表明，合理的索引設計是建立在對各種查詢的分析和預測上的，只有正確地使索引與程序結(jié)合起來,才能產(chǎn)生最佳的優(yōu)化方案。本文就SQL Server索引的性能問題進行了一些分析和實踐。
一、聚簇索引(clustered indexes)的使用
聚簇索引是一種對磁盤上實際數(shù)據(jù)重新組織以按指定的一個或多個列的值排序。由于聚簇索引的索引頁面指針指向數(shù)據(jù)頁面，所以使用聚簇索引查找數(shù)據(jù)幾乎總是比使用非聚簇索引快。每張表只能建一個聚簇索引，并且建聚簇索引需要至少相當該表120%的附加空間，以存放該表的副本和索引中間頁。建立聚簇索引的思想是：
1、大多數(shù)表都應該有聚簇索引或使用分區(qū)來降低對表尾頁的競爭，在一個高事務的環(huán)境中，對最后一頁的封鎖嚴重影響系統(tǒng)的吞吐量。
2、在聚簇索引下，數(shù)據(jù)在物理上按順序排在數(shù)據(jù)頁上，重復值也排在一起，因而在那些包含范圍檢查(between、、=、、=)或使用group by或order by的查詢時，一旦找到具有范圍中第一個鍵值的行，具有后續(xù)索引值的行保證物理上毗連在一起而不必進一步搜索，避免了大范圍掃描，可以大大提高查詢速度。
3、在一個頻繁發(fā)生插入操作的表上建立聚簇索引時，不要建在具有單調(diào)上升值的列(如IDENTITY)上，否則會經(jīng)常引起封鎖沖突。
4、在聚簇索引中不要包含經(jīng)常修改的列，因為碼值修改后，數(shù)據(jù)行必須移動到新的位置。
5、選擇聚簇索引應基于where子句和連接操作的類型。
聚簇索引的侯選列是：
1、主鍵列,該列在where子句中使用并且插入是隨機的。
2、按范圍存取的列，如pri_order 100 and pri_order 200。
3、在group by或order by中使用的列。
4、不經(jīng)常修改的列。
5、在連接操作中使用的列。
二、非聚簇索引(nonclustered indexes)的使用
SQL Server缺省情況下建立的索引是非聚簇索引，由于非聚簇索引不重新組織表中的數(shù)據(jù)，而是對每一行存儲索引列值并用一個指針指向數(shù)據(jù)所在的頁面。換句話說非聚簇索引具有在索引結(jié)構(gòu)和數(shù)據(jù)本身之間的一個額外級。一個表如果沒有聚簇索引時,可有250個非聚簇索引。每個非聚簇索引提供訪問數(shù)據(jù)的不同排序順序。在建立非聚簇索引時，要權衡索引對查詢速度的加快與降低修改速度之間的利弊。另外，還要考慮這些問題：
1、索引需要使用多少空間。
2、合適的列是否穩(wěn)定。
3、索引鍵是如何選擇的，掃描效果是否更佳。
4、是否有許多重復值。
對更新頻繁的表來說，表上的非聚簇索引比聚簇索引和根本沒有索引需要更多的額外開銷。對移到新頁的每一行而言，指向該數(shù)據(jù)的每個非聚簇索引的頁級行也必須更新，有時可能還需要索引頁的分理。從一個頁面刪除數(shù)據(jù)的進程也會有類似的開銷，另外，刪除進程還必須把數(shù)據(jù)移到頁面上部，以保證數(shù)據(jù)的連續(xù)性。所以，建立非聚簇索引要非常慎重。非聚簇索引常被用在以下情況：
1、某列常用于集合函數(shù)(如Sum,....)。
2、某列常用于join,order by,group by。
3、查尋出的數(shù)據(jù)不超過表中數(shù)據(jù)量的20%。
三、覆蓋索引(covering indexes)的使用
覆蓋索引是指那些索引項中包含查尋所需要的全部信息的非聚簇索引，這種索引之所以比較快也正是因為索引頁中包含了查尋所必須的數(shù)據(jù),不需去訪問數(shù)據(jù)頁。如果非聚簇索引中包含結(jié)果數(shù)據(jù),那么它的查詢速度將快于聚簇索引。
但是由于覆蓋索引的索引項比較多,要占用比較大的空間。而且update操作會引起索引值改變。所以如果潛在的覆蓋查詢并不常用或不太關鍵，則覆蓋索引的增加反而會降低性能。
四、索引的選擇技術
p_detail是住房公積金管理系統(tǒng)中記錄個人明細的表，有890000行，觀察在不同索引下的查詢運行效果，測試在C/S環(huán)境下進行，客戶機是IBM PII350(內(nèi)存64M),服務器是DEC Alpha1000A(內(nèi)存128M),數(shù)據(jù)庫為SYBASE11.0.3。
1、 select count(*) from p_detail where
op_date’19990101’ and op_date’
19991231’ and pri_surplus1300
2、 select count(*),sum(pri_surplus1) from p_detail
where op_date’19990101’ and
pay_month between‘199908’ and’199912’
不建任何索引查詢1 1分15秒
查詢2 1分7秒
在op_date上建非聚簇索引查詢1 57秒
查詢2 57秒
在op_date上建聚簇索引查詢1 1秒
查詢2 52秒
在pay_month、op_date、pri_surplus1上建索引查詢1 34秒
查詢2 1秒
在op_date、pay_month、pri_surplus1上建索引查詢1 1秒
查詢2 1秒
從以上查詢效果分析，索引的有無，建立方式的不同將會導致不同的查詢效果，選擇什么樣的索引基于用戶對數(shù)據(jù)的查詢條件,這些條件體現(xiàn)于where從句和join表達式中。一般來說建立索引的思路是：
(1)主鍵時常作為where子句的條件，應在表的主鍵列上建立聚簇索引，尤其當經(jīng)常用它作為連接的時候。
(2)有大量重復值且經(jīng)常有范圍查詢和排序、分組發(fā)生的列，或者非常頻繁地被訪問的列，可考慮建立聚簇索引。
(3)經(jīng)常同時存取多列，且每列都含有重復值可考慮建立復合索引來覆蓋一個或一組查詢，并把查詢引用最頻繁的列作為前導列，如果可能盡量使關鍵查詢形成覆蓋查詢。
(4)如果知道索引鍵的所有值都是唯一的，那么確保把索引定義成唯一索引。
(5)在一個經(jīng)常做插入操作的表上建索引時，使用fillfactor(填充因子)來減少頁分裂，同時提高并發(fā)度降低死鎖的發(fā)生。如果在只讀表上建索引，則可以把fillfactor置為100。
(6)在選擇索引鍵時，設法選擇那些采用小數(shù)據(jù)類型的列作為鍵以使每個索引頁能夠容納盡可能多的索引鍵和指針，通過這種方式，可使一個查詢必須遍歷的索引頁面降到最小。此外，盡可能地使用整數(shù)為鍵值，因為它能夠提供比任何數(shù)據(jù)類型都快的訪問速度。
五、索引的維護
上面講到,某些不合適的索引影響到SQL Server的性能,隨著應用系統(tǒng)的運行,數(shù)據(jù)不斷地發(fā)生變化,當數(shù)據(jù)變化達到某一個程度時將會影響到索引的使用。這時需要用戶自己來維護索引。索引的維護包括：
1、重建索引
隨著數(shù)據(jù)行的插入、刪除和數(shù)據(jù)頁的分裂，有些索引頁可能只包含幾頁數(shù)據(jù)，另外應用在執(zhí)行大塊I/O的時候，重建非聚簇索引可以降低分片，維護大塊I/O的效率。重建索引實際上是重新組織B-樹空間。在下面情況下需要重建索引：
(1)數(shù)據(jù)和使用模式大幅度變化。
(2)排序的順序發(fā)生改變。
(3)要進行大量插入操作或已經(jīng)完成。
(4)使用大塊I/O的查詢的磁盤讀次數(shù)比預料的要多。
(5)由于大量數(shù)據(jù)修改，使得數(shù)據(jù)頁和索引頁沒有充分使用而導致空間的使用超出估算。
(6)dbcc檢查出索引有問題。
當重建聚簇索引時，這張表的所有非聚簇索引將被重建。
2、索引統(tǒng)計信息的更新
當在一個包含數(shù)據(jù)的表上創(chuàng)建索引的時候，SQL Server會創(chuàng)建分布數(shù)據(jù)頁來存放有關索引的兩種統(tǒng)計信息：分布表和密度表。優(yōu)化器利用這個頁來判斷該索引對某個特定查詢是否有用。但這個統(tǒng)計信息并不動態(tài)地重新計算。這意味著，當表的數(shù)據(jù)改變之后，統(tǒng)計信息有可能是過時的，從而影響優(yōu)化器追求最有工作的目標。因此，在下面情況下應該運行update statistics命令：
(1)數(shù)據(jù)行的插入和刪除修改了數(shù)據(jù)的分布。
(2)對用truncate table刪除數(shù)據(jù)的表上增加數(shù)據(jù)行。
(3)修改索引列的值。
六、結(jié)束語
實踐表明，不恰當?shù)乃饕坏谑聼o補，反而會降低系統(tǒng)的執(zhí)行性能。因為大量的索引在插入、修改和刪除操作時比沒有索引花費更多的系統(tǒng)時間。例如下面情況下建立的索引是不恰當?shù)模?/p>
1、在查詢中很少或從不引用的列不會受益于索引，因為索引很少或從來不必搜索基于這些列的行。
2、只有兩個或三個值的列，如男性和女性(是或否)，從不會從索引中得到好處。
另外，鑒于索引加快了查詢速度，但減慢了數(shù)據(jù)更新速度的特點?？赏ㄟ^在一個段上建表，而在另一個段上建其非聚簇索引，而這兩段分別在單獨的物理設備上來改善操作性能。
重慶分公司
重慶分公司
