SQL语句优化

一、找出导致性能慢的原因

      首先你要知道是否跟sql语句有关,然后使用sql性能检测工具--sql server profiler,分析出sql慢的相关语句,就是执行时间过长,占用系统资源,cpu过多的

1.查看执行时间和cpu占用时间(查询后在消息中查看)

set statistics time on
select * from dbo.Product
set statistics time off

2.查看查询对I/0的操作情况(查询后在消息中查看)

set statistics io on

select * from dbo.Product

set statistics io off

逻辑读取:数据缓存中读取的页数

物理读取:从磁盘中读取的页数

预读:查询过程中,从磁盘放入缓存的页数

如果物理读取次数和预读次说比较多,可以使用索引进行优化。如果存在扫描表,或者扫描聚集索引,这表示在当前查询中你的索引是不合适的,是没有起到作用的,那么你就要修改完善优化你的索引。

二、优化方案

关于SQL语句优化方法:比如避免Select *语句;字段、子语句或子表的顺序。

(一)操作优化方案

1.避免Select *

Selcet中每少提取一个字段,数据的提取速度就会有相应的提升。提升的速度还要看您舍弃的字段的大小来判断。

1)尽量避免select * 的存在,使用具体的列代替*,避免多余的列

2)使用where限定具体要查询的数据,避免多余的行

3)使用top,distinct关键字减少多余重复的行

2. insert插入优化

分析说明:insert into select批量插入,明显提升效率,可以尽量避免一个个循环插入。

3. 优化修改删除语句

如果你同时修改或删除过多数据,会造成cpu利用率过高从而影响别人对数据库的访问。如果采用单一循环操作,效率会更低。折中的办法就是,分批操作数据。

delete product where id<1000
delete product where id>=1000 and id<2000
delete product where id>=2000 and id<3000

(二)避免全表扫描

在多数情况下,Oracle使用索引来更快地遍历表,优化器主要根据定义的索引来提高性能。但是,如果在SQL语句的where子句中写的SQL代码不合理,就会造成优化器删去索引而使用全表扫描。Where中少用NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE,它们会引起全表扫描。避免使用like和or语句。

  1. IS NULL 或IS NOT NULL操作(判断字段是否为空)

索引不索引空值,单列索引中,任何包含null值的列都将不会被包含在索引中。对于复合索引,如果每个列都为空,索引中同样不存在此记录. 如果至少有一个列不为空,则记录存在于索引中.如果所有的索引列都为空,ORACLE将认为整个键值为空而空不等于空。用其它相同功能的操作运算代替,如:a is not null 改为 a>0 或a>’’等。NOT我们在查询时经常在where子句使用一些逻辑表达式,如大于、小于、等于以及不等于等等,也可以使用and(与)、or(或)以及not(非)。NOT可用来对任何逻辑运算符号取反。如果要使用NOT,则应在取反的短语前面加上括号,并在短语前面加上NOT运算符。效率比较低,改为使用大于小于号控制检索,因为大于小于号允许使用索引。

  1. > 及 < 操作符(大于或小于操作符)

大于或小于操作符一般情况下是不用调整的,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化,执行A>2与A>=3有区别,因为A>2时ORACLE会先找出为2的记录索引再进行比较,而A>=3时ORACLE则直接找到=3的记录索引。

  1. LIKE操作符

LIKE操作符可以应用通配符查询,里面的通配符组合可能达到几乎是任意的查询, 但是若检索查询含有某一字符串的记录时,效率会很低,如果只是匹配以某一段开始或结尾使用like查询,效率不会太低。

  1. Order by语句

Order by语句对要排序的列没有什么特别的限制,也可以将函数加入列中。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度

三、SQL语句编写注意问题

1.   ORACLE共享内存SGA的原理

ORACLE对每个SQL 都会对其进行一次分析,并且占用共享内存,如果将SQL的字符串及格式写得完全相同,则ORACLE只会分析一次,共享内存也只会留下一次的分析结果,这不仅可以减少分析SQL的时间,而且可以减少共享内存重复的信息,ORACLE也可以准确统计SQL的执行频率。如果对表进行了统计分析,ORACLE会自动先进小表的链接,再进行大表的链接

2.  SQL语句索引的利用

(1) 对条件字段的一些优化

采用函数处理的字段不能利用索引, 条件内包括了多个本表的字段运算时不能进行索引ORACLE 的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

(2) WHERE子句中的连接顺序:

ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

(3) 使用DECODE函数来减少处理时间:

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.

(4) 整合简单,无关联的数据库访问:

如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系) 。

(5) 删除重复记录:使用子语句过滤掉一部分数据,可以最大程度的删除重复。

6) 用TRUNCATE替代DELETE:

当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.

(7) 尽量多使用COMMIT:

只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少,COMMIT所释放的资源:回滚段上用于恢复数据的信息;被程序语句获得的锁;redo log buffer 中的空间

(8) 用索引提高效率:

索引是表的一个概念部分,用来提高检索数据的效率,ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快.索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的:

(9) sql语句用大写的;因为oracle总是先解析sql语句,把小写的字母转换成大写的再执行。

(10) 在java代码中尽量少用连接符“+”连接字符串!

(11) 用UNION替换OR (适用于索引列)

对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低.如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.

(12) 用IN来替换OR

这是一条简单易记的规则,但是实际的执行效果还须检验,

(13)总是使用索引的第一个列:

如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引.当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引。

(14)  用WHERE替代ORDER BY:

ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.ORDER BY中所有的列必须定义为非空.WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列.

(15) 避免改变索引列的类型:

当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.内部发生的类型转换, 此字段上的索引将不会被用到, 最好把类型转换用显式表现出来. 注意当字符和数值比较时, ORACLE会优先将数值类型转换为字符类型。

(16) 如果检索数据量超过表中记录数的30%.使用索引将没有显著的效率提高;

(17) 避免使用耗费资源的操作:

带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序. 通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写. 如果你的数据库的SORT_AREA_SIZE调配得好, 使用UNION , MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强。

(18) 优化GROUP BY:

提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.

(19) 慎用distinct关键字

distinct在查询一个字段或者很少字段的情况下使用,会避免重复数据的出现,给查询带来优化效果。但是查询字段很多的情况下使用,则会大大降低查询效率。很明显带distinct的语句cpu时间和占用时间都高于不带distinct的语句。原因是当查询很多字段时,如果使用distinct,数据库引擎就会对数据进行比较,过滤掉重复数据,然而这个比较,过滤的过程则会毫不客气的占用系统资源,cpu时间。

(20) 慎用union关键字

此关键字主要功能是把各个查询语句的结果集合并到一个结果集中返回给你。用法

<select 语句1>union<select 语句2>union<select 语句3>

满足union的语句必须满足:1.列数相同。 2.对应列数的数据类型要保持兼容。执行过程:依次执行select语句-->合并结果集--->对结果集进行排序,过滤重复记录。union all,使用union all能对union进行一定的优化,因为union all不进行去重和排序的过程。需要注意的是,UNION ALL 将重复输出两个结果集合中相同记录. UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存.

(21) 判断表中是否存在数据

select count(*) from product select top(1) id from product

(22)连接查询的优化

减少连接表的数据数量可以提高效率,如果必不可免的使用表的连接,首先要弄明白你想要的数据是什么样子的,然后再做出决定使用哪一种连接。

各种连接的取值大小为:内连接结果集大小取决于左右表满足条件的数量;左连接取决与左表大小,右相反;完全连接和交叉连接取决与左右两个表的数据总数量

(23)用Where子句替代having子句

避免使用having子句,having只会在检索出所有记录之后才对结果集进行过滤。HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 可以通过WHERE子句限制记录的数目, (非oracle中)on、where、having这三个都可以加条件的子句中,on是最先执行,where次之,having最后,因为on是先把不符合条件的记录过滤后才进行统计,应该速度是最快的,where也应该比having快点的,因为它过滤数据后才进行sum,在两个表联接时才用on的,所以在一个表的时候,尽可能的使用where语句。where的作用时间是在计算之前就完成的,而having就是在计算后才起作用的,所以在这种情况下,两者的结果会不同。

(24)exists代替in、NOT EXISTS替代NOT IN:

Oracle中In子查询返回的结果不能超过1000条,使用exists为替代方案。ORACLE在执行in操作时,试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询。一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了。在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并.为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。

 

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 创作都市 设计师:CSDN官方博客 返回首页