mysql - 有效地将许多相似的记录插入MySQL

Question

我试图在一个“事务”中将许多(从几十到数百)条相似的记录插入到一​​个表中。这些记录仅在主键和一个字段上有所不同。该表是一个 MEMORY 表，插入的记录经常被 UPDATED 和经常被 DELETED，但有些可以在表中停留一天。表结构:

id     BIGINT
sid    CHAR
pid    INT
mask   INT
param1 INT
param2 INT
...    INT
paramN INT

PRIMARY (id, sid),
KEY [BTREE] (sid), (param1), (param2), (...), (paramN)

现在插入是通过准备好的语句进行的:

INSERT INTO object_subscriptions (id, sid, pid, mask, ...)
    VALUES (:id, :sid, :pid, :mask, ...)
    ON DUPLICATE KEY UPDATE mask = mask | VALUES(mask)

示例记录:

13194140000467 | 'fBF8OfQlAjSS8uXsAAzx' | 7 | 22 |    3 | 0 | 188 | 5123 | 1
18392199238192 | 'fBF8OfQlAjSS8uXsAAzx' | 7 | 22 |    3 | 0 | 188 | 5123 | 1
26342478965721 | 'fBF8OfQlAjSS8uXsAAzx' | 7 | 22 |    3 | 0 | 188 | 5123 | 1
64322445645318 | 'fBF8OfQlAjSS8uXsAAzx' | 7 | 22 |    3 | 0 | 188 | 5123 | 1
13194140000467 | 'n2pFykYGNnsp-JfCAeJO' | 5 | 97 | 1293 | 0 | 188 | 5123 | 0
18392199238192 | 'n2pFykYGNnsp-JfCAeJO' | 5 | 97 | 1293 | 0 | 188 | 5123 | 0
26342478965721 | 'n2pFykYGNnsp-JfCAeJO' | 5 | 97 | 1293 | 0 | 188 | 5123 | 0
64322445645318 | 'n2pFykYGNnsp-JfCAeJO' | 5 | 97 | 1293 | 0 | 188 | 5123 | 0

这在每批处理数十条记录时效果很好。每批数百个，它会大大减慢速度。可以将性能提高一倍或三倍的明显优化是在一条语句中设置多个 VALUE，但这仍然会留下发送 N params 以及 sid 的开销和 pid 与每条记录，即使只有 id 和 mask 在一个批处理中不同。

我正在考虑在准备好的语句中包含这些“固定”值，但是这些“参数”是根据用户输入生成的，不受信任。由于数据更改的频率和需要执行的索引查找次数，我有点被 MySQL 和 MEMORY 困住了。否则我会规范化数据或将所有内容移动到键值存储。

Answer 1

这是我能想到的避免重复共同元素的唯一方法。不幸的是，它可能过于冗长以至于它并不比您要替换的更好。

INSERT INTO object_subscriptions (id, sid, pid, mask, ...)
SELECT t1.id, t2.sid, t2.pid, t1.mask, ...
FROM (SELECT :id1 id, :mask1 mask
      UNION
      SELECT :id2, :mask2
      UNION
      SELECT :id3, :mask3
      ...
    ) t1
JOIN (SELECT :sid sid, :pid pid, ...) t2

这只做一批。要在单个查询中执行多个批处理，您可以将 UNION 添加到顶级 SELECT。

INSERT INTO object_subscriptions (id, sid, pid, mask, ...)
SELECT t1.id, t2.sid, t2.pid, t1.mask, ...
FROM (SELECT :batch1_id1 id, :batch1_mask1 mask
      UNION
      SELECT :batch1_id2, :batch1_mask2
      UNION
      SELECT :batch1_id3, :batch1_mask3
      ...
    ) t1
JOIN (SELECT :batch1_sid sid, :batch1_pid pid, ...) t2
UNION
SELECT t1.id, t2.sid, t2.pid, t1.mask, ...
FROM (SELECT :batch2_id1 id, :batch2_mask1 mask
      UNION
      SELECT :batch2_id2, :batch2_mask2
      UNION
      SELECT :batch2_id3, :batch2_mask3
      ...
    ) t1
JOIN (SELECT :batch2_sid sid, :batch2_pid pid, ...) t2
UNION
...