mysql - 使用 ActiveRecord 延迟删除 MySQL 中的临时表

Question

我正在尝试解决一个性能问题，即我们对大量非连续 ID 运行 WHERE IN 子句。根据this和 Performance MySQL 一书，您可以通过创建一个包含相关字段的临时表并加入您关心的表来提高性能。

我在 ActiveRecord::Base 类中有以下 Rails 代码:

def self.where_in(field, ids)
  tmp_table = "tmp_table_#{SecureRandom.uuid.gsub('-', '_')}"
  begin
    # Create temporary table with one column
    connection.execute("CREATE TEMPORARY TABLE #{tmp_table} (param INT NOT NULL PRIMARY KEY) ENGINE=Memory")

    # Insert ids into the table (doesn't have to be ids)
    vals = ids.map{|i| "(#{i})"}.join(", ")
    connection.execute("INSERT INTO #{tmp_table} (param) VALUES #{vals};")

    # Return the join relation which is the same as WHERE IN (...)
    return self.joins("INNER JOIN #{tmp_table} on #{field} = #{tmp_table}.param").all
  ensure
    # Drop table after we're done...this is the problem
    connection.execute("DROP TEMPORARY TABLE IF EXISTS #{tmp_table}")
  end
end

但问题是，这会创建一个 SQL 语句，该语句依赖于我在 ensure 语句中删除的临时表的存在。如果我删除 ensure 语句，它可以正常工作，但临时表仍然存在。

鉴于此，我的问题是:

我如何“推迟”删除此表，而不是将表名弹出到后台工作程序以便稍后删除？

或

如果我不删除表并假设连接池将获取连接，从而最终删除表是否安全？

Answer 1

经过大量研究，我回答了自己的问题:

1. 没有办法延迟表的删除，但是，我现在可以使用 ActiveRecord::Relation#load 方法强制关系执行查询。

2. 在我们的应用程序(我相信还有很多其他应用程序)中，我们缓存连接供以后使用并且很少回收它们，因此不删除表将是非常明显的内存泄漏。

我最终在 Util 类而不是 AR base 中编写了这个方法:

def self.where_in(collection, field, params)
  tmp_table = "tmp_table_#{SecureRandom.uuid.gsub('-', '_')}"
  collection.connection.execute("CREATE TEMPORARY TABLE #{tmp_table} (param INT NOT NULL PRIMARY KEY) ENGINE=Memory")

  vals = params.map{|i| "(#{i})"}.join(", ")
  collection.connection.execute("INSERT INTO #{tmp_table} (param) VALUES #{vals};")

  records = collection.joins("INNER JOIN #{tmp_table} on #{field} = #{tmp_table}.param").load

  yield records if block_given?

  collection.connection.execute("DROP TEMPORARY TABLE IF EXISTS #{tmp_table}")
  return records.to_a
end

当我进行基准测试并证明我的前提是这种方法实际上会更快时，问题就出现了。我使用了以下基准代码:

Benchmark.bm do |x|
  x.report { 1000.times { Thing.where(id: refs).count } }
  x.report { 1000.times { Util.where_in(Thing, :id, refs) {|o| o.count }}}
end

结果很糟糕:

   user     system      total        real
0.940000   0.050000   0.990000 (  1.650669)
8.950000   0.260000   9.210000 ( 12.201616)

由于 MySQL 缓存，我尝试的方法在多次迭代后速度明显变慢。可能还有其他我可以尝试的基准测试，但目前看来这种优化不值得。

好吧 ¯\_(ツ)_/¯