如何通过一次查询从一个30k的MySQL表中快速选择3条随机记录？
嗯，这是一个非常古老的问题，从来没有得到真正的解决。我们想要一个表中的3个随机行，大约有30k条记录。从MySQL的角度来看，这个表不是很大，但如果它代表商店的产品，它就是代表性的。例如，当一个人在网页中呈现3个随机产品时，随机选择是有用的。我们想要满足以下条件的单个SQL字符串解决方案：
- 在PHP中，由PDO或MySQLi生成的记录集必须正好有3行。
- 它们必须通过一次MySQL查询获得，而不需要使用存储过程。
- 解决方案必须是快速的，例如一个繁忙的apache2服务器，MySQL查询在许多情况下是瓶颈。因此，它必须避免创建临时表等。
- 这3条记录必须不是连续的，即它们不能彼此相邻。
该表包含以下字段：
CREATE TABLE Products (
ID INT(8) NOT NULL AUTO_INCREMENT,
Name VARCHAR(255) default NULL,
HasImages INT default 0,
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8;WHERE约束是Products.HasImages=1，它只允许获取具有可在网页上显示的图像的记录。大约三分之一的记录满足HasImages=1的条件。
为了寻求完美，我们首先抛开存在缺陷的解决方案：
I.使用ORDER BY RAND()的This basic solution，
太慢了，但保证每次查询都有3条真正随机的记录：
SELECT ID, Name FROM Products WHERE HasImages=1 ORDER BY RAND() LIMIT 3;*CPU约0.10s，扫描9690行，因为WHERE子句，使用where；使用临时；使用文件排序，在Debian Squeeze双核Linux机器上，还不错，但是
当使用临时表和文件排序时，不能扩展到更大的表，并且在测试Windows7::MySQL系统上第一次查询需要8.52秒。有了这么差的性能，避免一个网页，不是吗？
II.使用JOIN的riedsio的聪明解决方案...RAND()，
在MySQL select 10 random rows from 600K rows fast中，这里改编的只对单个随机记录有效，因为下面的查询结果几乎总是连续的记录。实际上，它只获得ID中3个连续记录的随机集：
SELECT Products.ID, Products.Name
FROM Products
INNER JOIN (SELECT (RAND() * (SELECT MAX(ID) FROM Products)) AS ID)
AS t ON Products.ID >= t.ID
WHERE (Products.HasImages=1)
ORDER BY Products.ID ASC
LIMIT 3;*CPU大约0.01 - 0.19s，随机扫描3200,9690,12000行左右，但大多数是9690条记录，使用where。
最好的解决方案似乎如下所示……RAND()，
在bernardo-siu提出的MySQL select 10 random rows from 600K rows fast上看到
SELECT Products.ID, Products.Name FROM Products
WHERE ((Products.Hasimages=1) AND RAND() < 16 * 3/30000) LIMIT 3;*CPU约0.01 - 0.03s，扫描9690行，使用where。
这里3是希望的行数，30000是表产品的RecordCount，16是扩大选择以保证3条记录选择的实验系数。我不知道在什么基础上因子16是可接受的近似值。
我们在大多数情况下都会得到3条随机记录，这是非常快的，但这是不必要的:有时查询只返回2行，有时甚至根本没有记录。
上面的三个方法扫描了表的所有记录，这里是9690行。
更好的SQL字符串？
回答 6
Stack Overflow用户
发布于 2013-09-22 23:52:33
丑陋，但迅速和随机。可能很快就会变得非常丑陋，特别是下面描述的调优，所以请确保你真的想这样做。
(SELECT Products.ID, Products.Name
FROM Products
INNER JOIN (SELECT RAND()*(SELECT MAX(ID) FROM Products) AS ID) AS t ON Products.ID >= t.ID
WHERE Products.HasImages=1
ORDER BY Products.ID
LIMIT 1)
UNION ALL
(SELECT Products.ID, Products.Name
FROM Products
INNER JOIN (SELECT RAND()*(SELECT MAX(ID) FROM Products) AS ID) AS t ON Products.ID >= t.ID
WHERE Products.HasImages=1
ORDER BY Products.ID
LIMIT 1)
UNION ALL
(SELECT Products.ID, Products.Name
FROM Products
INNER JOIN (SELECT RAND()*(SELECT MAX(ID) FROM Products) AS ID) AS t ON Products.ID >= t.ID
WHERE Products.HasImages=1
ORDER BY Products.ID
LIMIT 1)第一行出现的频率超出了应有的范围
如果表中的If之间有很大的差距，那么紧接在这些差距之后的行将有更大的机会被该查询获取。在某些情况下，它们会比正常情况下更频繁地出现。这不能在一般情况下解决，但对于常见的特殊情况有一个修复:当一个表中的0和第一个现有的ID之间存在差距时。
不使用子查询(SELECT RAND()*<max_id> AS ID)，而是使用类似于(SELECT <min_id> + RAND()*(<max_id> - <min_id>) AS ID)
删除重复项
如果按原样使用该查询，可能会返回重复的行。可以通过使用UNION而不是UNION ALL来避免这种情况。这样，重复项将被合并，但查询不再保证返回3行。你也可以解决这个问题，通过获取比你需要的更多的行，并像这样限制外部结果：
(SELECT ... LIMIT 1)
UNION (SELECT ... LIMIT 1)
UNION (SELECT ... LIMIT 1)
...
UNION (SELECT ... LIMIT 1)
LIMIT 3但是，仍然不能保证会读取3行数据。这只会让它更有可能。
Stack Overflow用户
发布于 2013-09-22 21:23:08
SELECT Products.ID, Products.Name
FROM Products
INNER JOIN (SELECT (RAND() * (SELECT MAX(ID) FROM Products)) AS ID) AS t ON Products.ID >= t.ID
WHERE (Products.HasImages=1)
ORDER BY Products.ID ASC
LIMIT 3;当然，上面给出的是“接近”的连续记录，您每次都会向它提供相同的ID，而不会过多地考虑rand函数的seed。
这应该会带来更多的“随机性”
SELECT Products.ID, Products.Name
FROM Products
INNER JOIN (SELECT (ROUND((RAND() * (max-min))+min)) AS ID) AS t ON Products.ID >= t.ID
WHERE (Products.HasImages=1)
ORDER BY Products.ID ASC
LIMIT 3;其中max和min是您选择的两个值，例如sake：
max = select max(id)
min = 225Stack Overflow用户
发布于 2013-09-22 20:40:25
这条语句的执行速度非常快( 30k记录表上的19ms)：
$db = new PDO('mysql:host=localhost;dbname=database;charset=utf8', 'username', 'password');
$stmt = $db->query("SELECT p.ID, p.Name, p.HasImages
FROM (SELECT @count := COUNT(*) + 1, @limit := 3 FROM Products WHERE HasImages = 1) vars
STRAIGHT_JOIN (SELECT t.*, @limit := @limit - 1 FROM Products t WHERE t.HasImages = 1 AND (@count := @count -1) AND RAND() < @limit / @count) p");
$products = $stmt->fetchAll(PDO::FETCH_ASSOC);其思想是“注入”具有随机化值的新列，然后按该列排序。这个注入列的生成和排序比"ORDER by RAND()“命令快得多。
“可能”有一个警告:您必须包含两次WHERE查询。
https://stackoverflow.com/questions/18943417
复制相似问题