我试图解决一个性能问题,我们正在运行一个where IN子句对大量的非顺序id。根据这本书和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语句,它可以正常工作,但是临时表仍然存在。
鉴于此,我的问题是:
我该如何"推迟"这个表的删除,而不是将表名弹出到后台工作线程中以便稍后删除?
或
不删除表并假设连接池将获取连接,从而最终删除表是否安全?
因此,经过相当多的研究,我回答了自己的问题:
-
没有办法推迟表的删除,但是,我现在可以使用
ActiveRecord::Relation#load
方法强制关系执行查询。 -
在我们的应用程序中(我相信还有很多其他的应用程序),我们缓存连接供以后使用,很少回收它们,所以不删除表将是一个非常明显的内存泄漏
我最终在Util
类中编码此方法,而不是AR基础:
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缓存,我尝试的方法在多次迭代中速度要慢得多。可能还有其他的基准我可以尝试,但目前看来,这个优化是不值得的。
好吧,¯_(ツ)_/¯