我正在做一个项目，它有一个很大的Question Bank，为了测试添加到系统，20个问题在运行时基于以下查询:

SELECT Question.* from Question JOIN Test 
ON Question.Subject_ID = Test.Subject_ID 
AND Question.Question_Level = Test.Test_Level 
ORDER BY RAND() 
LIMIT 20;

但是，众所周知，MySQL 的 RAND() 函数会杀死您的服务器，我一直在寻找更好的解决方案。

结果 EXPLAIN [上述查询]:

+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | Test     | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using temporary; Using filesort                    |
|  1 | SIMPLE      | Question | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------+

EXPLAIN 问题的结果:

+-------------------+------------------------------------------+------+-----+---------+----------------+
| Field             | Type                                     | Null | Key | Default | Extra          |
+-------------------+------------------------------------------+------+-----+---------+----------------+
| Question_ID       | int(11)                                  | NO   | PRI | NULL    | auto_increment |
| Questions         | varchar(100)                             | NO   |     | NULL    |                |
| Available_Options | varchar(200)                             | NO   |     | NULL    |                |
| Correct_Answer    | varchar(50)                              | NO   |     | NULL    |                |
| Subject_ID        | int(11)                                  | NO   |     | NULL    |                |
| Question_Level    | enum('Beginner','Intermediate','Expert') | NO   |     | NULL    |                |
| Created_By        | int(11)                                  | NO   |     | NULL    |                |
+-------------------+------------------------------------------+------+-----+---------+----------------+

EXPLAIN 测试的结果:

+----------------+------------------------------------------+------+-----+---------+----------------+
| Field          | Type                                     | Null | Key | Default | Extra          |
+----------------+------------------------------------------+------+-----+---------+----------------+
| Test_ID        | int(11)                                  | NO   | PRI | NULL    | auto_increment |
| Test_Name      | varchar(50)                              | NO   |     | NULL    |                |
| Test_Level     | enum('Beginner','Intermediate','Expert') | NO   |     | NULL    |                |
| Subject_ID     | int(11)                                  | NO   |     | NULL    |                |
| Question_Count | int(11)                                  | NO   |     | NULL    |                |
| Created_By     | int(11)                                  | NO   |     | NULL    |                |
+----------------+------------------------------------------+------+-----+---------+----------------+

如果您能帮助优化查询以减少服务器负载和执行时间，我们将不胜感激。

附言系统也有Deletion的能力，所以QUESTION和TEST表的AUTO_INCREMENT PRIMARY KEY可以有很大的差距。

最佳答案

我喜欢这个问题。这是一个非常好的优化难题，让我们暂时假设性能对于此查询非常重要，并且您不能使用任何动态插入的值(例如来自 PHP)。

一种高性能解决方案是添加具有随机值的列(例如称为“Rand”)，按此值对表进行排序，并定期重新生成和重新排序表。然后您可以使用这样的查询:

SELECT Question.* from Question 
JOIN Test 
ON Question.Subject_ID = Test.Subject_ID 
AND Question.Question_Level = Test.Test_Level  
WHERE Question.Rand > RAND() 
LIMIT 20

这将在 O(n) 时执行，只需要对表进行一次扫描，但如果生成的值非常接近 1，则可能会返回少于 20 个结果。如果这是一个可以接受的风险(例如，您可以通过编程方式检查不适当的结果并重新查询)，您最终将获得良好的运行时性能。

周期性地重新生成和重新排序数字是必要的，因为表格中早期具有高 Rand 值的行将受到青睐，并且会不成比例地频繁出现在结果中。 (想象一下，如果第一行幸运地获得 0.95 的兰特值)

更好的方法是创建一个包含连续整数的列，在该列上建立索引，然后随机选择一个插入点来获取 20 个结果。这样的查询可能如下所示:

SELECT Question.* from Question 
JOIN Test 
ON Question.Subject_ID = Test.Subject_ID 
AND Question.Question_Level = Test.Test_Level  
CROSS JOIN (SELECT MAX(Rand_id) AS max_id FROM Question)
WHERE Question.Rand_Id > ROUND(RAND() * max_id)
LIMIT 20

但是，如果您无法以任何方式更改表格怎么办？如果您的 SQL 有多困惑并不重要，并且缺少 id 的比例相对较低(大约 1/10)。您可以使用以下 SQL 以很高的概率完成 20 个随机问题:

SELECT Question.* from Question JOIN Test 
  ON Question.Subject_ID = Test.Subject_ID 
  AND Question.Question_Level = Test.Test_Level 
  WHERE Question.Question_ID IN (
    SELECT DISTINCT(ROUND(rand * max_id)) AS rand_id 
    FROM ( --generate 30 random numbers to make sure we get 20 results
      SELECT RAND() AS rand UNION ALL
      SELECT RAND() AS rand UNION ALL
      SELECT RAND() AS rand UNION ALL
      SELECT RAND() AS rand UNION ALL
      ...
      SELECT RAND() AS rand UNION ALL
      SELECT RAND() AS rand UNION ALL
      SELECT RAND() AS rand
    ) a 
    CROSS JOIN ( --get the max possible id from the Question table
      SELECT MAX(id) AS max_id FROM Question
    ) b
  )
LIMIT 20 --finally pare our results down to 20 in case we got too many

但是，这会在您的用例中引起问题，因为您实际上无法知道联接后的结果集中有多少结果(及其 ID)。加入主题和难度后，缺失 ID 的比例可能会非常高，您最终得到的结果可能远少于 20 个，即使对表中的 ID 进行了数百次随机猜测也是如此。

如果您能够使用 PHP 的逻辑(听起来您确实如此)，就会出现许多高性能解决方案。例如，您可以在 PHP 中创建一个对象，其工作是存储具有特定主题和难度级别的所有问题 ID 的数组。然后您可以选择 20 个随机数组索引并取回 20 个有效 ID，从而允许您运行一个非常简单的查询。

SELECT Question.* from Question WHERE Question_ID IN ($dynamically_inserted_ids)

无论如何，我希望这能激发您的想象力。

关于php - 优化大型数据库中的 JOIN ORDER BY RAND() mysql 查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/39054987/