使用大型数据库中的RAND() mysql查询优化连接顺序

Question

我正在从事一个具有大型Question银行的项目，对于系统中的Tests added，将根据以下查询在运行时动态获取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;

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

的 EXPLAIN [above query]结果

+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------+
| 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 Question结果

+-------------------+------------------------------------------+------+-----+---------+----------------+
| 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 Test结果

+----------------+------------------------------------------+------+-----+---------+----------------+
| 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    |                |
+----------------+------------------------------------------+------+-----+---------+----------------+

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

P.S.系统也具有删除的能力，所以试题和试题表的AUTO_INCREMENT主键可以有很大的空白。

Answer 1

你为什么不先在PHP中输入数字，然后按id选择问题呢？以下是我的观点的逻辑：

$MIN       = 1;
$MAX       = 50000; // You may want to get the MAX from your database
$questions = '';

for($i = 0; $i < 20; $i++)
   $questions .= mt_rand($MIN, $MAX) . ',';

// Removes last comma
$questions = rtrim($questions, ',');

$query = "SELECT * FROM Question WHERE Question.id IN ($questions)";

编辑1:

我在考虑这个问题，这促使我可以从数据库中选择所有ID，然后使用兰德()函数选择20个条目。

$values    = array(1, 5, 10000, 102021, 1000000); // Your database ID's
$questions = array_rand($values, 20);

$questions[0];
$questions[1];
$questions[2]; // etc

Answer 2

我喜欢这个问题。这是一个很好的优化难题，现在让我们假设性能对于这个查询非常重要，您不能使用任何动态插入的值(例如来自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值的行将受到青睐，并且在结果中显示得不成比例地频繁。(想象一下，如果第一行足够幸运地接收到.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有多混乱，而且缺少is的比例相对较低(比如大约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

但是，这将在您的用例中造成问题，因为实际上您无法知道连接后结果集中有多少结果(以及它们的in )。在加入主题和困难之后，缺少ID的比例可能很高，您可能最终得到的结果远远少于20个，即使对一个表中的ID可能有几百个随机猜测。

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

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

不管怎么说，我希望这能让你的想象力和一些可能性相结合。

Answer 3

创建以下索引：

CREATE INDEX Question_Subject_ID_idx ON Question (Subject_ID);
CREATE INDEX Test_Subject_ID_idx ON Test (Subject_ID);
CREATE INDEX Question_Question_Level_idx ON Question (Question_Level);
CREATE INDEX Test_Test_Level_idx ON Test (Test_Level);