mysql - 最快的随机选择 WHERE 列 X 是 Y (NULL)

Question

目前我正在使用:

SELECT * 
FROM 
  table AS t1
  JOIN (
    SELECT (RAND() * (SELECT MAX(id) FROM table where column_x is null)) AS id
  ) AS t2 
WHERE 
  t1.id >= t2.id
  and column_x is null
ORDER BY t1.id ASC
LIMIT 1

这通常非常快，但是当我包含突出显示的 column_x 为 Y(空)条件时，它会变慢。

当记录的 X 列为空时，最快的随机查询解决方案是什么？

ID 是 PK，X 列是 int(4)。表包含大约一百万条记录，总大小超过 1 GB，目前每 24 小时翻一番。

column_x 已编入索引。

列 ID 可能不连续。

本例中使用的数据库引擎是InnoDB。

谢谢。

Answer 1

获得真正随机的记录可能很慢。这个事实并没有太多解决办法。如果您希望它真正随机，则查询必须加载所有相关数据才能知道它必须从哪些记录中进行选择。

幸运的是，有更快的方法可以做到这一点。它们不是完全随机的，但如果您愿意用一点纯随机性来换取速度，那么它们应该足以满足大多数用途。

考虑到这一点，获得“随机”记录的最快方法是向您的数据库添加一个额外的列，该列填充了一个随机值。也许是主键的加盐 MD5 散列？任何。在此列上添加适当的索引，然后将该列简单地添加到查询中的 ORDER BY 子句中，您将以随机顺序取回记录。

要获得单个随机记录，只需指定 LIMIT 1 并添加一个 WHERE random_field > $random_value，其中随机值将是新字段范围内的值(例如，假设一个随机数的 MD5 散列)。

当然，不利的一面是，虽然您的记录将以随机顺序排列，但它们将以相同的随机顺序卡住。我确实说过这是为了查询速度而牺牲完美。您可以通过使用新值定期更新它们来解决这个问题，但我想如果您需要保持新鲜，这对您来说可能是个问题。

另一个不利方面是，如果您有存储限制并且您的数据库已经很大，或者如果您有严格的 DBA 需要通过才能添加列，那么添加一个额外的列可能会让人难以接受。但是同样，您必须权衡一些东西；如果你想要查询速度，你需要这个额外的列。

无论如何，我希望对您有所帮助。