在数据库应用系统中, 其性能受多方面的限制, 如操作系统、数据库管理系统以及前端开发工具等都有很大的影响。而对于基于WEB的应用, 最终用户和顾客合二为一, 是同一个对象。性能管理就该从最终用户需求出发, 目标应该是保证关键事务的性能需求以简单的消耗时间来得到定义。而从大多数数据库应用系统的实例来看, 相对于数据库其他操作, 查询操作占的比重最大, 是最为重要的一部分。查询优化也就有着非常重要的地位。高效的查询能极大的提高系统的性能。科学合理地构造查询系统, 是成功开发数据库应用系统非常重要的环节。为了优化数据库的性能, 除了在数据库的物理设计, 关系规范化等方面进行改进外, 还有一个简单有效的方法就是提高查询响应速度。本文结合数据库理论, 并以信息管理系统为例, 就查询优化必要性及如何进行数据库查询优化的策略进行探讨。
1 SQL处理过程及基本概念
SQL语句处理过程主要包括以下步骤。
(1) 语句编译。
语句解析首先会检验SQL语句的语法, 并将该语句拆散成片段。查询改写则使用明确定义变换和等价技术把语句片段重新整理, 为下一阶段优化做好准备。
(2) 语句优化。
优化器会根据数据字典自动选择优化方式。如果数据字典中有访问表的统计信息, 将基于开销的优化方法 (CBO) , 并获得最佳的吞吐量;如果没有, 将基于规则的优化方法 (RBO) 。
(3) 数据生成。
根据优化方法产生的执行计划查找物理空间, 对数据处理并返回结果集。
1.1 数据字典
数据字典中有访问表的统计信息大致有以下内容。
(1) 表信息统计。
包括记录的数量, 数据块的数量, 平均的记录大小等。
(2) 字段信息统计。
包括列的数值统计, 列的空值数量, 数据分布等。
(3) 索引信息统计。
包括索引树上叶节点统计, 索引的深度, 簇聚等特殊属性统计等。
(4) 系统信息统计。
包括存储的性能, 处理器的性能等。
1.2 数据访问方式
对于表中数据的访问, 数据库可能有三种访问方式:全表扫描, 根据索引扫描以及根据记录的唯一标识检索。全表扫描会访问表中所有的记录, 适合用于大数据量的查询。索引扫描会根据某个索引先定位记录的物理地址, 再根据这个地址来访问数据。唯一标识检索这种方式很少会被直接用到, 需要先知道记录的物理地址。
1.3 表的连接方式
如果SQL语句中访问表的数据多于1个, 就会涉及到表与表之间连接的选择, 包括表的访问次序, 以及表与表数据的连接方式。数据库一般会先选择有强过滤条件的表作为驱动表, 用其查询的结果集和其他的表做关联。连接的方式主要有以下几种。
嵌套循环连接 (Nested loops join) 。
哈希表连接 (Hash join) 。
顺序合并连接 (Sort merge join) 。
聚簇连接 (Cluster Join) 。
嵌套循环连接和哈希表连接是常用的两种连接方式, 一般嵌套循环连接在结果集数据量较小时效率较高, 而哈希表连接则在结果集数据量较大时效率较高。
1.4 执行计划及Hint
执行计划就是SQL语句的执行过程, 包括表的访问方式, 不同表的连接方式, 以及表的执行顺序。一般数据库会选择最优的执行计划, 但有些情况, 程序员或数据库管理员更清楚数据的最佳访问方案, 在这样的特殊情况下, 可以使用提示 (Hint) , 强制让SQL优化器按照提示的方式访问数据。
需要注意的是, 使用提示不是太好的习惯, 会给带来大量的维护成本, 尽量依靠统计信息来让执行计划走对才是最优的选择。常用的提示如下表。
1.4.1 访问路径的Hints
(1) FULL:对表选择全局扫描的方法。
(2) INDEX:对表选择索引的扫描方法。
(3) INDEX_FFS:对指定的表执行快速全索引扫描, 而不是全表扫描的办法。
1.4.2 连接顺序的Hints
(1) ORDERED:根据表出现在from中的顺序, 使数据库依此顺序对其连接。
(2) LEADING:将指定的表作为连接次序中的首表。
1.4.3 连接方式的Hints
(1) USE_HASH:将指定的表与其他行源通过哈希连接方式连接起来。
(2) USE_MERGE:将指定的表与其他行源通过合并排序连接方式连接起来。
(3) USE_NL:将指定表与嵌套的连接的行源进行连接, 并把指定表作为内部表。
1.4.4 并行处理的Hints
(1) PARALLEL:可指定并行方式访问数据。
(2) PARALLEL_INDEX:可指定并行方式通过索引访问数据。
1.4.5 查询结果转换的Hints
(1) MERGE:能够对视图的各个查询进行相应的合并。
(2) USE_CONCAT:对查询中的WHERE后面的OR条件进行转换为UNION ALL的组合查询。
1.4.6 其他Hints
(1) APPEND:直接插入到表的最后, 使用并行模式来提高速度。
(2) NOAPPEND:通过在插入语句生存期内停止并行模式来启动常规插入。
(3) CACHE:当进行全表扫描时, CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端。
2 SQL查询语句优化策略
SQL查询语句优化的最终目的就是在条件允许的时间内得到正确的查询结果。一般可以从多个方面入手, 如编译器, 执行计划以及数据生成的角度, 在不同层面对查询等进行优化。
2.1 从编译器角度优化
2.1.1 使用绑定变量
由于SQL执行的过程中首先要进行分析, 如果数据库的缓存中存在对应的SQL语句就会直接提取该语句进行执行操作, 避免分析过程从而提高语句执行的效率, 同时也减少数据库的缓存空间。所以应尽量使用绑定变量方式对SQL进行操作。
对于数据分布均匀的列, 一定要使用绑定变量 (示例1-b) ;对于数据分布不均匀的列, 使用直接拼接变量 (示例1-a) 比绑定变量好。
示例1 a:拼接变量
示例1 b:绑定变量
2.1.2 使用表的别名
当在SQL语句中连接多个表时, 使用表的别名, 并将之作为每列的前缀。这样可以减少解析时间。
2.1.3 Select语句中避免使用‘*’
当你想在Select语句中列出所有的列时, 使用‘*’虽然很方便, 但也是一个非常低效的方法。数据库在解析过程中, 会将‘*’依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。
2.2 从执行计划角度优化
索引是优化查询中最常用和重要的。在关系数据库系统中, 应避免不必要的全表扫描。而在表上建立合适的索引, 从而改变了对数据的访问路径, 就可以通过访问索引的方式获得记录的物理位置, 避免因全表扫描而造成的I/O开销, 达到访问表的目的。但创建索引也会增加系统的时空开销, 因此必须要与实际查询需求紧密结合, 才能达到查询优化的目的。
2.2.1 建立索引的原则
(1) 两表间的关联字段上常建索引。
(2) 对于多列排序的, 可在列上建立复合索引, 但复合索引应尽量少用。
(3) 避免高重复率字段建索引。
(4) 对于频繁进行GROUP BY或OR-DER BY操作列上建立索引。
(5) 对不同值较少的列上不需建立索引
(6) 对于大批量数据记录的插入及删除, 首先删除索引, 再进行数据的操作, 操作完成后再重建必要的索引。
2.2.2 使用索引的注意事项
(1) 条件中避免对表有索引的字段进行函数操作, 除非该字段有对应的函数索引, 否则数据库将做全表扫描。
示例2 a:全表扫描
示例2 b:使用索引
(2) 条件里的常量需要按数据类型写明确, 避免数据库隐含数据转换, 导致无法使用索引。
示例3 a:原始SQL (status的数据类型为字符型)
示例3 b:隐含数据转换后的SQL
(3) 对有索引的字段进行not in, is null, is not null, <>, !=等操作时, 数据库不会使用索引而执行全表扫描。可以考虑在设计表时, 对索引列设置为not null。这样就可以用其他操作来判断。
2.3 用union替换or (适用于索引列)
通常情况下, 用union替换条件中的or将会起到较好的效果。对索引列使用or将造成全表扫描。
2.4 选择正确的驱动表和驱动索引
随着SQL语句中表个数的增加, 查询的开销会成几何级数增加。而第一个表的结果集大小决定了所有开销的基数, 所以正确的选择驱动索引对性能优化具有决定性的作用。
(1) 从数据角度优化。
(1) 使用union all代替union。
union:会对结果进行筛选, 消除重复, 数据量大的情况下可能会引起磁盘排序。
union all:不会对结果集做任何处理, 效率更高。当明确知道不同union字句的结构集记录不会有重复, 或者最终结果集不要求合并重复记录时, 使用union all。
2.5 尽量使用内部函数
系统提供的内部函数可以满足用户大部分的操作, 而且不同的数据库也会有一些特殊的函数。在功能相同的情况下, 函数应该是最优的实现。另外使用函数也可以使语法更简洁、易读。
2.6 网络性能的优化
通常信息管理系统中, 客户端和服务器是通过广域网来连接的, 因此网络延迟时间也成为影响客户端响应时间的因素之一。在客户端尽量采用批量处理请求, 也可以减少信息往返服务器的次数, 达到优化的目的。
3 结语
本文中所提到的性能优化方法都是作者在查阅了大量文献资料, 并在实际经验中总结出来的。这些优化策略的确可以在一定程度上提高SQL语句的执行效率。但实际情况千差万别, 在具体使用这些方法时, 要根据情况合理地选择优化策略, 这样才能根据应用系统的特点, 充分发挥数据库的高效功能, 为应用程序提供高性能的服务。
摘要：信息时代中, 海量数据的存储和查询需要高性能的数据库系统。影响数据库性能的一个重要因素就是SQL查询的效率。为了提高数据库应用系统的执行效率, 本文从数据库查询的基本概念入手, 着重讨论SQL语句的处理过程, 以及SQL查询的优化策略。
关键词：SQL语句,优化,策略
参考文献
[1] 盖国强, 冯春培, 叶梁, 等.Oracle数据库性能优化[M].北京:人民邮电出版社, 2005, 6.
[2] 童家旺, 胡怡文, 冯大辉[译].Oracle性能诊断艺术[M].北京:人民邮电出版社, 2009, 10.
[3] 苏敬凯, 等[译].SQL应用重构[M].北京:机械工业出版社, 2010, 1.
【SQL语句的分析与查询优化策略】相关文章：
ORACLE中SQL语句的优化研究09-10
SQL语句练习题(包含有多表查询)-答案04-18
数据库的查询优化方法分析09-11
分析电子信息科技发展与优化策略09-11
常用的sql语句06-21
对记录进行增删改的sql语句04-13
sql选择语句04-09
类sql语句04-16
科技时代背景下化工工艺的优化策略分析02-12