V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
uil330
V2EX  ›  数据库

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

  •  
  •   uil330 · 2022-07-14 11:55:30 +08:00 · 1369 次点击
    这是一个创建于 624 天前的主题,其中的信息可能已经有所发展或是发生改变。

    比如说,我这里有些原始的数据表 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

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

    第 1 条附言  ·  2022-08-15 20:56:58 +08:00
    最后给大家说说我们的解决方案:

    就是楼下有人提到的,新建一个表,表内一行记录一个映射关系
    11 条回复    2022-07-15 22:01:25 +08:00
    sujin190
        1
    sujin190  
       2022-07-14 12:06:16 +08:00
    B 的项目反推表 A 的项目是啥逻辑。。
    从 A 项目查询 B 项目的明细?那这不就是用 user 去 A select ,这已经是最科学的方式了吧
    imn1
        2
    imn1  
       2022-07-14 12:33:07 +08:00
    如果 data 固定,user b 推导 time 也有 6 种结果,如果 time 范围更广,结果更多
    如果 data 不是固定的,或者 time 无固定范围 ……

    先给出可接受结果的标准是什么
    uil330
        3
    uil330  
    OP
       2022-07-14 12:56:19 +08:00
    @imn1 这里要求能精准到表 A 的对应的每一项,所以不能给出含糊的范围。。。。

    @sujin190 其实我们也开始也觉得用 SELECT 反推比较好,但是就怕表项一多起来,性能下降厉害
    因为做这些表的目的是能实时反应数据的情况(我们这边是在 google 的 big query 里面导入数据,然后使用者能通过显示板看到数据的情况)
    brader
        4
    brader  
       2022-07-14 14:08:57 +08:00
    有两种解决办法:
    方法一:B 表新增一个字段记录 A 表 ID ,多个用逗号隔开。
    方法二:新增多一个 C 表,C 表记录 A 、B 表的 ID 映射关系。

    简单就用方法一,需要用到关系 ID 做反推的高频查询,讲究查询效率,就用方法二。
    xiaoqiao24
        5
    xiaoqiao24  
       2022-07-14 16:34:15 +08:00
    用关联字段直接反查,性能问题可以加索引,多分表,再配合时间戳限制多次查询的方式来处理
    ca1123
        6
    ca1123  
       2022-07-14 19:21:24 +08:00
    这决定于你要一个还是两个 schema
    一个的话就改表结构, 添加一个外键
    两个 shcema 的话, 就用"地址". 让程序用"地址"去找原始数据
    wxf666
        7
    wxf666  
       2022-07-15 04:03:03 +08:00
    @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
        8
    brader  
       2022-07-15 09:51:06 +08:00
    @wxf666 原因是多方面的,有如下原因:
    1. 即使都使用上索引的情况,上面我描述的方法一的索引等级是 index ,方法二的索引等级是 ref (哪个索引等级快自行查阅资料)。
    2. 方法一逗号隔开的方式,某些时候会出现索引失效的情况,下面列举了一些情况:
    2-1. 查询计划器判断失误没有使用上索引。
    2-2. 当你需要存的 ID 比较多的时候,你不得不扩充 a_indexes 的长度,当 a_indexes 超过 767 个字节 的时候,索引就会失效。
    wxf666
        9
    wxf666  
       2022-07-15 12:52:27 +08:00
    @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
        10
    brader  
       2022-07-15 14:36:45 +08:00
    @wxf666 多实操,理论已经告诉你了,自己建一个表尝试一下,你弄个 varchar 字段,存储逗号隔开的 id ,如果你使用 utf8 编码的话,767/3 临界值大概在 255 字符,自己分别试试建 200 、500 字符长度下,使用 EXPLAIN 关键字观察你的 find_in_set 查询语句计划有何不同。
    wxf666
        11
    wxf666  
       2022-07-15 22:01:25 +08:00
    @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;
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2897 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 30ms · UTC 13:56 · PVG 21:56 · LAX 06:56 · JFK 09:56
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.