Sybase數(shù)據(jù)庫性能優(yōu)化

1、Sybase數(shù)據(jù)庫性能優(yōu)化在現(xiàn)有軟硬件條件下，充分發(fā)揮數(shù)據(jù)庫系統(tǒng)的潛能是DBA追求的最高境界，然而，數(shù)據(jù)庫性能調(diào)優(yōu)是一個(gè)非常復(fù)雜的問題，不僅需要精通數(shù)據(jù)庫的理論知識，更需要逐漸積累實(shí)踐經(jīng)驗(yàn)。這里主要針對Sybase數(shù)據(jù)庫簡要介紹一下如何進(jìn)行調(diào)優(yōu)，及調(diào)優(yōu)時(shí)所要注意的事項(xiàng)。Sybase數(shù)據(jù)庫系統(tǒng)的性能的優(yōu)化，是一項(xiàng)長期且受諸多因素影響的工作，它可劃分為以下4個(gè)層次： SQL Server級：包括對內(nèi)存的合理分配，鎖操作和臨時(shí)表的使用，與系統(tǒng)配置關(guān)聯(lián)的磁盤的I/O性能。 數(shù)據(jù)庫設(shè)計(jì)級：包括數(shù)據(jù)庫對象的設(shè)計(jì)，索引的創(chuàng)建，表中數(shù)據(jù)類型的選擇，數(shù)據(jù)庫設(shè)備的分配及使用。 應(yīng)用程序級：包括T_SQL查詢語句的

2、優(yōu)化，應(yīng)用級封鎖，事務(wù)和游標(biāo)的使用。 操作系統(tǒng)級：包括硬件、操作系統(tǒng)和網(wǎng)絡(luò)對總體性能的影響。 在數(shù)據(jù)庫應(yīng)用系統(tǒng)的管理維護(hù)中，運(yùn)行環(huán)境引起的性能劣化只有通過硬件的升級才能得到優(yōu)化，在系統(tǒng)硬件配置和網(wǎng)絡(luò)設(shè)計(jì)確定的情況下，影響系統(tǒng)性能的主要是其他三個(gè)層次方面的原因。在此就這三個(gè)方面進(jìn)行簡要討論、總結(jié)。1 SQL Server級的調(diào)優(yōu) 1.1 管理共享內(nèi)存數(shù)據(jù)庫性能優(yōu)化的首要方面是最優(yōu)管理內(nèi)存。數(shù)據(jù)庫占用的共享內(nèi)存分成數(shù)據(jù)緩沖（data cache）、存儲過程緩沖（Procedure cache）等幾塊。在isql 下使用 sp_configure cache 可以看到存儲過程緩沖所占百分比（proc

3、edure cache percent），整個(gè)數(shù)據(jù)緩沖大?。╰otal data cache size） 等參數(shù)。 CQ%SC ( 1.1.1 存儲過程緩沖（procedure cache）存儲過程緩沖保持以下對象的查詢計(jì)劃： x%mD6Ud 1.1.3 tempdb數(shù)據(jù)庫的優(yōu)化缺省情況下，tempdb數(shù)據(jù)庫是放置在master設(shè)備上，容量為2M，而臨時(shí)數(shù)據(jù)庫是活動最為平凡的數(shù)據(jù)庫常常被用來排序、創(chuàng)建臨時(shí)表、重格式化等操作，所以tempdb的優(yōu)化應(yīng)該受到特別的關(guān)注，缺省情況下，用于tempdb的system、default和logsegment段在主設(shè)備上分配了2MB空間。將第二個(gè)設(shè)備分配給t

4、empdb后，即可在default和logsegment段中將主設(shè)備刪除。使用這種方式，可以確保tempdb中的工作表和其它臨時(shí)表不會和主設(shè)備上的其它使用相互爭用。優(yōu)化tempdb數(shù)據(jù)庫有以下步驟：第一步：調(diào)整臨時(shí)庫的位置tempdb數(shù)據(jù)庫缺省放在master設(shè)備上，將臨時(shí)數(shù)據(jù)庫發(fā)在分離的設(shè)備上是更可取的。1）初始化一個(gè)用來存放臨時(shí)數(shù)據(jù)庫的設(shè)備（在 SQL Advantage中）diskinitname=tempdb_dev,physname=c:sybasetempdb.dat,vdevno=3,size=10240(注意：如果將tempdb數(shù)據(jù)庫放在多個(gè)設(shè)備上，需初始化多個(gè)數(shù)據(jù)庫設(shè)備)2)

5、將臨時(shí)數(shù)據(jù)庫擴(kuò)展到該一個(gè)設(shè)備上alterdatabasetempdbontempdb_dev=33)打開tempdb數(shù)據(jù)庫，從段上刪除master設(shè)備sp_dropsegmentdefault,tempdb,mastersp_dropsegmentlogsegment,tempdb,master4)發(fā)出如下命令，檢查default段中是否不再包含master設(shè)備selectdbid,name,segmapfromsysusages,sysdeviceswheresysdevices.low=sysusages.size+vstart-1anddbid=2and(status=2orstatus

6、=3)說明：若將臨時(shí)數(shù)據(jù)庫放在多個(gè)磁盤設(shè)備上，可以更好的利用并行查詢特性來提高查詢性能。第二步：將臨時(shí)數(shù)據(jù)庫與高速緩沖進(jìn)行綁定。由于臨時(shí)表的創(chuàng)建、使用，臨時(shí)數(shù)據(jù)庫會頻繁地使用數(shù)據(jù)緩存，所以應(yīng)為臨時(shí)數(shù)據(jù)庫創(chuàng)建高速緩存，從而可以使其常駐內(nèi)存并有助于分散I/O：1、創(chuàng)建命名高速緩存sp_cacheconfig“tempdb_cache”,”10m”,”mixed”2、重新啟動server3、捆綁臨時(shí)數(shù)據(jù)庫到tempdb_cache高速緩存sp_bindcache“tempdb_cache”,tempdb4、若有大的I/O，配置內(nèi)存池第三步：優(yōu)化臨時(shí)表大多數(shù)臨時(shí)表的使用是簡單的，很少需要優(yōu)化。但需要對

7、臨時(shí)表進(jìn)行復(fù)雜的訪問則應(yīng)通過使用多個(gè)過程或批處理來把表的創(chuàng)建和索引分開。以下兩種技術(shù)可以改善臨時(shí)表的優(yōu)化(系統(tǒng)中有auths和article表)1 在臨時(shí)表上創(chuàng)建索引 1）臨時(shí)表必須存在 2）統(tǒng)計(jì)頁必須存在（即不能在空表上創(chuàng)建索引）2 把對臨時(shí)表的復(fù)雜的使用分散到多個(gè)批處理或過程中，以便為優(yōu)化器提供信息下面的這個(gè)過程需要進(jìn)行優(yōu)化：createprocbase_procasselect*into#huge_resultfromauthsselect*fromarticle,#huge_resultwherearticle.author_code=#huge_result.author_codea

8、ndsex=”0”使用兩個(gè)過程可以得到更好的性能1）createprocbase_procasselect*into#huge_resultfromauthsexecselect_proc2)createprocselect_procasselect*fromarticle,#huge_resultwherearticle.author_code=#huge_result.author_codeandsex=”0” 說明：在同一個(gè)存儲過程或批處理中，創(chuàng)建并使用一個(gè)表時(shí)，查詢優(yōu)化器無法決定這個(gè)表的大小。2 數(shù)據(jù)庫設(shè)計(jì)級的調(diào)優(yōu)實(shí)現(xiàn)Sybase數(shù)據(jù)庫的優(yōu)化，首先要有一個(gè)好的數(shù)據(jù)庫設(shè)計(jì)方案。在實(shí)際工作

9、中，許多Sybase方案往往是由于數(shù)據(jù)庫設(shè)計(jì)得不好導(dǎo)致性能很差。實(shí)現(xiàn)良好的數(shù)據(jù)庫設(shè)計(jì)必須考慮這些問題: 2.1 邏輯數(shù)據(jù)庫規(guī)范化問題一般來說，邏輯數(shù)據(jù)庫設(shè)計(jì)會滿足規(guī)范化的前3級標(biāo)準(zhǔn)：第1規(guī)范：沒有重復(fù)的組或多值的列;第2規(guī)范: 每個(gè)非關(guān)鍵字段必須依賴于主關(guān)鍵字，不能依賴于一個(gè)組合式主關(guān)鍵字的某些組成部分;第3規(guī)范: 一個(gè)非關(guān)鍵字段不能依賴于另一個(gè)非關(guān)鍵字段。 遵守這些規(guī)則的設(shè)計(jì)會產(chǎn)生較少的列和更多的表，因而就減少了數(shù)據(jù)冗余，也減少了用于存儲數(shù)據(jù)的頁。但表關(guān)系也許需要通過復(fù)雜的合并來處理，這樣會降低系統(tǒng)的性能。某種程度上的非規(guī)范化可以改善系統(tǒng)的性能，非規(guī)范化過程可以根據(jù)性能方面不同的考慮用多種不

10、同的方法進(jìn)行，通常使用以下方法來提高性能。1.如果規(guī)范化設(shè)計(jì)產(chǎn)生了4路或更多路合并關(guān)系，就可以考慮在數(shù)據(jù)庫實(shí)體(表)中加入重復(fù)屬性(列)。2.常用的計(jì)算字段(如總計(jì)、最大值等)可以考慮存儲到數(shù)據(jù)庫實(shí)體中。比如某一個(gè)項(xiàng)目的計(jì)劃管理系統(tǒng)中有計(jì)劃表，其字段為:項(xiàng)目編號、年初計(jì)劃、二次計(jì)劃、調(diào)整計(jì)劃、補(bǔ)列計(jì)劃，而計(jì)劃總數(shù)(年初計(jì)劃+二次計(jì)劃+調(diào)整計(jì)劃+補(bǔ)列計(jì)劃)是用戶經(jīng)常需要在查詢和報(bào)表中用到的，在表的記錄量很大時(shí)，有必要把計(jì)劃總數(shù)作為1個(gè)獨(dú)立的字段加入到表中。這里可以采用觸發(fā)器以在客戶端保持?jǐn)?shù)據(jù)的一致性。3.重新定義實(shí)體以減少外部屬性數(shù)據(jù)或行數(shù)據(jù)的開支。相應(yīng)的非規(guī)范化類型是:(1)把1個(gè)實(shí)體(表)分

11、割成2個(gè)表(把所有的屬性分成2組)。這樣就把頻繁被訪問的數(shù)據(jù)同較少被訪問的數(shù)據(jù)分開了。這種方法要求在每個(gè)表中復(fù)制首要關(guān)鍵字。這樣產(chǎn)生的設(shè)計(jì)有利于并行處理，并將產(chǎn)生列數(shù)較少的表。(2)把1個(gè)實(shí)體(表)分割成2個(gè)表(把所有的行分成2組)。這種方法適用于那些將包含大量數(shù)據(jù)的實(shí)體(表)。在應(yīng)用中常要保留歷史記錄，但是歷史記錄很少用到。因此可以把頻繁被訪問的數(shù)據(jù)同較少被訪問的歷史數(shù)據(jù)分開。而且如果數(shù)據(jù)行是作為子集被邏輯工作組(部門、銷售分區(qū)、地理區(qū)域等)訪問的，那么這種方法也是很有好處的。2.2 生成物理數(shù)據(jù)庫要想正確選擇基本物理實(shí)現(xiàn)策略，必須了解和利用好數(shù)據(jù)庫訪問格式和硬件資源的操作特點(diǎn)，特別是內(nèi)存和

12、磁盤子系統(tǒng)i/o。以下是一些常用技巧：與每個(gè)表列相關(guān)的數(shù)據(jù)類型應(yīng)該反映數(shù)據(jù)所需的最小存儲空間，特別是對于被索引的列更是如此。比如能使用smallint類型就不要用integer類型，這樣索引字段可以被更快地讀取，而且可以在一個(gè)數(shù)據(jù)頁上放置更多的數(shù)據(jù)行，因而也就減少了i/o操作。把一個(gè)表放在某個(gè)物理設(shè)備上，再通過sql server的段把它的部分簇索引放在一個(gè)不同的物理設(shè)備上，這樣能提高性能。尤其是系統(tǒng)采用了多個(gè)智能型磁盤控制器和數(shù)據(jù)分離技術(shù)的情況下，這樣做的好處更加明顯。用sql server段把一個(gè)頻繁使用的大表分割開，并放在多個(gè)單獨(dú)的智能型磁盤控制器的數(shù)據(jù)庫設(shè)備上，這樣也可以提高性能。因?yàn)?/p>

13、有多個(gè)磁頭在查找，所以數(shù)據(jù)分離也能提高性能。用sql server段把文本或圖像列的數(shù)據(jù)存放在一個(gè)單獨(dú)的物理設(shè)備上可以提高性能。一個(gè)專用的智能型的控制器能進(jìn)一步提高性能。3 應(yīng)用程序級調(diào)優(yōu)3.1合理使用索引索引是數(shù)據(jù)庫中重要的數(shù)據(jù)結(jié)構(gòu)，它的根本目的就是提高查詢效率。SQL Server采用基于代價(jià)的優(yōu)化模型，它對每一個(gè)提交的有關(guān)表的查詢，決定是否使用索引或用哪一個(gè)索引。因?yàn)椴樵儓?zhí)行的大部分開銷是磁盤I/O，使用索引提高性能的一個(gè)主要目標(biāo)是避免全表掃描，因?yàn)槿頀呙栊枰獜拇疟P上讀表的每一個(gè)數(shù)據(jù)頁，如果有索引指向數(shù)據(jù)值，則查詢只需讀幾次磁盤就可以了。所以如果建立了合理的索引，優(yōu)化器就能利用索引加速

14、數(shù)據(jù)的查詢過程。通常要想建立合理的索引需遵循下面所介紹的一些原則。 對于每個(gè)可優(yōu)化的子句，優(yōu)化器都查看數(shù)據(jù)庫系統(tǒng)表，以確定是否有相關(guān)的索引能用于訪問數(shù)據(jù)。只有當(dāng)索引中的列的1個(gè)前綴與查詢子句中的列完全匹配時(shí)，這個(gè)索引才被認(rèn)為是有用的。因?yàn)樗饕歉鶕?jù)列的順序構(gòu)造的，所以要求匹配是精確的匹配。想用索引的次要列訪問數(shù)據(jù)，就像想在電話本中查找所有姓為某個(gè)姓氏的條目一樣，排序基本上沒有什么用，因?yàn)槟氵€是得查看每一行以確定它是否符合條件。如果1個(gè)子句有可用的索引，那么優(yōu)化器就會為它確定選擇性。在設(shè)計(jì)過程中，要根據(jù)查詢設(shè)計(jì)準(zhǔn)則仔細(xì)檢查所有的查詢，以查詢的優(yōu)化特點(diǎn)為基礎(chǔ)設(shè)計(jì)索引。索引的設(shè)計(jì)通常有以下原則：(1

15、) 比較窄的索引具有比較高的效率。對于比較窄的索引來說，每頁上能存放較多的索引行，而且索引的級別也較少。所以，緩存中能放置更多的索引頁，這樣也減少了I/O操作。(2) SQL Server優(yōu)化器能分析大量的索引和合并可能性。所以與較少的寬索引相比，較多的窄索引能向優(yōu)化器提供更多的選擇。但是不要保留不必要的索引，因?yàn)樗鼈儗⒃黾哟鎯途S護(hù)的開支。對于復(fù)合索引、組合索引或多列索引，SQL Server優(yōu)化器只保留最重要的列的分布統(tǒng)計(jì)信息，這樣，索引的第1列應(yīng)該有很大的選擇性。(3) 表上的索引過多會影響UPDATE、INSERT和DELETE的性能，因?yàn)樗械乃饕急仨氉鱿鄳?yīng)的調(diào)整。另外，所有的分頁

16、操作都被記錄在日志中，這也會增加I/O操作。(4) 對1個(gè)經(jīng)常被更新的列建立索引，會嚴(yán)重影響性能。(5) 由于存儲開支和I/O操作方面的原因，較小的自組索引比較大的索引性能更好一些。但它的缺點(diǎn)是要維護(hù)自組的列。(6) 盡量分析出每一個(gè)重要查詢的使用頻度，這樣可以找出使用最多的索引，然后可以先對這些索引進(jìn)行適當(dāng)?shù)膬?yōu)化。(7) 查詢中的WHERE子句中的任何列都很可能是個(gè)索引列，因?yàn)閮?yōu)化器重點(diǎn)處理這個(gè)子句。(8) 對小于1個(gè)范圍的小型表進(jìn)行索引是不劃算的，因?yàn)閷τ谛”韥碚f表掃描往往更快而且費(fèi)用低。(9) 與“ORDER BY”或“GROUP BY”一起使用的列一般適于做分族索引。如果“ORDER

17、BY”命令中用到的列上有分簇索引，那么就不會再生成1個(gè)工作表了，因?yàn)樾幸呀?jīng)排序了。“GROUP BY”命令則一定產(chǎn)生1個(gè)工作表。(10) 分簇索引不應(yīng)該構(gòu)造在經(jīng)常變化的列上，因?yàn)檫@會引起整行的移動。在實(shí)現(xiàn)大型交易處理系統(tǒng)時(shí)，尤其要注意這一點(diǎn)，因?yàn)檫@些系統(tǒng)中數(shù)據(jù)往往是頻繁變化的。3.2 避免或簡化排序應(yīng)當(dāng)盡量簡化或避免對大型表進(jìn)行重復(fù)的排序。當(dāng)能夠利用索引自動以適當(dāng)?shù)拇涡虍a(chǎn)生輸出時(shí)，優(yōu)化器就避免了排序這個(gè)步驟。為了避免不必要的排序，就要正確地增建索引，合理地合并數(shù)據(jù)庫表（盡管有時(shí)可能影響表的規(guī)范化，但相對于效率的提高是值得的）。如果排序不可避免，那么應(yīng)當(dāng)試圖簡化它，如縮小排序的列的范圍等。3.3

18、消除對大型表行數(shù)據(jù)的順序存取在嵌套查詢中，表的順序存取對查詢效率可能產(chǎn)生致命的影響。我們有時(shí)可以使用并集來避免順序存取。盡管也許在所有的檢查列上都有索引，但某些形式的where子句會強(qiáng)迫優(yōu)化器使用順序存取，這一點(diǎn)也應(yīng)注意。3.4 避免相關(guān)子查詢?nèi)绻粋€(gè)列同時(shí)在主查詢和where子句中出現(xiàn)，很可能當(dāng)主查詢中的列值改變之后，子查詢必須重新查詢一次。而且查詢嵌套層次越多，效率越低，因此應(yīng)當(dāng)盡量避免子查詢。如果子查詢不可避免，那么要在子查詢中過濾掉盡可能多的行。3.5 避免困難的正規(guī)表達(dá)式mathes和like關(guān)鍵字支持通配符匹配，但這種匹配特別耗時(shí)。例如：select * from customer

19、 where zipcode like “98_ _ _”，即使在zipcode字段上已建立了索引，在這種情況下也還是采用順序掃描的方式。如果把語句改為：select * from customer where zipcode “98000”，在執(zhí)行查詢時(shí)就會利用索引來查詢，顯然會大大提高速度。3.6 使用臨時(shí)表加速查詢把表的一個(gè)子集進(jìn)行排序并創(chuàng)建臨時(shí)表，有時(shí)能加速查詢。它有助于避免多重排序操作，而且在其他方面還能簡化優(yōu)化器的工作。臨時(shí)表中的行要比主表中的行少，而且物理順序就是所要求的順序，減少了磁盤i/o，所以查詢工作量可以得到大幅減少。但要注意，臨時(shí)表創(chuàng)建后不會反映主表的修改。在主表中數(shù)據(jù)

20、頻繁修改的情況下，注意不要丟失數(shù)據(jù)。4 Sybase調(diào)優(yōu)工具 在分析Sybase數(shù)據(jù)庫的性能時(shí)，常要用到一些數(shù)據(jù)庫系統(tǒng)本身提供的性能調(diào)優(yōu)工具，以下是最常用的幾個(gè)系統(tǒng)存儲過程： 存儲過程名稱主要功能Sp_sysmon企業(yè)級系統(tǒng)性能報(bào)告工具Sp_lock查看鎖的情況Sp_who查看線程的活動情況Sp_procqmode存儲過程的查詢處理模式Sp_configure配置SQL Serve 系統(tǒng)級參數(shù)Sp_estspace估計(jì)創(chuàng)建一個(gè)表需要的空間和時(shí)間Sp_spaccused估計(jì)表的總行數(shù)及表和索引占用的空間Sp_monitor監(jiān)視CPU I/O的統(tǒng)計(jì)活動情況可以從18個(gè)方面了解在用系統(tǒng)性能狀況，并在適當(dāng)?shù)臅r(shí)候利用環(huán)境參數(shù)進(jìn)行性能調(diào)優(yōu)：1、內(nèi)核管理（kernal） 2、應(yīng)用管理（appmgmt） 3、數(shù)據(jù)緩存管理（dcache）4、ESP管理（esp） 5、索引管理（indexmgmt） 6、鎖管理（locks）7、內(nèi)存管理（memory） 8、元數(shù)據(jù)高