Informix SQL 性能调优

1. Informix SQL 性能调优 谭永贻 yytan@cn.ibm.com

2. 大 纲 高效的SQL语句 高效的索引 连接(Join) 大表操作(Big Table) 锁等待

3. 改写过滤条件（1） 与compress_date相关的过滤条件可以精简成：

4. 改写过滤条件（2） 与customer_type相关的过滤条件可以改写成：

5. 合并union all的各部分 合并union all的各部分，得到如下的SQL语句：

6. Union 和 Union All 在可以确定结果集不会有重复的情况下，使用 union all 而不是 union

7. 字符串的数据类型（1） 当字符串的长度小于或等于10时，考虑用char替代varchar或lvarchar

8. 字符串的数据类型（2） 当字符串的长度大于10时，考虑用varchar或lvarchar 替代char

9. 为了使数据库服务器可以更好的利用索引，尽量不要在过滤条件的数据列上进行运算为了使数据库服务器可以更好的利用索引，尽量不要在过滤条件的数据列上进行运算 不要在过滤条件的数据列上进行运算 如果在数据列num上建有索引

10. 正则表达式 • 尽量避免正则表达式的首位是通配符 • 首位的正则表达式无法使用索引 上面这条SQL语句的运行时间要大大长于下面这条SQL语句 • 尽量避免过于复杂的正则表达式

11. 子字符串 • 尽量避免使用“起始位置不为1”的子字符串 • 非首位的子字符串无法使用索引 上面这条SQL语句的运行时间要大大长于下面这条SQL语句

12. SQL Optimization – correlated self-join This SQL statement is a typical correlated self-join SQL statement. The distinct operation cannot be performed on the column a.pricing_plan_id, because it depends on the criteria in the sub-query also.

13. “预计运行时间”最长的SQL语句 • 找到“预计运行时间”最长的SQL语句，并进行调优

14. “实际运行时间”最长的SQL语句 • 找到“实际运行时间”最长的SQL语句，并进行调优

15. 大 纲 高效的SQL语句 高效的索引 连接(Join) 大表操作(Big Table) 锁等待

16. 建立索引 • 在哪些数据列上建立索引 • 用于连接条件的数据列 • 用于过滤条件的数据列 • 用于ORDER BY 或GROUP BY的数据列 • 避免在高度重复的数据列上建立索引 • 例如性别 • 保持索引的size较小

17. 顺序扫描与索引 • 找到顺序扫描次数较多的表，找到使用了顺序扫描的SQL语句，考虑在合适的数据列上建立索引 如何找到顺序扫描次数较多的表： • select p.tabname::char(20) tabname, t.nrows, p.seqscans from sysmaster:sysptprof p, systables t where p.tabname=t.tabname and t.tabid >99 and p.seqscans > 0 order by p.seqscans desc; //第一列为表名，第二列为表的数据行数，第三列为表的顺序扫描次数 如何找到使用了顺序扫描的SQL语句： • 打开SQL tracing，过一段时间后执行如下SQL语句 • select distinct sql_statement from sysmaster:syssqltrace t inner join sysmaster:syssqltrace_iter i on t.sql_id = i.sql_id where i.sql_itr_info='Seq Scan' order by 1;

18. 删除多余的索引 • 删除多余的Index: 系统中有可能存在大量的多余的index，通过删除多余的索引以提高insert、delete、update记录的性能,同时可以节省大量的不必要的存储。 • create index index_1 on table item (length, width); • create index index_2 on table item (length); • //考虑删除index_2

19. 索引设计弹性 分开的栏位与复合式的索引 弹性的设计索引, 避免使用正则表达式或子字

20. 索引设计僵化而无用 合并的栏位与独立的索引 僵化的设计 – 导致必须使用正则表达式或子字

21. 在线创建或删除索引 • 在线创建或删除索引 • CREATE INDEX … ONLINE • DROP INDEX … ONLINE • 在数据表t1上进行“在线创建或删除索引”时,可同时对数据表t1中的数据进行增、删、改、查操作

22. 大 纲 高效的SQL语句 高效的索引 连接(Join) 大表操作(Big Table) 锁等待

23. Inner Join Table T2 Right Outter Join Table T1 Table T2 Table T1 Full Outter Join Table T2 Table T1 Table T1 Table T2 Cross Join Left Outter Join Table T1 Table T2 Table T1 Table T2 ANSI SQL-99 Joins • 9.40 • Right Outer • Full Outer • Cross Joins • pre-9.40 • Inner Join • Left Outer Join

24. 避免笛卡尔积 尽量避免笛卡尔积。如果可能的话，加上连接条件，例如x.job_num = y.job_num。

25. 数据库优化 - 子查询

26. 大 纲 高效的SQL语句 高效的索引 连接(Join) 大表操作(Big Table) 锁等待

27. Temp table with no log; 大表可用创建临时表with no log 加速查询 • 把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。 • 但要注意：临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据。 • Select * from tab into temp tmp_tab with no log; • Create temp table tmp_tab(c1 integer) with no log; • 参考文章： http://www.cniug.org/ibm-informix-article/466-informixids115.html • Begin Work; • Select * from big_table where id < 1000000 into Tmp_C with no log; (创建临时表) • Create index idx_tmp_a on Tmp_c(c1); (创建临时索引) • Select * from Tmp_c..; (原耗时的 big_table 被小临时表Tmp_c取代) • Select * from Tmp_c..; (原耗时的 big_table 被小临时表Tmp_c取代) • Drop table Tmp_c; • commit work;

28. 需要全部的结果? • select * from customer c, cust_calls u where c.customer_num = u.customer_num； • 等待全部的结果; 等待耗时 • select first 10 * from customer c, cust_calls u where c.customer_num = u.customer_num • 前10行的结果; 快速返回

29. 报表查询分页- SELECTSKIP 1 • Informix 数据分页SQL功能 • 当查询结果返回大量数据情况下，比如报表查询。需要按一定条件排序提供分页呈现数据。 • INFORMIX实现方案

30. 报表查询分页- SELECTSKIP 2 • Informix 数据库提供了非常便捷、高效的SQL。 SELECT SKIP M FIRST N FROM TABLENAME WHERE 1=1 ORDER BY COL; • SQL应用举例 • 原始数据 • name course score • --------------------------------------- • 张三 语文 74 • 张三 数学 99 • 张三 物理 93 • 李四 物理 94 • 李四 语文 78 • 李四 数学 84

31. 报表查询分页- SELECTSKIP 3 • 第一页： select skip 0 first 2 * from test_rowcols where 1=1 order by score; name course score --------------------------------------- 张三 语文 74 李四 语文 78 • 第二页： select skip 2 first 2 * from test_rowcols where 1=1 order by score; name course score --------------------------------------- 张三 物理 85 李四 物理 86

32. 并行数据库查询（PDQ） • SET PDQPRIORITY 语句覆盖 PDQPRIORITY 环境变量（但是比 MAX_PDQPRIORITY 配置参数的优先级要低）。 SET PDQPRIORITY 的作用域对例程而言为本地作用域，且不会影响同一会话内的其它例程。 • SPL 例程中不支持 SET PDQPRIORITY 语句。 • 在 Dynamic Server 中，将 PDQ 优先级设置为一个值，该值小于 100 除以准备好的语句的最大数的商。例如，如果有 2 个准备好的语句是活动的，则应该将 PDQ 优先级设置为小于 50。 • 例如，假设 DBA 将 MAX_PDQPRIORITY 参数设置为 50。则用户输入以下 SET PDQPRIORITY 语句，将查询优先级级别设置为资源的 80%： • 当处理查询时，数据库服务器使用 MAX_PDQPRIORITY 值作为因数乘以由用户设置的优先级级别。数据库服务器以静默方式处理优先级级别为 40 的查询。此优先级级别代表用户指定的资源的 80% 的一半。 SET PDQPRIORITY 80

33. 加速大报表运行时间 • 充分利用晚间系统空闲CPU • 加速大报表运行时间 • 并行数据库查询（PDQ）是一种 Informix 数据库服务器功能，当数据库服务器处理由决策支持应用程序所初始化的查询时，该功能可以显著提高性能。PDQ 允许数据库服务器将查询一个方面的某个工作分发给几个处理器。例如：如果查询要求聚集，则数据库服务器可以将聚集工作分发给几个处理器。PDQ 还包含用于资源管理的工具。

34. 大 纲 高效的SQL语句 高效的索引 连接(Join) 大表操作(Big Table) 锁等待

35. 锁等待 >onstat -g sql IBM Informix Dynamic Server Version 11.50.FC7 -- On-Line -- Up 2 days 11:11:56 -- 5946944 Kbytes Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 2091448 - dbmps CR Not Wait 0 0 9.22 Off 2091355 - dbmps CR Not Wait 0 0 9.22 Off 2091310 - dbbms CR Not Wait 0 0 9.22 Off 2091304 - dbbms CR Not Wait 0 0 9.22 Off 2091270 - dbmps CR Not Wait 0 0 9.22 Off 2091235 - dbbms CR Not Wait 0 0 9.22 Off 2091203 - dbmps CR Not Wait 0 0 9.22 Off 2091072 - dbbms CR Not Wait 0 0 9.22 Off 2090996 SELECT dbbms CR Not Wait 0 0 9.22 Off 2090757 - dbbms CR Not Wait 0 0 9.22 Off 2090631 - dbbms CR Not Wait 0 0 9.22 Off

36. 找到锁等待较多的表 • 大量的锁等待会导致这种现象：虽然数据库服务器处于“CPU利用率不高，磁盘I/O不忙”的状态，但用户请求被响应的速度非常慢 • 如何找到锁等待较多的表 • database sysmaster; • select dbsname,tabname, sum(pf_rqlock) as locks, sum(pf_wtlock) as lockwaits, sum(pf_deadlk) as deadlocks from sysactptnhdr,systabnames where pf_wtlock > 0 and systabnames.partnum = sysactptnhdr.partnum group by dbsname,tabname order by lockwaits desc;

37. 如何减少锁等待 • 从不同的角度进行调整，以减少锁等待 • 使用合理的表结构 • 使用合理的索引 • 避免顺序扫描 • 使用合理的隔离级别：数据库提供了多种不同的隔离级别（isolation level），需要根据具体的应用场景采用合理的隔离级别，以提高并发性 • 合理选择表的lock mode：page或row • 合理选择session的锁等待时间

38. 隔离级别 • Informix有以下隔离级别(isolation level)： • Dirty Read • Committed Read • Committed Read Last Committed • Cursor Stability • Repeatable Read

39. Table Lock Mode • CREATE TABLE new_table (COL1INTEGER NOTNULL， COL2CHAR(2)， ) WITH NO LOG LOCK MODE (PAGE)； • ALTER TABLE [table name] LOCK MODE (ROW); • ALTER TABLE [table name] LOCK MODE (PAGE); 考虑使用LOCK MODE(ROW) ，以减少锁冲突

40. Session的锁等待时间 设置session的锁等待时间：

41. Onstat -p IBM Informix Dynamic Server Version 11.50.FC7 -- On-Line -- Up 2 days 15:53:09 -- 5946944 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 61018430 87963528 25339369866 99.78 10343275 18386065 3953776843 99.76 isamtot open start read write rewrite delete commit rollbk 18831403799 802254551 1196339034 9632752507 1594064087 15193015 5264467 2136785 1250643 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 184578.09 5829.75 1828 1228 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 3048796 2265 42555201010 7 0 932 112054564 35812959 ixda-RA idx-RA da-RA RA-pgsused lchwaits 7701975 1065094 35697835 44370719 237373895

42. Onstat –p Before IBM Informix Dynamic Server Version 11.50.FC7 -- On-Line -- Up 2 days 15:53:09 -- 5946944 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 61018430 87963528 25339369866 99.78 10343275 18386065 3953776843 99.76 isamtot open start read write rewrite delete commit rollbk 18831403799 802254551 1196339034 9632752507 1594064087 15193015 5264467 2136785 1250643 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 184578.09 5829.75 1828 1228 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 3048796 2265 42555201010 7 0 932 112054564 35812959 ixda-RA idx-RA da-RA RA-pgsused lchwaits 7701975 1065094 35697835 44370719 237373895

43. Onstat –p - Now onbms:/home/rj>onstat -p IBM Informix Dynamic Server Version 11.50.FC7 -- On-Line -- Up 2 days 14:57:32 -- 5946944 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 36278062 45017554 9406415640 99.62 3671881 7501156 347327282 99.19 isamtot open start read write rewrite delete commit rollbk 7417164104 43299505 275100469 5941851694 258019809 2070443 2431382 759755 30572 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 52670.81 3282.16 1708 977 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 4553642 265 10753114364 0 0 274 3167937 4003639 ixda-RA idx-RA da-RA RA-pgsused lchwaits 6925910 1036041 20021824 27910476 6609925

44. 隔离级别 表象 高响应时间。低CPU利用率。(High response time. Low CPU utilization) 原因 CRV 系统的rollbk 太高。(1250643/213785) 代表每三个交易中既有一个失败。失败的原因是"CR Not Wait"。“CR Not Wait”是应用程序不等待已锁的项目而直接放弃。 解决之道 1. 检查 Table 锁方式 (dbschema -d dbmps) 2. 改 Table 锁方式 page mode 为 row mode. alter table t1 modify lock mode (row) 2. 修改应用程序, 增加隔离级别的等待时间 SET ISOLATION TO COMMITTED READ; SET LOCK MODE TO WAIT 20;

45. 45