MySql之优化查询语句

 一，减少重复查询   

SELECT t.province AS '省份',t.city as '城市',(select count(DISTINCT accountId) from try_apply_info where accountId = t.accountId) AS '人数'
  FROM try_apply_info t,try_activity a,try_apply ap 
 WHERE a.id = ap.activityId 
   AND ap.id = t.id 
   AND a.startTime >= 20160101 
   AND a.startTime < 20170101 
   AND a.reportTime >= 20160101 
   AND a.reportTime < 20170101 
   AND t.province IS NOT NULL AND t.province <> 'null' 
GROUP BY t.province,t.city

以上的sql需要查询几分钟甚至十几分钟才会出来，然而只需要很小的改动就能极大的提高查询效率，高手一眼就能看出来，

(select count(DISTINCT accountId) from try_apply_info where accountId = t.accountId) AS '人数'

这一句又查询了自身一次，完全没必要，只需改成以下,0.003s就能出结果了，毫秒杀有木有！！！

 count(DISTINCT t.accountId) AS '人数'

二，多用聚合求法

select c.accountId as '用户ID',a.accountName as '用户名',(select count(*) from cs_green_card where status = -1 and accountId = c.accountId) 
as '已使用张数',(select count(*) from cs_green_card 
where status = -2 and accountId = c.accountId) as '已过期张数' from cs_green_card c,cs_account a 
where c.accountId = a.accountId and c.startDate >= 20160101 and c.startDate < 20170401 GROUP BY c.accountId ORDER BY c.createAt desc

查询时间是0.572s，其实也不长，但我们有更好的做法

(select count(*) from cs_green_card where status = -1 and accountId = c.accountId) as '已使用张数'

这里其实也是减少查询，但因为有status = -1这个条件，我们需要用到聚合求法，将满足该条件的数据进行统计，时间缩短为0.02s

(SUM(CASE WHEN c.status = -1 THEN 1 ELSE 0 END) '已使用张数'

三 ，多用LEFT JOIN 联表查询

select a.id as '活动ID',a.title as '标题',a.startTime as '活动开始时间',a.applyTime as '申请截止时间',a.reportTime as '活动结束时间',
(select total from cs_counter where id = a.viewCounterId) as '试用人气',
a.applyNum as '申请人总数',(select COUNT(id) from try_apply_anonymous where activityId = a.id) as '匿名申请数',
(select count(b.id) from try_apply b where b.source = 0 and b.activityId = a.id) as 'PC申请人数',
(select count(b.id) from try_apply b where b.source = 2 and b.activityId = a.id) as 'WAP申请人数',
(select count(b.id) from try_apply b where b.source = 1 and b.activityId = a.id) as 'APP申请人数',
a.amount as '份数',a.specs as '规格',a.price as '价格',
TRUNCATE((select count(id) from try_report where activityId = a.id and `status` > 0)/(select count(id) from try_report where activityId= a.id),2) 
as '报告回收率'
from try_activity a
where a.enable = 1 and a.startTime >= 20160101 and a.startTime < 20160201 GROUP BY a.id; 

根据第一和第二，以上的sql还有很大的改进空间，但这里的重点是left join的用法，下面进行全面升级

SELECT a.id AS '活动ID',a.title AS '标题',a.startTime AS '活动开始时间',a.applyTime AS '申请截止时间',a.reportTime AS '活动结束时间',
       cc.total AS '试用人气',a.applyNum AS '申请人总数',COUNT(taa.id) AS '匿名申请数',
       tx.pc_cnt 'PC申请人数',tx.wap_cnt 'WAP申请人数',tx.app_cnt 'APP申请人数',
       a.amount AS '份数',a.specs AS '规格',a.price AS '价格' ,
       TRUNCATE(SUM(CASE WHEN tr.status > 0 THEN 1 ELSE 0 END)/COUNT(tr.id),2) AS '报告回收率'
   FROM try_activity a 
     LEFT JOIN cs_counter cc ON cc.id = a.viewCounterId
     LEFT JOIN try_apply_anonymous taa ON taa.`activityId` = a.id
     JOIN (SELECT a.id,SUM(CASE WHEN cc.source = 0 THEN 1 ELSE 0 END) pc_cnt,SUM(CASE WHEN cc.source = 2 THEN 1 ELSE 0 END) wap_cnt,
     SUM(CASE WHEN cc.source = 1 THEN 1 ELSE 0 END) app_cnt
         FROM try_activity a  LEFT JOIN try_apply cc ON cc.activityId = a.id GROUP BY id) tx ON tx.id = a.id
     LEFT JOIN try_report tr ON tr.activityId = a.id
WHERE a.enable = 1 AND a.startTime >= 20161201 AND a.startTime < 20170101
   GROUP BY a.id

注意：话说第1种的写法更快，时间是5s左右，但第2种需要10s左右，第1种速度更快的原因在于采用了单表查询，第2种多虽然使用left join 联表查询，但多表查询降低了速度