导航：首页 > 互联网科技 >

简单的sql优化分享

发表于：2024-09-24 作者：千家信息网编辑

千家信息网最后更新 2024年09月24日，这期内容当中小编将会给大家带来有关简单的sql优化分享，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。系统开发上线之后随着用户的使用数据量增加对sql的检测才刚

千家信息网最后更新 2024年09月24日简单的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