mysql - 搜索最佳解决方案以使其( select * from table order by rand() limit 40 )运行得更快

Question

select * from table order by rand() limit 40

正如我们所知，这按预期工作，但花费的时间太长。所以我需要让它运行得更快。

我得到了一些解决方案，例如:

SELECT t1.* FROM talbe AS t1 JOIN
    (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM table)-(SELECT MIN(id) FROM table))+(SELECT MIN(id) FROM table)) AS id) AS t2 
WHERE t1.id >= t2.id AND t1.type=1 
ORDER BY t1.id 
LIMIT 40

但它并不完全有效。有时无法获取 40 行记录。

Answer 1

你明白你的第二个版本在做什么吗？

它最多选择 40 条 id 大于或等于 MIN(id) 和 MAX(id) 之间的随机数的记录>。从某种意义上说，这不是选择 40 条随机记录:它是选择一个 随机记录和后跟 id 的 39 条记录。可以说它正在选择一组随机记录。显然，如果随机选择的记录与最大 id 太接近，则后面不会有 39。

此外，每个集群的概率分布在 id 中——因此该列中的任何不均匀性(例如，记录已被删除的间隙)都会导致结果不均匀。在极端情况下，假设一个人有 80 条 id 1–40 和 1,000,001–1,000,040 的记录。前四十个中的任何一个被选中的概率比后四十个小一百万倍(因为任何随机值 >40，例如 41 或 2,753 或 999,999 将仅返回从 1,000,000 开始的记录)。

因此，如果牢记所有这些，尽管存在缺陷，您还是很乐意继续使用第二个版本，而不是在 MIN(id ) 和 MAX(id) 可以在 MIN(id) 和第 40 个 id 之间选择 id > 从最大值开始。

SELECT   t1.*
FROM     `table` JOIN (
           SELECT min.id + ROUND(RAND() * (max.id - min.id)) AS id
           FROM   (SELECT id FROM `table` ORDER BY id DESC LIMIT 39, 1) max,
                  (SELECT id FROM `table` ORDER BY id ASC  LIMIT  0, 1) min
         ) AS pivot ON pivot.id <= table.id
WHERE    table.type = 1
ORDER BY table.id
LIMIT    40

但是，如果这种方法的上述缺陷是不可取的，那么 @Quassnoi的文章“Selecting random rows”(@Kay Nelson linked above)提供了一个更好的解决方案，该解决方案基于将 RAND() 与下一条记录被选中的概率进行比较。我建议添加一个 LIMIT 子句以确保 MySQL 在找到所需数量的随机记录后停止其表扫描:

SELECT  table.*, @lim := @lim - 1
FROM    `table`, (SELECT @cnt := COUNT(*) + 1, @lim := 40 FROM `table`) init
WHERE   RAND() < @lim / (@cnt := @cnt - 1)
LIMIT   40