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)是 一個非常復(fù)雜的問題， 不僅需要精通數(shù)據(jù)庫的理論知識， 更需要逐漸積累實踐經(jīng)驗。 這里主要針對 Sybase 數(shù)據(jù)庫簡要介紹一下如何進行調(diào)優(yōu)，及調(diào)優(yōu)時所要注意的事項。Sybase 數(shù)據(jù)庫系統(tǒng)的性能的優(yōu)化， 是一項長期且受諸多因素影響的工作， 它可劃分為以下 4 個層次： SQL Server 級：包括對內(nèi)存的合理分配，鎖操作和臨時表的使用，與系統(tǒng)配置關(guān)聯(lián)的磁盤的 I/O 性能。 數(shù)據(jù)庫設(shè)計級：包括數(shù)據(jù)庫對象的設(shè)計，索引的創(chuàng)建，表中數(shù)據(jù)類型的選擇，數(shù)據(jù)庫設(shè)備的分 配及使

2、用。應(yīng)用程序級：包括 T_SQL 查詢語句的優(yōu)化，應(yīng)用級封鎖，事務(wù)和游標的使用。 操作系統(tǒng)級：包括硬件、操作系統(tǒng)和網(wǎng)絡(luò)對總體性能的影響。在數(shù)據(jù)庫應(yīng)用系統(tǒng)的管理維護中，運行環(huán)境引起的性能劣化只有通過硬件的升級才能得到優(yōu)化，在系 統(tǒng)硬件配置和網(wǎng)絡(luò)設(shè)計確定的情況下，影響系統(tǒng)性能的主要是其他三個層次方面的原因。在此就這三個方 面進行簡要討論、總結(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_configu

3、re 'cache' 可以看到存儲過程緩沖 所占百分比( procedure cache percent )，整個數(shù)據(jù)緩沖大小( total data cache size ) 等參數(shù)。1.1.1 存儲過程緩沖( procedure cache )存儲過程緩沖保持以下對象的查詢計劃：Procedures ：存儲過程Triggers ：觸發(fā)器Views ：視圖Rules ：規(guī)則Defaults ：缺省Cursors ：游標存儲過程不可重入，意即每個并發(fā)用戶調(diào)用都會在內(nèi)存中產(chǎn)生一個拷貝。 Procedure ，triggers ， and views 當它們被裝載到存儲過程緩沖中時

4、，被查詢優(yōu)化器優(yōu)化，建立查詢計劃。如果存儲過程在緩沖中， 被調(diào)用時就不需要重新編譯。如果存儲過程緩沖太小，存儲過程就會經(jīng)常被其他調(diào)入內(nèi)存的存儲過程沖洗 掉，當再次被調(diào)用時，存儲過程又被調(diào)入內(nèi)存，再重新編譯，用戶請求因此不得不等待。最嚴重的情況， 如果存儲過程緩沖不夠，存儲過程甚至都不能運行。所以在內(nèi)存足夠的情況下，存儲過程緩沖參數(shù)比例盡 可能大一些。1.1.2 數(shù)據(jù)緩沖（ Data Cache ）數(shù)據(jù)緩沖用來緩存數(shù)據(jù)頁和索引頁，是除去存儲過程緩沖，系統(tǒng)其他占用的緩沖外的剩余內(nèi)存空間。 通過給服務(wù)器增加物理內(nèi)存擴大數(shù)據(jù)緩沖，是最有效的方法。當然，如果不能加內(nèi)存，就只能通過減少存 儲過程緩沖的比例

5、等方法來擴大數(shù)據(jù)緩沖了。配置足夠大的數(shù)據(jù)緩沖可防止其它服務(wù)器活動爭用高速緩存 空間，并加速使用這些表的查詢，因為所需頁始終都可在高速緩存中找到。同時，可以考慮將 “熱”表如： 用戶應(yīng)用程序?qū)ζ湫枨筝^大的表綁定到一個高速緩存上，而表上的索引綁定到其它高速緩存，以提高并發(fā) 性。具體做法如下：創(chuàng)建命名緩存sp_cacheconfig cache_name ，” sizeP|K|M|G ”例如創(chuàng)建一個 10MB 的命名緩存 pubs_cache：sp_cacheconfig pubs_cache， ”10M”把表綁定到指定的命名緩存：sp_bindcache cache_name ， dbname ，

6、owner.table_name ， indexname| ” text only ” 例如把 titles 表綁定到上面剛建的命名緩存中：sp_bindcache pubs_cache，pubs2.titles注意：每開辟一個緩沖占用 16K 的系統(tǒng)內(nèi)存，應(yīng)根據(jù)服務(wù)器的內(nèi)存大小來定義所要開的數(shù)據(jù)緩沖的個 數(shù)。1.1.3 tempdb 數(shù)據(jù)庫的優(yōu)化缺省情況下， tempdb 數(shù)據(jù)庫是放置在 master 設(shè)備上，容量為 2M ，而臨時數(shù)據(jù)庫是活動最為平凡 的數(shù)據(jù)庫常常被用來排序、創(chuàng)建臨時表、重格式化等操作，所以 tempdb 的優(yōu)化應(yīng)該受到特別的關(guān)注，缺 省情況下，用于 tempdb 的 sy

7、stem 、 default 和 logsegment 段在主設(shè)備上分配了 2MB 空間。將第二 個設(shè)備分配給 tempdb 后，即可在 default 和 logsegment 段中將主設(shè)備刪除。使用這種方式，可以確保 tempdb 中的工作表和其它臨時表不會和主設(shè)備上的其它使用相互爭用。優(yōu)化 tempdb 數(shù)據(jù)庫有以下步驟：第一步：調(diào)整臨時庫的位置tempdb數(shù)據(jù)庫缺省放在master設(shè)備上，將臨時數(shù)據(jù)庫發(fā)在分離的設(shè)備上是更可取的。1）初始化一個用來存放臨時數(shù)據(jù)庫的設(shè)備（在SQL Advantage 中）disk initname="tempdb_dev",physna

8、me="c:sybasetempdb.dat",vdevno=3,size=10240（注意：如果將 tempdb 數(shù)據(jù)庫放在多個設(shè)備上，需初始化多個數(shù)據(jù)庫設(shè)備 ）2）將臨時數(shù)據(jù)庫擴展到該一個設(shè)備上alter database tempdb on tempdb_dev=33）打開 tempdb 數(shù)據(jù)庫，從段上刪除master 設(shè)備sp_dropsegment "default",tempdb,mastersp_dropsegment logsegment,tempdb,master4）發(fā)出如下命令，檢查default 段中是否不再包含 master 設(shè)備

9、select dbid,name,segmap from sysusages,sysdeviceswhere sysdevices.low<=syusages.size+vstartand sysdevices.high>=sysusages.size+vstart-1and dbid=2and（status=2 or status=3） 說明：若將臨時數(shù)據(jù)庫放在多個磁盤設(shè)備上，可以更好的利用并行查詢特性來提高查詢性能。 第二步：將臨時數(shù)據(jù)庫與高速緩沖進行綁定。由于臨時表的創(chuàng)建、使用，臨時數(shù)據(jù)庫會頻繁地使用數(shù)據(jù)緩存，所以應(yīng)為臨時數(shù)據(jù)庫創(chuàng)建高速緩存， 從而可以使其常駐內(nèi)存并有助于分散

10、 I/O ：1、創(chuàng)建命名高速緩存sp_cacheconfig “ tempdb_cache ”, ” 10m” , ” mixed ”2、重新啟動 server3、捆綁臨時數(shù)據(jù)庫到tempdb_cache 高速緩存sp_bindcache “ tempdb_cache t”em,pdb4、若有大的 I/O ，配置內(nèi)存池第三步：優(yōu)化臨時表 大多數(shù)臨時表的使用是簡單的，很少需要優(yōu)化。但需要對臨時表進行復(fù)雜的訪問則應(yīng)通過使用多個過程或批處理來把表的創(chuàng)建和索引分開。以下兩種技術(shù)可以改善臨時表的優(yōu)化（系統(tǒng)中有auths和article表）1 在臨時表上創(chuàng)建索引1) 臨時表必須存在2) 統(tǒng)計頁必須存在(即

11、不能在空表上創(chuàng)建索引)2 把對臨時表的復(fù)雜的使用分散到多個批處理或過程中，以便為優(yōu)化器提供信息 下面的這個過程需要進行優(yōu)化：create proc base_procasselect * into #huge_result from authsselect * from article, #huge_result where article.author_code=#huge_result.author_code and sex= ”0” 使用兩個過程可以得到更好的性能1) create proc base_procasselect *into #huge_resultfrom authsex

12、ec select_proc2) create proc select_procasselect * from article,#huge_resultwhere article.author_code=#huge_result.author_code and sex= ”0” 說明：在同一個存儲過程或批處理中，創(chuàng)建并使用一個表時，查詢優(yōu)化器無法決定這個表的大小。2 數(shù)據(jù)庫設(shè)計級的調(diào)優(yōu)實現(xiàn) Sybase 數(shù)據(jù)庫的優(yōu)化，首先要有一個好的數(shù)據(jù)庫設(shè)計方案。在實際工作中，許多 Sybase 方案 往往是由于數(shù)據(jù)庫設(shè)計得不好導(dǎo)致性能很差。實現(xiàn)良好的數(shù)據(jù)庫設(shè)計必須考慮這些問題 :2.1 邏輯數(shù)據(jù)庫規(guī)范化問

13、題 一般來說，邏輯數(shù)據(jù)庫設(shè)計會滿足規(guī)范化的前 3 級標準： 第 1 規(guī)范：沒有重復(fù)的組或多值的列 ;第2規(guī)范: 每個非關(guān)鍵字段必須依賴于主關(guān)鍵字，不能依賴于一個組合式主關(guān)鍵字的某些組成部分 ;第3規(guī)范: 一個非關(guān)鍵字段不能依賴于另一個非關(guān)鍵字段遵守這些規(guī)則的設(shè)計會產(chǎn)生較少的列和更多的表， 因而就減少了數(shù)據(jù)冗余， 也減少了用于存儲數(shù)據(jù)的頁。 但表關(guān)系也許需要通過復(fù)雜的合并來處理，這樣會降低系統(tǒng)的性能。某種程度上的非規(guī)范化可以改善系統(tǒng) 的性能，非規(guī)范化過程可以根據(jù)性能方面不同的考慮用多種不同的方法進行，通常使用以下方法來提高性 能。1. 如果規(guī)范化設(shè)計產(chǎn)生了 4 路或更多路合并關(guān)系，就可以考慮在數(shù)

14、據(jù)庫實體（表）中加入重復(fù)屬性 （列 ）。2. 常用的計算字段 （如總計、最大值等 ） 可以考慮存儲到數(shù)據(jù)庫實體中。比如某一個項目的計劃管理系統(tǒng)中有計劃表，其字段為:項目編號、年初計劃、二次計劃、調(diào)整計劃、補列計劃，而計劃總數(shù)（年初計劃+二次計劃+調(diào)整計劃+補列計劃）是用戶經(jīng)常需要在查詢和報表中用到 的，在表的記錄量很大時，有必要把計劃總數(shù)作為 1 個獨立的字段加入到表中。這里可以采用觸發(fā)器以在 客戶端保持數(shù)據(jù)的一致性。3. 重新定義實體以減少外部屬性數(shù)據(jù)或行數(shù)據(jù)的開支。相應(yīng)的非規(guī)范化類型是:（1）把 1個實體（表）分割成 2個表（把所有的屬性分成 2 組）。這樣就把頻繁被訪問的數(shù)據(jù)同較少被訪問

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

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

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

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

19、設(shè)計索引。索引 的設(shè)計通常有以下原則：(1) 比較窄的索引具有比較高的效率。對于比較窄的索引來說，每頁上能存放較多的索引行，而且索 引的級別也較少。所以，緩存中能放置更多的索引頁，這樣也減少了 I/O 操作。(2) SQL Server 優(yōu)化器能分析大量的索引和合并可能性。所以與較少的寬索引相比，較多的窄索引能 向優(yōu)化器提供更多的選擇。但是不要保留不必要的索引，因為它們將增加存儲和維護的開支。對于復(fù)合索 引、組合索引或多列索引， SQL Server 優(yōu)化器只保留最重要的列的分布統(tǒng)計信息，這樣，索引的第 1列應(yīng) 該有很大的選擇性。(3) 表上的索引過多會影響 UPDATE、INSERT 和 D

20、ELETE 的性能，因為所有的索引都必須做相應(yīng)的調(diào) 整。另外，所有的分頁操作都被記錄在日志中，這也會增加 I/O 操作。(4) 對 1 個經(jīng)常被更新的列建立索引，會嚴重影響性能。(5) 由于存儲開支和 I/O 操作方面的原因，較小的自組索引比較大的索引性能更好一些。但它的缺點 是要維護自組的列。(6) 盡量分析出每一個重要查詢的使用頻度，這樣可以找出使用最多的索引，然后可以先對這些索引 進行適當?shù)膬?yōu)化。(7) 查詢中的 WHERE 子句中的任何列都很可能是個索引列，因為優(yōu)化器重點處理這個子句。(8) 對小于 1 個范圍的小型表進行索引是不劃算的，因為對于小表來說表掃描往往更快而且費用低。(9)

21、 與“ORDER BY或 “GROUP BY起使用的列一般適于做分族索引。如果“ORDER B”命令中用到的列上有分簇索引，那么就不會再生成 1個工作表了，因為行已經(jīng)排序了。“GROUPBY”命令則一定產(chǎn)生1個工作表。(10) 分簇索引不應(yīng)該構(gòu)造在經(jīng)常變化的列上， 因為這會引起整行的移動。 在實現(xiàn)大型交易處理系統(tǒng)時， 尤其要注意這一點，因為這些系統(tǒng)中數(shù)據(jù)往往是頻繁變化的。3.2 避免或簡化排序 應(yīng)當盡量簡化或避免對大型表進行重復(fù)的排序。當能夠利用索引自動以適當?shù)拇涡虍a(chǎn)生輸出時，優(yōu)化器就避免了排序這個步驟。為了避免不必要的排序，就要正確地增建索引，合理地合并數(shù)據(jù)庫表(盡管有 時可能影響表的規(guī)范化

22、，但相對于效率的提高是值得的)。如果排序不可避免，那么應(yīng)當試圖簡化它，如 縮小排序的列的范圍等。3.3 消除對大型表行數(shù)據(jù)的順序存取 在嵌套查詢中，表的順序存取對查詢效率可能產(chǎn)生致命的影響。我們有時可以使用并集來避免順序存取。盡管也許在所有的檢查列上都有索引， 但某些形式的 where 子句會強迫優(yōu)化器使用順序存取， 這一點 也應(yīng)注意。3.4 避免相關(guān)子查詢?nèi)绻粋€列同時在主查詢和 where 子句中出現(xiàn)， 很可能當主查詢中的列值改變之后， 子查詢必須 重新查詢一次。而且查詢嵌套層次越多，效率越低，因此應(yīng)當盡量避免子查詢。如果子查詢不可避免，那 么要在子查詢中過濾掉盡可能多的行。3.5 避免困

23、難的正規(guī)表達式mathes 和 like 關(guān)鍵字支持通配符匹配，但這種匹配特別耗時。例如： select * from customer where zipcode like“ 98_ _ ，_ 即”使在 zipcode 字段上已建立了索引，在這種情況下也還是采用順序掃描的方式。如果把語句改為： select * from customer where zipcode >“98000”，在執(zhí)行查詢時就會利用索引來查詢，顯然會大大提高速度。3.6 使用臨時表加速查詢 把表的一個子集進行排序并創(chuàng)建臨時表，有時能加速查詢。它有助于避免多重排序操作，而且在其他方面還能簡化優(yōu)化器的工作。臨時表中的

24、行要比主表中的行少，而且物理順序就是所要求的順序，減少了 磁盤 i/o ，所以查詢工作量可以得到大幅減少。但要注意，臨時表創(chuàng)建后不會反映主表的修改。在主表中數(shù) 據(jù)頻繁修改的情況下，注意不要丟失數(shù)據(jù)。4 Sybase 調(diào)優(yōu)工具在分析Sybase數(shù)據(jù)庫的性能時，常要用到一些數(shù)據(jù)庫系統(tǒng)本身提供的性能調(diào)優(yōu)工具，以下是最常用 的幾個系統(tǒng)存儲過程：存儲過程名稱主要功能Sp sysmon企業(yè)級系統(tǒng)性能報告工具Sp lock查看鎖的情況Sp_who查看線程的活動情況Sp procqmode存儲過程的查詢處理模式Sp configure配置SQL Serve系統(tǒng)級參數(shù)Sp estspace估計創(chuàng)建一個表需要的空

25、間和時間Sp spaccused估計表的總行數(shù)及表和索引占用的空間Sp monitor監(jiān)視CPU I/O的統(tǒng)計活動情況可以從18個方面了解在用系統(tǒng)性能狀況，并在適當?shù)臅r候利用環(huán)境參數(shù)進行性能調(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ù)高速緩存管理（ mdcache）9、任務(wù)管理（taskmgmt ）10、監(jiān)視器訪問SQL的執(zhí)行（monaccess）11、網(wǎng)絡(luò)I/O管理（netio ）12、并行查詢管理（parallel） 13、過程緩存管理（pcache） 14、恢復(fù)管理（recovery）15、事務(wù)管理（xactmgmt） 16、事務(wù)概要（xactsum）17、磁盤 I/O 管理（diskio）18、工作進程管理（wpm）括號后英文短詞是該模塊參數(shù)。在此簡要分析一下 sp_sysmon 對Adaptive Server系統(tǒng)運行情況的影響，有利于更好地熟悉系統(tǒng) 性能，更為有效地進行系統(tǒng)管理，合理地利用和配置系統(tǒng)資源，達到系統(tǒng)性能調(diào)優(yōu)的目的。這里要特別