分享
 
 
 

查询语句(SELECT)的优化

王朝other·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

这篇文章是基于Informix数据库写的。希望对大家有用。

程序设计中的一个闻名定律是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(hm, rq, xx)

索引字键顺序:首先是号码hm,其次是日期rq,最后是标志xx,所以where子句变量顺序应是where hm

(2)、将最具有限制性的条件放在前面,大值在前,小值在后。

如:where colA=1效率高

where colA=1 AND colA

(3)、避免采用MATCHES和LIKE通配符匹配查询

通配符匹配查询非凡耗费时间。即使在条件字段上建立了索引,在这种情况下也还是采用顺序扫描的方式。

例如语句:SELECT * FROM customer WHERE zipcode MATCHES “524*”

可以考虑将它改为SELECT * FROM customer WHERE ZipCode=“524000”,则在执行查询时就会利用索引来查询,显然会大大提高速度。

(4)、避免非开始的子串

例如语句:SELECT * FROM customer WHERE zipcode[2,3] “24”,在where子句中采用了非开始子串,因而这个语句也不会使用索引。

(5)、避免相关子查询

一个字段的标签同时在主查询和where子句中的查询中出现,那么很可能当主查询中的字段值改变之后,子查询必须重新查询一次。查询嵌套层次越多,效率越低,因此应当尽量避免子查询。假如子查询不可避免,那么要在子查询中过滤掉尽可能多的行。

例如:将下面的语句

select hm,rq from TabA

where item IN (select item form TabB where TabB.num=50)

改为:select hm,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_num1001) OR order_num=1008

虽然在cust_num和order_num上建有索引,但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合,所以应该改为如下语句:

SELECT * FROM orders WHERE cust_num=126 AND order_num1001

UNION

SELECT * FROM orders WHERE order_num=1008

这样就能利用索引路径处理查询。

(8)、对于大数据量的求和应避免使用单一的sum命令处理,可采用group by方式与其结合,有时其效率可提高几倍甚至百倍。

(9)、避免会引起磁盘读写的rowid*作。在where子句中或select语句中,用rowid要产生磁盘读写,是一个物理过程,会影响性能。

(10)、使用临时表加速查询

把表的一个子集进行排序并创建临时表,有时能加速查询。它有助于避免多重排序*作,而且在其他方面还能简化优化器的工作。

但要注重:临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下,注重不要丢失数据。

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有