目录

作者整理：周海平(kider)

概要... 1

优化的理由... 2

1.大小写对SQL语句的影响（ORACLE）... 2

2.尽量使用(NOT) EXISTS 替代( NOT)IN这样的操作... 3

3.在海量查询时尽量少用格式转换... 4

4.查询海量数据是，可以使用optimizer hints(ORACLE)4

5.ORDER BY和GROPU BY.. 4

6.对列的操作... 4

7.避免使用OR. 4

8.用IN来替换OR. 5

9.FROM.. 5

10.WHERE. 6

11.isnull或is notnull. 6

12.SELECT子句中避免使用‘ * ‘6

13.避免在索引列上使用计算... 6

14.Orderby语句... 7

15.通配符... 7

16.避免相关子查询... 7

17.用Where子句替换HAVING子句(ORACLE)7

18.联合... 8

19.让MYSQL服务器做的更好... 8

20.本地缓存数据... 8

21.索引准则... 9

22.尽量为所有在WHERE子句中被引用的列建立索引... 9

23.尽可能的使用唯一索引... 9

24.利用多列索引... 10

25.考虑不为某些列建立索引... 10

26.惯用的一些技巧... 10

27.尽量避免的事情... 10

28.表锁定... 11

29.慎用游标... 11

总结... 11

概要

和设计与调整数据库一样，优化执行SQL语句可以提高应用程序的性能。如果不遵循一些基本的原则，那么无论数据库结构设计得如何合理，无论数据库调整得如何好，将不会得到令用户满意得查询结果。对于SQL查询，应明确要完成得目标，并努力是查询效率最高，以最少得时间准确地检索数据。如果最终用户等待的是一个低速的查询，就好比饥饿者不耐烦地等待迟迟不到地饭菜。尽管大多数查询可有多种方式来完成，但查询的方式不同是导致同一查询执行时间为几秒种、几分钟或为几个小时的主要原因。

在此我们通过对MySQL的慢日志监控，找出主要的SQL语句运行慢的问题。然后对这些SQL进行优化。这里列出一些SQL语句优化的原则和方法，供参考。

优化的理由

1)SQL语句是对数据库(数据)进行操作的惟一途径；

2)SQL语句消耗了70%~90%的数据库资源；

3)SQL语句独立于程序设计逻辑，相对于对程序源代码的优化，对SQL语句的优化在时间成本和风险上的代价都很低；

4)SQL语句可以有不同的写法；

5)SQL语句易学，难精通。

1.大小写对SQL语句的影响（ORACLE）

SQL语句存放在内存中，可以被所有的数据库用户共享.因此,当你执行一个SQL语句时，如果它和之前的执行过的语句完全相同,就能很快获得已经被解析的语句。

当你向服务器提交一个SQL语句,首先会在指定内存中查找相同的语句。这里需要注明的是,两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同(包括空格,换行等).

A.字符级的比较:

当前被执行的语句和共享池中的语句必须完全相同.

例如:

        SELECT * FROM EMP;

和下列每一个都不同

        SELECT * from EMP;

        Select *  From Emp;

B.两个语句所指的对象必须完全相同:

例如：

用户对象名如何访问（声明部分）

Jack    sal_limit  private synonym

        Work_city          public synonym

        Plant_detail       publicsynonym

Jill      sal_limit  private synonym

        Work_city          public synonym

        Plant_detail tableowner

考虑一下下列SQL语句能否在这两个用户之间共享.

C.两个SQL语句中必须使用相同的名字的绑定变量(bind variables)

例如：第一组的两个SQL语句是相同的(可以共享),而第二组中的两个语句是不同的(即使在运行时,赋于不同的绑定变量相同的值)

a.

select pin , name from people where pin = :blk1.pin;

select pin , name from people where pin = :blk1.pin;

b.

select pin , name from people where pin = :blk1.ot_ind;

select pin , name from people where pin = :blk1.ov_ind;

2.尽量使用(NOT) EXISTS 替代( NOT)IN这样的操作

EXISTS要远比IN的效率高。里面关系到full table scan和range scan。几乎将所
有的IN操作符子查询改写为使用EXISTS的子查询。

语句1

SELECT dname, deptno

FROM dept

WHERE deptno NOT IN

(SELECT deptno FROM emp);

语句2

SELECT dname, deptno

FROM dept

WHERE NOT EXISTS

(SELECT deptno

FROM emp

WHERE dept.deptno = emp.deptno);

明显的，2要比1的执行性能好很多，因为1中对emp进行了full table scan,这是很浪费时间的操作。而且1中没有用到emp的index，因为没有where子句。而2中的语句对emp进行的是range scan。

通常来说，采用表连接（emp.depart_cod=depart.depart_code）的方式比EXISTS更有效率，所以：连接＞exists＞IN。

3.在海量查询时尽量少用格式转换

如用

WHERE a.order_no = b.order_no
而不用

WHERE TO_NUMBER (substr(a.order_no, instr(b.order_no, ‘.‘) - 1)= TO_NUMBER (substr(a.order_no, instr(b.order_no, ‘.‘) - 1)

4.查询海量数据是，可以使用optimizer hints(ORACLE)

例如/*+ORDERED */

如

SELECT /*+ FULL(EMP) */ E.ENAME

FROM EMP E

WHERE E.JOB = ‘CLERK‘;

而不是

SELECT E.ENAME

FROM EMP E

WHERE E.JOB || ‘‘ = ‘CLERK‘;

5.ORDER BY和GROPU BY

使用ORDER BY和GROUP BY短语，任何一种索引都有助于SELECT的性能提高。注意如果索引列里面有NULL值，Optimizer将无法优化。

6.对列的操作

任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

7.避免使用OR

通常情况下，用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描。注意，以上规则只针对多个索引列有效。如果有column没有被索引,查询效率可能会因为你没有选择OR而降低。

例如：

LOC_ID 和REGION上都建有索引.

高效:

   SELECTLOC_ID , LOC_DESC , REGION

 FROM LOCATION

       WHERE LOC_ID = 10

       UNION

       SELECT LOC_ID , LOC_DESC , REGION

       FROM LOCATION

       WHEREREGION = “MELBOURNE”

低效:

       SELECTLOC_ID , LOC_DESC , REGION

       FROMLOCATION

WHERE LOC_ID = 10 ORREGION = “MELBOURNE”

如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.

注意:

WHERE KEY1 = 10   (返回最少记录)

OR KEY2 = 20      (返回最多记录)

（ORACLE 内部将以上转换为WHERE KEY1 = 10 AND ((NOT KEY1 =10) AND KEY2 = 20)）

8.用IN来替换OR

低效:

SELECT….

FROM LOCATION

WHERE LOC_ID = 10

OR    LOC_ID = 20

OR    LOC_ID = 30

高效

SELECT…

FROM LOCATION

WHERE LOC_IN IN(10,20,30);

（这是一条简单易记的规则，但是实际的执行效果还须实际运行中检验）

9.FROM

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

例如:  

表 TAB1  16,384 条记录

表 TAB2    1   条记录

选择TAB2作为基础表 (最好的方法)

           select count(*) from tab1,tab2   执行时间0.96秒

选择TAB2作为基础表 (不佳的方法)

           select count(*) from tab2,tab1   执行时间26.09秒

10.WHERE

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

例如:

(低效,执行时间156.3秒)

SELECT …

FROM EMP E

WHERE  SAL > 50000

AND    JOB = ‘MANAGER’

AND    25 < (SELECTCOUNT(*) FROM EMP  WHERE MGR=E.EMPNO);

(高效,执行时间10.6秒)

SELECT …

FROM EMP E

WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO)

AND    SAL > 50000

AND    JOB = ‘MANAGER’;

11.is null或is not null

在where子句中使用is null或is not null的语句优化器是不允许使用索引的，尽量不用，少使用或不使用NOT。

12.SELECT子句中避免使用 ‘ * ‘

13.避免在索引列上使用计算

如（WHERE SAL  > 25000/12）

14.Order by语句

任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。

15.通配符

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

例如：

在下面的查询中索引得到了使用

select * from employee where last_name like ‘c%‘;

16.避免相关子查询

一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。

17.用Where子句替换HAVING子句(ORACLE)

避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作。如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销。

例如：

低效:

           SELECT REGION，AVG(LOG_SIZE)

           FROM LOCATION

           GROUP BY REGION

           HAVING REGION REGION != ‘SYDNEY’

          ANDREGION != ‘PERTH’

高效：

            SELECTREGION，AVG(LOG_SIZE)

           FROM LOCATION

           WHERE REGION REGION != ‘SYDNEY’

           AND REGION != ‘PERTH’

           GROUP BY REGION

(HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等. 除此而外,一般的条件应该写在WHERE子句中)

18.联合

最新MYSQL（如MYSQL4.0）的特征之一是对SQL联合的支持。联合（union）是一个将多个选择结果组合成为一个结果集的工具。

例如：

select first_name,last_name

from author

union

select fname,lname

from editor;

如果一个人既是作者又是编辑让它显示两次，如：

select first_name,last_name

from author

union all

  select fname,lname

from editor;

19.让MYSQL服务器做的更好

例如，要检索表中的几行，有如下程序：

○ for (int I = 0 ; I++; I<keymax){

select * from foobar where key=I;

process the row

}    

● select * from foobar wherekey< keymax;

for each row{

process the row

}    

前一种方法浪费MYSQL要在每次循环迭代时花费解析、优化和执行同一查询的开销。

后一种好些，让MYSQL一次检索出所有的行，避免多余的开销。

20.本地缓存数据

在应用中缓存数据一般会使性能得到提升。如客户订购应用中，它接收州的缩写作为输入，并根据数据库中的数据扩展为完整的州名。

Accept input from user into $state_abbr

Select state_name into $state_name from state wherestate_abbr=$state_abbr;

Set display field =$state_name

但是我们知道各州的查询不会经常变化，所以缓存各州的表将达到提高性能的目的。如：

#在应用启动时

select state_name,state_abbr from state;

for each row{

load state name into state_name

hash table indexed by state_abbr

}

accept inputfrom user into $state_abbr

set displayfield =state_name[$state_abbr]    

在此，每当用户输入一个定单时都会在数据库中保存一个查询。而查询工作在应用启动时就完成了。缓存数据适合于相对固定的信息，不希望对很快过期的数据进行缓存，因为保存本地缓存得到更新可能会比保存缓存中的数据开销更大。

21.索引准则

适当的索引对应用的性能至关重要，而且在MYSQL中建议用索引，它的速度时极快的。遗憾的是，索引也有相关的开销。每次向表中写入时（如INSERT、UPDATEH或DELETE），如果带有一个或多个索引，那么MYSQL也要更新各个索引。这样索引就增加了对各个表的写入操作的开销。此外，索引增加了数据库的规模。只有当某列被用于WHERE子句时，才能享受到索引的性能提升的好处。如果不使用索引，它就没有价值，而且会带来维护上的开销。

如果索引不经常用，那么它可能不值得维护，但如果能明显减少花费的时间有值得维护，所以这些决定必须要适合应用的需要。

但也有折衷的办法。

22.尽量为所有在WHERE子句中被引用的列建立索引

作为一般目标，可能希望为所有在WHERE子句中被引用的列建立索引。但是也有例外。如果用<、>=、=、 <=、>和BETWTEEN运算符比较或连接某些列，此时索引将被用到。而如果在WHERE子句中对某些列使用函数，那么将不会使用该列的索引。例如：

select * fromemployee where left(name,6)=’FOOBAR’

将不能利用name列的索引。

又例如：

select * fromemployee where name like ‘%FOOBAR’

也不利用name列的索引

相反，如果模式中有字符串前缀，则LIKE运算符将使用索引。如：

select * fromemployee where name like ‘FOOBAR%’

如前所述，要注意不要盲目为WHERE子句中被引用的列创建索引。维护索引的代价应当同性能的收益相均衡。

23.尽可能的使用唯一索引

如果知道索引中的数据是唯一的，如主键或其他键，就要使用唯一索引。唯一索引比普通索引更能提高性能。MYSQL能够利用值为唯一这一点来做出更为优化的假设。

24.利用多列索引

设计良好的多列索引可以减少所需索引的总数量。如果合适，MYSQL将使用多列索引的左边部分。设计很差的索引带来的后果是从不使用或极少使用。

熟悉应用查询情况对于确定多列索引是非常重要的。可以使用EXPLAIN SELECT工具验证结果（此工具很好用）。

25.考虑不为某些列建立索引

有时候，进行全表浏览要比必须读取索引和数据表更快，尤其是当索引包含的是平均分布的数据集是更是如此。对此典型的例子是性别，它有两个均匀分布的值（男和女）。通过性别需要读取大概一半的行。在种情况下进行全表扫描浏览要更快。

因此，需要始终对应用进行测试，以查看如何工作最合适。

26.惯用的一些技巧

• Use short primary keys. Use numbers, not strings,     when joining tables.

• When using multi-part keys, the first part should be     the most-used key.

• When in doubt, use columns with more duplicates     first to get better key compression.

• If you run the client and MySQL server on the same     machine, use sockets instead of TCP/IP when connecting to MySQL (this can     give you up to a 7.5 % improvement). You can do this by specifying no     hostname or localhost when connecting to the     MySQL server.

• Use --skip-locking (default on some OSes) if possible. This will turn off external     locking and will give better performance.

• Use application-level hashed values instead of using     long keys:

·  SELECT * FROM table_name WHERE hash=MD5(concat(col1,col2)) AND

·  col_1=‘constant‘ AND col_2=‘constant‘

• Store BLOB‘s that you need to access as files in     files. Store only the file name in the database.

• It is faster to remove all rows than to remove a     large part of the rows.

• If SQL is not fast enough, take a     look at the lower level interfaces to access the data.

27.尽量避免的事情

• Updates to a table or INSERT on a table with deleted rows, combined with SELECTS that take a long time.

• JOINS without using keys or keys which are not unique     enough.

• JOINS on columns that have different column types.

• Using HEAP tables when not using a full key match     with =

• Forgetting a WHERE clause with UPDATE or DELETE in the MySQL monitor. If you tend to do this, use the --i-am-a-dummy option to the mysq client.

28.表锁定

表锁定是低水平的事务处理。简而言之，MYSQL允许你锁定一组表，使得只有一个客户可以使用。与事物不同，它不限制表的类型。但是你不能回滚对已锁定表锁进行的操作。

锁定有两个基本功能：

A、操作一组表的多条语句，使它们作为一个工作单元来执行

B、在某些情况下使多个更新执行得更快

MYSQL支持三种锁定：

读、本地读和写

如：

lock tablesaccount write;

select@bal:=balance from account where account_id = 1;

update accountset balance=@bal*0.03 where account_id =1;

unlock tables;

29.慎用游标

在某些必须使用游标的场合，可考虑将符合条件的数据行转入临时表中，再对临时表定义游标进行操作，这样可使性能得到明显提高。

总结

经验显示，性能的最大改进得益于逻辑的数据库设计、索引设计和查询设计方面。反过来说，最大的性能问题常常是由其中这些相同方面中的不足引起的。其实SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，减少表扫描的I/O次数，尽量避免表搜索的发生。

其实SQL的性能优化是一个复杂的过程，上述这些只是在应用层次的一种体现，深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。