数据库小白想咨询下，怎么处理业务的原始数据跟处理过后的数据的连接关系？

[uil330]

比如说，我这里有些原始的数据表 A：

index	time	data	user
0	2021-01-01	0.1	a
1	2021-01-02	0.1	a
2	2021-01-03	0.1	a
3	2021-01-01	0.1	b
4	2021-01-04	0.1	b

然后根据业务需要，生成处理过后的表 B(这里的逻辑就是用户为单位计算 data 的总和)：

index	user	sum_data
0	a	0.3
1	b	0.2

然后某一天我们需要从表 B 的项目反推表 A 的项目（比如表 B 的第 0 项就是表 A 的 0 ，1 ，2 项）的时候，要怎么做比较好呢？

我现在有下面的思路：

1. 根据表 B 的生成逻辑来 SELECT 出表 A 的项目 -> 这是最直接的，但是计算量大
2. 表 B 里面加一列文字，记着使用的表 A 项目的 INDEX -> 计算量小，但是耗地方
3. 表 A 里面加一列文字，记着连接到的表 B 的 INDEX -> 这样每次生成新的表，都要动一次表 A

请问大神有什么好的思路？

最后给大家说说我们的解决方案：

就是楼下有人提到的，新建一个表，表内一行记录一个映射关系

[sujin190]

B 的项目反推表 A 的项目是啥逻辑。。
从 A 项目查询 B 项目的明细？那这不就是用 user 去 A select ，这已经是最科学的方式了吧

[imn1]

如果 data 固定，user b 推导 time 也有 6 种结果，如果 time 范围更广，结果更多
如果 data 不是固定的，或者 time 无固定范围 ……

先给出可接受结果的标准是什么

[uil330]

@imn1 这里要求能精准到表 A 的对应的每一项，所以不能给出含糊的范围。。。。

@sujin190 其实我们也开始也觉得用 SELECT 反推比较好，但是就怕表项一多起来，性能下降厉害
因为做这些表的目的是能实时反应数据的情况（我们这边是在 google 的 big query 里面导入数据，然后使用者能通过显示板看到数据的情况）

[brader]

有两种解决办法：
方法一：B 表新增一个字段记录 A 表 ID ，多个用逗号隔开。
方法二：新增多一个 C 表，C 表记录 A 、B 表的 ID 映射关系。

简单就用方法一，需要用到关系 ID 做反推的高频查询，讲究查询效率，就用方法二。

[xiaoqiao24]

用关联字段直接反查，性能问题可以加索引，多分表，再配合时间戳限制多次查询的方式来处理

[ca1123]

这决定于你要一个还是两个 schema
一个的话就改表结构, 添加一个外键
两个 shcema 的话, 就用"地址". 让程序用"地址"去找原始数据

[wxf666]

@brader 数据库新人求问，为何方法二高效？

方法一查表 B 得到 a_indexes （如"0,1,2"），然后去 join 表 A 得到各自数据，好像这个思路看起来也不慢？

SELECT a.*
FROM b JOIN a ON FIND_IN_SET(a.index, b.a_indexes)
WHERE b.index = 0

是这样实现吗？应该足够聪明，不是扫 a 全表吧？

[brader]

@wxf666 原因是多方面的，有如下原因：
1. 即使都使用上索引的情况，上面我描述的方法一的索引等级是 index ，方法二的索引等级是 ref （哪个索引等级快自行查阅资料）。
2. 方法一逗号隔开的方式，某些时候会出现索引失效的情况，下面列举了一些情况:
2-1. 查询计划器判断失误没有使用上索引。
2-2. 当你需要存的 ID 比较多的时候，你不得不扩充 a_indexes 的长度，当 a_indexes 超过 767 个字节 的时候，索引就会失效。

[wxf666]

@brader

1. 我查了下，你是说方法一会扫描表 a 整个聚集索引才得出结果？（这么傻的么。。）

有没有将 a_indexes 变成一张表再去 join 的方法（ split 后成为一张表）？我试着用了一下 json_table ，应该可行？

SELECT a.*
FROM b, JSON_TABLE(
CONCAT('[', b.a_ids, ']'),
'$[*]' COLUMNS (id INT PATH '$')
) AS ids JOIN a USING (id)
WHERE b.id = 1;


2.1 不知道😳


2.2 确实要扩充 a_ids 的长度，但不知你说的“索引失效”何意？超过 767 字节时，只是行溢出到其他页上？

而且，我读了下《 MySQL 技术内幕：InnoDB 存储引擎（第二版）》 111-116 页，大意是：

Compact 格式下，只要保证一页至少有两行数据，VARCHAR 、TEXT 、BLOB 可以不溢出到其他页（一页 16KB 情况下，大概是一行不超过 8098 字节？）。若溢出了，前 768 字节存在数据页上，其余在溢出页

[brader]

@wxf666 多实操，理论已经告诉你了，自己建一个表尝试一下，你弄个 varchar 字段，存储逗号隔开的 id ，如果你使用 utf8 编码的话，767/3 临界值大概在 255 字符，自己分别试试建 200 、500 字符长度下，使用 EXPLAIN 关键字观察你的 find_in_set 查询语句计划有何不同。

[wxf666]

@brader 我试了下，大致有以下结论：

1. 即使我用 text 类型存 "1,2,3,…"（ latin1 编码），无论是 700 / 7000 字节，find_in_set 都是 index

看来 MySQL 还不足够聪明优化 find_in_set

我还以为会生成个临时表，然后 FROM b JOIN a ON a.id IN tmp_table_of_a_ids 呢。。


2. JSON_TABLE 根据 "1,2,3,…" 生成表，再 join 表 a 是 eq_ref ，效率看来不错


3. 表 b 即使插入两行有 7000 字节的 ids 的行记录，也都在同一页（ 16KB ），不用担心查找溢出页导致的效率问题


4. 你说的方法二（新增 C 表），我试了下，插入一千万行(a_id, b_id)，磁盘占用 272.8 MB ，平均每行占用约 29 字节

看了下书，主要是每行数据额外记录了（记录头信息 5 字节 + 事务 ID 列 6 字节+ 回滚指针列 7 字节）= 20 字节，然后才是 (int, int) 的 8 字节，所以 C 表其实空间利用率很低

若是用 "1,2,3,…" 存储，即使每个 a_id 是 8 个数字+1 个逗号，一千万个记录也才 9 * (10 ^ 7) / (1 << 20) ≈ 85.8 MB

可即使是 85.8 MB ，楼主也说“计算量小，但是耗地方”

所以 C 表更不符合楼主要求


SET SESSION group_concat_max_len = 8192;
SET SESSION cte_max_recursion_depth = 2048;

CREATE TABLE `a` (
　`id` int NOT NULL AUTO_INCREMENT,
　`time` date NOT NULL,
　`data` int NOT NULL,
　`user` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
　 PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

CREATE TABLE `b` (
　`id` int NOT NULL AUTO_INCREMENT,
　`user` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
　`sum_data` int NOT NULL,
　`a_ids` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
　 PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO a (time, data, user)
WITH RECURSIVE
　 generate_series(i) AS (
　　 SELECT 0
　　 UNION ALL
　　 SELECT i+1 FROM generate_series WHERE i < 1600
　)
SELECT DATE_ADD('2021-01-01', INTERVAL i DAY), i + 1, 'a'
　 FROM generate_series
WHERE i < 199 　-- ids 长度为 2*9(1~9,) + 3*90(10~99,) + 4*100(100~199,) -1(末尾逗号) = 687 B
UNION ALL
SELECT DATE_ADD('2021-01-01', INTERVAL i DAY), i + 1, 'b'
　 FROM generate_series
WHERE i <= 1760 - 200 　-- ids 长度为 4*800(200~999,) + 5*761(1000~1760,) -1 = 7004 B
UNION ALL
SELECT DATE_ADD('2021-01-01', INTERVAL i DAY), i + 1, 'c'
　 FROM generate_series
WHERE i <= 3240 - 1761;　-- ids 长度为 5*1480(1761~3240,) -1 = 7399 B

INSERT INTO b (user, sum_data, a_ids)
SELECT user, sum(data), GROUP_CONCAT(id)
　 FROM a
GROUP BY user;

EXPLAIN
SELECT a.id
　 FROM b
　 JOIN a ON FIND_IN_SET(a.id, b.a_ids)
WHERE b.user = 'c';

EXPLAIN
SELECT a.*
　 FROM b,
　　　 JSON_TABLE(
　　　　　 CONCAT('[', b.a_ids, ']'),
　　　　　 '$[*]' COLUMNS (id INT PATH '$')
　　　 ) AS ids
　 JOIN a USING(id)
WHERE b.user = 'c';


C 表测试：

SET SESSION cte_max_recursion_depth = 1 << 31;

CREATE TABLE `c` (
　`a_id` int NOT NULL,
　`b_id` int NOT NULL,
　 PRIMARY KEY (`a_id`, `b_id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO c
WITH RECURSIVE
　 generate_series(i) AS (
　　 SELECT 1
　　 UNION ALL
　　 SELECT i+1 FROM generate_series WHERE i <= 10000000
　)
SELECT i, i
　 FROM generate_series;