简单的sql优化分享

这期内容当中小编将会给大家带来有关简单的sql优化分享，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。

创新互联是一家专注于网站设计制作、成都网站制作与策划设计,鱼峰网站建设哪家好?创新互联做网站,专注于网站建设十余年,网设计领域的专业建站公司;建站业务涵盖:鱼峰等地区。鱼峰做网站价格咨询:13518219792

系统开发上线之后随着用户的使用数据量增加对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优化分享了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注创新互联行业资讯频道。

网站题目：简单的sql优化分享
本文网址：http://cdiso.cn/article/ijpdos.html

简单的sql优化分享

其他资讯