php - (PHP) 具有排序依据和一定范围的 MySQL 随机行大表

Question

我有这张表:

person_id int(10) pk
fid bigint(20) unique
points int(6) index
birthday date index
4 FK columns int(6)
ENGINE = MyISAM

重要信息:该表包含超过 800 万行并且正在快速增长(目前每天 150 万行)

我想要的是:按点对表格排序时在一定范围内随机选择4行

我现在是怎么做的:在 PHP 中，我将某个范围随机化，假设这给我 20% 的低范围和 30% 的高范围。接下来我计算(*)表中的行数。在我确定最低行号之后:表计数/100 * 低范围。高范围也一样。在我使用 rand(lowest_row, highest_row) 计算随机行之后，它会给我一个范围内的行号。最后我选择了随机行:

SELECT * FROM `persons` WHERE points > 0 ORDER BY points desc LIMIT $random_offset, 1;

点 > 0 在查询中，因为我只想要至少有 1 个点的随机数。

上面的查询运行大约需要 1.5 秒，但由于我需要 4 行，所以需要 6 多秒，这对我来说太慢了。我认为按点排序最花时间，所以我在考虑制作表格的 VIEW，但我真的没有 View 经验，所以你怎么看？ View 是一个好的选择还是有更好的解决方案？

已添加:

我忘了说重要的是所有行都有相同的机会被选中。

谢谢，我感谢所有的帮助! :)

凯文

Answer 1

你的查询太慢了，而且会以指数方式变慢，因为在这里使用 LIMIT 会强制它进行全表排序，然后进行全表扫描，以获得结果。相反，您也应该在 PHP 端执行此操作(这种对 LIMIT 的“滥用”实际上是它是非标准 SQL 的原因，例如 MSSQL 和 Oracle 不支持它)。

首先确保在points 上有一个索引。这将使 select max(points), min(points) from persons 成为一个立即返回的查询。接下来，您可以根据这 2 个结果确定点范围，并使用 rand() 确定请求范围内的 4 个点。然后对每个结果重复:

SELECT * FROM persons WHERE points < $myValue ORDER BY points DESC LIMIT 1

因为它只需要检索一行，并且可以通过索引确定哪一行，所以这也将在毫秒级执行时间。