sql优化:开发人员应该要细心

客户反映

运行某个报表要45秒以上

select id, role_id, loginITPUB个人空间8F&A-^,lEl~
                          from tb_user e
~7S4d|4C.F/u(f f0                         where role_id in (Select IDITPUB个人空间[`-E%m7S%bMn
                                  From tb_role f
,PAW6gk{0                                  Connect By Prior Id = parent_idITPUB个人空间[y[lv2K(b
                                 Start With Id = 1);

直接拿

以上语句到数据库上执行只要 2秒钟左右

那现在问题只能定位到开发人员写的程序

检查sql

select id, role_id, login
]#L };sVH,J-z0                          from tb_user e
}6H`[2U4o0                         where role_id in (Select ID
L@X V/G6P0                                  From tb_role f

                               where e.role_id=f.id
UNXe$m?0                                  Connect By Prior Id = parent_id
V5Q#Q P#[A0                                 Start With Id = 1);

多了红色部分,去掉

再次运行恢复到2秒正常

以前该sql 是用exists 的写法

select id, role_id, loginITPUB个人空间 V'LFC&Pg0r aI/Jp
                          from tb_user e
"\JD
EdtGg0                         where exists  (Select ID
y^ d3k [6C0                                  From tb_role f

                               where e.role_id=f.idITPUB个人空间5k9au
~6d#Gcbr#G}
                                  Connect By Prior Id = parent_id
vv7T0D/Pf I2ON0                                 Start With Id = 1);

再改写成用in时开发人员忘记去掉红色代码部分导致执行时间过长

所以开发人员要细心,做为dba要更加严格审核sql才可提交出去