一 總結概要:
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.暫時刪除覺得沒有問題的部分