求助(查询效率)

penghongya11

各位大侠：
    有条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 ，请问这两项花费做和解释，谢谢

penghongya11

回复 penghongya11 的帖子

从vmstat里来看，查询执行的是系统上下文切换非常频繁procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
0  0      0 1049368 149004 28662776    0    0     0     0 1005   74  0  0 100  0  0
0  0      0 1049244 149004 28662796    0    0     0     0 2464 1699  0  0 100  0  0
1  0      0 1048996 149004 28662796    0    0     0     0 2845 1985  0  0 100  0  0
1  0      0 1048456 149004 28662860    0    0     0     0 1095  281  2  1 96  0  0
1  0      0 1047464 149012 28662852    0    0     0   276 1089  192  1  2 97  0  0
1  0      0 1046584 149012 28662872    0    0     0     0 2327 2393  2  2 97  0  0
1  0      0 1046080 149012 28662872    0    0     0     0 1005   72  1  2 97  0  0
2  0      0 1045640 149012 28662892    0    0     0     0 1111  384  1  2 97  0  0
1  0      0 1045020 149012 28662892    0    0     0     0 2760 3155  2  2 96  0  0
1  0      0 1044160 149012 28662944    0    0     0     0 2486 2529  2  2 96  0  0
1  0      0 1043292 149020 28662936    0    0     0   260 1353  741  2  2 97  0  0
1  0      0 1042960 149020 28663004    0    0     0     0 2961 3506  2  2 96  0  0
1  0      0 1042340 149020 28663004    0    0     0     0 2424 2346  2  2 97  0  0
1  0      0 1041744 149020 28663048    0    0     0     0 1537 1081  1  2 97  0  0
1  0      0 1041000 149020 28663048    0    0     0     0 1449  786  1  2 97  0  0
1  0      0 1040336 149028 28663076    0    0     0   284 2323 2390  2  2 97  0  0
1  0      0 1039220 149028 28663076    0    0     0     0 2964 3566  2  2 96  0  0
1  0      0 1038580 149028 28663132    0    0     0     0 1027  201  1  2 97  0  0
1  0      0 1037952 149028 28663132    0    0     0    16 2715 3311  2  2 96  0  0
1  0      0 1037464 149028 28663216    0    0     0    32 2937 3855  2  2 96  0  0
1  0      0 1036720 149036 28663208    0    0     0   340 1083  157  2  1 97  0  0
1  0      0 1034772 149036 28663268    0    0     0     0 6520 9025  2  2 95  0  0
1  0      0 1034152 149036 28663268    0    0     0     0 1310  576  2  2 97  0  0
1  0      0 1034204 149036 28663324    0    0     0     0 1729 1457  2  2 97  0  0
1  0      0 1033212 149036 28663324    0    0     0   104 6664 9897  2  2 95  0  0
0  0      0 1046116 149044 28663484    0    0     0   328 6565 9507  2  2 96  0  0
0  0      0 1046236 149044 28663484    0    0     0     0 1654  761  0  0 100  0  0
0  0      0 1046768 149044 28663552    0    0     0     0 1601  741  0  0 100  0  0
0  0      0 1046516 149044 28663552    0    0     0    12 1029  146  0  0 100  0  0

kider

主要时间都话费在了Sending data ，取数据发送数据。
这么复杂的SQL就是为了个count()，都懒的看了，能不能从业务逻辑上简化下？
如果是表类型为MyISAM效果要比InnoDB好很多。
需要注意的就是表与表关联的字段要有索引，类型对等，最好用简单的SQL拼凑来代替一个复杂的SQL。

hm3030

sending data耗费多，也说明在利用条件筛选数据时，回表的数据多，你可以利用子查询先过滤constant，再join。