V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
MrGoooo
V2EX  ›  问与答

关于一个 sql 优化问题

  •  
  •   MrGoooo · 2021-09-02 09:38:12 +08:00 · 1983 次点击
    这是一个创建于 939 天前的主题,其中的信息可能已经有所发展或是发生改变。

    有这样一个表,表里记录的是用户获得金币的记录表,每一条记录都详细的说明了这一条记录的金币是怎么得到的(或者用掉的),表结构大概如下:

    id,user_id,count(得到 /消费的金币数量),change_type(得到 /消费的类型),create_time

    现在有个需求:得到用户每一条记录创建时的剩余金币数,

    得到的结果大概为:

    id user_id count change_type create_time remain
    1 1 10 1 2020-10-10 10
    2 1 -2 2 2020-10-10 8
    3 1 15 3 2020-10-10 23
    4 1 -14 4 2020-10-10 9

    其实就是要这个 remain 字段。

    第 1 条附言  ·  2021-09-02 11:07:20 +08:00
    建表语句:
    CREATE TABLE `coin_record` (
    `id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
    `user_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
    `count` int(11) NULL DEFAULT NULL,
    `change_type` tinyint(3) NULL DEFAULT NULL,
    `create_time` datetime(0) NULL DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
    第 2 条附言  ·  2021-09-02 18:14:31 +08:00
    感谢大家的回答,我得到了想要的结果。就是用开窗函数。
    最终结果:
    SELECT
    *,
    sum( count ) over (ORDER BY id ) AS remain
    FROM
    coin_record
    WHERE user_id = 'XXXXX'

    注意:一定要是'ORDER BY id',不能是'ORDER BY create_time',因为时间可能相同,导致结果错误。
    第 3 条附言  ·  2021-09-03 09:26:57 +08:00
    了解一下开窗函数,explain 后发现会导致 filesort,好像用不了索引,不知道为啥
    第 4 条附言  ·  2021-09-03 09:35:25 +08:00
    使用临时变量不会导致 filesort,效率应该比开窗函数高
    SELECT
    coin_record.*,
    ( @last_sum := @last_sum + count ) total
    FROM
    coin_record,
    ( SELECT @last_sum := 0 ) r
    WHERE
    user_id = '1'
    第 5 条附言  ·  2021-09-03 10:02:11 +08:00
    分页之后,临时变量的赋值流程在 limit 语句执行之后,会导致得到的结果是错误的。
    分页语句 0(得到结果不正确):
    SELECT
    coin_record.*,
    ( @last_sum := @last_sum + count ) total
    FROM
    coin_record,
    ( SELECT @last_sum := 0 ) r
    WHERE
    user_id = '1'
    LIMIT 1,2;

    但是使用开窗函数辅助一下,临时变量的赋值流程会变为在 limit 语句执行之前,会导致得到的结果是正确的。
    分页语句 1(得到结果正确):
    SELECT
    coin_record.*,
    row_number() over(), -- 这条语句很流弊,将临时变量的赋值流程提前了
    ( @last_sum := @last_sum + count ) total
    FROM
    coin_record,
    ( SELECT @last_sum := 0 ) r
    WHERE
    user_id = '1'
    LIMIT 1,2;
    21 条回复    2021-09-03 09:27:45 +08:00
    MrGoooo
        1
    MrGoooo  
    OP
       2021-09-02 09:41:16 +08:00
    | id | user_id | count | change_type | create_time | remain |
    | ---- | ----| ---- | ----| ---- | ----|
    | 1 | 1 | 10 | 1 | 2020-10-10 | 10 |
    | 2 | 1 | -2 | 2 | 2020-10-10 | 8 |
    | 3 | 1 | 15 | 3 | 2020-10-10 | 23 |
    | 4 | 1 |-14 | 4 | 2020-10-10 | 9 |
    gtchan13579
        2
    gtchan13579  
       2021-09-02 09:45:30 +08:00
    remain 这个字段我认为不应该跟这个记录一起存储吧
    何况这个字段其实不需要存储,查询一下 userid 然后统计一下 count 就出来了吧
    MrGoooo
        3
    MrGoooo  
    OP
       2021-09-02 09:46:51 +08:00
    @gtchan13579 就是现在表里没有存 remain,需要把这个字段查出来,想对这个查询过程优化
    shyrock
        4
    shyrock  
       2021-09-02 09:52:52 +08:00
    remain 要存储当时的剩余金币值?你这相当于找回历史记录啊。写个脚本对每个用户追溯一次记录呗。
    AngryPanda
        5
    AngryPanda  
       2021-09-02 09:52:57 +08:00   ❤️ 1
    SELECT id, score, (SELECT SUM(score) FROM score WHERE id <= s.id) as remain FROM `score` s
    zhaokun
        6
    zhaokun  
       2021-09-02 09:53:43 +08:00   ❤️ 1
    SQL 都没有,何来优化之说??
    sadfQED2
        7
    sadfQED2  
       2021-09-02 10:04:58 +08:00 via Android
    建表语录,select 语录你发出来啊,不然优化空气啊
    MrGoooo
        8
    MrGoooo  
    OP
       2021-09-02 11:08:08 +08:00
    @zhaokun 5 楼的就是
    MrGoooo
        9
    MrGoooo  
    OP
       2021-09-02 11:09:51 +08:00
    @sadfQED2 5 楼的就是,建表语句已补充
    MrGoooo
        10
    MrGoooo  
    OP
       2021-09-02 11:13:09 +08:00
    @AngryPanda 我就是你这么写的,不过要加上用户 id 判断,
    SELECT id, count, (SELECT SUM(count) FROM coin_record WHERE id <= s.id and user_id = '1') as remain FROM `coin_record ` s where user_id = '1'
    0o0o0o0
        11
    0o0o0o0  
       2021-09-02 11:33:27 +08:00
    sum(count) over ( order by id ) as remain
    Egfly
        12
    Egfly  
       2021-09-02 11:36:56 +08:00
    SELECT
    s.id,
    SUM(s2.count) as remain
    FROM
    coin_record as s
    INNER JOIN coin_record as s2 ON s.user_id = s2.user_id AND s2.id <= s.id
    WHERE
    s.user_id = 1
    GROUP BY s.id
    Egfly
        13
    Egfly  
       2021-09-02 11:39:45 +08:00   ❤️ 1
    我觉得还是加一个 remain 字段,然后写个脚本维护一下历史数据比较好。适当的冗余一些字段,可以省很多事情
    c6h6benzene
        14
    c6h6benzene  
       2021-09-02 12:58:30 +08:00 via iPhone
    就是按行算 sum 吧。rows unbounded preceding
    IvanLi127
        15
    IvanLi127  
       2021-09-02 13:46:57 +08:00
    如果能把余额也记录在表里的话,每次变化时查出上次余额后计算成当前余额一起写入,应该就不需要想怎么优化了。毕竟数据写入后不太可能变化,挺适合冗余的。
    justfindu
        16
    justfindu  
       2021-09-02 13:54:11 +08:00
    你这个得每行进行重新计算. 一条 sql 看起来是没啥戏的
    dodosh
        17
    dodosh  
       2021-09-02 14:14:13 +08:00
    开窗函数。
    Jface
        18
    Jface  
       2021-09-02 14:17:54 +08:00
    感觉开窗函数可以搞定。sum(count ) over(partition by user_id order by create_time ) as remain
    sakasaka
        19
    sakasaka  
       2021-09-02 14:19:14 +08:00
    可取方案不是优化 SQL,而是优化存储结构,这样的 SQL 写起来蠢蠢的
    brookepe
        20
    brookepe  
       2021-09-02 16:07:41 +08:00
    ```
    select *, (uremain - IFNULL(sremain, 0)) as remain from (
    select
    coin_record.*,
    users.remain as uremain,
    (select sum(count) from coin_record cr1 where cr1.id > coin_record.id) sremain
    from coin_record left join users on users.id = coin_record.user_id
    ) as coin_record

    ```
    MrGoooo
        21
    MrGoooo  
    OP
       2021-09-03 09:27:45 +08:00
    了解一下开窗函数,explain 后发现会导致 filesort,好像用不了索引,不知道为啥
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5324 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 08:14 · PVG 16:14 · LAX 01:14 · JFK 04:14
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.