java 排查性能问题,性能问题排查

一 总结概要:

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.逐步执行

alert('aaaaaaa');

2.暂时删除觉得没有问题的部分

------君临天下，舍我其谁------