记一次简单的sql优化

系统开发上线之后  随着用户的使用 数据量增加   对sql的检测才刚刚开始

之前合同系统上线一年后 各个列表查询变慢     性能优化提到议程

进行了一个月的优化工作  记忆犹新啊。

新的系统在开发中  上线之前着重关注了下sql优化的问题  。 在此简单记录一下。

首先用压力测试工具  模拟了一万条业务数据。

首先po出 优化成果。

优化之前 18927s

单条sql 执行时间爆表 8.908：

优化之后  475s

单条sql执行 可以 0.328s：

 优化步骤：

 优化之前   不知道这个sql是谁写的，  想要查一些什么  这是最坑爹的呀 。

首先分析历史的sql  查询了什么 和列表中 给用户展示了什么：

列出内容   分析每个列表具体 查询路径。以及分析具体的可优化途径。

 列                                表  
 a.id,                            oa_ot_application
 a.code,                       oa_ot_application
 a.title,                         oa_ot_application
 a.module_id,              oa_ot_application
 a.pro_def_id,              oa_ot_application
 m.oa_req_url,             oa_ot_app_module 模板跳转路径
 a.pro_inst_id,             oa_ot_application
 e.real_name,               ims_ot_employee 真实姓名
 o.organization_name,       ims_ot_organization 真实部门
 t.taskName,               具体优化sql
 t.realName,                具体优化sql
 a.create_time,            oa_ot_application

分析优化之前sql  结合业务

优化前sql：
select distinct a.id,
                to_char(a.code),
                to_char(a.title),
                to_char(a.module_id),
                to_char(a.pro_def_id),
                to_char(m.oa_req_url),
                to_char(a.pro_inst_id),
                e.real_name,
                o.organization_name,
                t.taskName,
                t.realName,
                a.create_time,
                to_char(runtask.name_) as runtaskname             
  from oa_ot_application a
  left join (select distinct r.proc_inst_id_ as procInstId,
                             to_char(wmsys.wm_concat(distinct
                                                     to_char(t.taskName))) as taskName,
                             to_char(wmsys.wm_concat(distinct e.real_name)) as realName
               from oa_ot_process_record r
               left join (select t.proc_inst_id_ as procInstId,
                                t.name_ as taskName,
                                to_char(nvl(t.assignee_, i.user_id_)) as userId
                           from act_ru_task t
                           left join act_ru_identitylink i
                             on i.task_id_ = t.id_
                         union
                         select c.proc_inst_id as procInstId,
                                c.name as taskName,
                                to_char(c.user_id) as userId
                           from oa_ru_circulation c) t
                 on t.procInstId = r.proc_inst_id_
               left join ims_ot_user u
                 on u.user_login_name = t.userId
               left join ims_ot_employee e
                 on e.id = u.employee_id
              where (r.next_user_id_ = 'XXXXX' or r.user_id_ = 'XXXXXX')
              group by r.proc_inst_id_) t
    on t.procInstId = a.pro_inst_id
  left join oa_ot_app_module m
    on m.id = a.module_id
  left join ims_ot_organization o
    on o.id = a.create_org_id
  left join ims_ot_user u
    on u.user_login_name = a.creator
  left join ims_ot_employee e
    on e.id = u.employee_id
  left join oa_ot_doccheckuser ckuser
    on ckuser.app_id = a.id
  left join KM_OT_DOCRED b
    on b.app_id = a.id
  left join act_ru_task runtask
    on a.pro_inst_id = runtask.proc_inst_id_
 where  ((a.creator = 'XXXX') or
       (a.creator != 'XXXX' and t.procInstId is not null) or
       ckuser.login_name = 'XXXX')
   and a.module_id = 'XXXXXXXXX'
 order by a.create_time desc


优化后sql：
 select distinct a.id,
                 to_char(a.code),
                 to_char(a.title),
                 to_char(a.module_id),
                 to_char(a.pro_def_id),
                 to_char(m.oa_req_url),
                 to_char(a.pro_inst_id),
                 a.create_time,
                 e.real_name,
                 o.organization_name,
                 a.creator,
                 nvl(case
                       when a.pro_inst_id is null then  '草稿' else
                        (select to_char(t.name)
                           from (select t.proc_inst_id_ as proc_inst_id,
                                        t.name_         as name
                                   from act_ru_task t
                                 union
                                 select c.proc_inst_id as proc_inst_id,
                                        c.name         as name
                                   from oa_ru_circulation c) t
                          where t.proc_inst_id = a.pro_inst_id
                            and rownum = 1)
                     end,
                     '已办结') as taskName
   from oa_ot_application a
   left join oa_ot_app_module m
     on m.id = a.module_id
   left join ims_ot_user u
     on u.user_login_name = a.creator
   left join ims_ot_employee e
     on e.id = u.employee_id
   left join ims_ot_organization o
     on o.id = a.create_org_id
   left join oa_ot_doccheckuser d
     on a.id = d.app_id
  where (a.creator = 'XXXX' or d.login_name = 'XXXX')
    and a.module_id = 'XXXXXXXXXX'
  order by a.create_time desc