ruby-on-rails - 如何使用有偏概率有效地选择随机行。使用 Rails 2.3 和 PostgreSQL 8 分发？

Question

我写过一些简单的 Rails 应用程序，通过 ActiveRecord 抽象访问数据库，所以恐怕我对 PostgreSQL 引擎的内部工作了解不多。但是，我正在编写一个需要支持 100000 多行动态更新内容的 Rails 应用程序，我想知道我是否有效地使用了随机函数:

Database migration schema setting:
t.float: attribute1
t.integer: ticks

add_index :mytable, :attribute1
add_index :mytable, :ticks

基本上，我想要以下随机函数分布:

a) 在属性 1 中具有前 10% 值的行 = 30% 的机会被选中

b) 中间 60%(在属性 1 中)行 = 50% 的机会被选中

c) 低于 100 个刻度的最低 30%(在属性 1 中)= 15% 的机会被选中，

d) 并且对于那些具有最低 30% 属性 1 且具有超过 X(在此问题中使用 X = 100)的人，ticks = 5% 的机会被选中。

目前我有以下代码:

@rand = rand()
if @rand>0.7
    @randRowNum = Integer(rand(0.1) * Mytable.count )
    @row = Mytable.find(:first, :offset =>@randRowNum , :order => '"attribute1" DESC')
else
    if @rand>0.2
        @randRowNum = Integer((rand(0.6)+0.1) * Mytable.count)
        @row= Mytable.find(:first, :offset =>@randRowNum , :order => '"attribute1" DESC')
    else
        @row= Mytable.find(:first, :offset =>Integer(0.7 * Mytable.count), :order => '"attribute1" DESC')
        if !(@row.nil?) 
            if (@rand >0.05)
                @row= Mytable.find(:first, :order => 'RANDOM()', :conditions => ['"attribute1" <= '"#{@row.attribute1}", '"ticks" < 100' ] )
            else
                @row= Mytable.find(:first, :order => 'RANDOM()', :conditions => ['"attribute1" <= '"#{@row.attribute1}", '"ticks" >= 100' ] )
            end
        end
    end
end

1) 我想做的一件事是避免使用 :order => 'RANDOM()'，因为根据我的研究，似乎每次调用它时，都涉及 SQL 引擎首先扫描所有行，为它们分配一个随机值。因此使用 @randRowNum = Integer(rand(0.1) * Mytable.count ) 并由 @randRowNum 偏移。对于 a) 和 b)。我真的提高了效率吗？有没有更好的办法？

2) 我是否应该对 c) 和 d) 执行与 1) 相同的操作，并且是通过使用 :conditions => ['"attribute1"<= '"#{@row.attribute1}", '"ticks">= 100' ]，我是否强制 SQL 引擎扫描所有行？除了索引之外，还有什么可以提高此调用的效率(同时尽可能少的空间/存储开销)吗？

3) Mytable 中的条目总数可能在 Mytable.count 和 Mytable.find 调用之间发生了变化。我可以将这两个调用放在一个事务中，但是仅仅为了读取操作而锁定整个表似乎太过分了(目前，如果我得到一个@row.nil，我有额外的代码可以回退到简单的随机行选择从上面的代码)。是否可以在 Rails 中的单个原子 SQL 查询中移动 .count 调用？或者它会像在 Rails 中通过事务锁定一样有效吗？

4) 我也一直在阅读 PostgreSQL 中的存储过程...但是对于这种特殊情况，是否可以提高效率，值得将代码从 Activerecording 抽象转移到存储过程？

非常感谢!

附言开发/部署环境:鲁布 1.8.7 rails 2.3.14PostgreSQL 8(在 Heroku 上)

Answer 1

你的问题似乎有点含糊，如果我的解释有误，请纠正我。

如果您没有拆分 (c) 和 (d)，我只会将 [0,1) 上的均匀随机变量转换为 [0,1) 上的有偏随机变量，然后使用它来选择您的行偏移量.

不幸的是，(c) 和 (d) 是根据“ticks”的值拆分的——不同的列。这是最难的部分，也会使查询效率大大降低。

在你从底部取出70%处的attribute1的值后，还要取出第(c)行的数量；类似 SELECT COUNT(*) FROM foo WHERE attribute1 <= partiton_30 AND ticks < 100 的东西.然后用它来找到 ticks < 100 中的偏移量或 ticks >= 100个案。 (您可能需要一个关于 (attribute1, ticks) 或其他内容的索引；最佳顺序取决于您的数据)。

如果预先知道“滴答”阈值并且不需要经常更改，您可以将其缓存在列(BOOL ticks_above_threshold 或其他)中，这使得查询很多更有效率如果你有关于 (ticks_above_threshold, attribute1) 的索引(注意反转)。当然，每次更改阈值时都需要写入每一行。

(我认为您也可以使用“物化 View ”来避免主表因额外的列而变得困惑，但我不确定效率上的差异是什么。)

使用存储过程显然可以提高一些效率。我不会太担心它，除非服务器的延迟特别高。

编辑:

回答您的其他问题:

1. 索引 (BOOL ticks_above_threshold, attribute1) 应该比 (ticks, attribute1) 工作得更好(虽然我可能顺序错了)因为它可以让你比较第一个索引列是否相等。这很重要。

   索引通常使用某种平衡树来查找有效的列表。例如，取 A4 B2 C3 D1(有序字母，数字)并查找“字母大于 B 且数字大于 2 的行数”。您能做的最好的事情就是在 B 之后开始并遍历整个表。如果您按数字、字母订购，您将获得 1D 2B 3C 4A。同样，在 2s 之后开始。

   如果你的索引是 is_greater_than_2,letter，它看起来像 false,B false,D true,A true,C。您可以向索引询问 (true,B) 的位置——在 true,A 和 true,C 之间——并计算条目数直到结束。计算两个索引位置之间的项目数很快。

   Google App Engine 的 Restrictions on Queries更进一步:

   Inequality Filters Are Allowed on One Property Only

   A query may only use inequality filters (<, <=, >=, >, !=) on one property across all of its filters.

   ...

   The query mechanism relies on all results for a query to be adjacent to one another in the index table, to avoid having to scan the entire table for results. A single index table cannot represent multiple inequality filters on multiple properties while maintaining that all results are consecutive in the table.

2. 如果你们没有其他查询受益于 ticks 上的索引, 那么是的。

   在某些情况下，索引 a 可能会更快而不是 a,b (或 b,a )如果子句包括 b几乎总是正确的，并且您正在获取行数据(不仅仅是获取 COUNT() )(即，如果 1000 <= a AND a <= 1010 匹配一百万行并且 b > 100 仅在两行中失败，那么它最终可能会更快地执行两个额外的行查找而不是使用更大的索引)。