一、优化原则
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)