Informix查询语句SELECT的优化.docx
- 文档编号:29771579
- 上传时间:2023-07-26
- 格式:DOCX
- 页数:35
- 大小:38.56KB
Informix查询语句SELECT的优化.docx
《Informix查询语句SELECT的优化.docx》由会员分享,可在线阅读,更多相关《Informix查询语句SELECT的优化.docx(35页珍藏版)》请在冰豆网上搜索。
Informix查询语句SELECT的优化
查询语句(SELECT)的优化
程序设计中的一个著名定律是20%的代码用去了80%的时间,在数据库应用程序中也同样如此。
数据库应用程序的优化通常可分为两个方面:
源代码的优化和SQL语句的优化。
源代码的优化在时间成本和风险上代价很高;另一方面,源代码的优化对数据库系统性能的提升收效有限。
许多程序员认为查询优化是DBMS(数据库管理系统)的任务,与程序员所编写的SQL语句关系不大,这是错误的。
一个好的查询计划往往可以使程序性能提高数十倍。
查询计划是用户所提交的SQL语句的集合,查询规划是经过优化处理之后所产生的语句集合。
DBMS处理查询计划的过程是这样的:
在做完查询语句的词法、语法检查之后,将语句提交给DBMS的查询优化器,优化器做完代数优化和存取路径的优化之后,由预编译模块对语句进行处理并生成查询规划,然后在合适的时间提交给系统处理执行,最后将执行结果返回给用户。
虽然现在的数据库产品在查询优化方面已经做得越来越好,但由用户提交的SQL语句是系统优化的基础,很难设想一个原本糟糕的查询计划经过系统的优化之后会变得高效,因此用户所写语句的优劣至关重要。
1、对查询语句进行优化的理由
下列几方面的原因是我们进行SQL语句优化的理由:
◆SQL语句是对数据库(数据)进行操作的惟一途径;
◆SQL语句消耗了70%~90%的数据库资源;
◆SQL语句独立于程序设计逻辑,相对于对程序源代码的优化,对SQL语句的优化在时间成本和风险上的代价都很低;
◆SQL语句可以有不同的写法;
◆SQL语句易学,难精通。
从大多数数据库应用系统的实例来看,查询操作在各种数据库操作中所占据的比重最大,而查询操作所基于的SELECT语句在SQL语句中又是代价最大的语句。
2、查询语句(SELECT)的优化建议
(1)、合理使用索引:
where子句中变量顺序应与索引字键顺序相同.
如:
create index test_idx on test(bdh, rq, xz)
索引字键顺序:
首先是保单号bdh,其次是日期rq,最后是险种xz,所以where子句变量顺序应是where bdh〈=“P1234"and rq=“06/06/1999”and xz=“DAA”,不应是where xz=“DAA” and rq=“06/06/1999” and bdh 〈=“P1234”这样的不按索引字键顺序写法。
(2)、将最具有限制性的条件放在前面,大值在前,小值在后。
如:
where colA〈=10000 AND colA>=1 效率高
where colA>=1 AND colA<=10000 效率低
(3)、避免采用MATCHES和LIKE通配符匹配查询
通配符匹配查询特别耗费时间。
即使在条件字段上建立了索引,在这种情况下也还是采用顺序扫描的方式.
例如语句:
SELECT * FROM customer WHERE zipcode MATCHES “524*”
可以考虑将它改为SELECT * FROM customer WHERE ZipCode<=“524999” AND ZipCode 〉=“524000”,则在执行查询时就会利用索引来查询,显然会大大提高速度。
(4)、避免非开始的子串
例如语句:
SELECT * FROM customer WHERE zipcode[2,3] 〉“24”,在where子句中采用了非开始子串,因而这个语句也不会使用索引。
(5)、避免相关子查询
一个字段的标签同时在主查询和where子句中的查询中出现,那么很可能当主查询中的字段值改变之后,子查询必须重新查询一次。
查询嵌套层次越多,效率越低,因此应当尽量避免子查询.如果子查询不可避免,那么要在子查询中过滤掉尽可能多的行.
例如:
将下面的语句
select bdh,bf from TabA
where item IN (select item form TabB where TabB.num=50)
改为:
select bdh,bf from TabA, TabB
where TabA。
item=TabB.item AND TabB.num=50
(6)、避免或简化排序
应当简化或避免对大型表进行重复的排序.当能够利用索引自动以适当的次序产生输出时,优化器就避免了排序的步骤。
以下是一些影响因素:
◆索引中不包括一个或几个待排序的字段;
◆group by或order by子句中字段的次序与索引的次序不一样;
◆排序的字段来自不同的表。
为了避免不必要的排序,就要正确地增建索引,合理地合并数据库表(尽管有时可能影响表的规范化,但相对于效率的提高是值得的).如果排序不可避免,那么应当试图简化它,如缩小排序的字段的范围等。
(7)、消除对大型表行数据的顺序存取
在嵌套查询中,对表的顺序存取对查询效率可能产生致命的影响.比如采用顺序存取策略,一个嵌套3层的查询,如果每层都查询1000行,那么这个查询就要查询10亿行数据。
避免这种情况的主要方法就是对连接的字段进行索引。
例如,两个表:
学生表(学号、姓名、年龄……)和选课表(学号、课程号、成绩)。
如果两个表要做连接,就要在“学号”这个连接字段上建立索引。
还可以使用并集来避免顺序存取。
尽管在所有的检查列上都有索引,但某些形式的where子句强迫优化器使用顺序存取。
下面的查询将强迫对orders表执行顺序操作:
SELECT * FROM orders WHERE (cust_num=126 AND order_num>1001) OR order_num=1008
虽然在cust_num和order_num上建有索引,但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。
因为这个语句要检索的是分离的行的集合,所以应该改为如下语句:
SELECT * FROM orders WHERE cust_num=126 AND order_num>1001
UNION
SELECT * FROM orders WHERE order_num=1008
这样就能利用索引路径处理查询。
(8)、对于大数据量的求和应避免使用单一的sum命令处理,可采用group by方式与其结合,有时其效率可提高几倍甚至百倍.
(9)、避免会引起磁盘读写的rowid操作。
在where子句中或select语句中,用rowid要产生磁盘读写,是一个物理过程,会影响性能。
(10)、使用临时表加速查询
把表的一个子集进行排序并创建临时表,有时能加速查询。
它有助于避免多重排序操作,而且在其他方面还能简化优化器的工作.
但要注意:
临时表创建后不会反映主表的修改。
在主表中数据频繁修改的情况下,注意不要丢失数据。
五、其他措施
如何优化一套数据库应用程序,除了以上所述的措施外,还有一些分析并提高效率的措施在实际工作中亦应注意使用.
(1)、利用set explain on语句来分析数据库查找策略.
当发现某一部分INFORMIX语句运行特别慢又找不到原因时,可在程序中的查询语句之前加入“set explain on”语句,当程序运行时,在程序运行的当前目录下产生一个“sqexplain.out”文件,记录了INFORMIX数据库服务器采用何种优化策略来查找数据库。
在该文件中可以发现查找中有无使用索引条件,估计的查找代价等信息。
(2)、数据库在做dbimport后应运行update statistics语句。
当数据库系统用dbimport实用程序完成数据库装载后,应运行update statistics数据库语句保证系统表中统计信息准确,否则将影响数据库优化器的策略和系统运行性能。
例如:
select * from test where bdh matches “PC2002*”
test表在bdh字段上建立了索引,但在数据库运行update statistics前,数据库优化查找策略是按顺序查找而不是按索引查找,严重影响了查找速度。
(3)、经常插入和删除的大表应定期运行update statistics high语句。
对经常插入和删除的大表应定期运行update statistics语句保证系统表中统计信息准确,保证数据库优化器作出正确的优化策略提高系统运行性能。
(4)、对大文件采用load命令装载入库前可先去掉原有的日志方式和去掉索引,等load装载完成后再重建索引和日志,能大幅提高装载效率,避免出错产生。
◆去掉原有的日志方式:
ontape -S -N dbname
◆删除索引:
delete index indexname
◆用load 装入数据:
load from filename insert into tabname
◆重建索引:
create index to indexname on tabname(colA,colB,……)
◆重建日志:
ontape -C -B dbname
值此春节之际,献给广大informix学习者、爱好者~~~~~~~
聚集函数:
avg,求平均值
count,统计记录的条数
max,求最大值
min,求最小值
range,计算所选行的最大值与最小值的差
stdev,计算所选行的标准偏差
sum,求和函数
variance,函数返回值样本的方差做为所有选择行的方差的无偏估计.
它的公式,(sum(xi**2)-sum(xi)**2)/N)/(N—1)
其中xi是列中的每个值,N是列中值的总和.
时间函数:
day,mdy,month,weekday,year 这些函数返回与用来调用函数的表达式或自身变量的值。
Current返回当前的日期和时间值,可以用extend函数来调整date或datetime值的精度。
使用day和current 函数来将列值与当前日期进行比较。
Date函数将字符串函数转换为DATE值.例date(‘12/7/04')
To_char函数将datetime和date值转化为字符值。
To_date函数将字符值转化为datetime类型的值.例to_date(“1978—10—07 10:
00" ,"%Y-%m—%d %H:
%M)
基数函数:
cardinality(仅适用IDS)函数对集合包含的元素数目计数。
智能大对象函数,(仅适用与IDS)
filetoblob( ),将文件复制到BLOB列中
filetoclob( ),将文件复制到CLOB列中
locopy( ),将BLOB或CLOB类型的数据复制到另一个BLOB或CLOB列中
lotofile( ),将BLOB或CLOB复制到文件中
字符串处理函数:
lower,将字符串中每个大写字母转换为小写字母
upper,将字符串中每个小写字母转换为大写字母
initcap,将字符串中每个词的首写字母转换成大写
replace,将字符串中的某一组字符转换成其他字符,例replace(col,”each",”eve”)
substr,返回字符串中的某一部分,例substr(col,1,2)
substring,返回字符串中的某一部分,例substring(col,from 1 to 4)
lpad,使用lpad函数已用重复次数达到必要次数的字符序列在左边填充或截断的字符串的副本,这取决于字符串中填充部分的指定长度。
举例:
字段 col 为char(15)类型,select lpad(col,21,”_") from tab_name则显示为在col前加上六个_.
Rpad,使用rpad函数已用重复次数达到必要次数的字符序列在右边填充或截断的字符串的副本,这取决于字符串中填充部分的指定长度.
举例:
字段col为char(15)类型,select rpad(col,21,”_") from tab_name则显示为在col后边加上六个_。
其他函数:
hex,返回表达式的十六进制数
round,返回表达式的四舍五入值
trunc,返回表达式的截断值
length,计算表达式的长度
user,返回执行查询的用户的用户名(登陆帐户名)
today,返回当前系统日期
dbservername,返回数据库服务器的名称,同sitename
dbinfo,返回数据库的相关信息
decode,函数来将一个具有一个值的表达式转换为另一个值
decode(test,a,a_value,b,b_value,c,c_value……),decode函数不支持TEXT和BYTE类型。
Nvl,来将求值为空的表达式转化为另一个想要指定的值。
另外还可以在select语句中使用存储过程,如select spl($test) from tab_name
如有不对或不全面的地方请大家跟贴说明,谢谢.
1.合理使用索引
索引是数据库中重要的数据结构,它的根本目的就是为了提高查询效率。
现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构。
索引的使用要恰到好处,其使用原则如下:
●在经常进行连接,但是没有指定为外键的列上建立索引,而不经常连接的字段则由优化器自动生成索引.
●在频繁进行排序或分组(即进行group by或order by操作)的列上建立索引。
●在条件表达式中经常用到的不同值较多的列上建立检索,在不同值少的列上不要建立索引。
比如在雇员表的“性别”列上只有“男”与“女"两个不同值,因此就无必要建立索引。
如果建立索引不但不会提高查询效率,反而会严重降低更新速度。
●如果待排序的列有多个,可以在这些列上建立复合索引(compound index)。
●使用系统工具。
如Informix数据库有一个tbcheck工具,可以在可疑的索引上进行检查。
在一些数据库服务器上,索引可能失效或者因为频繁操作而使得读取效率降低,如果一个使用索引的查询不明不白地慢下来,可以试着用tbcheck工具检查索引的完整性,必要时进行修复。
另外,当数据库表更新大量数据后,删除并重建索引可以提高查询速度。
2.避免或简化排序
应当简化或避免对大型表进行重复的排序.当能够利用索引自动以适当的次序产生输出时,优化器就避免了排序的步骤。
以下是一些影响因素:
●索引中不包括一个或几个待排序的列;
●group by或order by子句中列的次序与索引的次序不一样;
●排序的列来自不同的表.
为了避免不必要的排序,就要正确地增建索引,合理地合并数据库表(尽管有时可能影响表的规范化,但相对于效率的提高是值得的)。
如果排序不可避免,那么应当试图简化它,如缩小排序的列的范围等。
3.消除对大型表行数据的顺序存取
在嵌套查询中,对表的顺序存取对查询效率可能产生致命的影响。
比如采用顺序存取策略,一个嵌套3层的查询,如果每层都查询1000行,那么这个查询就要查询10亿行数据。
避免这种情况的主要方法就是对连接的列进行索引。
例如,两个表:
学生表(学号、姓名、年龄……)和选课表(学号、课程号、成绩)。
如果两个表要做连接,就要在“学号”这个连接字段上建立索引.
还可以使用并集来避免顺序存取。
尽管在所有的检查列上都有索引,但某些形式的where子句强迫优化器使用顺序存取。
下面的查询将强迫对orders表执行顺序操作:
SELECT * FROM orders WHERE (customer_num=104 AND order_num〉1001) OR order_num=1008
虽然在customer_num和order_num上建有索引,但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。
因为这个语句要检索的是分离的行的集合,所以应该改为如下语句:
SELECT * FROM orders WHERE customer_num=104 AND order_num〉1001
UNION
SELECT * FROM orders WHERE order_num=1008
这样就能利用索引路径处理查询。
4.避免相关子查询
一个列的标签同时在主查询和where子句中的查询中出现,那么很可能当主查询中的列值改变之后,子查询必须重新查询一次。
查询嵌套层次越多,效率越低,因此应当尽量避免子查询。
如果子查询不可避免,那么要在子查询中过滤掉尽可能多的行。
5.避免困难的正规表达式
MATCHES和LIKE关键字支持通配符匹配,技术上叫正规表达式。
但这种匹配特别耗费时间。
例如:
SELECT * FROM customer WHERE zipcode LIKE “98_ _ _"
即使在zipcode字段上建立了索引,在这种情况下也还是采用顺序扫描的方式。
如果把语句改为SELECT * FROM customer WHERE zipcode 〉“98000",在执行查询时就会利用索引来查询,显然会大大提高速度。
另外,还要避免非开始的子串。
例如语句:
SELECT * FROM customer WHERE zipcode[2,3] >“80”,在where子句中采用了非开始子串,因而这个语句也不会使用索引。
6.使用临时表加速查询
把表的一个子集进行排序并创建临时表,有时能加速查询。
它有助于避免多重排序操作,而且在其他方面还能简化优化器的工作。
例如:
SELECT cust.name,rcvbles。
balance,……other columns
FROM cust,rcvbles
WHERE cust。
customer_id = rcvlbes。
customer_id
AND rcvblls.balance>0
AND cust。
postcode>“98000"
ORDER BY cust。
name
如果这个查询要被执行多次而不止一次,可以把所有未付款的客户找出来放在一个临时文件中,并按客户的名字进行排序:
SELECT cust。
name,rcvbles.balance,……other columns
FROM cust,rcvbles
WHERE cust.customer_id = rcvlbes.customer_id
AND rcvblls.balance〉0
ORDER BY cust.name
INTO TEMP cust_with_balance
然后以下面的方式在临时表中查询:
SELECT * FROM cust_with_balance
WHERE postcode>“98000"
临时表中的行要比主表中的行少,而且物理顺序就是所要求的顺序,减少了磁盘I/O,所以查询工作量可以得到大幅减少。
注意:
临时表创建后不会反映主表的修改.在主表中数据频繁修改的情况下,注意不要丢失数据。
7.用排序来取代非顺序存取
非顺序磁盘存取是最慢的操作,表现在磁盘存取臂的来回移动。
SQL语句隐藏了这一情况,使得我们在写应用程序时很容易写出要求存取大量非顺序页的查询。
有些时候,用数据库的排序能力来替代非顺序的存取能改进查询。
下面我们举一个制造公司的例子来说明如何进行查询优化.制造公司数据库中包括3个表,模式如下所示:
1.part表
零件号零件描述其他列
(part_num)(part_desc)(other column)
102,032Seageat 30G disk……
500,049Novel 10M network card……
……
2.vendor表
厂商号厂商名其他列
(vendor _num)(vendor_name) (other column)
910,257Seageat Corp……
523,045IBM Corp……
……
3.parven表
零件号厂商号零件数量
(part_num)(vendor_num)(part_amount)
102,032910,2573,450,000
234,423321,0014,000,000
……
下面的查询将在这些表上定期运行,并产生关于所有零件数量的报表:
SELECT part_desc,vendor_name,part_amount
FROM part,vendor,parven
WHERE part。
part_num=parven。
part_num
AND parven。
vendor_num = vendor。
vendor_num
ORDER BY part。
part_num
如果不建立索引,上述查询代码的开销将十分巨大。
为此,我们在零件号和厂商号上建立索引。
索引的建立避免了在嵌套中反复扫描.关于表与索引的统计信息如下:
表行尺寸行数量每页行数量数据页数量
(table)(row size)(Row count)(Rows/Pages)(Data Pages)
part15010,00025400
Vendor1501,000 2540
Parven13 15,000300 50
索引键尺寸每页键数量页面数量
(Indexes)(Key Size)(Keys/Page)(Leaf Pages)
part450020
Vendor45002
Parven825060
看起来是个相对简单的3表连接,但是其查询开销是很大的。
通过查看系统表可以看到,在part_num上和vendor_num上有簇索引,因此索引是按照物理顺序存放的.parven表没有特定的存放次序.这些表的大小说明从缓冲页中非顺序存取的成功率很小.此语句的优化查询规划是:
首先从part中顺序读取400页,然后再对parven表非顺序存取1万次,每次2页(一个索引页、一个数据页),总计2万个磁盘页,最后对vendor表非顺序存取1.5万次,合3万个磁盘页.可以看出在这个索引好的连接上花费的磁盘存取为5.04万次.
实际上,我们可以通过使用临时表分3个步骤来提高查询效率:
1.从parven表中按vendor_num的次序读数据:
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Informix 查询 语句 SELECT 优化