求助(查询效率)
各位大侠：有条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'
idselect_typetable type possible_keys key key_lenref rowsExtra
----------------------------------------------------------------------------------------------------------------------------------------------------
1SIMPLE pa range Date Date 8 (NULL) 493463Using where
1SIMPLE pj eq_refPRIMARY,NewIndex1,CustomerId,BusinessDeveloperIdPRIMARY 4 kkfun_newhz.pa.ProjectId 1Using where
1SIMPLE pc ref Pcn_ProjectId Pcn_ProjectId4 kkfun_newhz.pa.ProjectId 4Using where
1SIMPLE bd eq_refPRIMARY,AgentId PRIMARY 4 kkfun_newhz.pj.BusinessDeveloperId 1Using where
1SIMPLE ag eq_refPRIMARY PRIMARY 4 kkfun_newhz.pc.PannerId 1Using index
1SIMPLE pr eq_refPRIMARY PRIMARY 4 kkfun_newhz.pa.ProvinceId 1Using index
1SIMPLE cm eq_refPRIMARY PRIMARY 4 kkfun_newhz.pj.CustomerId 1Using index
Status Duration CPU_userCPU_systemBlock_ops_inBlock_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.77765410.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 ，请问这两项花费做和解释，谢谢
回复 penghongya11 的帖子
从vmstat里来看，查询执行的是系统上下文切换非常频繁procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
rb swpd free buffcache si so bi bo in cs us sy id wa st
00 0 1049368 149004 28662776 0 0 0 0 1005 7400 10000
00 0 1049244 149004 28662796 0 0 0 0 2464 169900 10000
10 0 1048996 149004 28662796 0 0 0 0 2845 198500 10000
10 0 1048456 149004 28662860 0 0 0 0 109528121 9600
10 0 1047464 149012 28662852 0 0 0 276 108919212 9700
10 0 1046584 149012 28662872 0 0 0 0 2327 239322 9700
10 0 1046080 149012 28662872 0 0 0 0 1005 7212 9700
20 0 1045640 149012 28662892 0 0 0 0 111138412 9700
10 0 1045020 149012 28662892 0 0 0 0 2760 315522 9600
10 0 1044160 149012 28662944 0 0 0 0 2486 252922 9600
10 0 1043292 149020 28662936 0 0 0 260 135374122 9700
10 0 1042960 149020 28663004 0 0 0 0 2961 350622 9600
10 0 1042340 149020 28663004 0 0 0 0 2424 234622 9700
10 0 1041744 149020 28663048 0 0 0 0 1537 108112 9700
10 0 1041000 149020 28663048 0 0 0 0 144978612 9700
10 0 1040336 149028 28663076 0 0 0 284 2323 239022 9700
10 0 1039220 149028 28663076 0 0 0 0 2964 356622 9600
10 0 1038580 149028 28663132 0 0 0 0 102720112 9700
10 0 1037952 149028 28663132 0 0 0 16 2715 331122 9600
10 0 1037464 149028 28663216 0 0 0 32 2937 385522 9600
10 0 1036720 149036 28663208 0 0 0 340 108315721 9700
10 0 1034772 149036 28663268 0 0 0 0 6520 902522 9500
10 0 1034152 149036 28663268 0 0 0 0 131057622 9700
10 0 1034204 149036 28663324 0 0 0 0 1729 145722 9700
10 0 1033212 149036 28663324 0 0 0 104 6664 989722 9500
00 0 1046116 149044 28663484 0 0 0 328 6565 950722 9600
00 0 1046236 149044 28663484 0 0 0 0 165476100 10000
00 0 1046768 149044 28663552 0 0 0 0 160174100 10000
00 0 1046516 149044 28663552 0 0 0 12 102914600 10000
主要时间都话费在了Sending data ，取数据发送数据。
这么复杂的SQL就是为了个count()，都懒的看了，能不能从业务逻辑上简化下？
如果是表类型为MyISAM效果要比InnoDB好很多。
需要注意的就是表与表关联的字段要有索引，类型对等，最好用简单的SQL拼凑来代替一个复杂的SQL。 sending data耗费多，也说明在利用条件筛选数据时，回表的数据多，你可以利用子查询先过滤constant，再join。
页:
[1]