- 积分
- 51
- UID
- 3034
- 阅读权限
- 20
- 注册时间
- 2009-10-20
- 精华
- 在线时间
- 小时
- 最后登录
- 1970-1-1
- 职业
- 1
|
各位大侠:
有条sql语句,主表2-3百万左右,其他从表量不大,具体sql语句如下:
SELECT
COUNT((pa.id)
FROM ACTIVATION_PRODUCT_FEE_LOG pa
LEFT JOIN PROJECT_NEW pj
ON pa.ProjectId = pj.ID
JOIN PROJECT_COOPERATE_NEW pc
ON pj.ID = pc.ProjectId
LEFT JOIN BUSINESS_DEVELOPER bd
ON pj.BusinessDeveloperId = bd.ID
LEFT JOIN AGENT ag
ON bd.agentid = ag.id
LEFT JOIN PROVINCE pr
ON pa.ProvinceId = pr.id
JOIN CUSTOMER cm
ON cm.ID = pj.CustomerId
WHERE pa.Date BETWEEN '2011-03-01 00:00:00.0'
AND '2011-04-01 00:00:00.0'
AND pa.Deduct = 0
AND pc.PannerId = bd.AgentId
AND pc.PannerType = 'AG'
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- ------ ------ ------------------------------------------------ ------------- ------- ---------------------------------- ------ -----------
1 SIMPLE pa range Date Date 8 (NULL) 493463 Using where
1 SIMPLE pj eq_ref PRIMARY,NewIndex1,CustomerId,BusinessDeveloperId PRIMARY 4 kkfun_newhz.pa.ProjectId 1 Using where
1 SIMPLE pc ref Pcn_ProjectId Pcn_ProjectId 4 kkfun_newhz.pa.ProjectId 4 Using where
1 SIMPLE bd eq_ref PRIMARY,AgentId PRIMARY 4 kkfun_newhz.pj.BusinessDeveloperId 1 Using where
1 SIMPLE ag eq_ref PRIMARY PRIMARY 4 kkfun_newhz.pc.PannerId 1 Using index
1 SIMPLE pr eq_ref PRIMARY PRIMARY 4 kkfun_newhz.pa.ProvinceId 1 Using index
1 SIMPLE cm eq_ref PRIMARY PRIMARY 4 kkfun_newhz.pj.CustomerId 1 Using index
Status Duration CPU_user CPU_system Block_ops_in Block_ops_out
------------------------------ --------- --------- ---------- ------------ -------------
starting 0.000017 0.000000 0.000000 0 0
checking query cache for query 0.000183 0.001000 0.000000 0 0
Opening tables 0.000034 0.000000 0.000000 0 0
System lock 0.000015 0.000000 0.000000 0 0
Table lock 0.000078 0.000000 0.000000 0 0
init 0.000084 0.000000 0.000000 0 0
optimizing 0.000056 0.000000 0.000000 0 0
statistics 0.000254 0.000000 0.000000 0 0
preparing 0.000067 0.000000 0.000000 0 0
executing 0.000050 0.000000 0.000000 0 0
Sending data 21.777654 10.254441 11.553244 0 0
end 0.001086 0.000000 0.001000 0 0
removing tmp table 0.000020 0.000000 0.000000 0 0
end 0.000009 0.000000 0.000000 0 0
query end 0.000006 0.000000 0.000000 0 0
freeing items 0.000108 0.000000 0.000000 0 0
logging slow query 0.000005 0.000000 0.000000 0 0
logging slow query 0.000003 0.000000 0.000000 0 0
cleaning up 0.000010 0.000000 0.000000 0 0
效率太低了,机器配置不低,从划分时间上看:主要是CPU_user,CPU_system ,请问这两项花费做和解释,谢谢
|
|