簡單的sql優化分享

這期內容當中小編將會給大家帶來有關簡單的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
 

上述就是小編為大家分享的簡單的sql優化分享了，如果剛好有類似的疑惑，不妨參照上述分析進行理解。如果想知道更多相關知識，歡迎關注億速云行業資訊頻道。