V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
wuzhizuiguo
V2EX  ›  MySQL

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

  •  
  •   wuzhizuiguo · 68 天前 · 1320 次点击
    这是一个创建于 68 天前的主题,其中的信息可能已经有所发展或是发生改变。
    大致 有订单表 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 条附言  ·  67 天前
    感谢大佬们的热心解答.
    查询 还有时间限制.. 找出某个时间段范围内, 各门店在此期间的订单销售额, 单数,单价.
    如果是按天的,不要求实时, 大佬们说的 缓存, 或者之前的数据定时统计,今天的库里查询,再汇总 挺好的.

    限制主要在 时间跨度大了,分组 求和再排序的 耗时比较久.
    27 条回复    2020-12-25 16:23:21 +08:00
    zoharSoul
        1
    zoharSoul   68 天前
    丢到数仓跑...
    cloverzrg2
        2
    cloverzrg2   68 天前
    你这个要实时获取最新的?
    不是的话,放到缓存里,每隔一个小时左右更新一次就行了
    wangritian
        3
    wangritian   68 天前
    门店表加 total 字段,订单完成时同步增加,加好索引
    wuzhizuiguo
        4
    wuzhizuiguo   68 天前
    @zoharSoul 第一次遇到.. 数仓,这个我段位不够
    wuzhizuiguo
        5
    wuzhizuiguo   68 天前
    @cloverzrg2 是的,现在要实时的..
    matrix67
        6
    matrix67   68 天前
    @cloverzrg2 #2 2 楼老哥说的对
    不需要实时的话,是不是再开个表,每天晚上统计每个门店销售额,然后从这个表 select 就行了。

    还有最近看 elk,它的 demo 里面有销售数据,是不是可以来搞这个,做到准实时。
    wuzhizuiguo
        7
    wuzhizuiguo   68 天前
    @wangritian 嗯,原来也加了表, 但是数据不准. 又返回去了.
    LJ2010
        8
    LJ2010   68 天前
    写个定时服务,每天 /月统计一次订单表
    insert to 门点销售额表
    查询直接查 统计表
    renmu123
        9
    renmu123   68 天前 via Android
    数据量这么大了很难做到实时,一般这种产品都是看前一天数据的,参考微信小程序的数据统计。就说产品这个需求是伪需求,以及技术难度过高,占据过多性能,,把产品怼回去
    wuzhizuiguo
        10
    wuzhizuiguo   68 天前
    @matrix67 好的,现在算是实时的, 底下商户也要看下实际销售情况. 没想到 order by 这么慢
    LJ2010
        11
    LJ2010   68 天前
    补充下, 单表查询慢的话 ,可以增加分区,或者拆表(不过会增加业务处理逻辑的复杂度),一般分区根据分区字段排序查询,速度应该都可以
    kiracyan
        12
    kiracyan   68 天前   ❤️ 1
    你把旧数据汇总一份 当天的在这个表里拿 往前的数据从汇总表里拿 然后查当天的信息再加起来排序可以吗?
    wuzhizuiguo
        13
    wuzhizuiguo   68 天前
    @renmu123 哈哈, 限制也是有点儿, 时间, 但 时间跨度大一些 就明显慢了.
    shyrock
        14
    shyrock   68 天前
    total 加索引了吗? explain 一下查询计划看看
    liian2019
        15
    liian2019   68 天前
    整个定时任务,定时统计数据插入到统计表,比如根据门店定时汇总销售额。单纯靠订单表来统计不靠谱,以后量大了,查不动是迟早的事,要是因为这个查询影响正常下单业务就得不偿失了。
    ElmerZhang
        16
    ElmerZhang   68 天前
    又要实时,又要性能的情况下
    方案 A:另外加个汇总表,定单表与汇总表的更新做成事务。这样的缺点就是订单业务中耦合了统计的逻辑,不优雅,而且对性能也会有一些影响。
    方案 B:仍然是加个汇总表,但是通过 [otter]( https://github.com/alibaba/otter) 来更新,没有方案 A 中提到的缺点,但是架构变复杂了。
    wangritian
        17
    wangritian   68 天前
    @wuzhizuiguo 数据不准的原因是什么呢?开启事务和订单行锁,一般不会出现并发异常
    zhaokun
        18
    zhaokun   68 天前
    total 没有索引,total 维护一个字段加上索引压力不大
    xupefei
        19
    xupefei   68 天前 via iPhone
    百万条数据,用 spark 开 16 节点的话也就十秒钟。
    ElmerZhang
        20
    ElmerZhang   68 天前
    纠正一下我 #16 的回复,方案 B 中应该是用 [canal]( https://github.com/alibaba/canal)
    IDAEngine
        21
    IDAEngine   67 天前 via iPhone
    尽量不要弄实时,10 分钟刷一次还是可以接受的,毕竟数据量不小了,而且还会增加
    Nillouise
        22
    Nillouise   67 天前
    门店数量应该不多吧,应该只有几百几千个?为什么找前 20 个会很慢?

    用 dynamodb 按 storeId 做分区键感觉可以解决?
    Nillouise
        23
    Nillouise   67 天前
    我发现可以定时统计出前 100 销售额的门店,后面实时查询的时候就只查这 100 个门店即可。
    wuzhizuiguo
        24
    wuzhizuiguo   67 天前
    @wangritian 没有加锁, 订单插入了,异常 统计销售的没有插入..
    SlipStupig
        25
    SlipStupig   67 天前
    1.首先先检查索引,正确索引效率能提高百倍。
    2. 使用正确数据结构,能也能有所帮助
    3. 如果经济状态有富裕的情况下去,尽量提高机器配置,这个可能比绞尽脑汁提高语句来的快
    4.改造订单号,好的订单号设计,能减轻检索压力
    5. 表按时间分区,比如:ordered_2020_12_25,时间跨度长的话,可以直接通过对分区表的操作,实现 mapreduce,这个就是另外, 时区跨度过大,采用 filter sort,而不是直接排
    wuzhizuiguo
        26
    wuzhizuiguo   67 天前
    @Nillouise 因为要按额度排序, 选择时间范围内, 数据量多
    wuzhizuiguo
        27
    wuzhizuiguo   67 天前
    谢谢. 汇总下. 分区尝试了,失败了.
    缓存, 如果是看整体的, 不涉及时间的可以. 跑定时器,查出所有的门店情况, 缓存着.
    老数据每天存起来, 查的时候, 时间按天来, 以前的去找老数据,今天的去库里 建时间索引查询.
    还有就是数据库的性能也很关键,同一条语句, 正式测试环境差距很大, 耗时是 0.几秒 -> 三四秒, 再加条数统计就更爆炸了.
    准备暂时不改动... 慢就慢吧.
    要改的时候, 就采用 订单产生后 相应的去统计表里 按天增量数据, 查询的时候 老数据结合新数据.
    关于   ·   帮助文档   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   3179 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 21ms · UTC 14:23 · PVG 22:23 · LAX 06:23 · JFK 09:23
    ♥ Do have faith in what you're doing.