SYBASEIQ与ASE数据库使用经验交流

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数据库