返回随机结果(按rand()排序) [英] Return random results ( order by rand() )

问题描述

我记得在某处读过说用rand()使用order很不好，我只是将它作为起始页，然后找到了一篇证明它的文章.对于大型数据库，按rand()排序可能会非常慢，建议的解决方案是在php中生成一个随机数，然后基于该数进行选择.问题是我需要验证其他字段才能返回我的记录.我可能还删除了一些旧记录，这也可能导致问题.任何人都可以提供一种体面的方法从符合某些条件的表中选择一些随机记录(例如，字段paid必须等于1)吗?

I remember reading somewhere that using order by rand() is bad and I just startpaged it and found an article that proves it. Order by rand() can be extremely slow with large databases and the suggested solution was to generate a random number in php and select based upon it. The problem is that I need to verify other fields in order to return my records. I may also have some old records deleted, that may also cause an issue. Can anyone provide a decent way to select a few random records from a table that match certain conditions ( for example field paid must be equal to 1 ) ?

推荐答案

使用RAND()进行排序可能会很慢，原因是您在强制数据库在返回任何内容之前对整个表进行实际排序.仅将负载减少到单个表扫描就快得多(尽管仍然有些慢).

The reason that ordering by RAND() can be slow is that you're forcing the database to actually sort the whole table before returning anything. Just reducing the load to a single table scan is much faster (albeit still somewhat slow).

这意味着您可以通过避免订购来参与其中:

This means that you could get part of the way just by avoiding the ordering:

  SELECT *
    FROM my_table
   WHERE RAND() < 0.1
ORDER BY RAND()
   LIMIT 100

这将选择表中所有行的大约1％，对其进行排序并返回前100行.请注意，这里的主要问题(以及@cmd的答案)是您不能确定查询根本不返回任何内容.

This will select approximately 1% of all the rows in the table, sort them and return the top 100. Just note that the main issue here (as well as with @cmd's answer) is that you can't be sure that the query returns anything at all.

上面的方法应该包括对整个表的扫描(以确定要使用哪些行)，然后进行大约1％的行排序.如果您有很多行，则可以相应地减少百分比.

The approach above should involve a whole table scan (to decide which rows to use) followed by a sort of approximately 1% of the rows. If you have a lot of rows, you can reduce the percentage accordingly.

这篇关于返回随机结果(按rand()排序)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！