SYBASEIQ與ASE數(shù)據(jù)庫(kù)使用經(jīng)驗(yàn)交流

SYBASEIQ與ASE數(shù)據(jù)庫(kù)

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

Query_Plan=on

當(dāng)打開此選項(xiàng)時(shí)，SybaseIQ將產(chǎn)生有關(guān)查詢的消息。這些消息包括有關(guān)使用連接索引、連接順序、查詢的連接算法以及使用數(shù)據(jù)提取選項(xiàng)提取的列等內(nèi)容的消息。

Query_Detail=on

當(dāng)QUERY_DETAIL和QUERY_PLAN（或QUERY_PLAN_AS_HTML）都打開時(shí)，SybaseIQ將在產(chǎn)生其查詢計(jì)劃時(shí)顯示有關(guān)查詢的其它信息。1.2.IQ優(yōu)化方法Query_Timing=on

此選項(xiàng)控制對(duì)查詢引擎中有關(guān)子查詢及其它某些重復(fù)函數(shù)的計(jì)時(shí)統(tǒng)計(jì)信息的收集。Query_Plan_After_Run=on

打開QUERY_PLAN_AFTER_RUN時(shí)，在已經(jīng)運(yùn)行完查詢之后，將打印查詢計(jì)劃。

要讓此選項(xiàng)工作，必須將QUERY_PLAN選項(xiàng)設(shè)置為ON（缺省值）?？梢詫⒋诉x項(xiàng)與QUERY_DETAIL一起使用，以便在查詢計(jì)劃報(bào)告中生成其它信息。1.2.IQ優(yōu)化方法Index_Advisor=on

當(dāng)此選項(xiàng)設(shè)置為ON時(shí)，索引顧問會(huì)在SybaseIQ查詢計(jì)劃中打印索引建議Query_Plan_As_Html=on

QUERY_PLAN_AS_HTML導(dǎo)致以HTML格式生成圖形化查詢計(jì)劃。Query_Plan_As_Html_Directory=‘/tmp’

如將QUERY_PLAN_AS_HTML選項(xiàng)設(shè)為ON并用QUERY_PLAN_AS_HTML_DIRECTORY選項(xiàng)指定了一個(gè)目錄，則SybaseIQ會(huì)將HTML查詢計(jì)劃寫入該指定目錄中。1.2.IQ優(yōu)化方法NoExec=‘On’

在確定如何處理查詢時(shí)，IQ優(yōu)化程序?qū)⑸刹樵冇?jì)劃，以映射它如何計(jì)劃讓查詢引擎處理查詢1.2.IQ優(yōu)化方法優(yōu)化建議:盡量避免循環(huán)單條處理數(shù)據(jù),而采用批量處理盡量避免大批量遠(yuǎn)程插入數(shù)據(jù),而使用加載數(shù)據(jù)注意索引的使用COMMIT1.3.IQ數(shù)據(jù)庫(kù)數(shù)據(jù)遷移方法Loadtable方法數(shù)據(jù)提取選項(xiàng)方法Insertlocation方法代理表方法bcp方法1.3.IQ數(shù)據(jù)庫(kù)數(shù)據(jù)遷移方法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數(shù)據(jù)庫(kù)數(shù)據(jù)遷移方法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使用多線索裝載，是最推薦的方式。但要求數(shù)據(jù)文件中最后一個(gè)字段也必須有“列分隔符”。1.3.IQ數(shù)據(jù)庫(kù)數(shù)據(jù)遷移方法數(shù)據(jù)提取選項(xiàng)方法SETTEMPORARYOPTIONtemp_extract_name1='/amls/tmp/QTBP_STD_TRANSACTION.txt';

SETTEMPORARYOPTIONTemp_Extract_Column_Delimiter='|!';

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

SybaseIQ連接到指定的服務(wù)器和數(shù)據(jù)庫(kù)，然后返回來自對(duì)這些表所做查詢的結(jié)果，以將結(jié)果插入當(dāng)前數(shù)據(jù)庫(kù)中。1.3.IQ數(shù)據(jù)庫(kù)數(shù)據(jù)遷移方法Insertlocation方法（步驟）將遠(yuǎn)程服務(wù)器添加到interfaces文件中。

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

bcptable_nameoutdatafile–Uusername-Ppassword-Sserver1.3.IQ數(shù)據(jù)庫(kù)數(shù)據(jù)遷移方法小結(jié)1LOADTABLE方法用于將數(shù)據(jù)從文件裝載到IQ數(shù)據(jù)庫(kù)中；數(shù)據(jù)提取選項(xiàng)方法用于將數(shù)據(jù)從IQ數(shù)據(jù)庫(kù)中導(dǎo)出到文件中；

INSERTLOCATION、代理表方法用于將數(shù)據(jù)從另一個(gè)數(shù)據(jù)庫(kù)傳輸?shù)疆?dāng)前數(shù)據(jù)庫(kù)；

BCP方法用于將數(shù)據(jù)從IQ數(shù)據(jù)庫(kù)導(dǎo)出到文件中。1.3.IQ數(shù)據(jù)庫(kù)數(shù)據(jù)遷移方法小結(jié)2IQ數(shù)據(jù)庫(kù)導(dǎo)入，推薦：

LOADTABLEIQ數(shù)據(jù)庫(kù)導(dǎo)出，推薦：數(shù)據(jù)提取選項(xiàng)法兩個(gè)庫(kù)之間的數(shù)據(jù)傳輸，推薦：

INSERTLOCATION方法，效率較高，且對(duì)于包含有柜員手工錄入的數(shù)據(jù)，大大減少了出錯(cuò)的幾率。1.4IQ數(shù)據(jù)庫(kù)系統(tǒng)管理1、日志查看（logfiles）。2、sp_iqcontext3、sp_iqstatus………目錄ASE特點(diǎn)與優(yōu)化方法2IQ特點(diǎn)與優(yōu)化方法1Q&A32.1.ASE特點(diǎn)適合做聯(lián)機(jī)高并發(fā)業(yè)務(wù)

短小精悍、快進(jìn)快出，快速響應(yīng)事務(wù)處理2.2.ASE優(yōu)化方法優(yōu)化三個(gè)方面應(yīng)用程序客戶端與服務(wù)器交互服務(wù)器2.2.ASE優(yōu)化方法應(yīng)用程序優(yōu)化邏輯優(yōu)化語句優(yōu)化用戶對(duì)象設(shè)計(jì)2.2.ASE優(yōu)化方法邏輯優(yōu)化根據(jù)業(yè)務(wù),來調(diào)整實(shí)現(xiàn)業(yè)務(wù)的邏輯與方式,盡量減少訪問數(shù)據(jù)庫(kù)資源,或降低業(yè)務(wù)實(shí)現(xiàn)復(fù)雜度2.2.ASE優(yōu)化方法語句優(yōu)化性能更優(yōu)化的查詢方式or是否可轉(zhuǎn)換為union。舉例(or.Vs.union)類型匹配update變長(zhǎng)字段大事務(wù)多次提交…索引使用情況表達(dá)式與索引2.2.ASE優(yōu)化方法用戶對(duì)象設(shè)計(jì)鎖模式在OLTP系統(tǒng)中，只保留必要的索引對(duì)于DSS系統(tǒng)中，可建立更多的索引以加快Select的速度在經(jīng)常連接的列上建索引Orderby的列上建索引…2.2.ASE優(yōu)化方法調(diào)優(yōu)方法與工具setstatisticsio、setstatisticstimeisql–pDBArtisanProActiveDBAsp_sysmon2.2.ASE優(yōu)化方法客戶端與服務(wù)器交互存儲(chǔ)過程大尺寸數(shù)據(jù)包服務(wù)器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優(yōu)化方法客戶端與服務(wù)器交互游標(biāo)游標(biāo)一次返回多行 execsql[atconnection_name]opencursor_name[row_count=n]2.2.ASE優(yōu)化方法服務(wù)器優(yōu)化CPU(engine)內(nèi)存I/O鎖2.2.ASE優(yōu)化方法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優(yōu)化方法內(nèi)存高速緩存sp_configure‘maxmemory’sp_cacheconfig、sp_poolconfigsp_configure‘procedurecachesize’熱點(diǎn)表行級(jí)鎖命名cache分區(qū)2.2.ASE優(yōu)化方法I/OKernelAsyncI/OAsyncPrefech內(nèi)存sp_cacheconfigsp_poolconfig數(shù)據(jù)分布分區(qū)段2.2.ASE優(yōu)化方法分區(qū)Roundrobin分區(qū)(老的分區(qū)方式)鎖競(jìng)爭(zhēng)水平分區(qū)(額外license)HashListRange循環(huán)2.2.ASE優(yōu)化方法數(shù)據(jù)分布情況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優(yōu)化方法通過查詢計(jì)劃看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）區(qū)2.2.ASE優(yōu)化方法分區(qū)表索引類型Localindex縮小查找索引樹并行注意：分區(qū)列Globalindex精確查找注意：維護(hù)開銷Createtable…partitionby…(partition_name…onsegment)Createindex…localindex(partition_name…onsegment)2.2.ASE優(yōu)化方法鎖用戶表鎖模式鎖競(jìng)爭(zhēng)死鎖進(jìn)程死鎖業(yè)務(wù)死鎖sp_lockssp_object_stats多tempdb2.2.ASE優(yōu)化方法服務(wù)器維護(hù)ReorgUpdatestatistics索引有效性2.3.ASE數(shù)據(jù)庫(kù)