sql优化----不同写法下group by 的影响

今天要对实现这样的一个统计效果的sql进行优化。

分析：可以看出此统计是要求针对经销商，车系两个维度对试驾次数和潜客数两个指标进行统计，试驾率是由这两个指标进行计算而得到。

1、维度：经销商，车系

2、指标：试驾次数，潜客数

原版sql如下:

分析问题：此sql给人的第一印象就是用了子查询效率低，事实它确实特别慢。而且对于不熟悉业务的人来说想看懂很痛苦。

    所以我选择了重写。

仔细分析其实现：

                分别计算各经销商不同车系的试驾次数，潜客数作为子查询，然后用left  join 将其连接起来。

  这里就把纬度降低到了经销商，故group by 语句后面只是按经销商分组。

SELECTt_dealer.t_org_rboid AS rboId,org_rbo.namechn AS rboName,t_dealer.id AS dealerId,t_dealer.namechn AS namechnName,t_dealer. CODE AS namechnCode,t_dealer.t_org_smallregionid AS smallregionId,org_smallregion.namechn AS smallregionName,yxsjcs_temp.*, qks_temp.*FROM(SELECTdealer.id AS dealer_idFROMt_dealer dealerWHEREdealer.isdel = 0) AS dealer_tempLEFT JOIN (SELECTdealer.id AS yxsjcsDealerId,SUM( CASE  WHEN carline_model.t_carline_id = '84e46cf1-022f-11e7-aa3c-000c2909830f' THEN 1 ELSE0 END ) AS 'yxsjcs_84e46cf1-022f-11e7-aa3c-000c2909830f',SUM( CASE  WHEN carline_model.t_carline_id = '187d6738-cd8b-11e6-aa3c-000c2909830f' THEN 1 ELSE0 END ) AS 'yxsjcs_187d6738-cd8b-11e6-aa3c-000c2909830f',SUM( CASE  WHEN carline_model.t_carline_id = '8e463ec6-bac7-11e6-9783-fcaa14cf7e62' THEN 1 ELSE0 END ) AS 'yxsjcs_8e463ec6-bac7-11e6-9783-fcaa14cf7e62',SUM( CASE  WHEN carline_model.t_carline_id = '8e46426f-bac7-11e6-9783-fcaa14cf7e62' THEN 1 ELSE0 END ) AS 'yxsjcs_8e46426f-bac7-11e6-9783-fcaa14cf7e62',SUM(1) AS yxsjcs_qcxFROMt_car_drive car_driveLEFT JOIN t_dealer dealer ON dealer.id = car_drive.t_dealer_idLEFT JOIN t_carline_model carline_model ON carline_model.id = car_drive.t_carline_idLEFT JOIN t_carline carline ON carline.id = carline_model.t_carline_idWHEREcar_drive.ReasonType != 99 AND carline.isdrive = 1GROUP BY yxsjcsDealerId) AS yxsjcs_temp ON yxsjcs_temp.yxsjcsDealerId = dealer_temp.dealer_idLEFT JOIN (SELECT dealer.id AS qksDealerId,SUM( CASE WHEN dealer_potentialcustomer.t_carline_id = '84e46cf1-022f-11e7-aa3c-000c2909830f' THEN dealer_potentialcustomer.number ELSE 0 END ) AS 'qks_84e46cf1-022f-11e7-aa3c-000c2909830f',SUM( CASE WHEN dealer_potentialcustomer.t_carline_id = '187d6738-cd8b-11e6-aa3c-000c2909830f' THEN dealer_potentialcustomer.number ELSE 0 END ) AS 'qks_187d6738-cd8b-11e6-aa3c-000c2909830f',SUM( CASE WHEN dealer_potentialcustomer.t_carline_id = '8e463ec6-bac7-11e6-9783-fcaa14cf7e62' THEN dealer_potentialcustomer.number ELSE0 END ) AS 'qks_8e463ec6-bac7-11e6-9783-fcaa14cf7e62',SUM( CASE WHEN dealer_potentialcustomer.t_carline_id = '8e46426f-bac7-11e6-9783-fcaa14cf7e62' THEN dealer_potentialcustomer.number ELSE0 END ) AS 'qks_8e46426f-bac7-11e6-9783-fcaa14cf7e62',SUM( dealer_potentialcustomer.number) AS qks_qcxFROMt_dealer_potentialcustomer AS dealer_potentialcustomerLEFT JOIN t_carline ON t_carline.id = dealer_potentialcustomer.t_carline_idLEFT JOIN t_dealer dealer ON dealer.id = dealer_potentialcustomer.t_dealer_idLEFT JOIN t_carline carline ON carline.id = dealer_potentialcustomer.t_carline_idWHEREdealer_potentialcustomer.isdel = 0GROUP BYqksDealerId) AS qks_temp ON qks_temp.qksDealerId = dealer_temp.dealer_idLEFT JOIN t_dealer ON t_dealer.id = dealer_temp.dealer_idLEFT JOIN t_org org_rbo ON org_rbo.id = t_dealer.t_org_rboidLEFT JOIN t_org org_smallregion ON org_smallregion.id = t_dealer.t_org_smallregionidWHEREorg_rbo.isdel = 0ORDER BYnamechnName DESC

重写后的sql:

SELECT u.`namechn` AS rboName,u.`t_dealer_name` AS namechnName,u.`code` AS namechnCode,SUM( CASE WHEN u.lineId='8e46426f-bac7-11e6-9783-fcaa14cf7e62' THEN  u.isdrive ELSE 0 END) AS isdrive1,SUM( CASE WHEN u.lineId='8e46426f-bac7-11e6-9783-fcaa14cf7e62' THEN  u.potent ELSE 0 END) AS potent1,SUM( CASE WHEN u.lineId='187d6738-cd8b-11e6-aa3c-000c2909830f' THEN  u.isdrive ELSE 0 END) AS isdrive2,SUM( CASE WHEN u.lineId='187d6738-cd8b-11e6-aa3c-000c2909830f' THEN  u.potent ELSE 0 END) AS potent2,SUM( CASE WHEN u.lineId='84e46cf1-022f-11e7-aa3c-000c2909830f' THEN  u.isdrive ELSE 0 END) AS isdrive3,SUM( CASE WHEN u.lineId='84e46cf1-022f-11e7-aa3c-000c2909830f' THEN  u.potent ELSE 0 END) AS potent3,SUM( CASE WHEN u.lineId='8e463ec6-bac7-11e6-9783-fcaa14cf7e62' THEN  u.isdrive ELSE 0 END) AS isdrive4,SUM( CASE WHEN u.lineId='8e463ec6-bac7-11e6-9783-fcaa14cf7e62' THEN  u.potent ELSE 0 END) AS potent4,SUM( u.isdrive) AS totalIsdrive,SUM( u.potent ) AS totalpotentFROM (SELECT   a.`namechn`,  t.`t_dealer_name`,  d.`code`,  line.name,  COUNT(t.`id`) AS isdrive,  0 AS potent,  t.t_dealer_id ,  line.id AS lineIdFROM  t_dealer AS dLEFT JOIN t_car_drive AS t   ON d.`id`=t.`t_dealer_id`LEFT JOIN `t_org` AS  a  ON a.`id`=t.`t_org_rboid`LEFT JOIN  t_carline_model AS m ON m.id=t.`t_carline_id`LEFT JOIN  t_carline  AS line ON m.t_carline_id=line.idWHERE t.ReasonType != 99 AND line.isdrive = 1GROUP BY d.`code`,line.idUNION SELECT   a.`namechn`,  d.`namechn`,  d.`code`,  line.name ,  0  AS isdrive,  SUM(p.number) AS potent,  d.id ,  line.id AS lineIdFROM   t_dealer AS dLEFT JOIN t_dealer_potentialcustomer AS p   ON d.`id`=p.`t_dealer_id`INNER JOIN `t_org` AS  a  ON a.`id`=d.`t_org_rboid`LEFT JOIN  t_carline  AS line ON p.t_carline_id=line.idWHERE  p.isdel = 0GROUP BY d.code, line.id) AS uGROUP BY u.code

实现：

           同时以经销商和车系为纬度，分别统计试驾次数和潜客数，然后通过union将其结果结合起来。

           把上面的结果作为子查询数据，已经销商为纬度进行聚合计算。

问题发现过程：

1、改写后查询效率确实有了很大的提高。

2、针对改写后的语句查看执行计划，建立相应索引。效率又提升了一倍还多。

3、有趣的事情发生了：建立索引后再执行原来的sql ,发现效率居然比改写后的sql 要高，快大概零点几秒。

4、经过多方测试发现，改写后的sql group by 有句后面是两个字段，这里的分组特别耗时，所以把查询拖慢了。而第一种由于group by 一个字段反而效率更高一点。

总结，谨慎的使用group  by 。建立索引时，对group  by 后面的字段建立索引往往会有惊喜的效果。