发现生产环境的Oracle数据库cpu使用率上升超过70%，其中一条查询语句达到每秒调用40多次。现在我们来观摩下该语句:
select t.id,t.level,t.policy, t.type,t1.point,t2.channel,t3.user from t_wlf_charge t, t_wlf_point t1, t_wlf_channel t2, (select t4.id,t4.phone user from t_wlf_user t4 where t4.phone=? union select t5.id,t5.city user from t_wlf_city t5 where t5.city=? or t5.city=0) t3 where t.id=t1.id and t.id=t2.id and t.id=t3.id and t.code=? and (t1.point=NVL(?,t1.point) or t1.point=0) and (t2.channel=? or t2.channel=0) order by t.type asc
开始考虑缓存，但发现关联查询缓存命中率是个问题，而且本身重复查询率就低。最后决定通过视图优化以上1主表+5从表的关联查询，先创建视图:
create or replace view v_wlf_charge as select
t.id,t.level,t.policy, t.type,t.code,nvl(t1.point,0) point,nvl(t2.channel,0) channel, nvl(t3.phone,0) phone,nvl(t4.city,0) city from t_wlf_charge t left join t_wlf_point t1 on t.id=t1.id left join t_wlf_channel t2 on t.id=t2.id left join t_wlf_phone t3 on t.id=t3.id left join t_wlf_city t4 on t.id=t4.id where t.id is not null
再用这条脚本查:
select id,level,policy, type,point,channel,phone as user from v_wlf_charge where code=? and point in(0,?) and channel in (0,?) and phone in (0,?) and city in (0,?) order by type asc