1、常见优化规则1

一、为什么要对SQL进行优化

我们开发项目上线初期，由于业务数据量相对较少，一些SQL的执行效率对程序运行效率的影响不太明显，而开发和运维人员也无法判断SQL对程序的运行效率有多大，故很少针对SQL进行专门的优化，而随着时间的积累，业务数据量的增多，SQL的执行效率对程序的运行效率的影响逐渐增大，此时对SQL的优化就很有必要。

二、SQL优化的一些方法

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
5.in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
6.下面的查询也将导致全表扫描：
select id from t where name like ‘%abc%’
7.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num=1002
8.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name,1,3)=’abc’—name以abc开头的id
应改为:
select id from t where name like ‘abc%’
9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
11.不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(…)
12.很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)
13.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
14.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，
因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。
一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
15.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
16.尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，
其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
17.任何地方都不要使用 select from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
18.避免频繁创建和删除临时表，以减少系统表资源的消耗。 19.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
20.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，
以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。 21.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
22.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
23.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。 24.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。
在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。 25.尽量避免大事务操作，提高系统并发能力。 26.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

2、常见优化规则2

2.1 表连接数

  • 连接的表越多，性能越差
  • 可能的话，将连接拆分成若干个过程逐一执行
  • 优先执行可显著减少数据量的连接，既降低了复杂度，也能够容易按照预期执行
  • 如果不可避免多表连接，很可能是设计缺陷
  • 外链接效果差，因为必须对左右表进行表扫描
  • 尽量使用inner join查询

2.2 使用临时表

如果不可避免，可以考虑使用临时表或表变量存放中间结果。

2.3 少用子查询

2.4 视图嵌套

不要过深,一般视图嵌套不要超过2个为宜。

3、SQL编写注意事项

3.1 NULL列

Null列使用索引没有意义，任何包含null值的列都不会被包含在索引中。因此where语句中的is null或is not null的语句优化器是不允许使用索引的。

3.2 concat或||

concat或||是mysql和oracle的字符串连接操作，如果对列进行该函数操作，那么也开会忽略索引的使用。比较下面的查询语句： — 忽律索引 select … from .. where first_name || ‘’ || last_name = ‘bill gates’ ; — 使用索引 select … from .. where first_name = ‘bill’ and last_name = ‘bill gates’ ; #### 3.3 like 通配符出现在首位，无法使用索引，反之可以。 — 无法使用索引 select .. from .. where name like ‘%t%’ ; — 可以使用索引 select .. from .. where name like ‘t%’ ; #### 3.4 order by order by子句中不要使用非索引列或嵌套表达式，这样都会导致性能降低。

3.5 Not运算

not运算无法使用索引，可以改成其他能够使用索引的操作。如下： — 索引无效 select .. from .. where sal != 3000 ; — 索引生效 select .. from .. where sal < 3000 or sal > 3000; #### 3.6 where与having select .. from .. on .. where .. group by .. having .. order by .. limit ..，以上是sql语句的语法结构，其中on、where和having是有过滤行为的，过滤行为越能提前完成就越可以减少传递给下一个阶段的数据量，因此如果在having中的过滤行为能够在where中完成，则应该优先考虑where来实现。

3.7 exists替代in

not in是最低效的，因为要对子查询的表进行全表扫描。可以考虑使用外链接或not exists。如下： — 正确 SELECT FROM EMP WHERE EMPNO > 0 AND EXISTS (SELECT ‘X‘ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’) — 错误 SELECT FROM EMP WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB‘)

3.8 索引

索引的好处可以实现折半查找，时间复杂度是

O(log2n)O(log2n)

，但是也有成本，需要额外的空间存放索引数据，并且每次insert、update和delete都会对索引进行更新，因此会多增加4、5次的磁盘IO。所以给一些不必要使用索引的字段增加索引，会降低系统的性能。对于oracle来讲，SQL语句尽量大写，内部需要向将小写转成大写，再执行。

不要在索引列上使用函数，这样会停止使用索引，进行全表扫描，如下： — 错误 SELECT … FROM DEPT WHERE SAL * 12 > 25000; — 正确 SELECT … FROM DEPT WHERE SAL > 25000/12;
#### 3.9 >与>= — 直接定位到4的记录(推荐) select .. from .. where SAL >= 4 ; — 先定位到3，再向后找1个(不推荐) select .. from .. where SAL > 3 ; #### 3.10 union代替or 在索引列上，可以使用union替换or操作。索引列上的or操作会造成全表扫描。 — 高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = ‘MELBOURNE’ — 低效: SELECT LOC_ID ,LOC_DESC ,REGION FROM LOCATION WHERE LOC_ID=10 OR REGION =‘MELBOURNE’

3.11 is null & is not null

如果列可空，避免使用索引。对于多个列使用的索引，起码保证至少有个列不为空。对于多列索引，只有访问了第一个列才会启用索引，如果访问后面的列则使用的是全表扫描。

— 低效: (索引失效) SELECT .. FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL; — 高效: (索引有效) SELECT .. FROM DEPARTMENT WHERE DEPT_CODE >=0;

3.12 union & union all

union具有去重的操作，增加了计算时间。union all不需要去重，但会包含相同记录。同样功能下，首选union all操作。2、常见优化规则

2.1 表连接数

连接的表越多，性能越差

可能的话，将连接拆分成若干个过程逐一执行

优先执行可显著减少数据量的连接，既降低了复杂度，也能够容易按照预期执行

如果不可避免多表连接，很可能是设计缺陷

外链接效果差，因为必须对左右表进行表扫描

尽量使用inner join查询

2.2 使用临时表

如果不可避免，可以考虑使用临时表或表变量存放中间结果。

2.3 少用子查询

2.4 视图嵌套

不要过深,一般视图嵌套不要超过2个为宜。

3、SQL编写注意事项

3.1 NULL列

Null列使用索引没有意义，任何包含null值的列都不会被包含在索引中。因此where语句中的is null或is not null的语句优化器是不允许使用索引的。

3.2 concat或||

concat或||是mysql和oracle的字符串连接操作，如果对列进行该函数操作，那么也开会忽略索引的使用。比较下面的查询语句：

— 忽律索引

select … from .. where first_name || ‘’ || last_name = ‘bill gates’ ;

— 使用索引

select … from .. where first_name = ‘bill’ and last_name = ‘bill gates’ ;

3.3 like

通配符出现在首位，无法使用索引，反之可以。

— 无法使用索引

select .. from .. where name like ‘%t%’ ;

— 可以使用索引

select .. from .. where name like ‘t%’ ;

3.4 order by

order by子句中不要使用非索引列或嵌套表达式，这样都会导致性能降低。

3.5 Not运算

not运算无法使用索引，可以改成其他能够使用索引的操作。如下：

— 索引无效

select .. from .. where sal != 3000 ;

— 索引生效

select .. from .. where sal < 3000 or sal > 3000;

3.6 where与having

select .. from .. on .. where .. group by .. having .. order by .. limit ..，以上是sql语句的语法结构，其中on、where和having是有过滤行为的，过滤行为越能提前完成就越可以减少传递给下一个阶段的数据量，因此如果在having中的过滤行为能够在where中完成，则应该优先考虑where来实现。

3.7 exists替代in

not in是最低效的，因为要对子查询的表进行全表扫描。可以考虑使用外链接或not exists。如下：

— 正确

SELECT *

FROM EMP

WHERE 

  1. EMPNO > 0 
  3. AND  EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')

— 错误

SELECT *

FROM EMP

WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’)

3.8 索引

索引的好处可以实现折半查找，时间复杂度是

O(log2n)

，但是也有成本，需要额外的空间存放索引数据，并且每次insert、update和delete都会对索引进行更新，因此会多增加4、5次的磁盘IO。所以给一些不必要使用索引的字段增加索引，会降低系统的性能。对于oracle来讲，SQL语句尽量大写，内部需要向将小写转成大写，再执行。

不要在索引列上使用函数，这样会停止使用索引，进行全表扫描，如下：

— 错误

SELECT … FROM DEPT WHERE SAL * 12 > 25000;

— 正确

SELECT … FROM DEPT WHERE SAL > 25000/12;

3.9 >与>=

— 直接定位到4的记录(推荐)

select .. from .. where SAL >= 4 ;

— 先定位到3，再向后找1个(不推荐)

select .. from .. where SAL > 3 ;

3.10 union代替or

在索引列上，可以使用union替换or操作。索引列上的or操作会造成全表扫描。

— 高效:

SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10

UNION

SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = ‘MELBOURNE’

— 低效:

SELECT LOC_ID ,LOC_DESC ,REGION FROM LOCATION WHERE LOC_ID=10 OR REGION =’MELBOURNE’

3.11 is null & is not null

如果列可空，避免使用索引。对于多个列使用的索引，起码保证至少有个列不为空。对于多列索引，只有访问了第一个列才会启用索引，如果访问后面的列则使用的是全表扫描。

— 低效: (索引失效)

SELECT .. FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;

— 高效: (索引有效)

SELECT .. FROM DEPARTMENT WHERE DEPT_CODE >=0;

3.12 union & union all

union具有去重的操作，增加了计算时间。union all不需要去重，但会包含相同记录。同样功能下，首选union all操作。