优化 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_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

当查询时间范围较小时，查询速度较快，但当查询范围扩大时，查询速度极慢。

kashen-mysql DDLtoExcel

解析 MySQL 8.0 的 DDL 脚本，并将表结构导出为 Excel 格式。适用于用户需将 MySQL 的 `CREATE TABLE` 语句转换为结构化的 Excel 文件场景，导出内容包含字段名、数据类型、长度及精度等信息。支持多张表的批量处理，每张表对应 Excel 中的一个独立工作表。适用于数据库文档编写、数据字典生成及模式分析等任务。

下载

优化建议：

1. 取消索引：删除 goods 表的 create_time 索引。
2. 修改索引：将 orders 表的 goods_id 索引修改为 (create_time, goods_id, amount, status) 联合索引。
3. 重写 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 进行查询，可以获得较好的性能。