一 总结概要:
1.首先解决问题的方向不对,没有证实靠猜测就盲目优化SQL语句.(其实SQL语句性能不慢的,猜测 in(11,22,33,...) 语句的性能问题)
2.对早已经发现的问题熟视无睹,明明知道页面展现有性能问题(IT性能慢,FIREFOX正常没有引起足够的重视)
3.在自己写纯SQL语句优化无结果的情况下,重新审视过去走过的弯路
4.不要放过每个细节,自己思路一定要清晰,坚持就是胜利了
二 实际操作过程:
1.数据库环境 192.168.0.70 cem_vcc
2.需要优化的SQL语句
基本SQL A: select vl.* from vccrawlog vl,
cem_cc_operator op,
cem_cc_operator_acd_group ag
where op.user_id=ag.user_id and ag.site_id=op.site_id
and ag.site_id=2010010
and auto_rec_name is not null and is_rec_deleted=0 and endtime is not null
and vl.site_id=2010010
and acd_group_id in
(542,632,622,862,752,692,702,822,552,722,532,662,812,562,642,782,832,732,872,802,792,742,712,652) and ( (callee_id =op.site_extension and call_type=0 ) or( caller_id =op.site_extension and call_type=1 ))
order by sequence desc;
要求 1.A语句需要进行 COUNT(*) 操作
2.A语句需要进行 分页
业务简要说明: 查询VCCRAWLOG满足所有acd_group_id in
(542,632,622,862,752,692,702,822,552,722,532,662,812,562,642,782,832,732,872,802,792,742,712,652) 里面座席数据.
http://localhost:8080/vcc10/page/listRecords.action?&site=8ins
mysql 命令
show create table vccrawlog;
show index from vccrawlog;
SQL_NO_CACHE 不用缓存
select DISTINCT
*************************************************
1.COUNT语句
select sum(cnt) from
(
select count(*) cnt from vccrawlog vl, cem_cc_operator op, cem_cc_operator_acd_group ag
where op.user_id=ag.user_id and ag.site_id=op.site_id
and vl.site_id=op.site_id and vl.caller_id =op.site_extension and vl.call_type=1
and ag.site_id=2010010 and vl.auto_rec_name is not null and vl.is_rec_deleted=0 and vl.endtime is not null
and ag.acd_group_id in (542,632,622,862,752,692,702,822,552,722,532,662,812,562,642,782,832,732,872,802,792,742,712,652)
union all
select count(*) cnt from vccrawlog vl, cem_cc_operator op, cem_cc_operator_acd_group ag
where op.user_id=ag.user_id and ag.site_id=op.site_id
and vl.site_id=op.site_id and vl.callee_id =op.site_extension and vl.call_type=0
and ag.site_id=2010010 and vl.auto_rec_name is not null and vl.is_rec_deleted=0 and vl.endtime is not null
and ag.acd_group_id in (542,632,622,862,752,692,702,822,552,722,532,662,812,562,642,782,832,732,872,802,792,742,712,652)
) a;
说明 通过union all 可以分别利用 caller_id 和 callee_id索引
****************************************************
2 .countSql:
select sum(cnt) from
(
select count(*) cnt from vccrawlog vl, ( select op.site_id,op.site_extension from cem_cc_operator op, cem_cc_operator_acd_group ag
where op.user_id=ag.user_id and ag.site_id=op.site_id
and ag.site_id=2010010
and ag.acd_group_id in (542,632,622,862,752,692,702,822,552,722,532,662,812,562,642,782,832,732,872,802,792,742,712,652)
union select 2010010,20100108888 ) operators
where vl.site_id=operators.site_id and vl.caller_id =operators.site_extension and vl.call_type=1
and vl.auto_rec_name is not null and vl.is_rec_deleted=0 and vl.endtime is not null
union all
select count(*) cnt from vccrawlog vl, ( select op.site_id,op.site_extension from cem_cc_operator op, cem_cc_operator_acd_group ag
where op.user_id=ag.user_id and ag.site_id=op.site_id
and ag.site_id=2010010
and ag.acd_group_id in (542,632,622,862,752,692,702,822,552,722,532,662,812,562,642,782,832,732,872,802,792,742,712,652)
union select 2010010,20100108888 ) operators
where vl.site_id=operators.site_id and vl.callee_id =operators.site_extension and vl.call_type=0
and vl.auto_rec_name is not null and vl.is_rec_deleted=0 and vl.endtime is not null
) a;
说明:
select op.site_id,op.site_extension from cem_cc_operator op, cem_cc_operator_acd_group ag
where op.user_id=ag.user_id and ag.site_id=op.site_id and ag.site_id=2010010
and ag.acd_group_id in (542,632,622,862,752,692,702,822,552,722,532,662,812,562,642,782,832,732,872,802,792,742,712,652)
union select 2010010,20100108888
可以去掉大量的重复数据
******************************************************************************
页面性能排查:
1.逐步执行
<script language="javascript">
alert('aaaaaaa');
</script>
2.暂时删除觉得没有问题的部分
------君临天下，舍我其谁
------