ruby-on-rails - 如何对数百万行执行这种计算量大的查询

Question

我正在使用幂等性来确保同一条消息不会多次保存到数据库中。为确保这一点，我需要 3 列的组合。我没有在可能为空的 3 列上建立索引，而是进行计算和摘要并将其存储在索引且唯一的列上。

我现在需要将此计算应用于所有以前的消息，其中有数百万行。

消息.rb:

 def set_unique_identifier
    part_one   = mm_id || SecureRandom.uuid
    part_two   = c_id
    part_three = s_id
    self.unique_identifier = Digest::SHA1.hexdigest("#{part_one}-#{part_two}-#{part_three}")
  end

然后我有一个像这样的迁移:

Message.find_each.with_index do |message, index|
  message.set_unique_identifier
  message.save
  puts "SETTING UNIQUE IDENTIFIER FOR #{index}" if index % 1000 == 0
end

但是，很明显，这将花费很长时间来计算。是否有使用原始 SQL 执行此操作的更快方法？

Answer 1

好吧，无论具有一百万行的解决方案是什么，您都会涉及到一定程度的计算。您可以做的是减少数据的移动。 Postgresql 的加密模块支持 SHA1 哈希和 UUID 生成。

使用那些您可以使用的逻辑，将逻辑保留在服务器中并将其作为单个 SQL 语句执行，或者如果您想以 block 的形式执行，则可以使用多个语句。

UPDATE message SET unique_identifier = encode(digest(
mm_id || gen_random_uuid() || '-' || c_id || '-' || s_id
,'sha1'),'hex');

但是，您所做的实际上不会检查唯一性，因为随机成分意味着可以允许具有相同 mm_id、c_id、s_id 的两条消息。

您最好使用唯一的数据库约束。您可以在原始列上创建唯一索引。

CREATE UNIQUE INDEX ON message(mm_id,c_id,s_id);

并依靠 postgres 来处理。这是我首先要做的，在您以这种方式尝试并可以衡量性能之前，我不会担心性能问题。

另一种方法是在函数上创建索引。它将以大致相同的方式运行:

CREATE UNIQUE INDEX ON message (encode(digest(mm_id || c_id || s_id,'sha1'),'hex'));