优化 mysql 商品销售情况统计查询性能
问题描述：
针对如下 sql 查询：
select p.title, count(o.id) as total, coalesce(sum(o.amount), 0) as success_amount,coalesce(sum(success.amount), 0) as failed_amount,coalesce(sum(failed.amount), 0) as total_amount,count(success.id) as success_total,count(failed.id) as failed_totalfrom goods as g left join orders as o on o.goods_id = g.id left join orders as success on success.goods_id = g.id and success.status = 1 left join orders as failed on failed.goods_id = g.id and failed.status = 2 group by `p`.`id`order by total desclimit 10
当查询时间范围较小时，查询速度较快，但当查询范围扩大时，查询速度极慢。
优化建议：
取消索引：删除 goods 表的 create_time 索引。修改索引：将 orders 表的 goods_id 索引修改为 (create_time, goods_id, amount, status) 联合索引。重写 sql：优化后的 sql 如下：
SELECT g.title, COUNT(*) AS total, COALESCE(SUM(o.amount), 0) AS total_amount, COALESCE(SUM(IF(o.status = 1, o.amount, 0)), 0) AS success_amount, COALESCE(SUM(IF(o.status = 2, o.amount, 0)), 0) AS failed_amount, COALESCE(SUM(o.status = 1), 0) AS success_total, COALESCE(SUM(o.status = 2), 0) AS failed_total FROM orders AS o JOIN goods AS g ON g.id = o.goods_id WHERE o.create_time BETWEEN 'xxx' AND 'yyy' GROUP BY o.id ORDER BY total DESC LIMIT 10
注：
由于数据量较小（商品数量：8000，订单数量：100000），考虑使用 sqlite 代替 mysql 进行查询，可以获得较好的性能。
以上就是如何优化 MySQL 商品销售情况统计查询性能，使其在查询时间范围扩大时也能保持快速响应？的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/42513.html
微信扫一扫 
支付宝扫一扫 
