首页 > 代码库 > SQL优化大全心得

SQL优化大全心得

目录

作者整理:周海平(kider)

概要... 1

优化的理由... 2

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

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

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

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

5.ORDER BYGROPU BY.. 4

6.对列的操作... 4

7.避免使用OR. 4

8.IN来替换OR. 5

9.FROM.. 5

10.WHERE. 6

11.isnullis 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语句能否在这两个用户之间共享.

SQL

能否共享

原因

select max(sal_cap) from sal_limit;

不能

每个用户都有一个private  synonym - sal_limit , 它们是不同的对象

select count(*) from work_city where  sdesc like ‘NEW%‘;

两个用户访问相同的对象public  synonym - work_city

select a.sdesc,b.location from work_city  a , plant_detail b where a.city_id = b.city_id

不能

用户jack 通过private synonym访问plant_detail jill 是表的所有者,对象不同.


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 scanrange 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中没有用到empindex,因为没有where子句。而2中的语句对emp进行的是range scan


通常来说,采用表连接(emp.depart_cod=depart.depart_code)的方式比EXISTS更有效率,所以:连接existsIN

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 BYGROPU BY

使用ORDER BYGROUP 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 nullis not null

where子句中使用is nullis 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 REGIONAVG(LOG_SIZE)

           FROM LOCATION

           GROUP BY REGION

           HAVING REGION REGION != ‘SYDNEY’

          ANDREGION != ‘PERTH’

高效

            SELECTREGIONAVG(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中建议用索引,它的速度时极快的。遗憾的是,索引也有相关的开销。每次向表中写入时(如INSERTUPDATEHDELETE),如果带有一个或多个索引,那么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的性能优化是一个复杂的过程,上述这些只是在应用层次的一种体现,深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。