商品销售情况统计查询优化
为了优化mysql商品销售情况统计查询，以下是建议的优化方案：
去掉创建索引：
删除 goods 表的 create_time 索引。 删除 orders 表的 goods_id 索引。修改索引：
将 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
考虑使用其他数据库：
对于少量数据（几十万），使用sqlite数据库可能会有更好的性能。
