php rand() sql优化,php – 在大型数据库中优化RAND()mysql查询的JOIN ORDER

我正在开发一个具有大型问题库的项目,并且对于添加到系统的测试,基于以下查询动态地在运行时获取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 | |

+----------------+------------------------------------------+------+-----+---------+----------------+

任何帮助将被赞赏优化查询以减少服务器负载和执行时间.

附：系统也具有删除功能,因此QUESTION和TEST表的AUTO_INCREMENT PRIMARY KEY可能有很大的间隙.