SQL 脚本优化方案.docx

数据库性能优化方案脚本优化优化前SQLSQL脚本 select tb.c_id, tb.c_task_cde, tb.c_task_nme, tb.c_clnt_nme, tb.c_mobile, tb.c_city, to_char(ta.c_task_tm, yyyy-mm-dd hh24:mi:ss) as c_task_tm, tb.c_province, (select distinct name from sys_user where loginname = ta.c_task_found) as c_task_found, ta.c_task_nature_mrk, (select distinct name from sys_user where loginname = ta.c_task_appoint_nme) as c_task_appoint_nme, tb.c_clnt_cde, tb.c_call_mrk, tb.c_ser_mrk, hccs.src as c_task_aount, rwpfcs.str as history_track_count, to_char(tb.t_crt_tm, yyyy-mm-dd hh24:mi:ss) as t_crt_tm from tb_task_base tb, tb_task_assign ta, (select tkb.c_mobile, (select count(tchr.c_task_cde) from tb_call_history_record tchr where tchr.c_del = 1 and tchr.c_mobile = tkb.c_mobile and tchr.c_task_cde = tkb.c_task_cde and tchr.c_clnt_cde = tkb.c_clnt_cde) as src, rowid as rowcode from tb_task_base tkb where tkb.c_del = 1 and tkb.c_task_mrk = 1) hccs, (select ttbs.c_id, (select count(tods.c_task_cde) from tb_operate_del tods where tods.c_del = 1 and tods.c_task_cde = ttbs.c_task_cde and tods.c_mobile = ttbs.c_mobile and tods.c_operate_mrk = 8 and tods.c_fk_task_id = ttbs.c_id) as str, rowid as rowcode from tb_task_base ttbs where ttbs.c_del = 1 and ttbs.c_task_mrk = 1) rwpfcs where tb.c_del = 1 and ta.c_del = 1 and rwpfcs.rowcode = tb.rowid and ta.c_fk_task_id = tb.c_id and hccs.rowcode = tb.rowid and tb.c_task_mrk in (1, 8) and exists (select null from sys_duty d, sys_user u, sys_r_duty_agency rda, sys_r_user_duty_agency ruda where d.status = 1 and u.status = 1 and ta.c_crt_cde = u.id and d.id = rda.dutyid and rda.id = ruda.agency_duty_id and ruda.userid = u.id and d.marktype = 3) and hccs.src = 0 and rwpfcs.str = 0 order by tb.c_task_cde desc执行效率，逻辑读分析，问题描述(1)优化前SQL逻辑读性能截图 (2)逻辑读分析：该语句在数据库中执行后的逻辑读数据行为rows processed：42，消耗逻辑读为：consistent gets：11150，优化前的每行逻辑读占用为：consistent gets/rows processed，通过该公式获得每行逻辑读占用为265，该效率不满足正常性能需要。(3)问题描述：该查询产生大量的逻辑读的原因是由于SQL语句中有过多而复杂的子查询；在开发库上的数据量仅为50左右，所以无法测试该问题。该SQL是电销系统系统上线以来一直存在，随着数量的增大，消耗数据读也会随之增加，性能会相应下降。测试环境说明测试数据库：数据库用户名SQL涉及的相关表及数据量：tb_task_base表数据量为：138636tb_task_assign表数据量为：53tb_operate_del表数据量为：76tb_call_history_record表数据量为：0sys_duty表数据量为：4sys_user表数据量为：51sys_r_duty_agency表数据量为：4sys_r_user_duty_agency表数据量为：69相关表的索引字段：Tb_task_base表：索引名：PK_T_CRT_NME列名：C_CRT_NMTb_task_assign表：索引名：INDEX_ASSIGN_C_TASK_APPOINT_N 列名：C_TASK_APPOINT_NME索引名：INDEX_ASSIGN_C_TASK_CDE 列名：C_TASK_CDE索引名：C_TASK_FOUND 列名：C_TASK_FOUND Tb_operate_del表： 索引名：INDEX_OPERATE_C_CRT_NME列名：C_CRT_NME 索引名：INDEX_OPERATE_C_FK_TASK_ID列名：C_FK_TASK_ID 索引名：INDEX_OPERATE_C_MOBILE列名：C_MOBILE 索引名：INDEX_OPERATE_C_OPERATE_MRK列名：C_OPERATE_MRK 索引名：INDEX_OPERATE_C_TASK_CDE列名：C_TASK_CDE 索引名：INDEX_OPERATE_T_CRT_TM列名：T_CRT_TM 索引名：INDEX_OPERATE_T_NXT_TRCT_TM列名：T_NXT_TRCT_TM所属业务模块及场景模块：任务管理任务回收查询(条件：呼出次数为0，任务跟踪次数为0，其它不填查询)优化方案脚本优化方案1)sql本身语句优化： select tb.c_id, tb.c_task_cde, tb.c_task_nme, tb.c_clnt_nme, tb.c_mobile, tb.c_city, to_char(ta.c_task_tm, yyyy-mm-dd hh24:mi:ss) as c_task_tm, tb.c_province, (select name from sys_user where status = 1 and loginname = ta.c_task_found) as c_task_found, ta.c_task_nature_mrk, (select name from sys_user where status = 1 and loginname = ta.c_task_appoint_nme) as c_task_appoint_nme, tb.c_clnt_cde, tb.c_call_mrk, tb.c_ser_mrk, hccs.src as c_task_aount, rwpfcs.str as history_track_count, to_char(tb.t_crt_tm, yyyy-mm-dd hh24:mi:ss) as t_crt_tm from tb_task_base tb inner join tb_task_assign ta on ta.c_fk_task_id = tb.c_id inner join (select tkb.c_mobile, (select count(tchr.c_task_cde) from tb_call_history_record tchr where tchr.c_del = 1 and tchr.c_mobile = tkb.c_mobile and tchr.c_task_cde = tkb.c_task_cde and tchr.c_clnt_cde = tkb.c_clnt_cde) as src, rowid as rowcode from tb_task_base tkb where tkb.c_del = 1 and tkb.c_task_mrk = 1) hccs on hccs.rowcode = tb.rowid inner join (select ttbs.c_id, (select count(tods.c_task_cde) from tb_operate_del tods where tods.c_del = 1 and tods.c_task_cde = ttbs.c_task_cde and tods.c_mobile = ttbs.c_mobile and tods.c_operate_mrk = 8 and tods.c_fk_task_id = ttbs.c_id) as str, rowid as rowcode from tb_task_base ttbs where ttbs.c_del = 1 and ttbs.c_task_mrk = 1) rwpfcs on rwpfcs.rowcode = tb.rowid inner join (select u.id from sys_duty d inner join sys_user u on d.status = 1 and u.status = 1 and d.marktype = 3 inner join sys_r_duty_agency rda on d.id = rda.dutyid inner join sys_r_user_duty_agency ruda on rda.id = ruda.agency_duty_id and ruda.userid = u.id) us on us.id = ta.c_crt_cde and tb.c_del = 1 and ta.c_del = 1 and tb.c_task_mrk = 1 and hccs.src = 0 and rwpfcs.str = 0 ord