V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
Breadykid
V2EX  ›  程序员

千万级单表 sql 查询问题

  •  
  •   Breadykid ·
    breadkid · 2019-08-28 14:33:12 +08:00 · 5400 次点击
    这是一个创建于 1674 天前的主题,其中的信息可能已经有所发展或是发生改变。

    划重点:单表 3500w

    单表查
    表 [用户]
    字段 [用户名,金额,日期时间]
    查每天同一时间用户 A 和用户 B 金额之和的最大值
    如果不用

    	
    SELECT
    	max( a.金额 + b.金额 ),
    	a.日期时间 
    FROM
    	( SELECT 金额,日期时间 FROM 用户表 WHERE 用户名 = 'a' ) a
    	LEFT JOIN ( SELECT 金额,日期时间 FROM 用户表 WHERE 用户名 = 'b' ) b 
        	ON a.日期时间 = b.日期时间
    

    该怎么写,不能加索引
    上面这个 sql 查得特别慢,近 1 分钟了

    第 1 条附言  ·  2019-08-29 09:26:53 +08:00
    少年们,已经采用了每日计算前一天加和最大值的定时任务来解决,勉强找了一张表,把记录塞了进去。。。
    谢谢米娜~
    48 条回复    2019-08-30 18:34:34 +08:00
    vus520
        1
    vus520  
       2019-08-28 14:37:51 +08:00
    拆 SQL....
    php01
        2
    php01  
       2019-08-28 14:40:41 +08:00
    select 金额,日期时间 from 用户表 用户名 in ('a','b');
    用程序计算。
    此后,加表,作为统计表,统计表同日累加金额。
    hosaos
        3
    hosaos  
       2019-08-28 14:40:54 +08:00
    代码里做啊 ,同一时间如果不长的话,先定个变量 MaxMoney=0,取 A 的数据(可分页),然后取 B,值大了就覆盖 MaxMoney
    php01
        4
    php01  
       2019-08-28 14:43:19 +08:00
    @php01 单表 3500w 数据,光取出来就时间话费很长了。所以你先整体全部算一遍,重新做一张统计表,把统计结果写入,以后每次增改,同步这张统计表。
    Breadykid
        5
    Breadykid  
    OP
       2019-08-28 14:53:05 +08:00
    @php01 目前有类似的统计表,但是没有能放这个数值的表,要是建新表的话里面只有这一个字段,但是直接在 3500w 单表里取,怎么取都要很久,光
    ```SELECT 金额,日期时间 FROM 用户表 WHERE 用户名 = 'a'```
    就花 46s
    glacer
        6
    glacer  
       2019-08-28 14:53:14 +08:00
    为什么不能加索引? join 如果是用临时表的话是无法使用索引的
    no1xsyzy
        7
    no1xsyzy  
       2019-08-28 15:10:01 +08:00
    `SELECT 金额,日期时间 FROM 用户表 WHERE 用户名 = 'a'` 取出来有多少个?
    Breadykid
        8
    Breadykid  
    OP
       2019-08-28 15:16:29 +08:00
    Immortal
        9
    Immortal  
       2019-08-28 16:05:18 +08:00
    拿到上一次查询结果的 id 号 作为这一次查询的 where 条件:>id
    虽然上一次 id 不一定是昨天的最后一条数据 但肯定小于今天的数据
    如果在两天内的数据量下执行 max 可能会好点 你可以试试
    shyrock
        10
    shyrock  
       2019-08-28 16:08:31 +08:00
    其他需求都普普通通,但是这个‘不能加索引’就很骚了。。。能问下为什么?
    npe
        11
    npe  
       2019-08-28 16:23:07 +08:00
    where 用户=a,b
    group by 时间
    max 金额

    ----

    数据量过大建议分表,可以按月进行拆分。
    Aresxue
        12
    Aresxue  
       2019-08-28 16:24:24 +08:00
    拆呗,不过也不知道你想咋样啊,你是怕单条 sql 超时了还是怕数据库 IO 时间过长压力过大,亦或是要求必须一条 sql
    搞定啊,不知道你想干啥这工作也没法开展不是。如果只是怕数据库长时间 IO 压力太大,就拆呗,搞成几条单表查询,在代码里做计算。如果必须要一条 sql 搞定,你还不准在任何列上用索引,那几乎没啥太大优化空间了,语义上的优化对你这种简单 sql 帮助不大。
    jziwenchen
        13
    jziwenchen  
       2019-08-28 16:25:35 +08:00
    ```SELECT 金额,日期时间 FROM 用户表 WHERE 用户名 = 'a'```
    这条语句花了 46s ?? 4000w 数据并不多啊 怎么会这么慢...
    shadow88sky
        14
    shadow88sky  
       2019-08-28 16:27:09 +08:00
    将表同步到 elasticsearch 中,学一些简单的 es 语法,就搞定了
    jziwenchen
        15
    jziwenchen  
       2019-08-28 16:33:08 +08:00
    这样可以吗?

    select max(a.金额 + b.金额) , 日期
    from 表 as a
    left join 表 as b on b. 日期 = a.日期
    where a.用户='a' and a.用户='b'
    arrow8899
        16
    arrow8899  
       2019-08-28 16:37:28 +08:00
    这样做的目的是啥
    1762628386
        17
    1762628386  
       2019-08-28 16:40:41 +08:00   ❤️ 3
    建议收购公司,夺得话语权,然后加上索引就行了
    liuhuansir
        18
    liuhuansir  
       2019-08-28 16:44:01 +08:00
    SELECT 金额,日期时间 FROM 用户表 WHERE 用户名 = 'a' 这个都花了 46s,那单纯优化 sql 已经不可能了,纯粹是数据量过大,只能拆了
    onepunch
        19
    onepunch  
       2019-08-28 16:44:20 +08:00
    如果是阿里云建议了解一下分析型数据 ,最近接触的业务使用了下非常好用。跟生产数据一致,而且查询都是秒级的。
    场景跟你很像,生产环境无法追加索引。

    https://cn.aliyun.com/product/ads
    ylsc633
        20
    ylsc633  
       2019-08-28 17:03:10 +08:00
    粗略看了下 你这是不是可以改成

    where name in ('a','b') group by time
    wlkq
        21
    wlkq  
       2019-08-28 17:33:31 +08:00
    2 楼的方法算最优解了吧,又不能加索引
    IamUNICODE
        22
    IamUNICODE  
       2019-08-28 17:45:13 +08:00
    想知道为什么不能加索引,把理由说出来,我这好早做准备。。
    onepunch
        23
    onepunch  
       2019-08-28 17:50:50 +08:00
    @IamUNICODE 加索引会锁表,数据越多时间越长,生产环境不允许停机那么长时间,如果是自己的电脑那无所谓了
    hhhzccc
        24
    hhhzccc  
       2019-08-28 18:10:04 +08:00
    再加一张临时中间表,
    nthhdy
        25
    nthhdy  
       2019-08-28 18:22:15 +08:00
    为啥不能加索引
    arthas2234
        26
    arthas2234  
       2019-08-28 18:32:12 +08:00
    一般这种大表,不会涉及修改数据的话,做归档处理。你现在加也不迟,先实现个功能拆表,后面再实现需求
    jowenzzzzz
        27
    jowenzzzzz  
       2019-08-28 18:33:42 +08:00 via Android
    看不懂这个代码,同一时间求最大值,没 group by,同一时间是天?同一时间是否有多条记录?
    luozic
        28
    luozic  
       2019-08-28 19:42:06 +08:00
    mysql 还是别的,mysql 拆表,别的 oracle postgresql 倒是有不少骚操作。
    jzmws
        29
    jzmws  
       2019-08-28 20:44:31 +08:00
    4000w 的数据不多呀 ! 增加硬件陪着
    lilongn1
        30
    lilongn1  
       2019-08-28 21:09:21 +08:00 via iPhone
    Select max(sum(金额) )
    from user
    where 用户 in (‘ a ’,’ b ’)
    group by 日期
    reus
        31
    reus  
       2019-08-28 22:13:50 +08:00
    @onepunch 建从库,加了索引,再切换主从……
    Breadykid
        32
    Breadykid  
    OP
       2019-08-29 09:21:26 +08:00
    @shyrock 生产环境不让随便加索引 [无话语权的小兵开发的功能不该涉及索引
    Breadykid
        33
    Breadykid  
    OP
       2019-08-29 09:23:03 +08:00
    @jziwenchen 你这个和我的有什么本质的区别吗?
    Breadykid
        34
    Breadykid  
    OP
       2019-08-29 09:24:46 +08:00
    @Aresxue 产品希望这个查询接口执行时间不超过 3s,无论用一条 sql 还是代码里操作
    Breadykid
        35
    Breadykid  
    OP
       2019-08-29 09:27:32 +08:00
    @shadow88sky 木有 es,哭哭
    Breadykid
        36
    Breadykid  
    OP
       2019-08-29 09:28:06 +08:00
    @1762628386 梦做得不错
    Breadykid
        37
    Breadykid  
    OP
       2019-08-29 09:31:54 +08:00
    @luozic 用的 mysql,不过我很喜欢 pg 的哇,想问 pg 有什么骚操作哇
    aliipay
        38
    aliipay  
       2019-08-29 10:05:23 +08:00
    每日计算前一天加和最大值的定时任务
    ---------------------
    是所有 ab 用户组合都事先算好?
    IamUNICODE
        39
    IamUNICODE  
       2019-08-29 10:08:04 +08:00
    @onepunch 我懂了,原来是这原因,那我得先做好准备了。。
    hjq98765
        40
    hjq98765  
       2019-08-29 10:16:48 +08:00
    在做 a 表和 b 表的子查询的时候,应该是各做了一次用户表的全表扫描,如果以后只看 a 和 b 两个用户的话,可以先生成一个临时表 where 用户名 in ('a','b'),然后再在这个 ab 表上分别查 a 和 b,可以少一次用户表的全表扫描
    netnr
        41
    netnr  
       2019-08-29 10:28:48 +08:00
    给出一个彩票方案:两张表分别排序取前 100 条最大值,在比较时间,有相同的,恭喜你中奖了,机率很渺茫
    Aresxue
        42
    Aresxue  
       2019-08-29 10:48:07 +08:00
    @Breadykid 那二楼方案是最佳的,先把 a 和 b 的数据从数据库里取出来,然后在程序里做计算,计算量大的话大可以优化,应该能控制在比较短的时间,这个方案的可行性主要看你从数据库里取 a 和 b 的数据要多久,要是这一步都超过 3s 那就没得玩。
    PS:合理的方案还是加索引,不让加索引是因为加索引的时候会锁表,会影响表的正常使用(涉及到该表的业务全都没无法使用),时间过长的情况下可能导致更严重的问题,整个数据库崩了都有可能(几率不大),加了索引后还要清理内存碎片对表进行分析优化,不然 CBO 可能不会及时得知变更,计算执行计划的成本的时候出现失误。和 DBA 商量商量趁哪天服务升级或夜深人静了服务没什么人使用的时候把索引给加上吧。
    LuciferGo
        43
    LuciferGo  
       2019-08-29 15:14:05 +08:00
    每天抽对应日期的 AB 用户数据到中间表再做处理,相对直接查更快。这种大表没索引最终要么归档 要么还是要停一下加索引
    luozic
        44
    luozic  
       2019-08-29 15:14:47 +08:00
    视图里面可以得到汇总金额,后面就用代码算,计算机结果塞进去临时表来存。
    jziwenchen
        45
    jziwenchen  
       2019-08-29 16:05:26 +08:00
    @Breadykid

    你用 explain 工具可以看到啊 你的 SQL 用到临时表 , join 就没有啊; 明显会快;并且你单表查询都要 46s 怎么优化 SQL 都慢.
    Breadykid
        46
    Breadykid  
    OP
       2019-08-30 17:41:05 +08:00
    @aliipay 是的哇
    aliipay
        47
    aliipay  
       2019-08-30 17:55:23 +08:00
    @netnr 有相同的日期也不一定和是最大
    netnr
        48
    netnr  
       2019-08-30 18:34:34 +08:00
    @aliipay 是的,考虑不周
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   3091 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 41ms · UTC 12:51 · PVG 20:51 · LAX 05:51 · JFK 08:51
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.