MySQL : ORDER BY RAND ()의 대안

MySQL : ORDER BY RAND ()의 대안

---

MySQL의 ORDER BY RAND()기능 에 대한 몇 가지 대안에 대해 읽었 지만 대부분의 대안은 단일 임의 결과가 필요한 경우에만 적용됩니다.

누구든지 다음과 같이 여러 임의의 결과를 반환하는 쿼리를 최적화하는 방법을 알고 있습니까?

   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 업데이트

이 솔루션은 인덱싱 된 열을 사용하는 것이 가장 좋습니다 .

다음은 100,000 개의 행으로 표시된 최적화 된 쿼리 벤치의 간단한 예입니다.

최적화 : 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 / 개수 (*). 4는 같은 숫자 여야합니다. 반환 횟수를 변경해도 속도에 그다지 영향을 미치지 않습니다. 한계 4와 한계 1000의 벤치 마크는 동일합니다. 제한 10,000은 최대 600ms에 걸렸습니다.

조인에 대한 참고 사항 : ID 만 무작위 화하는 것이 전체 행을 무작위 화하는 것보다 빠릅니다. 전체 행을 메모리에 복사해야하기 때문에 무작위 화합니다. 조인은 테이블 스캔을 방지하기 위해 하위 쿼리 Its에 연결된 모든 테이블이 될 수 있습니다.

where 절 참고 : where count는 무작위 화되는 결과의 양을 제한합니다. 결과의 백분율을 사용하여 전체 테이블이 아닌 정렬합니다.

참고 하위 쿼리 : if 조인 및 추가 where 절 조건을 수행하려면 하위 쿼리와 하위 쿼리 모두에 넣어야합니다. 정확한 카운트를 가지고 올바른 데이터를 가져옵니다.

최적화되지 않음 : 1200ms

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

장점

4 배보다 더 빨리 order by rand(). 이 솔루션은 인덱싱 된 열이있는 모든 테이블에서 사용할 수 있습니다.

단점

복잡한 쿼리로 약간 복잡합니다. 하위 쿼리에 2 개의 코드베이스를 유지해야합니다.

---

여기에 대안이 있지만 여전히 RAND () 사용을 기반으로합니다.

  SELECT u.id, 
         p.photo,
         ROUND(RAND() * x.m_id) 'rand_ind'
    FROM users u, 
         profiles p,
         (SELECT MAX(t.id) 'm_id'
            FROM USERS t) x
   WHERE p.memberid = u.id 
     AND p.photo != '' 
     AND (u.ownership=1 OR u.stamp=1) 
ORDER BY rand_ind
   LIMIT 18

이것은 약간 더 복잡하지만 random_ind 값의 더 나은 분포를 제공합니다.

  SELECT u.id, 
         p.photo,
         FLOOR(1 + RAND() * x.m_id) 'rand_ind'
    FROM users u, 
         profiles p,
         (SELECT MAX(t.id) - 1 'm_id'
            FROM USERS t) x
   WHERE p.memberid = u.id 
     AND p.photo != '' 
     AND (u.ownership=1 OR u.stamp=1) 
ORDER BY rand_ind
   LIMIT 18

---

가장 빠르지는 않지만 일반적인 ORDER BY RAND()방법 보다 빠릅니다 .

ORDER BY RAND()인덱싱 된 열만 찾는 데 사용할 때 그렇게 느리지 않습니다. 다음과 같이 하나의 쿼리에서 모든 ID를 가져올 수 있습니다.

SELECT id
FROM testTable
ORDER BY RAND();

임의의 ID 시퀀스를 가져오고 JOIN다른 SELECT 또는 WHERE 매개 변수를 사용하여 다른 쿼리에 대한 결과를 가져옵니다.

SELECT t.*
FROM testTable t
JOIN
    (SELECT id
    FROM `testTable`
    ORDER BY RAND()) AS z ON z.id= t.id   
WHERE t.isVisible = 1
LIMIT 100; 

귀하의 경우에는 다음과 같습니다.

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

매우 무딘 방법이며 매우 큰 테이블에서는 적절하지 않을 수 있지만 여전히 일반적인 것보다 빠릅니다 RAND(). 거의 400000에서 3000 개의 임의 행을 검색하는 실행 시간이 20 배 빨라졌습니다.

---

I ran into this today and was trying to use 'DISTINCT' along with JOINs, but was getting duplicates I assume because the RAND was making each JOINed row distinct. I muddled around a bit and found a solution that works, like this:

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

---

Create a column or join to a select with random numbers (generated in for example php) and order by this column.

---

Order by rand() is very slow on large tables,

I found the following workaround in a php script:

Select min(id) as min, max(id) as max from table;

Then do random in php

$rand = rand($min, $max);

Then

'Select * from table where id>'.$rand.' limit 1';

Seems to be quite fast....

---

The solution I am using is also posted in the link below: How can i optimize MySQL's ORDER BY RAND() function?

I am assuming your users table is going to be larger than your profiles table, if not then it's 1 to 1 cardinality.

If so, I would first do a random selection on user table before joining with profile table.

First do selection:

SELECT *
FROM users
WHERE users.ownership = 1 OR users.stamp = 1

Then from this pool, pick out random rows through calculated probability. If your table has M rows and you want to pick out N random rows, the probability of random selection should be N/M. Hence:

SELECT *
FROM
(
    SELECT *
    FROM users
    WHERE users.ownership = 1 OR users.stamp = 1
) as U
WHERE 
    rand() <= $limitCount / (SELECT count(*) FROM users WHERE users.ownership = 1 OR users.stamp = 1)

Where N is $limitCount and M is the subquery that calculates the table row count. However, since we are working on probability, it is possible to have LESS than $limitCount of rows returned. Therefore we should multiply N by a factor to increase the random pool size.

i.e:

SELECT*
FROM
(
    SELECT *
    FROM users
    WHERE users.ownership = 1 OR users.stamp = 1
) as U
WHERE 
    rand() <= $limitCount * $factor / (SELECT count(*) FROM users WHERE users.ownership = 1 OR users.stamp = 1)

I usually set $factor = 2. You can set the factor to a lower value to further reduce the random pool size (e.g. 1.5).

At this point, we would have already limited a M size table down to roughly 2N size. From here we can do a JOIN then LIMIT.

SELECT * 
FROM
(
       SELECT *
        FROM
        (
            SELECT *
            FROM users
            WHERE users.ownership = 1 OR users.stamp = 1
        ) as U
        WHERE 
            rand() <= $limitCount * $factor / (SELECT count(*) FROM users WHERE users.ownership = 1 OR users.stamp = 1)
) as randUser
JOIN profiles
ON randUser.id = profiles.memberid AND profiles.photo != ''
LIMIT $limitCount

On a large table, this query will outperform a normal ORDER by RAND() query.

Hope this helps!

ReferenceURL : https://stackoverflow.com/questions/1823306/mysql-alternatives-to-order-by-rand