为了获得稳定的执（zhí）行性（xìng）能，SQL语句越简单越好。对复杂的SQL语句，要设法（fǎ）对之进行简（jiǎn）化，本（běn）文给（gěi）大家介绍优化SQL语句提高数据（jù）库性能。

现（xiàn）在（zài）数（shù）据越来越复杂和（hé）庞大，很多时（shí）候（hòu）影响（xiǎng）程（chéng）序运行性能不（bú）理想的原因中除了（le）一（yī）部分是因（yīn）为应用程序的负载（zǎi）确实超（chāo）过了服务器的实际（jì）处理能力外，更多的是（shì）因（yīn）为系统存在大量的SQL语句需（xū）要优化。

一、问题的提出

在项（xiàng）目实际使用中，数据（jù）是一个（gè）长期累计的过（guò）程，随（suí）着数据库中（zhōng）数据的增加，系（xì）统的响应速（sù）度就成（chéng）为目前系统需要解决的最主要的问题（tí）之一。系统（tǒng）优（yōu）化中一个很重要的方面就是SQL语（yǔ）句的优化。对（duì）于（yú）海量数据，劣质SQL语句和优质SQL语句之间的速度差别可以（yǐ）达到成千上百倍，因（yīn）此高质量的SQL语句，更（gèng）能（néng）提高系统的可用性。

二、SQL语句编（biān）写注（zhù）意问（wèn）题

下面（miàn）就某些SQL语句的where子句编写中需要注意的问题作详细（xì）介绍。在这些where子句中，即使某（mǒu）些列存在索（suǒ）引（yǐn），但（dàn）是由于编写了劣质的SQL，系（xì）统在运行该SQL语句时也不能使用（yòng）该（gāi）索引（yǐn），而同样使用全（quán）表（biǎo）扫（sǎo）描，这就造（zào）成了响应速度的（de）极大降（jiàng）低。

1. 操作符优化

(a) IN 操作符

 在使用中尽量（liàng）用EXISTS替代（dài）IN、用NOT EXISTS替代NOT IN  。

在许多基于基（jī）础表的查询（xún）中，为（wéi）了满足一个（gè）条件，往（wǎng）往需要对另一个（gè）表进行联接。在这种情况下， 使用EXISTS(或NOT EXISTS)通常将提（tí）高查询的（de）效率。。在子查询中，NOT IN子句（jù）将执行一个内部（bù）的排（pái）序和合（hé）并。 无论在哪种情况（kuàng）下，NOT IN都是最低效的（de） (因为（wéi）它对子查询中的表执行了一个全（quán）表遍（biàn）历)。。为了避免（miǎn）使用NOT IN ，我们可以把它改写成外连接（jiē）(Outer Joins)或NOT EXISTS。

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

(b) IS NULL 或IS NOT NULL操作（判断字段是否（fǒu）为空（kōng））

判断字段是否为空（kōng）一般是不会应用索引的，因为索引是不索引空值的。不能用null作索引，任何包含（hán）null值的（de）列都将（jiāng）不会被包含在索引中。即使（shǐ）索引有多列这样的情况下，只要这些列中（zhōng）有（yǒu）一列含有null，该（gāi）列就会从索（suǒ）引中排除。也就是说（shuō）如果某列存在（zài）空值，即（jí）使对该列建索引也（yě）不会提高性能。任何在（zài）where子句中使用is null或is not null的语句优（yōu）化器是不（bú）允许使用索引的。 

    例子：

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

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

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

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

两者（zhě）的区别在于（yú）， 前者将直（zhí）接跳到第（dì）一个id等于（yú）101的记（jì）录（lù）而后者将（jiāng）首先定位到（dào）id=100的记录（lù）并且向（xiàng）前扫（sǎo）描到第一个id大于100的记录。

(d)LIKE操作符

LIKE操作符可以应（yīng）用通配符查询，里面的通配（pèi）符组合可能达到几乎是（shì）任意的查询，但是（shì）如（rú）果用得不好则会产（chǎn）生性（xìng）能上（shàng）的（de）问题，如（rú）like '%福瑞希%'这种查询（xún）不（bú）会引用索引，而like'福瑞希%'则会（huì）引用范围（wéi）索引。

一个实际（jì）例子：用dt_article表（biǎo）中（zhōng）内（nèi）容可（kě）来查（chá）询， content like'%福（fú）瑞希%'这（zhè）个条件会产生全表扫描，如果改成contentlike '福瑞希%'则会利用（yòng）content的索引进行范围（wéi）的查询，性能肯定大（dà）大提高。

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

(e) UNION操作（zuò）符

当SQL语句需要UNION两个查询结果集合（hé）时（shí），这两个结（jié）果集合会以UNION-ALL的方式被合（hé）并， 然后在输（shū）出最终结（jié）果前进行去重和（hé）排序。 假如（rú）用UNION ALL替代UNION， 这样排序就不是必要了。 效（xiào）率就会因此得到提高。 需要注重的是（shì），UNION ALL 将重复输出两个（gè）结（jié）果集合中相同记录。 因此各位还是要从业务需求分析（xī）使用UNIONALL的可行性（xìng）。 UNION 将对结（jié）果集合去（qù）重排序，这（zhè）个操作会使用（yòng）到SORT_AREA_SIZE这块（kuài）内存。 对于这块内存的优化也是（shì）相（xiàng）当重（chóng）要的。

(f) NOT

我们要避免在索引（yǐn）列上使用NOT， NOT会（huì）产生在和在索引列（liè）上使用函数相同的影（yǐng）响。 当查询列碰到（dào）”NOT，他就会（huì）停止使（shǐ）用索引转而执行全（quán）表（biǎo）扫（sǎo）描。

(g) OR

    通常情况下， 用（yòng）UNION替换WHERE子句（jù）中（zhōng）的OR将（jiāng）会（huì）起到较好（hǎo）的（de）效果。 对索引列使用OR将造成全表扫描。 注重， 以上规（guī）则只针对多个（gè）索引列（liè）有效。 假如有column没有被索引， 查（chá）询效率可能会因为你没有（yǒu）选择OR而（ér）降低。 在下面（miàn）的例子中， title和（hé）category_id上都（dōu）建有索（suǒ）引。

（推荐）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 假如你坚（jiān）持要用OR， 那就（jiù）需（xū）要返（fǎn）回记录最少的索引列写（xiě）在最前面。 
       另外在一些情况（kuàng）下，也可（kě）以使（shǐ）用（yòng）IN来替代OR，     这（zhè）是（shì）一条简单易记的规则，但是实际的执行效（xiào）果（guǒ）还须检验。

（推（tuī）荐（jiàn））select * from dt_article where category_id in (89，92)

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

(h) DISTINCT

     当提交一个包含一对多表信息的查询（xún）时，避免在SELECT子句中使（shǐ）用DISTINCT。 一般可以（yǐ）考虑用EXIST替换， EXISTS 使（shǐ）查询更为迅速，因（yīn）为RDBMS核（hé）心模块将在（zài）子查询的条件一旦（dàn）满足（zú）后，马上返回结果。 

2. SQL书写的影响

 (a) WHERE后面的（de）条件顺序影响（xiǎng）

WHERE子句（jù）后（hòu）面的条件顺序对大数据量表的查询会（huì）产生直接的影响。如：

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

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

以上（shàng）两个SQL中category_id（电压（yā）等级（jí））及is_hot（销户标志）两个字段都（dōu）没进行索引，所以（yǐ）执行的（de）时候都是全表扫描，第一条SQL的is_hot=1在（zài）记录（lù）集内比率（lǜ）为（wéi）99%，而（ér）category_id=92的（de）比（bǐ）率只（zhī）为1%，在（zài）进（jìn）行第一条SQL的时候99%条（tiáo）记（jì）录都进（jìn）行category_id及is_hot的比较，而在进行第（dì）二条SQL的时候1%条记录（lù）都进行category_id及is_hot的比较，以此可（kě）以得出第二条SQL的CPU占用率明（míng）显比第一条（tiáo）低。

WHERE解析是（shì）采（cǎi）用自下而上的（de）顺序解（jiě）析WHERE子句，根据这个原理，表（biǎo）之（zhī）间的连（lián）接必须写在（zài）其他（tā）WHERE条件之前， 那（nà）些（xiē）可（kě）以（yǐ）过滤（lǜ）掉（diào）最大数（shù）量记录的条件必须写在（zài）WHERE子句（jù）的末尾（wěi）。 

3. 更多方面SQL优（yōu）化资料（liào）分享

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

ORACLE 的解析器（qì）按（àn）照（zhào）从右到左的顺序（xù）处理FROM子句（jù）中的表名，FROM子句中（zhōng）写在（zài）最后的表（biǎo）(基础（chǔ）表 driving table)将被最（zuì）先处理，在FROM子句中包含（hán）多个表的情（qíng）况下,你必须选择记录条数最少的表作为基（jī）础表。如果有3个（gè）以上的表连接查询, 那就需要选择交（jiāo）叉表（biǎo）(intersectiontable)作为基础表, 交（jiāo）叉表是（shì）指那（nà）个被其他表所引用的表.

（2） SELECT子（zǐ）句中避免使用 ‘ * ‘：

ORACLE在解析的过程中, 会将'*' 依（yī）次转（zhuǎn）换成所有的（de）列名, 这个工作是通（tōng）过查询数据字典完成的（de）, 这意味着（zhe）将（jiāng）耗费更多的时（shí）间。

（3） 减少访（fǎng）问（wèn）数据库的次数：

ORACLE在内部执行了许多工作: 解析（xī）SQL语句, 估算索引的（de）利用率, 绑定变量 , 读数据块等。

（4） 整合（hé）简单,无关联（lián）的数据库（kù）访问：

如（rú）果你有几个简单的数据库查询（xún）语句,你可以把它们整合到一个查询中(即使（shǐ）它们之间没（méi）有关系) 。

（5） 用（yòng）TRUNCATE替代DELETE：

当删除表中的记（jì）录时,在通常情况下, 回滚段(rollbacksegments ) 用来（lái）存放可以被（bèi）恢复的信息. 如果你没有（yǒu）COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地（dì）说是（shì）恢复（fù）到执（zhí）行删除命令（lìng）之前的（de）状况) 而当运用（yòng）TRUNCATE时, 回滚段不再存放任何可被恢复的信（xìn）息.当命令运行后,数（shù）据不能被（bèi）恢复.因此很（hěn）少的资源（yuán）被调用,执（zhí）行时间也（yě）会很短（duǎn）. (译者按: TRUNCATE只（zhī）在（zài）删（shān）除全表适用（yòng）,TRUNCATE是DDL不是（shì）DML) 。

（6） 尽量多使用（yòng）COMMIT：

只要有可能,在程序中尽量多使用COMMIT, 这样程（chéng）序的（de）性（xìng）能得（dé）到提高,需求也会因为（wéi）COMMIT所释放的资源（yuán）而减少，COMMIT所释放的（de）资源:

a. 回滚段上用于恢复（fù）数（shù）据的信（xìn）息.
b. 被程（chéng）序语句获得的锁（suǒ）
c. redo log buffer 中的空间

（7） 通（tōng）过内部函（hán）数（shù）提高（gāo）SQL效率：

复杂的SQL往往牺牲了执行（háng）效（xiào）率. 能够掌握上面的运用函（hán）数解决问题的方（fāng）法在实际工作中是非常有意义的（de）。

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

当在SQL语句中（zhōng）连接多个表时（shí）, 请使用表的别名并把别名前缀于（yú）每（měi）个Column上（shàng）.这样一来（lái）,就可（kě）以（yǐ）减少解析的时间并减少那些（xiē）由Column歧义引起的语法错误。

（9） 总是使（shǐ）用（yòng）索（suǒ）引的第一个列（liè）：

如果索引是（shì）建立在（zài）多个列上, 只有在（zài）它的第一个（gè）列(leading column)被（bèi）where子句引用时,优化器才会选择使用（yòng）该索引. 这也是一条（tiáo）简单而重要的（de）规（guī）则，当仅引用索（suǒ）引的第（dì）二个列时（shí）,优化（huà）器使（shǐ）用（yòng）了全表扫（sǎo）描而忽略了索引。

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

带有（yǒu）DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的（de）SQL语句会启动SQL引擎执（zhí）行耗费资源（yuán）的排（pái）序(SORT)功能. DISTINCT需要（yào）一次排序操作, 而其他的至少需（xū）要（yào）执行两次排序（xù）. 通（tōng）常（cháng）, 带（dài）有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写. 如（rú）果你的数据库的SORT_AREA_SIZE调配得好, 使（shǐ）用UNION , MINUS, INTERSECT也（yě）是可以考虑的, 毕竟它们的可读性很强。