一个sql语句的优化：
最近维护一个项目,其中一个sql严重影响性能,需要对两个海量table查询（icasuser有3000条记录,logon_info有近40万记录.）由于耗时多，页面基本不显示。
以下测试时间为在本地数据库的时间(本地oralce数据库数据量小,icasuser只有3000条记录)
最开始的sql，用时间5s
select count(*) from (
SELECT
icasuser.userid AS userid, icasuser.rsa AS rsa, icasuser.roleid AS roleid,
icasuser.fname as fname, icasuser.lname as lname, icasuser.centerid as centerid,
icasuser.status as status, icasuser.priv as priv, icasuser.password as password,
icasuser.edate as edate, icasuser.coachid as coachid, icasuser.analystid as analystid,
icasuser.trainerid as trainerid, icasuser.title as title, icasuser.action as action
FROM icasuser, logon_info
WHERE SYSDATE - logon_info.login_time > 90 AND icasuser.userid = logon_info.sbcuid
UNION
SELECT
icasuser.userid AS userid, icasuser.rsa AS rsa, icasuser.roleid AS roleid,
icasuser.fname as fname, icasuser.lname as lname, icasuser.centerid as centerid,
icasuser.status as status, icasuser.priv as priv, icasuser.password as password,
icasuser.edate as edate, icasuser.coachid as coachid, icasuser.analystid as analystid,
icasuser.trainerid as trainerid, icasuser.title as title, icasuser.action as action
FROM icasuser, logon_info
WHERE (SYSDATE - TO_DATE(TRIM(icasuser.edate), ''''''''''''''''YYYY-MM-DD'''''''''''''''')) > 90 AND icasuser.userid NOT IN (SELECT logon_info.sbcuid FROM logon_info GROUP BY logon_info.sbcuid)
)
一：先优化第一个union子句：
--用时0.6s
select count(*) from (SELECT
m.userid AS userid, m.rsa AS rsa, m.roleid AS roleid,
m.fname as fname, m.lname as lname, m.centerid as centerid,
m.status as status, m.priv as priv, m.password as password,
m.edate as edate, m.coachid as coachid, m.analystid as analystid,
m.trainerid as trainerid, m.title as title, m.action as action
from icasuser m where exists(select ''''''''''''''''x'''''''''''''''' from ( SELECT t.userid as usid FROM icasuser t, logon_info d
where t.userid=d.sbcuid group by t.userid having max(d.login_time) < sysdate-90 ) gg where usid=m.userid))
继续优化：把exists换为in：
--用时0.04s：
select count(*) from(SELECT
m.userid AS userid, m.rsa AS rsa, m.roleid AS roleid,
m.fname as fname, m.lname as lname, m.centerid as centerid,
m.status as status, m.priv as priv, m.password as password,
m.edate as edate, m.coachid as coachid, m.analystid as analystid,
m.trainerid as trainerid, m.title as title, m.action as action
from icasuser m where userid in(select usid from ( SELECT t.userid as usid FROM icasuser t, logon_info d
where t.userid=d.sbcuid group by t.userid having max(d.login_time) < sysdate-90 )))
二:改写原sql语句:
--用时0.8s
select count(*) from (
SELECT
m.userid AS userid, m.rsa AS rsa, m.roleid AS roleid,
m.fname as fname, m.lname as lname, m.centerid as centerid,
m.status as status, m.priv as priv, m.password as password,
m.edate as edate, m.coachid as coachid, m.analystid as analystid,
m.trainerid as trainerid, m.title as title, m.action as action
from icasuser m where userid in(select usid from ( SELECT t.userid as usid FROM icasuser t, logon_info d
where t.userid=d.sbcuid group by t.userid having max(d.login_time) < sysdate-90 )
) or (SYSDATE - TO_DATE(TRIM(m.edate), ''''''''''''''''YYYY-MM-DD'''''''''''''''')) > 90)
继续: 把or改为union:
--用时0.047s
select count(*) from(SELECT
m.userid AS userid, m.rsa AS rsa, m.roleid AS roleid,
m.fname as fname, m.lname as lname, m.centerid as centerid,
m.status as status, m.priv as priv, m.password as password,
m.edate as edate, m.coachid as coachid, m.analystid as analystid,
m.trainerid as trainerid, m.title as title, m.action as action
from icasuser m where userid in(select usid from ( SELECT t.userid as usid FROM icasuser t, logon_info d
where t.userid=d.sbcuid group by t.userid having max(d.login_time) < sysdate-90 ))
union
SELECT
m.userid AS userid, m.rsa AS rsa, m.roleid AS roleid,
m.fname as fname, m.lname as lname, m.centerid as centerid,
m.status as status, m.priv as priv, m.password as password,
m.edate as edate, m.coachid as coachid, m.analystid as analystid,
m.trainerid as trainerid, m.title as title, m.action as action
from icasuser m where (SYSDATE - TO_DATE(TRIM(m.edate), ''''''''''''''''YYYY-MM-DD'''''''''''''''')) > 90)
优化后的sql比原来的快了5/0.04=120倍
总结:
1: 很多材料说exists比in快,可是我发现in快多了.不知道对否?
2:如果追求速度,可以考虑把where后的or改写成union
------
该系统用的翻页类比较垃圾，它是先把数据库的数据全部读取到一个RowSet中，然后在RowSet中取1到10，11到20。。。。记录。传说还是一个高手所写，晕。oracle的翻页sql写法如下。有时间我重新写一个翻页类。
select * from
(select rownum numrow,fsql.* from (select t.userid,t.edate from icasuser t order by t.userid) fsql ) where numrow >100 and numrow<200
