关于一个 sql 优化问题

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 1504 天前的主题，其中的信息可能已经有所发展或是发生改变。

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

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;

2020-10-10

金币

user_id

remain

21 条回复 2021-09-03 09:27:45 +08:00

MrGoooo

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

2021-09-02 09:45:30 +08:00

remain 这个字段我认为不应该跟这个记录一起存储吧
何况这个字段其实不需要存储，查询一下 userid 然后统计一下 count 就出来了吧

MrGoooo

2021-09-02 09:46:51 +08:00

@gtchan13579 就是现在表里没有存 remain,需要把这个字段查出来,想对这个查询过程优化

shyrock

2021-09-02 09:52:52 +08:00

remain 要存储当时的剩余金币值？你这相当于找回历史记录啊。写个脚本对每个用户追溯一次记录呗。

AngryPanda

2021-09-02 09:52:57 +08:00

SELECT id, score, (SELECT SUM(score) FROM score WHERE id <= s.id) as remain FROM `score` s

zhaokun

2021-09-02 09:53:43 +08:00

SQL 都没有，何来优化之说？？

sadfQED2

2021-09-02 10:04:58 +08:00 via Android

建表语录，select 语录你发出来啊，不然优化空气啊

MrGoooo

2021-09-02 11:08:08 +08:00

@zhaokun 5 楼的就是

MrGoooo

2021-09-02 11:09:51 +08:00

@sadfQED2 5 楼的就是,建表语句已补充

MrGoooo

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

2021-09-02 11:33:27 +08:00

sum(count) over ( order by id ) as remain

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

2021-09-02 11:39:45 +08:00

我觉得还是加一个 remain 字段，然后写个脚本维护一下历史数据比较好。适当的冗余一些字段，可以省很多事情

c6h6benzene

2021-09-02 12:58:30 +08:00 via iPhone

就是按行算 sum 吧。rows unbounded preceding

IvanLi127

2021-09-02 13:46:57 +08:00

如果能把余额也记录在表里的话，每次变化时查出上次余额后计算成当前余额一起写入，应该就不需要想怎么优化了。毕竟数据写入后不太可能变化，挺适合冗余的。

justfindu

2021-09-02 13:54:11 +08:00

你这个得每行进行重新计算. 一条 sql 看起来是没啥戏的

dodosh 2021-09-02 14:14:13 +08:00

开窗函数。

Jface

2021-09-02 14:17:54 +08:00

感觉开窗函数可以搞定。sum(count ) over(partition by user_id order by create_time ) as remain

sakasaka

2021-09-02 14:19:14 +08:00

可取方案不是优化 SQL，而是优化存储结构，这样的 SQL 写起来蠢蠢的

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

2021-09-03 09:27:45 +08:00

了解一下开窗函数,explain 后发现会导致 filesort,好像用不了索引,不知道为啥