设计模式 - 用于在 MySQL 中获取随机行的无间隙辅助 ID

Question

在我从事的几个项目中，我遇到了从大型（>1M行）表中获取随机行的需要。对于这么大的表，ORDER BY rand() LIMIT 1是没有选择的，因为它会很快使数据库瘫痪。

通常的解决方案是在 MIN(id) 和 MAX(id) 之间生成一个随机数，然后直接选择该行。但是，如果 id 序列中存在很大的间隙，这将需要大量重新滚动或使用WHERE id >= :myrandomnumber这将导致成功大间隙的行获得比平均水平多得多的命中率。

我一直在考虑通过创建一个仅用于随机化目的的新索引列来解决这个问题，比如说id2.此列始终是表中 1 和行数之间的无间隙序列。

问题：保持此序列无间隙的最佳方法是什么？

想到的第一个解决方案是创建一个帮助表recycled_ids，其中包含列 tablename 和 id2 .每当从tablename中删除一行时，该行的id2都会插入到recycled_ids。插入新行时，将从recycled_ids中选择id2，如果没有可用行，则创建一个新行。有没有更简单的方法？

奖励问题：是否有ORM或框架已经这样做了，或者有其他有效的随机行选择？这是一个现有的模式，它有名字吗？

---

更新：我为此编写了一个快速基准测试，并针对具有 125,000 行和 30,000 个间隙的表运行它。结果非常有希望：

Fetch a random row 100 times using id2: 0.0234689712524 seconds
Fetch a random row 100 times using ORDER BY rand() LIMIT 1: 54.992347002 seconds

插入测试数据时，我每插入五行就随机删除一行。序列始终保持无间隙。

for($i=1; $i<=$amount; $i++) {
    insert_row();
    if($i % 5 == 0)
        delete_random_row();
}

在我的低端虚拟服务器上，再次运行该循环需要 $amount = 10000 9 秒。这是每行 0.009 秒，它包括每五次迭代删除一行。随着表的增长，它确实会变慢，但获取随机行不会。

我原来的问题仍然适用。

Answer 1

这是我

的做法 -

1. 从表中选择 MAX（id）
2. 在 PHP（或您使用的任何语言）中，生成一个介于 1 和 MAX（id） 之间的随机整数
3. SELECT * FROM table WHERE id >= $random ORDER BY id ASC LIMIT 1
4. 如果 3 不返回任何内容，则SELECT * FROM table WHERE id < $random ORDER BY id DESC LIMIT 1

避免运行任何速度非常慢的查询。它还避免了额外的列，保持无间隙，确实是一项令人讨厌的工作！

Answer 2

排名我会

说。

SET @rank:= 1;  
SELECT * FROM
  (
  SELECT @rank:= @rank + 1 as rank, * FROM table1  
  ) s
WHERE s.rank = $random;