sql查询优化.pdf - 墨天轮文档

sql查询优化.pdf

张静懿

5页

0次

2025-04-22

免费下载

数据库查询优化:数据库的查询优化技术

疯狂代码 http://www.crazycoder.cn/ ĵ:http:/www.crazycoder.cn/DataBase/Article67667.html

数据库系统是管理信息系统核心基于数据库联机事务处理(OLTP)以及联机分析处理(OLAP)是银行、企业

、政府等部门最为重要计算机应用的从大多数系统应用例子来看查询操作在各种数据库操作中所占据比重最大

而查询操作所基于SELECT语句在SQL语句中又是代价最大语句举例来说如果数据量积累到定程度比如个银行账

户数据库表信息积累到上百万甚至上千万条记录全表扫描次往往需要数十分钟甚至数小时如果采用比全表扫描

更好查询策略往往可以使查询时间降为几分钟由此可见查询优化技术重要性

笔者在应用项目实施中发现许多员在利用些前端数据库开发工具(如PowerBuilder、Delphi等)开发数据库应用

时只注重用户界面华丽并不重视查询语句效率问题导致所开发出来应用系统效率低下资源浪费严重因此如何设

计高效合理查询语句就显得非常重要本文以应用例子为基础结合数据库理论介绍查询优化技术在现实系统中运

用

分析问题

许多员认为查询优化是DBMS(数据库管理系统)任务和员所编写SQL语句关系不大这是个好查询计划往往可以

使性能提高数十倍查询计划是用户所提交SQL语句集合查询规划是经过优化处理的后所产生语句集合DBMS处

理查询计划过程是这样:在做完查询语句词法、语法检查的后将语句提交给DBMS查询优化器优化器做完代数优

化和存取路径优化的后由预编译模块对语句进行处理并生成查询规划然后在合适时间提交给系统处理执行最后

将执行结果返回给用户在实际数据库产品(如Oracle、Sybase等)高版本中都是采用基于代价优化思路方法这种

优化能根据从系统字典表所得到信息来估计区别查询规划代价然后选择个较优规划虽然现在数据库产品在查询

优化方面已经做得越来越好但由用户提交SQL语句是系统优化基础很难设想个原本糟糕查询计划经过系统优化

的后会变得高效因此用户所写语句优劣至关重要系统所做查询优化我们暂不讨论下面重点介绍说明改善用户查

询计划解决方案

解决问题

下面以关系数据库系统Informix为例介绍改善用户查询计划思路方法

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[23] >“80”在where子句

中采用了非开始子串因而这个语句也不会使用索引

6．使用临时表加速查询

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

工作例如:

SELECT cust.namercvbles.balance……other columns

FROM custrcvbles

WHERE cust.customer_id = rcvlbes.customer_id