MySQL:ORDER BY RAND()的替代方法 [英] MySQL: Alternatives to ORDER BY RAND()

问题描述

我已经阅读了关于MySQL的ORDER BY RAND()函数的一些替代方法，但是大多数替代方法仅适用于需要单个随机结果的地方.

I've read about a few alternatives to MySQL's ORDER BY RAND() function, but most of the alternatives apply only to where on a single random result is needed.

任何人都不知道如何优化返回多个随机结果的查询，例如:

Does anyone have any idea how to optimize a query that returns multiple random results, such as this:

   SELECT u.id, 
          p.photo 
     FROM users u, profiles p 
    WHERE p.memberid = u.id 
      AND p.photo != '' 
      AND (u.ownership=1 OR u.stamp=1) 
 ORDER BY RAND() 
    LIMIT 18 

推荐答案

更新2016

使用索引列，此解决方案效果最佳.

UPDATE 2016

This solution works best using an indexed column.

这是标记为100,000行的优化查询平台的简单示例.

Here is a simple example of and optimized query bench marked with 100,000 rows.

优化: 300ms

SELECT 
    g.*
FROM
    table g
        JOIN
    (SELECT 
        id
    FROM
        table
    WHERE
        RAND() < (SELECT 
                ((4 / COUNT(*)) * 10)
            FROM
                table)
    ORDER BY RAND()
    LIMIT 4) AS z ON z.id= g.id

有关限额的说明:限额4和4/count(*). 4位数必须相同.更改退货数量不会对速度产生太大影响.极限4和极限1000的基准相同.限制10,000将其耗时最长为600ms

note about limit ammount: limit 4 and 4/count(*). The 4s need to be the same number. Changing how many you return doesn't effect the speed that much. Benchmark at limit 4 and limit 1000 are the same. Limit 10,000 took it up to 600ms

关于连接的注意事项:仅对ID进行随机分配要比对整行进行随机分配要快.由于必须将整行复制到内存中，因此需要将其随机化.联接可以是链接到其子查询的任何表，以防止进行表扫描.

note about join: Randomizing just the id is faster than randomizing a whole row. Since it has to copy the entire row into memory then randomize it. The join can be any table that is linked to the subquery Its to prevent tablescans.

请注意where子句:where计数限制了随机结果的数量.它采用结果的一定百分比并对它们进行排序，而不是对整个表进行排序.

note where clause: The where count limits down the ammount of results that are being randomized. It takes a percentage of the results and sorts them rather than the whole table.

注释子查询:如果执行连接和额外的where子句条件，则需要将它们同时放在子查询和子子查询中.要获得准确的计数并提取正确的数据.

note sub query: The if doing joins and extra where clause conditions you need to put them both in the subquery and the subsubquery. To have an accurate count and pull back correct data.

未优化: 1200ms

SELECT 
    g.*
FROM
    table g
ORDER BY RAND()
LIMIT 4

PROS

比order by rand()快4倍.该解决方案可以与具有索引列的任何表一起使用.

4x faster than order by rand(). This solution can work with any table with a indexed column.

缺点

对于复杂的查询来说有点复杂.需要在子查询中维护2个代码库

It is a bit complex with complex queries. Need to maintain 2 code bases in the subqueries

这篇关于MySQL:ORDER BY RAND()的替代方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！