mysql查询语句优化
用explain优化了一个营业日汇总报表的查询。将一处type为all的查询改为range。
sql是一个报表使用:
EXPLAIN SELECT DATE_FORMAT(t1.business_date,‘%Y-%m-%d‘) AS business_date, IF(t3.people_num>0, (t1.need_pay_amount/t3.people_num),0) AS avg_people_money,.. FROM ( SELECT (SUM(timer_charge) + SUM(time_out_money)) AS timer_charge, 0.00 AS avg_people_money, .. FROM consumption_history c WHERE c.`status_code` = 1 AND c.`business_date`>=‘2014-12-04‘ AND c.`business_date`<=‘2014-12-04‘ GROUP BY c.`business_date` ) t1 LEFT JOIN ( SELECT GROUP_CONCAT(CONCAT(p.payment_pattern_name, ‘=‘, h.payment_money)) AS payment_money_info,SUM(IF(p.payment_property=1,h.payment_money,0)) AS actually_pay_money,.. FROM consumption_history c INNER JOIN consumption_payment_history h ON h.consumption_id=c.`consumption_id` INNER JOIN payment_pattern p ON h.`payment_type_id`=p.`payment_pattern_id` WHERE c.`status_code` = 1 AND c.`business_date`>=‘2014-12-04‘ AND c.`business_date`<=‘2014-12-04‘ GROUP BY c.`business_date` ) t2 ON t1.business_date = t2.business_date LEFT JOIN ( SELECT business_date,SUM(people_num) AS people_num, SUM(order_amount) AS dish_sale_amount FROM dining_order_history c WHERE c.`status_code` = 1 AND c.`business_date`>=‘2014-12-04‘ AND c.`business_date`<=‘2014-12-04‘ GROUP BY c.`business_date` ) t3 ON t1.business_date=t3.business_date ORDER BY t1.`business_date`
可以看到explain的结果,这里重点说明下几个列
对type查询方式做下简单说明
在一个consumption_history达到几万条(数据量具体多少forget了)的情况下,性能优化相当明显,从30秒连接超时,到1秒出结果。
看来上述sql是优化的比较到位了。
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。