Informix 11 SQL性能调整

1 前言.........................................................................................................................................2
2 实验环境.................................................................................................................................2
3 SQL执行计划..........................................................................................................................2
3.1 获取执行计划...........................................................................................................2
3.2 执行计划分析示例...................................................................................................5
3.2.1 实例1：表的访问方式，sequencial scan 与index scan..............................5
3.2.2 实例2：连接方式............................................................................................8
4 统计信息............................................................................................................................... 11
4.1 统计信息基本知识................................................................................................. 11
4.2 怎样更新统计信息................................................................................................. 11
4.3 查看数据表的数据分布信息.................................................................................12
4.4 统计信息对SQL执行效率的影响.......................................................................13
4.4.1 实例1：..........................................................................................................13
4.4.2 实例2：..........................................................................................................14
5 SQL Directives.......................................................................................................................20
5.1 什么是SQL Directives...........................................................................................20
5.2 INFORMIX 有哪些SQL Directives：.................................................................20
5.3 怎样使用SQL Directives.......................................................................................20
5.3.1 Using Directives: ORDERED......................................................................20
5.3.2 Using Directives: INDEX / AVOID_INDEX...............................................20
5.3.3 Using Directives: FULL / AVOID_FULL....................................................20
5.3.4 Using Directives: JOIN METHOD..............................................................21
5.3.5 Using Directives: OPTIMIZATION GOAL ................................................21
5.3.6 一个复杂SQL的例子...................................................................................21
5.3.6.1 不加Directives的情况..........................................................................21
5.3.6.2 加Directives的情况..............................................................................22
6 SQL Tracing...........................................................................................................................24
6.1 打开SQL Tracing...................................................................................................26
6.1.1 通过设置ONCONFIG 文件参数：..............................................................26
6.1.2 通过Admin Command...................................................................................27
6.1.3 一个SQLTracing的例子：...........................................................................27
7 INFORMIX SQL调整检查列表...........................................................................................29
7.1 在select语句中应指明需要访问的列名，而不要用*........................................29
7.2 使用first, skip关键词...........................................................................................29
7.3 避免不必要的算术运算符.....................................................................................30
7.4 避免不必要的函数运算.........................................................................................30
7.5 使用exists替代distinct.........................................................................................31
7.6 Use OR instead of UNION on the same table.........................................................31
7.7 Use BETWEEN instead of IN ................................................................................32
7.8 正确使用index scan 和sequential scan ...............................................................32
8 总结.......................................................................................................................................36
1 前言
SQL 语言是关系型数据库与外界交互的重要途径，书写高效率的SQL 是每一个DBA 和开
发人员必备的技能。众所周知，不同的数据库各有所长，SQL 也有不同，为了帮助应用开
发者在最新版的INFORMIX数据库上书写高效的SQL，利用INFORMIX的最新功能对SQL
语句进行SQL 调优、追踪，本文以调整SQL 执行效率为最终目标，给大家介绍如何查看
INFORMIX 的SQL 执行计划，如何通过统计信息，SQLDirectives 调整执行计划，如何通
过SQL Drill-down监控SQL的执行效率，并且总结了书写SQL语句时若干需要注意的地方，
可以作为一个实用的SQL Cheklist使用。本文尽量减少理论的论述而注重实例分析和实际操
作，相信对大家一定会有所帮助。