mysql - 聚合行并创建主键

Question

我需要修复缺少主键的问题，我想获得一些想法最好的方法。

我们在几个生产数据库上有一个程序，我们每隔一段时间插入新数据小时进入汇总表。五列是关键，其他列是值是不同的总和。我们使用 ON DUPLICATE KEY UPDATE 添加到每次插入时求和。插入语句看起来像这样:

INSERT INTO sums (key1,key2,key3,key4,key5,sum1,sum2) VALUES (..., 13, 42, 3)
ON DUPLICATE KEY UPDATE sum1=VALUES(sum1)+sum1,sum2=VALUES(sum2)+sum2

问题是创建表时未设置主键(不是我的错:)。现在我需要聚合具有相同键的行，然后添加首要的关键。由于缺少主键，表已增长到大约一些系统上有 700 000 000 行，所以我需要一些有效的方法来做到这一点。

我想做到这一点，而不必每次都推迟添加新行小时。因为系统现在的工作方式是保存插入并做他们以后将需要大量的工作。

我执行的每个操作都不能将表锁定超过 45 分钟左右。我希望创建实际主键的时间比我管理的要短先合并一些行。也许为少数几个创建索引会更快首先是键列，以便我有一个用于聚合行操作的索引？

我也不确定聚合行的最佳方式是什么。任何好将不胜感激。

Answer 1

首先，将您现有的总和表重命名为 sums_old 并创建新的、正确的总和表，这样您就可以保持每小时处理一次。但是请注意，除非您应用聚合数据，否则总和表中的数据将不正确。

现在，应用以下查询来更新表:

INSERT INTO sums (key1, key2, key3, key4, key5, sum1, sum2)
SELECT key1, key2, key3, key4, key5, sum1, sum2 FROM sums_old
ON DUPLICATE KEY UPDATE sum1 = VALUES(sum1) + sum1, sum2 = VALUES(sum2) + sum2

但是等等，因为你正在使用 MyISAM，并且你不希望表被锁定太久，用 LIMIT 分块进行:

INSERT INTO sums (key1, key2, key3, key4, key5, sum1, sum2)
SELECT key1, key2, key3, key4, key5, sum1, sum2 FROM sums_old
ORDER BY some_index
LIMIT 0, 250000
ON DUPLICATE KEY UPDATE sum1 = VALUES(sum1) + sum1, sum2 = VALUES(sum2) + sum2

INSERT INTO sums (key1, key2, key3, key4, key5, sum1, sum2)
SELECT key1, key2, key3, key4, key5, sum1, sum2 FROM sums_old
ORDER BY some_index
LIMIT 250000, 250000
ON DUPLICATE KEY UPDATE sum1 = VALUES(sum1) + sum1, sum2 = VALUES(sum2) + sum2

INSERT INTO sums (key1, key2, key3, key4, key5, sum1, sum2)
SELECT key1, key2, key3, key4, key5, sum1, sum2 FROM sums_old
ORDER BY some_index
LIMIT 500000, 250000
ON DUPLICATE KEY UPDATE sum1 = VALUES(sum1) + sum1, sum2 = VALUES(sum2) + sum2

...

您需要按某个键进行排序才能分块进行，因此如果您没有，则需要将其添加到 sums_old 表中。

找出什么是好的 block 大小。