我目前正在一个智力竞赛网站上工作。
我有一个大约有1000个问题的数据库,但数据库会一周又一周地增长。
每天,每个参加测试的用户将随机获得5个问题。
问题是,我不希望一个用户在两个不同的日期两次得到相同的问题。
我存储了用户回答的所有问题的历史记录,这样我就可以知道用户id:1在YYYY-MM-DD 日期已经回答了问题id:4到问题id:6
基本上:
entry_id | user_id | question_id | answer_id | good | date
因此,当我为用户随机得到5个问题时,我有两个选项:
SELECT question, question_id FROM questions WHERE question_id != 'X' AND question_id != 'Y' AND question_id != 'Z' ORDER BY RAND() LIMIT 0,5
OR(更容易)
SELECT question, question_id FROM questions WHERE question_id NOT IN(X,Y,Z)
我的问题:
假设我的用户到目前为止已经回答了500个问题(100天的活动)。我想问他新问题的时间会非常长
... NOT IT({huge list of ids for which the user has already answered})
或
... question_id != 'A' AND question_id != 'B' and so on and so on.
我担心的是,随着时间的推移,我的查询可能会变得非常缓慢。想象一下,一个我必须回答5个问题的用户,知道他已经回答了5000个问题,这个查询会杀死我的服务器,不是吗?
有没有办法让我知道所有问题的id都已经得到回答,并确保查询不会太难为我的服务器处理,从而为我的用户随机获得5个问题?
提前感谢!
NOT EXISTS
在这种情况下可能会为您提供更好的服务。
SELECT q.question, q.question_id
FROM questions q
WHERE NOT EXISTS(SELECT NULL
FROM answers a
WHERE a.question_id = q.question_id
AND a.user_id = 'YourUser')
ORDER BY RAND() LIMIT 0,5
是的,您可以使用NOT EXISTS
进行等效的NOT IN
查询。
MySQL对查询进行"从外到内"的评估。也就是说,它首先获得外部表达式outer_expr的值,然后运行子查询并捕获它生成的行。
一个非常有用的优化是"通知"子查询,唯一感兴趣的行是那些内部表达式inner_expr等于outer_expr的行。这是通过在子查询的WHERE子句中下推一个适当的相等项来完成的。也就是说,将比较转换为:
EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)
转换后,MySQL可以使用下推式等式来限制在评估子查询时必须检查的行数:
SELECT q.* FROM questions q WHERE
NOT EXISTS(SELECT 1 FROM answers a
WHERE a.question_id = q.question_id
AND a.user_id = 'UserId')
ORDER BY RAND() LIMIT 0,5
WHERE子句中的"NOT IN"应该可以完成以下操作:
SELECT
XYZ
FROM
QUESTIONS
WHERE
ID NOT IN (SELECT ID FROM QUESTION_HISTORY WHERE USER_ID = @USERID)
我建议您为此使用多个查询,因为"ORDER BY RAND()"对于大表来说相当慢。
首先选择所有可能的ID
SELECT q.question_id
FROM questions q
WHERE q.question_id NOT IN
( SELECT a.question_id
FROM anwered a
WHERE a.question_id = q.question_id AND a.user_id = 'userID'
)
然后,你可以用你喜欢的语言随机选择五个元素,并进行另一个查询
SELECT q.question_id, ...
FROM questions q
WHERE q.question_id IN ('id1', 'id2', 'id3', 'id4', 'id5');
我认为这应该运行得更快,但基准测试可能比胡乱猜测要好。
到目前为止,所有的建议都涉及在数据库上运行相当昂贵的查询。如果您有许多用户和许多问题,可能会遇到性能问题。如果这是一个问题,您可以选择存储复杂性而不是时间复杂性:
警告:提前优化!
对于每个用户,预先生成一个随机排序的问题ID集。在应用程序代码中执行此操作,并将其作为blob存储到数据库中。还为每个用户存储他们在该列表中的位置。现在,您所要做的就是加载列表,跳到正确的位置,然后返回相关问题。
您可以使用伪随机数生成算法,例如Mersenne twister来生成问题ID列表。对于每个用户,创建一个不同的种子,以便为不同的用户获得不同的问题序列。
存储预先计算的1000个问题列表所需的每个用户10个KB。这个数字似乎不太高。但是,它确实会影响您的性能,因为当您加载该字段时,数据库必须将所有额外的数据发送到您的应用程序。
这是一个远没有其他解决方案那么简单的解决方案,而且肯定是过早的优化。尽管如此,我还是认为应该建议将其作为复杂SQL查询的替代方案。
子选择
SELECT *
FROM questions
WHERE question_id NOT IN (
SELECT question_id
FROM answers
WHERE user = XX
)