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 

子句中使用参数，也会导致全表扫描。因为

SQL

只有在运行时才会解析局部变量，但优化程序不能将访问计划

的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择

的输入项。如下面语句将进行全表扫描：

  

select id from t where num=@num  

可以改为强制查询使用索引：

  

select id from t with(index(

索引名

)) where num=@num 

8.

应尽量避免在

 where 

子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

 

select id from t where num/2=100

应改为

:

select id from t where num=100*2

9.

应尽量避免在

where

子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

 

select id from t where substring(name,1,3)='abc'--name

以

abc

开头的

id

select id from t where datediff(day,createdate,'2005-11-30')=0--

‘2005

-11-

30’生成的

id

应改为

:

select id from t where name like 'abc%'

select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

10.

不要在

 where

子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

 

11.

在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用

该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

 

12.

不要写一些没有意义的查询，如需要生成一个空表结构：

 

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

 

create table #t(...)

13.

很多时候用

 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)

14.

并不是所有索引对查询都有效，

SQL

是根据表中数据来进行查询优化的，当索引列有大量数据重复时，

SQL

查询可能不会去

利用索引，如一表中有字段

sex

，

male

、

female

几乎各一半，那么即使在

sex

上建了索引也对查询效率起不了作用。

 

15.

索引并不是越多越好，

索引固然可以提高相应的

 select

的效率，

但同时也降低了

 insert

及

 update

的效率，

因为

 insert

或

 update

时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过

6

个，若太多则应

考虑一些不常使用到的列上建的索引是否有必要。

 

16.

应尽可能的避免更新

 clustered

索引数据列，因为

 clustered

索引数据列的顺序就是表记录的物理存储顺序，一旦该列值

改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新

 clustered

索引数据列，那么需要考虑是否应

将该索引建为

 clustered

索引。

 

17.

尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这

是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

 

18.

尽可能的使用

 varchar/nvarchar

代替

 char/nchar

，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来

说，在一个相对较小的字段内搜索效率显然要高些。

 

19.

任何地方都不要使用

 select * from t

，用具体的字段列表代替“*”，不要返回用不到的任何字段。

 

20.

尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

 

21.

避免频繁创建和删除临时表，以减少系统表资源的消耗。

 

22.

临时表并不是不可使用，

适当地使用它们可以使某些例程更有效，

例如，

当需要重复引用大型表或常用表中的某个数据集时。

但是，对于一次性事件，最好使用导出表。

 

23.

在新建临时表时，如果一次性插入数据量很大，那么可以使用

 select into

代替

 create table

，避免造成大量

 log

，以

提高速度；如果数据量不大，为了缓和系统表的资源，应先

create table

，然后

insert

。

 

24.

如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先

 truncate table

，然后

 drop table

，这样可

以避免系统表的较长时间锁定。

 

25.

尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过

1

万行，那么就应该考虑改写。

 

26.

使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

 

27.

与临时表一样，游标并不是不可使用。对小型数据集使用

 FAST_FORWARD

游标通常要优于其他逐行处理方法，尤其是在必须

引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标

的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

 

28.

在所有的存储过程和触发器的开始处设置

 SET NOCOUNT ON

，在结束时设置

 SET NOCOUNT OFF

。无需在执行存储过程和触

发器的每个语句后向客户端发送

 DONE_IN_PROC

消息。

 

29.

尽量避免大事务操作，提高系统并发能力。

 

30.

尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理

 

 

SQL

性能优化

 

1.

查询的模糊匹配

 

     

尽量避免在一个复杂查询里面使用

 

LIKE '%parm1%'

——

 

红色标识位置的百分号会导致相关列的索引无法使用，最好不要用

.

解决办法

:

其实只需要对该脚本略做改进，查询速度便会提高近百倍。改进方法如下：

 

        a

、修改前台程序

——

把查询条件的供应商名称一栏由原来的文本输入改为下拉列表，

用户模糊输入供应商名称时，直接在前台就帮忙定位到具

体的供应商，这样在调用后台程序时，这列就可以直接用等于来关联了。

 

        b

、直接修改后台

——

根据输入条件，先查出符合条件的供应商，并把相关记录保存在一个临时表里头，然后再用临时表去做复杂关联

 

2.

索引问题

 

       

在做性能跟踪分析过程中，经常发现有不少后台程序的性能问题是因为缺少合适索引造成的，有些表甚至一个索引都没有。这种情况往往都是

因为在设计表时，没去定义索引，而开发初期，由于表记录很少，索引创建与否，可能对性能没啥影响，开发人员因此也未多加重视。然一旦程序发

布到生产环境，随着时间的推移，表记录越来越多，这时缺少索引，对性能的影响便会越来越大了。

 

       

这个问题需要数据库设计人员和开发人员共同关注

 

法则：不要在建立的索引的数据列上进行下列操作

:

 

避免对索引字段进行计算操作

 

避免在索引字段上使用

not

，

<>

，

!=

避免在索引列上使用

IS NULL

和

IS NOT NULL

避免在索引列上出现数据类型转换

 

避免在索引字段上使用函数

 

避免建立索引的列中使用空值。

 

3.

复杂操作

 

部分

UPDATE

、

SELECT

语句

 

写得很复杂（经常嵌套多级子查询）

——

可以考虑适当拆成几步，先生成一些临时数据表，再进行关联操作

 

4.update

 

同一个表的修改在一个过程里出现好几十次，如：

 

                update table1

                set

 col1=...

                where col2=...;

                 

                update table1

                set

 col1=...

                where col2=...

                ......

       

象这类脚本其实可以很简单就整合在一个

UPDATE

语句来完成（前些时候在协助

xxx

项目做性能问题分析时就发现存在这种情况）

 

5.

在可以使用

UNION ALL

的语句里，使用了

UN

ION

 

UNION

因为会将各查询子集的记录做比较，故比起

UNION ALL

，通常速度都会慢上许多。一般来说，如果使用

UNION ALL

能满足要求的话，

 

               

务必使用

UNION ALL

。还有一种情况大家可能会忽略掉，就是虽然要求几个子集的并集需要过滤掉重复记录，但由于脚本的特殊性，不

可能存在重复记录，这时便应该使用

UNION ALL

，如

xx

模块的某个查询程序就曾经存在这种情况，见，由于语句的特殊性，在这个脚本

 

               

中几个子集的记录绝对不可能重复，故可以改用

UNION ALL

）

 

6.

在

WHERE

语句中，尽量避免对索引字段进行计算操作

 

               

这个常识相信绝大部分开发人员都应该知道，

但仍有不少人这么使用，我想其中一个最主要的原因可能是为了编写方便吧，但如果仅为了

编

 

               

写简单而损害了性能，那就不可取了

 

                9

月份在对

XX

系统做性能分析时发现，有大量的后台程序存在类似用法，如：

 

                ......

                where trunc(creat

e_date)=trunc(:dat

e1)

               

虽然已对

creat

e_date

字段建了索引，但由于加了

TRUNC

，使得索引无法用上。此处正确的写法应该是

 

                where create_date>=trunc(:date1) and create_dat

e<trunc(:dat

e1)+1

               

或者是

 

                where create_date between trunc(:date1) and trunc(:dat

e1)+1-1/(24*60*60)

               

注意：因

between

的范围是个闭区间（

great

er than or equal to low value and less than or equal to high value.

），

 

               

故严格意义上应该再减去一个趋于

0

的小数，这里暂且设置成减去

1

秒（

1/(24*60*60)

），如果不要求这么精确的话，可以略掉这步

 

7.

对

W

here

语句的法则

 

7.1

避免在

WHERE

子句中使用

in

，

not in

，

or

或者

having

。

 

可以使用

 exist

和

not exist

代替

 

in

和

not in

。

 

可以使用表链接代替

 

exist

。

 

Having

可以用

where

代替，如果无法代替可以分两步处理。

 

例子

 

SELECT *

FROM ORDERS WHERE CUSTOMER_NAME NOT IN

 

                    (SELECT CUSTOMER_NAME FROM CUSTOMER)

优化

 

SELECT *

FROM ORDERS WHERE CUSTOMER_NAME not exist

 

                    (SELECT CUSTOMER_NAME FROM CUSTOMER)

7.2

不要以字符格式声明数字，要以数字格式声明字符值。（日期同样）

 

否则会使索引无效，产生全表扫描。

 

例子

 

使用：

SELECT emp.ename, emp.job F

ROM emp WHERE emp.empno = 7369;

不要使用：

SELECT emp.ename, emp.job F

ROM emp WHERE emp.empno = ‘7369’

 

7.3 WHER

E

后面的条件顺序影响

 

 Oracle

从下到上处理

Where

子句中多个查询条件，所以表连接语句应写在其他

Where

条件前，可以过滤掉最大数量记录的条件必须写在

Where

子句的末尾。

 

       

       

WHERE

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

 

 

    Select * from zl_yhjbqk where dy_dj = '1KV

以下

' and xh_bz=1

 

    Select * from zl_yhjbqk where xh_bz=1

and dy_dj = '1KV

以下

'

 

   

以上两个

SQL

中

dy_dj

（

电压等级）

及

xh_bz

（销户标志）

两个字段都没进行索引，

所以执行的时候都是全表扫描，

第一条

SQL

的

dy_dj = '1KV

以下

'

条件在记录集内比率为

99%

，

而

xh_bz=1

的比率只为

0.5%

，在进行第一条

SQL

的时候

99%

条记录都进行

dy_dj

及

xh_bz

的比较，而在进

行第二条

SQL

的时候

0.5%

条记录都进行

dy_dj

及

xh_bz

的比较，以此可以得出第二条

SQL

的

CPU

占用率明显比第一条低。

 

8.

对

Select

语句的法则

 

在应用程序、包和过程中限制使用

select

 * from table

这种方式。

 

例子

 

使用

 

SELECT empno,ename,category FROM emp WHERE empno = '7369‘

 

而不要使用

 

SELECT * FROM emp WHERE empno = '7369'

9.

排序

 

避免使用耗费资源的操作

 

带有

DISTINCT,UNION,MINUS,INTERSECT,ORDER BY

的

SQL

语句会启动

SQL

引擎

 

执行，耗费资源的排序

(SORT)

功能

. DISTINCT

需要一

次排序操作

,

而其他的至少需要执行两次排序

 

10.

临时表

 

慎重使用临时表可以极大的提高系统性能

 

 11.ORDER BY

 

ORDER BY

子句只在两种严格的条件下使用索引

.

ORDER BY

中所有的列必须包含在相同的索引中并保持在索引中的排列顺序

.

ORDER BY

中所有的列必须定义为非空

.

 

12.SQL

书写的影响（共享

SQ

L

语句可以提高操作效率）

 

同一功能同一性能不同写法

SQL

的影响

 

 

   

如一个

SQL

在

A

程序员写的为

 

    Select * from zl_yhjbqk

 

    B

程序员写的为

 

    Select * from dlyx.zl_yhjbqk

（带表所有者的前缀）

 

 

    C

程序员写的为

 

    Select * from DLYX.ZLYHJBQK

（大写表名）

 

 

    D

程序员写的为

 

    Select *

from DLYX.ZLYHJBQK

（中间多了空格）

 

 

     

以上四个

SQL

在

ORACLE

分析整理之后产生的结果及执行的时间是一样的，

但是从

ORACLE

共享内存

SGA

的原理，

可以得出

ORACLE

对每个

SQL

都会对其进行一次分析，

并且占用共享内存，如果将

SQL

的字符串及格式写得完全相同则

ORACLE

只会分析一次，共享内存也只会留下一次的

分析结果，这不仅可以减少分析

SQL

的时间，而且可以减少共享内存重复的信息，

ORACLE

也可以准确统计

SQL

的执行频率。

 

 

   

推荐方案：不同区域出现的相同的

Sql

语句，要保证查询字符完全相同，以利用

SGA

共享池，防止相同的

Sql

语句被多次分析。

 

IN

其实与等于相似，比如

in(1,2)

就是

 

= 1 or = 2

的一种简单写法，所以一般在元素少的时候使用

IN

，如果多的话就用

exists

 

exists

的用法跟

in

不一样，一般都需要和子表进行关联，而且关联时，需要用索引，这样就可以加快速度。

 

 

你的

SQL

语句用

NOT EXISTS

可以写成

 

select MC001 from BOMMC WHERE NOT EXISTS (SELECT MD001 FROM BOMMD where BOMMC.MC001 = BOMMD.MD001)