使用sql语句提高数据库性能-凯发k官方网站-首页

为了获得稳定的执行性能，sql语句越简单越好。对复杂的sql语句，要设法对之进行简化，本文给大家介绍优化sql语句提高数据库性能。

现在数据越来越复杂和庞大，很多时候影响程序运行性能不理想的原因中除了一部分是因为应用程序的负载确实超过了服务器的实际处理能力外，更多的是因为系统存在大量的sql语句需要优化。

一、问题的提出

在项目实际使用中，数据是一个长期累计的过程，随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是sql语句的优化。对于海量数据，劣质sql语句和优质sql语句之间的速度差别可以达到成千上百倍，因此高质量的sql语句，更能提高系统的可用性。

二、sql语句编写注意问题

下面就某些sql语句的where子句编写中需要注意的问题作详细介绍。在这些where子句中，即使某些列存在索引，但是由于编写了劣质的sql，系统在运行该sql语句时也不能使用该索引，而同样使用全表扫描，这就造成了响应速度的极大降低。

1. 操作符优化

(a) in 操作符

 在使用中尽量用exists替代in、用not exists替代not in  。

在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下， 使用exists(或not exists)通常将提高查询的效率。。在子查询中，not in子句将执行一个内部的排序和合并。 无论在哪种情况下，not in都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。。为了避免使用not in ，我们可以把它改写成外连接(outer joins)或not exists。

例子： 
（推荐）select* from dt_article where exists(select id from dt_article_category wheredt_article_category。id=dt_article。category_id andtitle='公司新闻')
（不推荐）select* from dt_article where category_id in (select id from dt_article_categorywhere title='公司新闻')

(b) is null 或is not null操作（判断字段是否为空）

判断字段是否为空一般是不会应用索引的，因为索引是不索引空值的。不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。 

    例子：

（推荐）select* from dt_article where title>'';
 （不推荐）select* from dt_article where title is null;

(c) > 及 < 操作符（大于或小于操作符）

（推荐）select * from dt_article where id>=101;

（不推荐）select * from dt_article where id>100;

两者的区别在于， 前者将直接跳到第一个id等于101的记录而后者将首先定位到id=100的记录并且向前扫描到第一个id大于100的记录。

(d)like操作符

like操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如like '%福瑞希%'这种查询不会引用索引，而like'福瑞希%'则会引用范围索引。

一个实际例子：用dt_article表中内容可来查询， content like'%福瑞希%'这个条件会产生全表扫描，如果改成contentlike '福瑞希%'则会利用content的索引进行范围的查询，性能肯定大大提高。

在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。

(e) union操作符

当sql语句需要union两个查询结果集合时，这两个结果集合会以union-all的方式被合并， 然后在输出最终结果前进行去重和排序。 假如用union all替代union， 这样排序就不是必要了。 效率就会因此得到提高。 需要注重的是，union all 将重复输出两个结果集合中相同记录。 因此各位还是要从业务需求分析使用unionall的可行性。 union 将对结果集合去重排序，这个操作会使用到sort_area_size这块内存。 对于这块内存的优化也是相当重要的。

(f) not

我们要避免在索引列上使用not， not会产生在和在索引列上使用函数相同的影响。 当查询列碰到”not，他就会停止使用索引转而执行全表扫描。

(g) or

    通常情况下， 用union替换where子句中的or将会起到较好的效果。 对索引列使用or将造成全表扫描。 注重， 以上规则只针对多个索引列有效。 假如有column没有被索引， 查询效率可能会因为你没有选择or而降低。 在下面的例子中， title和category_id上都建有索引。

（推荐）select * from dt_article where title='清洗空气' union all select * from dt_article where category_id=92

（不推荐）select * from dt_article where title='清洗空气' or category_id=92 假如你坚持要用or， 那就需要返回记录最少的索引列写在最前面。 
       另外在一些情况下，也可以使用in来替代or，     这是一条简单易记的规则，但是实际的执行效果还须检验。

（推荐）select * from dt_article where category_id in (89，92)

（不推荐）select * from dt_article where category_id=92 or category_id=89

(h) distinct

     当提交一个包含一对多表信息的查询时，避免在select子句中使用distinct。 一般可以考虑用exist替换， exists 使查询更为迅速，因为rdbms核心模块将在子查询的条件一旦满足后，马上返回结果。 

2. sql书写的影响

 (a) where后面的条件顺序影响

where子句后面的条件顺序对大数据量表的查询会产生直接的影响。如：

select * from dt_article wherecategory_id=92 and is_hot=1

select * from dt_article where is_hot=1 and category_id=92

以上两个sql中category_id（电压等级）及is_hot（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，第一条sql的is_hot=1在记录集内比率为99%，而category_id=92的比率只为1%，在进行第一条sql的时候99%条记录都进行category_id及is_hot的比较，而在进行第二条sql的时候1%条记录都进行category_id及is_hot的比较，以此可以得出第二条sql的cpu占用率明显比第一条低。

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

3. 更多方面sql优化资料分享

（1） 选择最有效率的表名顺序(只在基于规则的优化器中有效)：

oracle 的解析器按照从右到左的顺序处理from子句中的表名，from子句中写在最后的表(基础表 driving table)将被最先处理，在from子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersectiontable)作为基础表, 交叉表是指那个被其他表所引用的表.

（2） select子句中避免使用 ‘ * ‘：

oracle在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。

（3） 减少访问数据库的次数：

oracle在内部执行了许多工作: 解析sql语句, 估算索引的利用率, 绑定变量 , 读数据块等。

（4） 整合简单,无关联的数据库访问：

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

（5） 用truncate替代delete：

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

（6） 尽量多使用commit：

只要有可能,在程序中尽量多使用commit, 这样程序的性能得到提高,需求也会因为commit所释放的资源而减少，commit所释放的资源:

a. 回滚段上用于恢复数据的信息.
b. 被程序语句获得的锁
c. redo log buffer 中的空间

（7） 通过内部函数提高sql效率：

复杂的sql往往牺牲了执行效率. 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的。

（8） 使用表的别名(alias)：

当在sql语句中连接多个表时, 请使用表的别名并把别名前缀于每个column上.这样一来,就可以减少解析的时间并减少那些由column歧义引起的语法错误。

（9） 总是使用索引的第一个列：

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

（10） 避免使用耗费资源的操作：

带有distinct,union,minus,intersect,order by的sql语句会启动sql引擎执行耗费资源的排序(sort)功能. distinct需要一次排序操作, 而其他的至少需要执行两次排序. 通常, 带有union, minus , intersect的sql语句都可以用其他方式重写. 如果你的数据库的sort_area_size调配得好, 使用union , minus, intersect也是可以考虑的, 毕竟它们的可读性很强。

莱芜软件开发为您保驾护航