SQL优化

SQL优化

1、 注重细节 。

我们在程序中编写 sql 语句时，通常都会在数据库工具中跑一下，我通常遇到最多的就是在变量之间未分隔开，变量名书写不正确等。因此在 sql 中，首先要注意书写规范。虽然数据库不区分大小写，但是 sql 语句在执行时， ORACLE 会将它全部转化为大写，如果在写 SQL 语句时时就全部写成大写，能节省些资源。

2 、使用表的别名

当 SQL 语句中涉及到多个表时，使用表的别名，并将别前缀在每一个列上，这样可以减少简析时间，避免一些语法上的歧义造成的错误。

3、  3、 在有条件查询和连接并存时，我们应尽量先做条件查询再进行连接

我曾写过一条语句如下：

select n.adm_no adm_no1,m.adm_no adm_no2 ,m.third_name ,n.quick from

    (select adm_no,third_name from T_COMPLAIN_ADMLOG where  log_time in

(select max(log_time) from T_COMPLAIN_ADMLOG  where N_STATE='YJSFXC'group by adm_no)) m

                 full join

                   (select distinct adm_no,quick from T_COMPLAIN_ADMLOG where quick=1 ) n

                       on m.adm_no=n.adm_no

 

在这条语句中先做 distinct 与后做 distinct 的性能在差距上是很明显的，前者只花了几十毫秒，后者则花费 6~7 秒时间。

 

4、  4、 合理使用 in ， exists 有助于提高效率

In 是把外表和内表做 hash 连接，而 exists 是对外表作 loop 循环，每次 loop 循环在对内表进行查询，对于认为 exists 始终比 in 效率高的说法是不准确的

如果内表和外表大小相当，那么使用 exists 和使用 in 的效率差别不大，如果两个表一个大一个小，则子查询大的表用 exists ，子查询小的用 in ，

例如：表 A （大表）、表 B( 小表 ) 

select * from B where B.c1 in (select A.c1 from A) 效率低

Select  *  from B where exists(select A.c1  from A  where  A.c1=B.c1)  效率高

 

而 not  in 和 not exists 相比 not in 对内外表都使用全表扫描，没有使用索引， not exists 仍能对内表使用索引。因此不管内外表谁大， not exists 都比 not in 快。

5 、“非”操作符无法使用索引 ，如： NOT 、 != 、 <> 、 !< 、 !> 、 NOT IN 、 NOT LIKE

   对于以下例子：

   Select * from   t_ employee  where salary <>3000;

可以改写为：

  Select * from  t_employee  where salary<3000 or salary>3000;

两者的查询结果一样，但是第二种的查询方案会叫第一种更快，第二种查询允许对 salary 列使用索引，第一种不行  

6 、 > 及 < 操作符 （ 大于或小于操作符 ）

大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有 100 万记录，一个数值型字段 A ， 30 万记录的 A=0 ， 30 万记录的 A=1 ， 39 万记录的 A=2 ， 1 万记录的 A=3 。那么执行 A>2 与 A>=3 的效果就有很大的区别了，因为 A>2 时 ORACLE 会先找出为 2 的记录索引再进行比较，而 A>=3 时 ORACLE 则直接找到 =3 的记录索引。

7 、 like 操作符

我们在进行模糊查询时，如果有可能尽量少使用 %X%  而是用 AX% 和 BX% ，因为前者会产生全表扫描，后者则是采用索引 AX 、 BX 的索引，效率更高。

8 、IS NULL 或 IS NOT NULL 操作 （ 判断字段是否为空 ）

判断字段是否为空会导致系统无法使用索引，因为索引是不索引空值的。

推荐方案：用其它相同功能的操作运算代替，如： a is not null 改为 a>0 或 a>’’ 等。不允许字段为空，而用一个缺省值代替空值，如申请中状态字段不允许为空，缺省为申请。

 

9、字段提取尽量按照“用多少，取多少”的原则，避免使用“select *”

字段大小越大，数目越多， select 所耗费的资源越多，取一个 int 型的数据比取 char 型数据快很多，我们没少取一个字段，数据的提取速度就会有相应的提升。

10 、 count(*) 与 count( 字段 ) 的比较

使用 count(*) 与使用 count( 主键 ) 的速度相当，而 count(*) 比使用除主键之外的字段汇总快。

因此可以直接使用 count( 主键 )

11 、不要再索引列上使用计算

在 where 语句中，如果索引列是函数的一部分，优化器将不使用索引，而使用全表扫描，降低 sql 效率。

例如：   

低效  ： select  *  from  T1  where   T1.C1*12>35000;     

高效  ：Select  *  from  T2  where   T1.C1>35000/12;     

12 、优化 group by 语句

提高 group  by 语句的效率，可以讲不需要的记录在执行 group by 之前过滤掉，如以下示例：

低效：  select  job,avg(salary)  from  employee  group  by  job  having   job=’teacher’  or          job=’manager’

高效：  select   job ,avg(sal)  from  employee  where job=’teacher’  or job=’manager’ group by  job