mysql商品销售情况统计查询特别慢,如何优化呢？

站长

2024年07月17日 10:11 · 阅读数 31

问题描述

goods

字段	类型	说明
id	主键	递增主键
title	varchar	商品名称
create_time	int64	创建时间(索引)

orders

字段	类型	说明
id	主键	递增主键
goods_id	int64	商品id(索引)
amount	decimal	金额
status	tinyint	状态 0:进行中,1:已成功,2:已失败(索引)
create_time	int64	创建时间(索引)

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_total
FROM 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 DESC
LIMIT 10

查询结果

total	total_amount	success_total	failed_total	success_amount	failed_amount
订单总数量	订单总金额	成功的订单数量	失败的订单数量	成功的订单金额	失败的订单金额

商品数量:8000个左右订单数量:100000左右

以上sql是可以查询出来的但是查询当天的还可以(300-500ms左右)，查询昨天，本周，上周，本月，上月，以及本季度，上季度，今年，以及去年和全部的时候，特别的慢，

有想过用clickhouse 但是比较麻烦，要涉及到数据同步

请教下如何优化sql达到目的呢？

1个回答

test

2024-07-17

建议尝试：

去掉俩表的 create_time 索引
orders 表的 goods_id 索引改成 (create_time, goods_id, amount, status)
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

就几十万的数据量，感觉换 SQLite 来查询，应该都没啥性能问题

适合作为回答的

经过验证的有效解决办法
自己的经验指引，对解决问题有帮助
遵循 Markdown 语法排版，代码语义正确

不该作为回答的

询问内容细节或回复楼层
与题目无关的内容
“赞”“顶”“同问”“看手册”“解决了没”等毫无意义的内容