.jpg)
優(yōu)化 mysql 商品銷售情況統(tǒng)計(jì)查詢性能
問(wèn)題描述：
針對(duì)如下 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
當(dāng)查詢時(shí)間范圍較小時(shí)，查詢速度較快，但當(dāng)查詢范圍擴(kuò)大時(shí)，查詢速度極慢。
優(yōu)化建議：
- 取消索引：刪除 goods 表的 create_time 索引。
- 修改索引：將 orders 表的 goods_id 索引修改為 (create_time, goods_id, amount, status) 聯(lián)合索引。
- 重寫(xiě) sql：優(yōu)化后的 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
注：
由于數(shù)據(jù)量較小（商品數(shù)量：8000，訂單數(shù)量：100000），考慮使用 sqlite 代替 mysql 進(jìn)行查詢，可以獲得較好的性能。
? 版權(quán)聲明
文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載。
THE END
.png)
