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

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

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

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