SYBASE IQ与ASE数据库使用经验交流

第 12 页
1.2. IQ优化方法
sql语句的执行计划
执行计划是分析、比较语句效率的重要手段
第 13 页
1.2. IQ优化方法
sql语句的执行计划
生成执行计划 Query_Plan=on
当打开此选项时，Sybase IQ 将产生有关查询的消息。这些消息包括有关使
用连接索引、连接顺序、查询的连接算法以及使用数据提取选项提取的列等 内容的消息。 Query_Detail=on 当 QUERY_DETAIL 和 QUERY_PLAN （或 QUERY_PLAN_AS_HTML）都打开时， Sybase IQ 将在产生其查询计划时显示有关查询的其它信息。
第 14 页
1.2. IQ优化方法
Query_Timing=on
此选项控制对查询引擎中有关子查询及其它某些重复函数的计时统计信 息的收集。
Query_Plan_After_Run=on
打开 QUERY_PLAN_AFTER_RUN 时，在已经运行完查询之后，将打印查询 计划。 要让此选项工作，必须将 QUERY_PLAN 选项设置为 ON （缺省值）。 可 以将此选项与QUERY_DETAIL 一起使用，以便在查询计划报告中生成其 它信息。
代理表方法
bcp方法
第 19 页
1.3. IQ数据库数据迁移方法
Load table 方法
单线索装载
LOAD TABLE service ( service_key '|!' , call_waiting_flag '|!' , caller_id_flag '|!' , voice_mail_flag '|!' , cellular_flag '|!' , internet_flag '|!' , isdn_flag '\x0a' ) FROM '/ tmp /import/service.dat' ESCAPES OFF QUOTES OFF NOTIFY 1000000 WITH CHECKPOINT ON; COMMIT;
第 26 页
1.3. IQ数据库数据迁移方法
小结1
LOAD TABLE 方法用于将数据从文件装载到 IQ 数据 库中； 数据提取选项方法用于将数据从 IQ 数据库中导出到 文件中； INSERT LOCATION 、 代理表方法用于将数据从另一个 数据库传输到当前数据库； BCP 方法用于将数据从 IQ 数据库导出到文件中。
CREATE EXTERNLOGIN fred ASEserver.my_asedb ’ TO ASEserver {SELECT int_col FROM SQL_Types}; REMOTE LOGIN frederick IDENTIFIED BY banana
第 24 页
1.3. IQ数据库数据迁移方法
节省存储空间
第4页
1.1. IQ特点
表级锁
读不封锁读 读不封锁写 写不封锁读 写封锁写
不等待其他进程释放锁资源,应用进程得到错误信息并立即返回
第5页
1.1. IQ特点
适合做大批量数据处理
复杂查询速度快 批量加载数据速度快
不适合做联机高并发业务
锁机制 插入、更新等相对较慢
第6页
1.2. IQ优化方法
SQL优化 索引选择
SQL语句的执行计划
第7页
1.2. IQ优化方法
IQ中SQL优化思路
对于最关键的地方进行优化 表设计,索引设计是关键 对于关键SQL查看IQ查询计划，进行诊断然后优化SQL 使用工具监控IQ收集监控统计信息 调整数据库/Server选项选项
如将 QUERY_PLAN_AS_HTML 选项设为 ON 并用
QUERY_PLAN_AS_HTML_DIRECTORY 选项指定了一个目录，则 Sybase IQ 会将 HTML 查询计划写入该指定目录中。
第 16 页
1.2. IQ优化方法
NoExec = ‘On’
在确定如何处理查询时，IQ 优化程序将生成查询计划，以映射它如何计 划让查询引擎处理查询
第 17 页
1.2. IQ优化方法
优化建议:
尽量避免循环单条处理数据,而采用批量处理 尽量避免大批量远程插入数据,而使用加载数据
注意索引的使用
COMMIT
第 18 页
1.3. IQ数据库数据迁移方法
Load table 方法 数据提取选项方法 Insert location 方法
• 1、日志查看（logfiles）。 • 2、sp_iqcontext • 3、sp_iqstatus • ………
第 29 页
目录
1
2 3
IQ特点与优化方法 ASE特点与优化方法 Q&A
第 30 页
2.1. ASE特点
适合做联机高并发业务
短小精悍、快进快出，快速响应事务处理
第 31 页
第 10 页
1.2. IQ优化方法
LF索引
关联查询的关联字段 GROUP BY、ORDER BY中的字段 MIN、MAX、SUM、AVG、COUNT等聚集函数的参数字段 COUNT DISTINCT、SELECT DISTINCT WHERE子句中等于、不等于、>、>=、<、<=、BETWEEN、IN、
第 34 页
2.2. ASE优化方法
语句优化
性能更优化的查询方式
• • • • • or是否可转换为union。举例(or .Vs. union) 类型匹配 update变长字段 大事务多次提交 …
范围条件等
第 11 页
1.2. IQ优化方法
HG索引
关联查询的关联字段 SELECT DISTINCT、COUNT DISTINCT COUNT、MIN、MAX
GROUP BY、ORDER BY
IN的参数 等于、不等于
HNG索引
AVG、SUM的参数字段 范围（ BETWEEN或范围比较）中出现的字段 Root String 查询。例如：WHERE cust_name like “Stan%”。（其它情况的 like使用FP索引）。
代理表方法
1. 创建远程服务器 2. 创建外部登录凭据 3. 创建代理表定义
如果该表已存在于远程存储位置，则使用 CREATE EXISTING TABLE 语句。此语句为远程服务器上现有的表 定义代理表。 如果该表未存在于远程存储位置，则使用 CREATE TABLE 语句。此语句在远程服务器上创建新表，并且还为该表定 义代理表。
注意 ： 左边的 LOAD 使用多线索装载 ， 是最 推荐的方式 。 但要求数 据文件中最后一个字段也 必须有 “ 列分隔符 ” 。
第 21 页
1.3. IQ数据库数据迁移方法
数据提取选项方法
SET TEMPORARY OPTION temp_extract_name1 = '/amls/tmp/ QTBP_STD_TRANSACTION.txt'; SET TEMPORARY OPTION Temp_Extract_Column_Delimiter ='|!'; SELECT * FROM QTBP_STD_TRANSACTION where TRANS_DATE='20120829'; 数据提取功能可大大提高对大型结果集执行查询的性能 。
注意 ： 左边的 LOAD 使用单线索装载 ， 没有 多线索装载好。
第 20 页
1.3. IQ数据库数据迁移方法
Load table 方法
多线程装载
LOAD TABLE service ( service_key '|!' , call_waiting_flag '|!' , caller_id_flag '|!' , voice_mail_flag '|!' , cellular_flag '|!' , internet_flag '|!' , isdn_flag '|!' ) FROM '/ tmp /import/service.dat' ESCAPES OFF QUOTES OFF NOTIFY 1000000 ROW DELIMITED BY '\x0a' WITH CHECKPOINT ON; COMMIT;
FP索引
Ad-Hoc关联（join）字段适合建立FP索引（热点查询中的关联字
段，这种类型的查询是高度动态的，事先不能确定）。根据字段
的“基数”确定具体的FP类型 在SELECT列表中出现的字段
在一些计算表达式中，例如SUM（A+B）
在WHERE子句的LIKE条件中或SUBSTR函数中，例如：column like “%x”
第 27 页
1.3. IQ数据库数据迁移方法
小结2
IQ数据库导入，推荐： LOAD TABLE IQ数据库导出，推荐：数据提取选项法 两个库之间的数据传输，推荐： INSERT LOCATION 方法，效率较高，且对于包含有柜员手工录入的数据，
大大减少了出错的几率。
第 28 页
1.4 IQ数据库系统管理
第 25 页
1.3. IQ数据库数库的 BCP 语法与 ASE 数据库的 BCP 语法相 同 。但是 IQ 数据库对 BCP IN 支持的不是很好，不建 议使用。
bcp table_name out datafile –U username -P password -S server
第8页
1.2. IQ优化方法
索引选择
索引选择因素
字段中唯一值的数量（Number of unique value）
字段的数据类型
查询的类型（Type of query） 磁盘空间（Disk space usage）
第9页
1.2. IQ优化方法
索引选择
主要IQ索引的适用情况
第 15 页
1.2. IQ优化方法
Index_Advisor=on
当此选项设置为 ON 时，索引顾问会在 Sybase IQ 查询计划中打印索引 建议
Query_Plan_As_Html=on
QUERY_PLAN_AS_HTML 导致以 HTML 格式生成图形化查询计划。
Query_Plan_As_Html_Directory=‘/tmp’
第 23 页
1.3. IQ数据库数据迁移方法
Insert location 方法（步骤）
1. 2. 3. 4. 将远程服务器添加到 interfaces 文件中。 创建远程服务器。 创建外部登录凭据。 使用 INSERT... LOCATIION 使用 INSERT... LOCATIION CREATE SERVER ASEserver 只有登录名和 DBA 帐户才能添加或修改外部登录凭据。 以下语句允许本地用户 fred 通过使用远程登录 frederick CLASS 'asejdbc' 和口令banana 获得对服务器 ASEserver 的访问权限。 INSERT local_SQL_Types LOCATION ‘ ’ USING ‘rimu:6666/ my_ased
2.2. ASE优化方法
优化三个方面
应用程序 客户端与服务器交互 服务器
第 32 页
2.2. ASE优化方法
应用程序优化
逻辑优化 语句优化 用户对象设计
第 33 页
2.2. ASE优化方法
逻辑优化
根据业务,来调整实现业务的逻辑与方式,尽量减少访问 数据库资源,或降低业务实现复杂度
第 22 页
1.3. IQ数据库数据迁移方法
Insert location 方法
INSERT...LOCATION 允许插入来自 Adaptive Server Enterprise或 Sybase IQ 数据库的数据 。 Sybase IQ 连接到指定的服务器和数据库 ， 然后返回 来自对这些表所做查询的结果 ， 以将结果插入当前数 据库中。
SYBASE IQ与ASE数据库 使用经验交流
软件开发中心 应用开发六部
目录
1 2 3
IQ特点与优化方法 ASE特点与优化方法 Q&A
第2页
目录
1 2 3
IQ特点与优化方法 ASE特点与优化方法 Q&A
第3页
1.1. IQ特点
列式存储与压缩存储
读取数据快
数据存储方式
存储优化FP索引