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

千万级单表 sql 查询问题

  •  
  •   Breadykid · 48 天前 · 3310 次点击
    这是一个创建于 48 天前的主题,其中的信息可能已经有所发展或是发生改变。

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

    ----

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

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

    https://cn.aliyun.com/product/ads
        20
    ylsc633   48 天前
    粗略看了下 你这是不是可以改成

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

    你用 explain 工具可以看到啊 你的 SQL 用到临时表 , join 就没有啊; 明显会快;并且你单表查询都要 46s 怎么优化 SQL 都慢.
        46
    Breadykid   46 天前
    @aliipay 是的哇
        47
    aliipay   46 天前
    @netnr 有相同的日期也不一定和是最大
        48
    netnr   46 天前
    @aliipay 是的,考虑不周
    关于   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   970 人在线   最高记录 5043   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.3 · 25ms · UTC 21:20 · PVG 05:20 · LAX 14:20 · JFK 17:20
    ♥ Do have faith in what you're doing.