MySQL 随机，间隔超过 40 万行

Question

我一直没有问，因为我觉得这个问题被问了很多，但仍然缺乏明确的答案:

Objects 表:超过 4000 万行填充了 UPC、EIN、ISBN 作为 obj_id 主键。差距

*Obj_Cat* 表:将对象链接到类别。栏目 | obj_id | cat_id |

问题:返回 5 个非连续随机 obj_id 的最佳方法是什么？有没有比我列出的更好的方法？

解决方案 1: SELECT objects.obj_id FROM objects left join obj_cat on objects.obj_id=obj_cat.obj_id WHERE obj_cat.cat_id=cat_id ORDER BY RAND() LIMIT 1;运行5次

• 处理大表时非常慢。

解决方案 2: SELECT obj_id FROM objects WHERE obj_id >= (SELECT FLOOR( MAX(obj_id) * RAND()) FROMobjects) LIMIT 1; 运行 5 次(不包括 obj_cat 连接以使其更易于理解)

• 如果您的行没有间隙或间隙可以忽略不计，这是最佳解决方案。非常快。

• 不适用于类别，因为编号中不可避免地会有间隙。

解决方案 3: SELECT FLOOR(RAND() * COUNT(objects.*)) ASoffsetFROM objects, obj_cat WHERE objects.obj_id=obj_cat.obj_id和 obj_cat.cat_id=cat_id； SELECT obj_id FROM objects LIMIT $offset, 1 运行 5 次

• 非常灵活。比解决方案 1 快得多。适用于间隙。但是在超过 4000 万行时，单个“LIMIT $offset, 1”仍然需要 1 分钟。

我使用了解决方案 3，但它很慢。我当前的解决方案是移动使用 Solr randomsortfield，因为它很容易在 fq 中指定我的类别。

Solr 解决方案: ?q=*&fl=obj_id&fq=cat:(cat_id)&sort=random_* desc&rows=5

• 相当快，每个类别大约需要 45 秒，但会返回 5 个非连续的结果。

人们在处理大型数据集时是否发现了更好的方法？我知道这似乎是一个重复的问题，但我想我会贡献我对 40M+ 表的经验。

Answer 1

对于这么大的数据集，您无法进行这样的即时计算。您需要利用时间内存权衡。在 obj_cat 表中创建一个新的无符号整数索引列，其宽度大于最大行数，并用随机数填充每一行。这样就可以简单的生成一个随机数，直接选择最接近的匹配五次。这比尝试使用 ORDER BY RAND() 快几个数量级。