SQL优化要点总结

SQL优化要点总结（共6篇）

SQL优化要点总结 篇1

文章比较简短,但很实用,SQL优化要点总结:

1、尽量避免大表的全表扫描，建立合适的索引,务必注意索引是把双刃剑，不能滥用，用好则已，用不好伤人害已。

2、能不排序则不排序，除了order by，注意一些隐含的操作也会有排序，比如UNION,DISTINCT操作

3、尽量少用子查询改用多表联接，因为子查询最终优化器也是转换成多表联接，当语句很复杂的时候，优化器不一定能转换对

4、必要时使用Hints干扰执行计划，优化器不是万能的，很多时候误判

5、合理使用动态绑定，语句共享,减少SQL语句的硬解析次数，尤其是OLTP系统，数据仓库系统后台ETL计算有循环语句执行需考虑

6、多层嵌套，考虑使用中间表或临时表做转储

7、数据量大考虑建立表分区、索引分区，减少数据检索范

8、物化视图的使用,query rewrite特性激动人心

SQL优化要点总结 篇2

随着信息化进程的不断加快,数据库的应用越来越广泛,信息系统的优劣与数据库系统的性能有着直接的关联。随着数据库规模的不断扩大,如何保持数据库应用系统高效地运行,受到人们越来越多的关注。

2 优化器

对于信息管理系统来说,其最关键的核心是数据库系统,对于众多的应用系统来说,查询操作在整个系统中占据着相当大的比重,也就是说,查询速度的快慢直接影响着信息管理系统的性能。当数据库的规模越大这个特性表现的越明显,良好的查询语句对系统性能的提高起着积极的作用。

2.1 Oracle 优化器

无论SQL语句的性能如何, 最终都要在Oracle数据库中执行,Oracle数据库在执行SQL语句之前, 首先通过优化器利用初始化的参数,并利用指定的优化方法对执行计划进行分析,并执行。

当前,Oracle优化器主要有RBO和CBO两种优化方式。其中RBO是基于规则的优化器,根据访问的路径和访问路径的等级去选择SQL语句的执行计划,假如一条SQL语句有多个路径可以通过,Oralce会自动选择等级最低的访问路径。RBO优化器仅含有几条在小表上低效利用的索引,无形之中增加了I/O,该优化方式效率相对较低;CBO是基于代价的优化器, 其成本主要由可用访问路径、嵌入的提示、对象的统计信息等组成,CBO会选择成本代价最低的执行计划。当前,CBO优化器成为Oracle数据库优化的主要组成,CBO的构成如图1所示

利用Oracle优化器对SQL语句进行分析, 在所有的查询中,有一半经过RBO优化之后,执行的速度会快一点,而另一半则由CBO优化后,执行的速度最快。从Oracle8i版本以后,RBO优化已经不再发展RBO。

2.2 影响执行计划的因素

影响执行计划的因素较多,总结起来主要有几种。

(1)连接顺序。当前 ,数据库进行数据查询 ,大部分情况下都需要由若干表连接,一般采取将查询结果只有一行记录的表优先。

(2)访问路径。对于路径的扫描,Oracle优化器的方式有簇扫描、索引扫描、行ID扫描、全表扫描、散列扫描等。由于Oracel数据库对I/O的评估原则是“块”在整个表中所占的比例来确定选取何种扫描路径。

(3)连接方式。查询优化的重点是连接操作 ,内表与外表之间进行连接的算法主要有归并连接、散列连接及嵌套循环连接,每种连接都有其自己的优点。

(4)成本估算。其成本的代价主要从I/O、CPU和通信三方面进行考虑,其中I/O是最主要的。在成本估算中占的比重最大。

3 优化 SQL 语句

3.1 可优化的 SQL 语句类型

优化器可以优化SQL语句的类型主要有几种。

(1)简单语句。对于数据表的操作 ,主要的操作动作有select、update、insert及delete语句, 这种类型的语句主要包括from和where。一般来说,可以优化的地方主要在where中。其影响效率的问题主要集中在几个方面:分组或排序过程中包含了过多的中间结果集、对索引的列使用了全表扫描。

(2)连接语句。利用多个表相结合的方式查找相关的数据信息,这也是数据库最常用的方法,由from子句实现多个表的连接, 利用where将相关的条件进行关联。其影响效率的问题主要集中在几个方面:表连接顺序不是最优、分组或排序过程包含了过多的中间结果集、索引列使用了全表扫描等。

(3)外部连接。该方式与连接语句有相同之处 ,同样涉及到多表连接的问题。其影响效率的问题也与连接语句相近。

(4)复杂语句。对于select、update、insert及delete语句中的子查询以select形式存在。该方式的问题是内部查询的效率对外部查询的效率有影响。

(5)复杂查询。利用组操作符将若干简单语句结合起来形成的语句,一般情况下,将语句拆分为上述的四种类型的语句之后再进行优化处理。

对于查询问题的分析,主要是要尽可能地减少子查询或者使用子查询返回的结果集要尽量地减少。

3.2 优化的规则

(1) 在索引列字段上尽可能地避免使用“! = ”“NULL”、“<>”及“not”等符号 , 尽可能地不要使用隐式类型的转换。这些符号的使用可能会对索引信息造成影响,进行转变为全表扫描,影响了数据库的性能。

(2)尽可能地不要使用“select * from表名”。“*”符号代表需要返回所有列,就意味着要扫描所有的返回记录,收取所有的列名与列值。使Oracle不断地进行磁盘的读取及交换。如果需要,将“*”换成具体的列名。

(3)避免在索引列字段中使用改变列的函数。当函数改变了索引列的类型及内容时,可能使原来可以使用的索引值变得无法继续使用,从而影响了系统的效率。

(4)绑定变量进行传值。绑定变量进行传值与数据库收到语句解析后的内容是一致的,Oracle可以在下一条语句到来时直接存入缓存并执行,不必再进行解析和生成执行过程。

(5)合理建立索引。良好的索引机制 ,可以使系统查询速度更快。

(6)利用where代替having子句。where子句是在分组之前对条件进行筛选, 而Having是分组后进行筛选分组前筛选可以有效减少分组的时间和资源的消耗。

比较下面两个SQL语句:

语句A:select name , num from department wherenum not in (select num from school);

语句B:select name,num from department where notexists (select num from school where department.num=school.num)。

通过在数据库中执行这两条语句, 其结果是相同的, 执行语句A时,Oracle首先对school表进行整个扫描,没有在school上建立num索引,语句B使用了联合查询, 对school表进行部分扫描, 利用了num列的索引。语句B的效率要高于语句A。

4 结束语

sql 百万级数据库优化方案 篇3

2010-05-05分页存储过程（二）在sqlserver中返回更加准确的分页结果

2013-09-09一个删选数据的例子,使用GROUP、DISTINCT实例解析

2009-06-06三步堵死 SQL Server注入漏洞

2012-05-05sqlserver中根据字符分割字符串的最好的写法分享

2009-03-03压缩技术给SQL Server备份文件瘦身

2013-11-11SQLServer 2008 CDC功能实现数据变更捕获脚本

2009-09-09sqlserver 字符串分拆 语句

2013-03-03数据库中两张表之间的数据同步增加、删除与更新实现思路

SQl实训总结书 篇4

这周的实训是SQL,通过短暂的几天时间让我深有感触, 从一开始的构思到今天写下这份总结。期间时间虽然只有四天但是也学到了不少的知识。

在实训的刚开始构想只有大体的思路，忽略了一些细节，因此在我真正做这个数据库时发现有很多错误，有的时候要解决一个错误反反复复会花上很多时间。有的时候实在想不出来会再网上查或者直接问同学,我们班有的同学还是很厉害的,所以,在他们身上也学到了不少知识。

SQL优化要点总结 篇5

SELECT <目标列名序列> -- 需要哪些列

FROM <表名> -- 来自于哪些表

[WHERE <行选择条件>] -- 根据什么条件

[GROUP BY <分组依据列>] --分组依据

[HAVING <组选择条件>]

[ORDER BY <排序依据列>]

<目标列名序列>部分能够包含的内容有如下结构：

SELECT [ ALL | DISTINCT ]

[ TOP expression [ PERCENT ] [ WITH TIES ] ]

::=

{

*

| { table_name | view_name | table_alias }.*

| {

[ { table_name | view_name | table_alias }. ]

{ column_name | $IDENTITY } } ]

| expression

[ [ AS ] column_alias ] }

| column_alias = expression

SQL谓词下压的优化 篇6

虽然影响查询操作的效率有很多因素,如数据库的设计、系统的架构,甚至在分布式数据库中网络因素也是影响查询操作效率的关键。不论如何设计数据库以及数据库的架构或选用什么类型的数据库,从用户角度来看都是使用统一的SQL语句作为访问数据库的接口,所以本文的关注点是优化SQL语句。

1 问题描述

首先使用“select*from A inner join B on A.id=B.id where A.age=10 and B.age=20”SQL语句来说明SQL执行过程,该条SQL语句首先会使用A.id=B.id作为条件对表A和表B进行笛卡尔积运算,然后对笛卡尔积的结果使用A.age=10和B.age=20进行过滤,最终得到结果。

但是先进行笛卡尔积运算会生成大量中间结果,所以可以先使用A.age=10和B.age=10分别在表A和表B中过滤掉不满足条件的数据,然后再对过滤以后的数据进行连接操作。由于事先过滤两个表中的数据所以产生的中间结果更小,这样的改变会大大提高连接操作的效率。

像上述这种使用where条件对表进行过滤的处理称为“where谓词下压”,同理也可以使用join后面的关键字对数据表在连接之前进行过滤,即“join谓词下压”。使用下压谓词的方法可以尽早过滤掉非结果集中的数据,这样会大大减少产生的中间结果,从而提高连接操作的效率。不过谓词下压的方法只适用于内连接的情况,对于外连接(左外连接和右外连接)上述方法将会产生错误的结果。

假设有如下两个关系表A和B,然后分别测试join谓词和where谓词下压时得到的结果。

执行“select*from A left join B on A.id=B.id and A.age>10”语句得到表2左侧的数据,而“select*from (select*from A where A.age>10) as A lefe join B on A.id=B.id”语句是把A.age>10下压的SQL,得到表2右侧的数据。通过结果对比发现,在左外连接中下压join谓词是会影响结果的。

执行“select*from A left join B on A.id=B.id where B.age>100”语句得到表3左侧的数据,而“select*from A left join (select*from B where B.age>100) as B on A.id=B.id”语句是把B.age>100下压的SQL,得到表3右侧的数据。同样,通过结果对比发现在左外连接中下压where谓词会得到不同的结果。

通过上述对比的结果可以发现,在外连接中join谓词和where谓词在有些情况下是不可以下压的,并且在左外连接和右外连接中下压的规则是不一样的。本文的目的就是给出在外连接中join谓词和where谓词下压的规则,并且给出为什么不能下压的证明。

2 关系代数

关系代数和SQL具有等价的描述能力,可以在关系代数中使用一些规则进行运算,这些运算规则为关系代数的变化提供理论基础。关系代数的理论中包含了多种运算符,常用的针对集合运算的运算符有交、并、差和笛卡尔积,同时也包含一些其他运算符如投影、选择、连接以及除法操作。对于关系代数理论来讲,还有许多等价的变化规则,使用这些规则可以对关系代数的表达式进行等价变化,下面将会给出本文中所用到的等价变化规则以及外连接的定义。

笛卡尔积与选择的交换规则中需要假设F是选择谓词,而E1×E2是笛卡尔积。

规则1:σF(E1×E2)=σF(E1)×E2,条件为F只选择E1中元组。

规则2:σF1F2 (E1×E2)=σF (E1)×σF (E2),条件为F1和F2分别选择E1和E2。

规则3:σF1F2(E1×E2)=σF2 (σF1(E1)×E2),条件为F1和F2分别选择E1和E2。

规则4:σF(E1UE2)=σF(E1)∪σF(E2),条件为E1和E2拥有相同的列(字段)。

投影与笛卡尔积的分配规则中A1,A2,…，Am是E1的列,B1,B2,…Bn是E2的列。

规则:

左外连接定义规则中左外连接的条件用F表示,笛卡尔积用E1×E2表示。同时F取值又有三种情况:F条件涉及E1、F条件涉及E2、F条件涉及E1和E2。其中当F条件涉及E时左外连接将退化为内连接。

两个关系的左外连接定义为E1=A∪B,其中A和B的集合定义如下:

3 谓词的下压规则与证明

在SQL语句中位于join关键字后面的条件称为“join谓词”,如“select*from A inner join B on A.id=B.id where A.age=10 and B.age=20”中的A.id=B.id就属于“join谓词”。而where关键字后面的条件则称为“where谓词”,如A.age=10和B.age=20称为“where谓词”。下面给出在左外连接时join谓词和where谓词下压的规则,右外连接的规则与左外连接的规则相反,此处为简略描述只给出左外连接的规则。

上述的规则表示如果join关键字后面的条件是用于过滤左表数据的那么不可以下压,而如果是用于过滤右表的数据则可以下压。而where谓词正好相反,如果where关键字后面的条件用于过滤左表则可以下压,如果用于过滤右表则不可以下压。

下面会给出左外连接中用于过滤左表的join谓词为什么不可以下压的根据。前面已对左外连接给出了定义,在定义中可知左外连接可以使用两个集合并集的形式表示,即A UB,并且在定义中说明了F条件是涉及左表E,的,同时集合A和集合B的定义在左外连接的定义中已给出。

根据“笛卡尔积与选择的交换”规则,如果条件F只用于过滤左表E1,此时把左外连接表达式E1中的F条件下压,下压后的表达式为σF(E2)E2。根据前面给出的左外连接的定义,可以把σ(E1)E2写成CUD的形式,集合C和集合D的定义如下:

如果F(tr)取值永远为真,那么tr就表示σF(E1)集合内所有的数据,同时集合C中的数据条数与σF(E1)中数据条数相同,那么集合D中的tr∈σF(E1)∧trA条件矛盾,此时集合D是空集。所以无法被表示成σF(E1)E2形式,即条件F无法被下压。

下面给出左外连接中where谓词不可以下压的证明,根据前面给出的左外连接定义,可以把集合根据变换规则改写为σF(A∪B)形式,其中F1是用于过滤右表E2的条件,而F2则是连接条件。集合A和集合B的定义在左外连接的定义中已给出。根据前面给出的变换规则,σF (A∪B)最终可以被写成σF1^F2 (E1×E2)∪σF1 (B)的形式。

根据“笛卡尔积与选择的交换”规则,如果F,是用于过滤右表E的,那么左外连接中的F1可以下压并且写成形式。同时根据左外连接的定义可以把表示为C∪D,同时集合C和集合D的定义如下:

在左外连接中可能存在右侧为NULL的情况,但是由于F1是用于过滤右表E2,可能会把右侧为NULL的行过滤掉。对于集合D而言,当trA为真时那么下压之后的结果集中会包含一条右侧为NULL的数据,但未下压的表达式的结果集中不包含右侧为NULL的数据,这样导致两次得到的结果不相同。经过上述的证明可知,用于过滤右表的where谓词不可以下压。

4 结语

本文主要通过研究关系代数的方法对SQL语句进行改写优化,在以前的SQL优化方法中,谓词下压规则都是针对内连接的,而本文使用关系代数做外连接首先进行了定义,给出了谓词在外连接中下压的规则并给出了不可下压的证明。本文提出的谓词在外连接中下压的规则可以为SQL语句的优化改写提供理论基础。

摘要：编写SQL语言时保证其正确性是首要的目标,但对于SQL执行的效率仍然值得研究。随着各行各业的数据量急剧增多,SQL查询的执行效率得到广泛关注。基于此,通过研究关系代数的理论,提出了关系代数中谓词的下压规则及下压条件,从而为SQL语言的优化打下了理论基础。

关键词：关系代数,查询优化,谓词下压,SQL语句优化

参考文献

[1]王峥,王亚平.关系代数与SQL查询优化的研究[J].电子设计工程,2009(8):110-112.

[2]冯凯平,李晓良.基于关系代数树的查询优化方法实例分析[J].电子设计工程,2012(7):5-8.

[3]梁志宏,靳延安,周华.等价关系代数查询优化方法的研究[J].山西师范大学学报:自然科学版,2004(2):34-38.

[4]徐新静.SQL优化技术及应用[J].天津冶金,20 11(2):25-27.

[5]宋琦.SQL优化技术及应用分析[J].信息与电脑:理论版,2011(9):175.

[6]冯卫兵.关系数据库的查询优化[J].现代计算机:专业版,2010(1):30-33.