主页 / dba / 问题 / 249718
Accepted
Joe
Asked: 2019-09-27 11:15:11 +0800 CST

如何在没有过多重复检查的情况下将值插入高度规范化的数据库？

  • 772

情况：

我们项目的负责人决定使用高度规范化的数据库作为我们的数据库设计。这意味着从字面上看，大表的每个字段都是一个 ID，而不是实际值。他的意图是没有任何形式的重复，即使在重复不会伤害的地方（人们的名字，那种东西）。

但是这确实会导致一个问题：插入新数据时，我们需要检查每个子表以查看该值是否存在（第一次查询），如果不存在则插入它（第二次查询）或检索 ID，执行这实际上适用于主表中的每一列（所以 30 次左右），然后我们可以创建我们真正想要获取的对象。（创建对象大约需要 60 次数据库命中！）。

我们在spring工作，所以我们使用jdbcTemplate来实际建立一个数据库连接，而且每次查询都是昂贵的。当我们插入或更新数以千计的新记录时，这实际上会大大降低数据库的速度。

整个过程对我来说完全是肮脏和错误的，因此我想问：有没有更好的方法？是否可以让子查询插入一个值（如果不存在，或者不存在），并在两种情况下返回实际键，立即用于在主表中设置 ID？是否有一个优雅的解决方案可以减少查询数量而不引入太多复杂的 SQL（为了团队成员）？

mysql mariadb

2 个回答

  1. Best Answer

    有点想...

    正式地，您有类似于此简化结构的内容：

    CREATE TABLE slave1 (id PK, value UNIQUE);
CREATE TABLE slave2 (id PK, value UNIQUE);
CREATE TABLE main (id PK, id_slave1 FK, id_slave2 FK);

    当需要插入 2 条记录 (id_1, val_1_1, val_1_2) 和 (id_2, val_2_1, val_2_2) 时，执行：

    CREATE TEMPORARY TABLE temp (val_slave1, val_slave2) [ENGINE=Memory];

INSERT INTO temp (val_slave1, val_slave2)
VALUES (val_1_1, val_1_2),
       (val_2_1, val_2_2);

INSERT IGNORE INTO slave1 (value)
SELECT DISTINCT val_slave1
FROM temp;

INSERT IGNORE INTO slave2 (value)
SELECT DISTINCT val_slave2
FROM temp;

INSERT INTO main (id_slave1, id_slave2)
SELECT slave1.id, slave2.id
FROM temp
JOIN slave1 ON temp.val_slave1 = slave1.value
JOIN slave2 ON temp.val_slave2 = slave2.value;

    当插入的值数量很少时，引擎temp可能是内存，如果插入的数据数组很大，则可能是 InnoDB 或其他东西。

    INSERT IGNORE 在 UNIQUE 索引字段上运行得足够快。它保证从表中没有重复，并且在插入主表时必须插入的值将存在于从表中。

    最终查询也必须很快 - 特别是当临时表字段也被索引时。

    如果您只需要插入一条记录，那么您当然可以不使用 table temp...但我认为统一比稍微简化更安全。

    当然，这可以优化。例如，可能所有的插入都加入到一个存储过程中，不需要在“60 次数据库命中”中，一个 CALL 就足够了。最后，您必须只执行 3 个独立于要插入的记录数的查询。并且其中只有一个（插入到 temptable 中）可能很大（甚至可以分为很多插入）。

    • 3

  2. 如果您可以批量处理数据，我可以为您提供一种技术，该技术涉及每批 2 个查询——一个用于为批次插入任何新名称，一个用于查找所有 id。并且它不会像REPLACE, INSERT IGNORE, IODKU 等那样浪费 id。

    1. 构建一个包含所有非规范化数据的临时表。它有用于 id 的空列。
    2. 为需要规范化的每一列运行 2 个查询。（在您的情况下为 2*30 个查询）。
    3. INSERT .. SELECT ..将数据移动到真实表中。
    4. 截断或删除临时表。

    见批量标准化

    当我需要将大量数据（可能来自许多客户）放入表中并且需要一些（不是全部）规范化时，这是设计（并在几年前完善）的。

    起初我有INSERT IGNORE，但很快意识到我可能会用完 auto_increment id。我不愿意使用 8-byte BIGINTs。毕竟，标准化它的一个目的是为了节省空间。

    • 0

相关问题