数百万数据门店销售订单按门店分组排序分页取销售额前 20 条怎么优化 order by

大致有订单表 order, 含 amount storeid (每单销售额, 销售门店)
select storeid,sum(amount) as total
from
order
group by storeid
order by total desc
limit 0,20
数据量快千万了, 不加 order by 排序很快. 但是分页需要显示销售额最高的前 20 条数据, 不可避免排序
一般数据量大的改怎么分组排序? (数据量很大, 取出来放代码里排统计也不行)

实际上也需要连接其他表? 是先主表分组后再 left join 还是 left join 后再分组?
请问下大佬们是怎么解决的.

第 1 条附言 · 2020-12-25 10:38:14 +08:00

感谢大佬们的热心解答.
查询还有时间限制.. 找出某个时间段范围内, 各门店在此期间的订单销售额, 单数,单价.
如果是按天的,不要求实时, 大佬们说的缓存, 或者之前的数据定时统计,今天的库里查询,再汇总挺好的.

限制主要在时间跨度大了,分组求和再排序的耗时比较久.

order

storeid

排序

分组

27 条回复 • 2020-12-25 16:23:21 +08:00

zoharSoul

2020-12-24 17:09:09 +08:00

丢到数仓跑...

cloverzrg2

2020-12-24 17:13:29 +08:00

你这个要实时获取最新的？
不是的话，放到缓存里，每隔一个小时左右更新一次就行了

wangritian

2020-12-24 17:13:57 +08:00

门店表加 total 字段，订单完成时同步增加，加好索引

wuzhizuiguo

2020-12-24 17:33:10 +08:00

@zoharSoul 第一次遇到.. 数仓,这个我段位不够

wuzhizuiguo

2020-12-24 17:39:46 +08:00

@cloverzrg2 是的,现在要实时的..

matrix67

2020-12-24 17:40:09 +08:00

@cloverzrg2 #2 2 楼老哥说的对
不需要实时的话，是不是再开个表，每天晚上统计每个门店销售额，然后从这个表 select 就行了。

还有最近看 elk，它的 demo 里面有销售数据，是不是可以来搞这个，做到准实时。

wuzhizuiguo

2020-12-24 17:40:49 +08:00

@wangritian 嗯,原来也加了表, 但是数据不准. 又返回去了.

LJ2010

2020-12-24 17:41:34 +08:00

写个定时服务,每天 /月统计一次订单表
insert to 门点销售额表
查询直接查统计表

renmu123

2020-12-24 17:43:33 +08:00 via Android

数据量这么大了很难做到实时，一般这种产品都是看前一天数据的，参考微信小程序的数据统计。就说产品这个需求是伪需求，以及技术难度过高，占据过多性能，，把产品怼回去

wuzhizuiguo

2020-12-24 17:43:39 +08:00

@matrix67 好的,现在算是实时的, 底下商户也要看下实际销售情况. 没想到 order by 这么慢

LJ2010

2020-12-24 17:44:29 +08:00

补充下, 单表查询慢的话 ,可以增加分区,或者拆表(不过会增加业务处理逻辑的复杂度),一般分区根据分区字段排序查询,速度应该都可以

kiracyan

2020-12-24 17:45:22 +08:00

你把旧数据汇总一份当天的在这个表里拿往前的数据从汇总表里拿然后查当天的信息再加起来排序可以吗？

wuzhizuiguo

2020-12-24 17:45:35 +08:00

@renmu123 哈哈, 限制也是有点儿, 时间, 但时间跨度大一些就明显慢了.

shyrock

2020-12-24 17:46:26 +08:00

total 加索引了吗？ explain 一下查询计划看看

liian2019

2020-12-24 17:47:24 +08:00

整个定时任务，定时统计数据插入到统计表，比如根据门店定时汇总销售额。单纯靠订单表来统计不靠谱，以后量大了，查不动是迟早的事，要是因为这个查询影响正常下单业务就得不偿失了。

ElmerZhang

2020-12-24 17:48:23 +08:00

又要实时，又要性能的情况下
方案 A：另外加个汇总表，定单表与汇总表的更新做成事务。这样的缺点就是订单业务中耦合了统计的逻辑，不优雅，而且对性能也会有一些影响。
方案 B：仍然是加个汇总表，但是通过 [otter]( https://github.com/alibaba/otter) 来更新，没有方案 A 中提到的缺点，但是架构变复杂了。

wangritian

2020-12-24 17:53:40 +08:00

@wuzhizuiguo 数据不准的原因是什么呢？开启事务和订单行锁，一般不会出现并发异常

zhaokun

2020-12-24 18:27:47 +08:00

total 没有索引，total 维护一个字段加上索引压力不大

xupefei

2020-12-24 18:39:48 +08:00 via iPhone

百万条数据，用 spark 开 16 节点的话也就十秒钟。

ElmerZhang

2020-12-24 18:40:14 +08:00

纠正一下我 #16 的回复，方案 B 中应该是用 [canal]( https://github.com/alibaba/canal)

IDAEngine

2020-12-25 06:15:09 +08:00 via iPhone

尽量不要弄实时，10 分钟刷一次还是可以接受的，毕竟数据量不小了，而且还会增加

Nillouise

2020-12-25 08:56:49 +08:00

门店数量应该不多吧，应该只有几百几千个？为什么找前 20 个会很慢？

用 dynamodb 按 storeId 做分区键感觉可以解决？

Nillouise

2020-12-25 09:20:13 +08:00

我发现可以定时统计出前 100 销售额的门店，后面实时查询的时候就只查这 100 个门店即可。

wuzhizuiguo

2020-12-25 10:55:24 +08:00

@wangritian 没有加锁, 订单插入了,异常统计销售的没有插入..

SlipStupig

2020-12-25 11:20:59 +08:00

1.首先先检查索引，正确索引效率能提高百倍。
2. 使用正确数据结构，能也能有所帮助
3. 如果经济状态有富裕的情况下去，尽量提高机器配置，这个可能比绞尽脑汁提高语句来的快
4.改造订单号，好的订单号设计，能减轻检索压力
5. 表按时间分区，比如：ordered_2020_12_25，时间跨度长的话，可以直接通过对分区表的操作，实现 mapreduce，这个就是另外，时区跨度过大，采用 filter sort，而不是直接排

wuzhizuiguo

2020-12-25 11:28:54 +08:00

@Nillouise 因为要按额度排序, 选择时间范围内, 数据量多

wuzhizuiguo

2020-12-25 16:23:21 +08:00

谢谢. 汇总下. 分区尝试了,失败了.
缓存, 如果是看整体的, 不涉及时间的可以. 跑定时器,查出所有的门店情况, 缓存着.
老数据每天存起来, 查的时候, 时间按天来, 以前的去找老数据,今天的去库里建时间索引查询.
还有就是数据库的性能也很关键,同一条语句, 正式测试环境差距很大, 耗时是 0.几秒 -> 三四秒, 再加条数统计就更爆炸了.
准备暂时不改动... 慢就慢吧.
要改的时候, 就采用订单产生后相应的去统计表里按天增量数据, 查询的时候老数据结合新数据.

数百万数据 门店销售订单 按门店 分组排序分页 取销售额前 20 条 怎么优化 order by

数百万数据门店销售订单按门店分组排序分页取销售额前 20 条怎么优化 order by