Sql语句优化原则

一、优化原则

1、 避免使用硬编码，改用绑定变量实现。

举例：

String str =’ select * from t_zx_ryjbxxb where xm=’+params;

上面这条语句使用了硬编码，使用这种方式存在两方面问题：

每次执行sql语句时都需要重新解析sql语句；

可能会遭遇sql注入攻击。

如在上面的语句中输入张三' or 1='1，则会把所有的记录都显示出来。

解决的方法就是使用占位符代替硬编码。如下：

String str =’ select * from t_zx_ryjbxxb where xm=?’;

2、 当插入的数据为数据表中的记录数量的10%以上，首先需要删除该表的索引来提高数据的插入效率，当数据插入后，再建立索引。

3、 避免在索引列上使用函数或计算，在where子句中，如果索引是函数的一部分，优化器将不再使用索引而使用全表扫描。

举例：

低效：select * from dept where sal*12 >2500;

高效：select * from dept where sal>2500/12;

4、 避免在索引列上使用not和 “!=”，索引只能告诉什么存在于表中，而不能告诉什么不存在于表中，当数据库遇到not 和 “!=”时，就会停止使用索引而去执行全表扫描。

5、 使用关联查询替代in ，可以提高查询的效率。

6、 使用not exists子查询替代 not in。

在子查询中，NOT IN子句将执行一个内部的排序和合并。 无论在哪种情况下，NOT IN都是最低效的 （因为它对子查询中的表执行了一个全表遍历）。 为了避免使用NOT IN ，我们可以把它改写成外连接（Outer Joins）或NOT EXISTS.

举例：

select * from t_zx_ryjbxxb where rybh not in
(select rybh from t_zx_cqrb) and jwh=''

select * from t_zx_ryjbxxb a where not exists
(select 1 from t_zx_cqrb b where a.rybh =b.rybh)

使用union-all 替代 union：当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并， 然后在输出最终结果前进行排序。

union在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。

 而union all只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。

7、 减少访问数据库的次数。使用关联查询解决N+1条sql语句问题

举例：

select * from CUSTOMERS; 
select * from ORDERS where CUSTOMER_ID=1;
select * from ORDERS where CUSTOMER_ID=2;
select * from ORDERS where CUSTOMER_ID=3;
select * from ORDERS where CUSTOMER_ID=4;

可以采用关联查询为：

select * from CUSTOMERS left outer join ORDERS 
on CUSTOMERS.ID=ORDERS.CUSTOMER_ID

8、 总是使用索引的第一个列

举例：

表t_zx_ryjbxxb上存在(sfzjhm ，xm)这样的复合索引

如果对表使用以下sql语句：

Select * from t_zx_ryjbxxb where xm=’张三’，发现没有使用索引。

如果索引是建立在多个列上， 只有在它的第一个列（leading column）被where子句引用时，优化器才会选择使用该索引。

9、 避免在可选择性低的列上建立索引, 比如在人员信息表的“性别”列上只有“男”与“女”两个不同值

10、 尽量不要使用 以'%'开头的like语句。

举例：

Sleect * from t_zx_ryjbxxb where xm like ‘张三%’ 可以使用索引

Select * from t_zx_ryjbxxb where xm like ‘%张三’ 不会使用索引

Select * from t_zx_ryjbxxb where xm like ‘%张三%’ 不会使用索引

11、 如果使用函数作为查询列，可以使用函数索引优化查询效率

注1：函数索引只有在CBO下才能被用到，所以必须收集统计信息。

举例：

CREATE OR REPLACE FUNCTION f_sfzh18
(as_sfzh    in varchar2)
return varchar2 DETERMINISTIC

As

。。。。。。。。。。。。。

create index ind_zjhm_18 on t_zx_ryjbxxb (f_sfzh18(SFZJHM));

exec dbms_stats.gather_table_stats(ownname=>'wlrk_new',tabname=>'t_zx_ryjbxxb',estimate_percent=>5,cascade=>true);

二、特殊sql写法

1、 CASE表达式应用

--列出雇员的工资等级

         select ename,(case 

                       when sal<1000 then '低'

                       when sal>=1000 and sal<3000 then '中'

                       when sal>3000 then '高'

                       else '未知'

                       end) as "工资等级"

         from emp;

ENAME      工资

---------- ----

SMITH      低

ALLEN      中

WARD       中

JONES      中

MARTIN     中

BLAKE      中

CLARK      中

SCOTT      未知

KING       高

TURNER     中

ADAMS      中

ENAME      工资

---------- ----

JAMES      低

FORD       未知

MILLER     中

2、 行列转换

select sum(case when (months_between(sysdate,csrq)/12)<5 then 1 else 0 end) 小于5岁,sum(case when ((months_between(sysdate,csrq)/12) between 5 and 10) then 1 else 0 end) 在5到10岁之间,sum(case when (months_between(sysdate,csrq)/12)>10 then 1 else 0 end)  大于10岁

from t_zx_ryjbxxb

where rownum<100000

输出结果：

   小于5岁 在5到10岁之间   大于10岁

---------- ------------- ----------

       106            11      90014

3、 Decode函数

decode函数比较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回default值；如果未定义default值，则返回空值。

该函数的语法结构：

decode (expression, search_1, result_1)

decode (expression, search_1, result_1, search_2, result_2)

decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n)

decode (expression, search_1, result_1, default)

decode (expression, search_1, result_1, search_2, result_2, default)

decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default)