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

Question

问题描述

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达到目的呢？

Answer 1

建议尝试：

1. 去掉俩表的 create_time 索引
2. orders 表的 goods_id 索引改成 (create_time, goods_id, amount, status)
3. 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 来查询，应该都没啥性能问题