問題描述

我已經(jīng)閱讀了 MySQL ORDER BY RAND() 函數(shù)的一些替代方案，但大多數(shù)替代方案僅適用于需要單個(gè)隨機(jī)結(jié)果的地方.

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.

有誰知道如何優(yōu)化返回多個(gè)隨機(jī)結(jié)果的查詢，例如:

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 

推薦答案

UPDATE 2016

此解決方案在使用索引列時(shí)效果最佳.

這里是一個(gè)簡(jiǎn)單的例子，優(yōu)化的查詢平臺(tái)標(biāo)有 100,000 行.

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

優(yōu)化:300 毫秒

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

注意限制數(shù)量:限制 4 和 4/count(*).4s 必須是相同的數(shù)字.更改返回的數(shù)量不會(huì)對(duì)速度產(chǎn)生太大影響.限制 4 和限制 1000 的基準(zhǔn)是相同的.限制 10,000 花了 600 毫秒

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

關(guān)于加入的注意事項(xiàng):僅隨機(jī)化 id 比隨機(jī)化整行更快.由于它必須將整行復(fù)制到內(nèi)存中，然后對(duì)其進(jìn)行隨機(jī)化.聯(lián)接可以是鏈接到子查詢的任何表，以防止表掃描.

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 計(jì)數(shù)限制了隨機(jī)結(jié)果的數(shù)量.它會(huì)根據(jù)結(jié)果的百分比對(duì)它們進(jìn)行排序，而不是對(duì)整個(gè)表格進(jìn)行排序.

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.

注意子查詢:如果執(zhí)行連接和額外的 where 子句條件，您需要將它們同時(shí)放在子查詢和子子查詢中.進(jìn)行準(zhǔn)確的計(jì)數(shù)并拉回正確的數(shù)據(jù).

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.

未優(yōu)化:1200 毫秒

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

優(yōu)點(diǎn)

比 order by rand() 快 4 倍.此解決方案適用于任何帶有索引列的表.

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

缺點(diǎn)

復(fù)雜查詢有點(diǎn)復(fù)雜.需要在子查詢中維護(hù)2個(gè)代碼庫

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

這篇關(guān)于MySQL:ORDER BY RAND() 的替代方案的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！