SYBASEIQ與ASE數據庫

使用經驗交流軟件開發中心應用開發六部目錄IQ特點與優化方法ASE特點與優化方法2Q&A31目錄IQ特點與優化方法1ASE特點與優化方法2Q&A31.1.IQ特點列式存儲與壓縮存儲讀取數據快數據存儲方式存儲優化FP索引節省存儲空間1.1.IQ特點表級鎖讀不封鎖讀讀不封鎖寫寫不封鎖讀寫封鎖寫不等待其他進程釋放鎖資源,應用進程得到錯誤信息并立即返回1.1.IQ特點適合做大批量數據處理復雜查詢速度快批量加載數據速度快不適合做聯機高并發業務鎖機制插入、更新等相對較慢1.2.IQ優化方法SQL優化索引選擇SQL語句的執行計劃1.2.IQ優化方法IQ中SQL優化思路對于最關鍵的地方進行優化表設計,索引設計是關鍵對于關鍵SQL查看IQ查詢計劃，進行診斷然后優化SQL使用工具監控IQ收集監控統計信息調整數據庫/Server選項選項1.2.IQ優化方法索引選擇索引選擇因素字段中唯一值的數量（Numberofuniquevalue）字段的數據類型查詢的類型（Typeofquery）磁盤空間（Diskspaceusage）1.2.IQ優化方法索引選擇主要IQ索引的適用情況FP索引Ad-Hoc關聯（join）字段適合建立FP索引（熱點查詢中的關聯字段，這種類型的查詢是高度動態的，事先不能確定）。根據字段的“基數”確定具體的FP類型在SELECT列表中出現的字段在一些計算表達式中，例如SUM（A+B）在WHERE子句的LIKE條件中或SUBSTR函數中，例如：columnlike“%x”1.2.IQ優化方法LF索引關聯查詢的關聯字段GROUPBY、ORDERBY中的字段MIN、MAX、SUM、AVG、COUNT等聚集函數的參數字段COUNTDISTINCT、SELECTDISTINCTWHERE子句中等于、不等于、>、>=、<、<=、BETWEEN、IN、范圍條件等1.2.IQ優化方法HG索引關聯查詢的關聯字段SELECTDISTINCT、COUNTDISTINCTCOUNT、MIN、MAXGROUPBY、ORDERBYIN的參數等于、不等于HNG索引AVG、SUM的參數字段范圍（BETWEEN或范圍比較）中出現的字段RootString查詢。例如：WHEREcust_namelike“Stan%”。（其它情況的like使用FP索引）。1.2.IQ優化方法sql語句的執行計劃執行計劃是分析、比較語句效率的重要手段1.2.IQ優化方法sql語句的執行計劃生成執行計劃

Query_Plan=on

當打開此選項時，SybaseIQ將產生有關查詢的消息。這些消息包括有關使用連接索引、連接順序、查詢的連接算法以及使用數據提取選項提取的列等內容的消息。

Query_Detail=on

當QUERY_DETAIL和QUERY_PLAN（或QUERY_PLAN_AS_HTML）都打開時，SybaseIQ將在產生其查詢計劃時顯示有關查詢的其它信息。1.2.IQ優化方法Query_Timing=on

此選項控制對查詢引擎中有關子查詢及其它某些重復函數的計時統計信息的收集。Query_Plan_After_Run=on

打開QUERY_PLAN_AFTER_RUN時，在已經運行完查詢之后，將打印查詢計劃。

要讓此選項工作，必須將QUERY_PLAN選項設置為ON（缺省值）。可以將此選項與QUERY_DETAIL一起使用，以便在查詢計劃報告中生成其它信息。1.2.IQ優化方法Index_Advisor=on

當此選項設置為ON時，索引顧問會在SybaseIQ查詢計劃中打印索引建議Query_Plan_As_Html=on

QUERY_PLAN_AS_HTML導致以HTML格式生成圖形化查詢計劃。Query_Plan_As_Html_Directory=‘/tmp’

如將QUERY_PLAN_AS_HTML選項設為ON并用QUERY_PLAN_AS_HTML_DIRECTORY選項指定了一個目錄，則SybaseIQ會將HTML查詢計劃寫入該指定目錄中。1.2.IQ優化方法NoExec=‘On’

在確定如何處理查詢時，IQ優化程序將生成查詢計劃，以映射它如何計劃讓查詢引擎處理查詢1.2.IQ優化方法優化建議:盡量避免循環單條處理數據,而采用批量處理盡量避免大批量遠程插入數據,而使用加載數據注意索引的使用COMMIT1.3.IQ數據庫數據遷移方法Loadtable方法數據提取選項方法Insertlocation方法代理表方法bcp方法1.3.IQ數據庫數據遷移方法Loadtable方法單線索裝載LOADTABLEservice(service_key'|!',call_waiting_flag'|!',caller_id_flag'|!',voice_mail_flag'|!',cellular_flag'|!',internet_flag'|!',isdn_flag'\x0a')FROM'/tmp/import/service.dat'ESCAPESOFFQUOTESOFFNOTIFY1000000WITHCHECKPOINTON;COMMIT;注意：左邊的

LOAD使用單線索裝載，沒有多線索裝載好。1.3.IQ數據庫數據遷移方法Loadtable方法多線程裝載

LOADTABLEservice(service_key'|!',call_waiting_flag'|!',caller_id_flag'|!',voice_mail_flag'|!',cellular_flag'|!',internet_flag'|!',isdn_flag'|!')FROM'/tmp/import/service.dat'ESCAPESOFFQUOTESOFFNOTIFY1000000ROWDELIMITEDBY'\x0a'WITHCHECKPOINTON;COMMIT;注意：左邊的LOAD使用多線索裝載，是最推薦的方式。但要求數據文件中最后一個字段也必須有“列分隔符”。1.3.IQ數據庫數據遷移方法數據提取選項方法SETTEMPORARYOPTIONtemp_extract_name1='/amls/tmp/QTBP_STD_TRANSACTION.txt';

SETTEMPORARYOPTIONTemp_Extract_Column_Delimiter='|!';

SELECT*FROMQTBP_STD_TRANSACTIONwhereTRANS_DATE='20120829';數據提取功能可大大提高對大型結果集執行查詢的性能。1.3.IQ數據庫數據遷移方法Insertlocation方法INSERT...LOCATION允許插入來自AdaptiveServerEnterprise或SybaseIQ數據庫的數據。

SybaseIQ連接到指定的服務器和數據庫，然后返回來自對這些表所做查詢的結果，以將結果插入當前數據庫中。1.3.IQ數據庫數據遷移方法Insertlocation方法（步驟）將遠程服務器添加到interfaces文件中。

創建遠程服務器。創建外部登錄憑據。使用INSERT...LOCATIION使用INSERT...LOCATIIONCREATESERVERASEserverCLASS'asejdbc'USING‘rimu:6666/my_ased’只有登錄名和DBA帳戶才能添加或修改外部登錄憑據。以下語句允許本地用戶fred通過使用遠程登錄frederick和口令banana獲得對服務器ASEserver的訪問權限。CREATEEXTERNLOGINfredTOASEserverREMOTELOGINfrederickIDENTIFIEDBYbananaINSERTlocal_SQL_TypesLOCATION‘ASEserver.my_asedb’{SELECTint_colFROMSQL_Types};1.3.IQ數據庫數據遷移方法代理表方法創建遠程服務器創建外部登錄憑據創建代理表定義如果該表已存在于遠程存儲位置，則使用CREATEEXISTINGTABLE語句。此語句為遠程服務器上現有的表定義代理表。如果該表未存在于遠程存儲位置，則使用CREATETABLE語句。此語句在遠程服務器上創建新表，并且還為該表定義代理表。1.3.IQ數據庫數據遷移方法bcp方法IQ數據庫的BCP語法與ASE數據庫的BCP語法相同。但是IQ數據庫對BCPIN支持的不是很好，不建議使用。

bcptable_nameoutdatafile–Uusername-Ppassword-Sserver1.3.IQ數據庫數據遷移方法小結1LOADTABLE方法用于將數據從文件裝載到IQ數據庫中；數據提取選項方法用于將數據從IQ數據庫中導出到文件中；

INSERTLOCATION、代理表方法用于將數據從另一個數據庫傳輸到當前數據庫；

BCP方法用于將數據從IQ數據庫導出到文件中。1.3.IQ數據庫數據遷移方法小結2IQ數據庫導入，推薦：

LOADTABLEIQ數據庫導出，推薦：數據提取選項法兩個庫之間的數據傳輸，推薦：

INSERTLOCATION方法，效率較高，且對于包含有柜員手工錄入的數據，大大減少了出錯的幾率。1.4IQ數據庫系統管理1、日志查看（logfiles）。2、sp_iqcontext3、sp_iqstatus………目錄ASE特點與優化方法2IQ特點與優化方法1Q&A32.1.ASE特點適合做聯機高并發業務

短小精悍、快進快出，快速響應事務處理2.2.ASE優化方法優化三個方面應用程序客戶端與服務器交互服務器2.2.ASE優化方法應用程序優化邏輯優化語句優化用戶對象設計2.2.ASE優化方法邏輯優化根據業務,來調整實現業務的邏輯與方式,盡量減少訪問數據庫資源,或降低業務實現復雜度2.2.ASE優化方法語句優化性能更優化的查詢方式or是否可轉換為union。舉例(or.Vs.union)類型匹配update變長字段大事務多次提交…索引使用情況表達式與索引2.2.ASE優化方法用戶對象設計鎖模式在OLTP系統中，只保留必要的索引對于DSS系統中，可建立更多的索引以加快Select的速度在經常連接的列上建索引Orderby的列上建索引…2.2.ASE優化方法調優方法與工具setstatisticsio、setstatisticstimeisql–pDBArtisanProActiveDBAsp_sysmon2.2.ASE優化方法客戶端與服務器交互存儲過程大尺寸數據包服務器sp_configure‘defaultnetworkpacketsize’sp_configure‘maxnetworkpacketsize’客戶端jdbc(jdbc:sybase:tds:192.168.1.26:5000/db?packetsize=4096)odbc(packetsize)isql(-A)ctlibrary(cs_packetsize=size)Bcp-A…2.2.ASE優化方法客戶端與服務器交互游標游標一次返回多行 execsql[atconnection_name]opencursor_name[row_count=n]2.2.ASE優化方法服務器優化CPU(engine)內存I/O鎖2.2.ASE優化方法CPU多CPUsp_configure‘maxonlineengines’sp_configure‘maxonlineenginesatstartup’并行sp_configure‘maxonlineenginesatstartup’sp_configure‘maxparalleldegree’sp_configure‘maxscanparalleldegree’setparallel_degree3setscan_parallel_degree3hintdbcctraceon(310)、showplan、statisticsio2.2.ASE優化方法內存高速緩存sp_configure‘maxmemory’sp_cacheconfig、sp_poolconfigsp_configure‘procedurecachesize’熱點表行級鎖命名cache分區2.2.ASE優化方法I/OKernelAsyncI/OAsyncPrefech內存sp_cacheconfigsp_poolconfig數據分布分區段2.2.ASE優化方法分區Roundrobin分區(老的分區方式)鎖競爭水平分區(額外license)HashListRange循環2.2.ASE優化方法數據分布情況1>sp_helpartitionTBP_TRANSACTION2>gonametypepartition_typepartitionspartition_keys

TBP_TRANSACTIONbasetablerange48TRANSKEY(1rowaffected)

partition_namepartition_idpagesrow_countsegmentcreate_date

p20090174653476218473defaultNov25200911:53PMp200902762534819224defaultNov25200911:53PM。。。。。。p20120678721531587794526761591defaultDec2220118:03PMp20120780321537288042826809739defaultDec2220118:03PM。。。。。。p20121186721560010defaultDec2220118:03PMp20121288321565710defaultDec2220118:03PM

Partition_Conditions

VALUES<=('20090131')VALUES<=('20090228')。。。。。。VALUES<=('20121130')VALUES<=('20121231')

Avg_pagesMax_pagesMin_pagesRatio(Max/Avg)Ratio(Min/Avg)

317165104183313.2848300.000003(returnstatus=0)2.2.ASE優化方法通過查詢計劃看1>select*fromTBP_TRANSACTIONwhereTRANSKEYlike'20120821%'2>goQUERYPLANFORSTATEMENT1(atline1).STEP1ThetypeofqueryisSELECT.2operator(s)underroot|ROOT:EMITOperator(VA=2)|||RESTRICTOperator(VA=1)(0)(0)(0)(6)(0)|||||SCANOperator(VA=0)|||FROMTABLE|||TBP_TRANSACTION|||[EliminatedPartitions:1234567891011121314151617181920212223242526272829303132333435363738394041424345464748]|||Index:UNI_TBP_TRANSACTION|||ForwardScan.|||Positioningbykey.|||Keysare:|||TRANSKEYASC|||UsingI/OSize16Kbytesforindexleafpages.|||WithLRUBufferReplacementStrategyforindexleafpages.|||UsingI/OSize16Kbytesfordatapages.|||WithLRUBufferReplacementStrategyfordatapages.44直接找到44（=3×12+8）區2.2.ASE優化方法分區表索引類型Localindex縮小查找索引樹并行注意：分區列Globalindex精確查找注意：維護開銷Createtable…partitionby…(partition_name…onsegment)Createindex…localindex(partition_name…onsegment)2.2.ASE優化方法鎖用戶表鎖模式鎖競爭死鎖進程死鎖業務死鎖sp_lockssp_object_stats多tempdb2.2.ASE優化方法服務器維護ReorgUpdatestatistics索引有效性2.3.ASE數據庫