我有一个数据库表,里面装满了大约 ~30k 条记录。
我想一次随机选择一条记录(当用户要求时),从表中删除该记录,然后将其插入另一个表中。
我听说/发现做ORDER BY RAND()
可能很慢。所以我正在使用这个算法(伪代码):
lowest = getLowestId(); //get lowest primary key id from table
highest = getHighestId(); //get highest primary key id from table
do
{
id = rand(lowest, highest); //get random number between a range of lowest id and highest id
idExists = checkIfRandomIdExists( id );
}
while (! idExists);
row = getRow (id);
process(row);
delete(id);
现在,有 30k 条记录,我似乎很快就得到了随机 ID。但是,随着表大小减小到 15k、10k、5k、100 等(可能是几个月),我担心这可能会开始变慢。
我是否可以执行任何操作以使此方法更有效,或者是否有行计数,此时我应该开始执行ORDER BY RAND()
而不是此方法?(例如,当剩下 5k 行时,开始按 RAND() 排序?
您可以使用该方法获取一个随机 ID,但与其检查它是否存在,不如尝试获取最接近的 ID?
SELECT * FROM table WHERE id >= $randomId ORDER BY id LIMIT 0,1
然后,如果失败了,请选择较低的。
一种方法可能是确定记录数并按记录选择:
select floor(count(*) * rand()) from thetable;
在限制中使用生成的记录编号(例如,chosenrec
):
select * from thetable limit chosenrec, 1;
我可能会推荐一个费舍尔-耶茨洗牌,而不是在一个单独的表格中。 要生成此表,请创建一个表,如下所示:
CREATE TABLE Shuffle
(
SequentialId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
OtherTableId INT NOT NULL
)
值得注意的是,不要打扰外键约束。 例如,在 SQL Server 中,我会说添加带有 ON DELETE CASCADE
的外键约束;如果你有一个在MySQL中可行的存储引擎,那就去吧。
现在,用您选择的语言:
- 获取另一个表中所有 ID 的数组(如注释中@Truth建议的那样)。
- 使用费舍尔-耶茨(需要线性时间)洗牌这些 ID。
- 按顺序将它们插入到
Shuffle
表中。
现在,您有一个随机顺序,因此您只需INNER JOIN
到Shuffle
表,然后ORDER BY Shuffle.SequentialId
查找第一条记录。 如果您无法执行ON DELETE CASCADE
,则可以手动从Shuffle
中删除记录。