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개의 행으로 표시된 최적화된 쿼리 벤치의 간단한 예입니다.

최적화: 300 ms

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

limit ammount: limit 4 및 4/counts)에 대해 설명합니다.4s는 같은 숫자여야 합니다.돌아오는 횟수를 변경해도 속도에 큰 영향은 없습니다.제한 4와 제한 1000의 벤치마크는 동일합니다.10,000개 제한으로 최대 600ms까지

가입에 관한 주의: ID만 랜덤화하는 것이 행 전체를 랜덤화하는 것보다 빠릅니다.행 전체를 메모리에 복사한 후 랜덤화해야 합니다.조인에는 테이블 검색을 방지하기 위해 하위 쿼리에 연결된 모든 테이블을 사용할 수 있습니다.

여기서 절:여기서 카운트는 랜덤화된 결과의 암마운트를 제한합니다.결과 중 일정 비율을 차지하여 전체 표가 아닌 정렬합니다.

참고 하위 쿼리:if joins 및 extra where 구 조건을 실행하여 서브쿼리와 서브서브쿼리에 둘 다 배치해야 합니다.정확한 카운트를 하고 정확한 데이터를 추출합니다.

최적화되지 않음: 1200 ms

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

장점

보다 4배 빠른 속도order by rand()이 솔루션은 인덱스 컬럼이 있는 모든 테이블에서 사용할 수 있습니다.

단점

복잡한 쿼리로 인해 조금 복잡합니다.서브쿼리에 2개의 코드 베이스를 유지해야 합니다.

대체 방법은 다음과 같습니다만, 여전히 LAND()를 사용하고 있습니다.

  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배 빨라졌습니다.

Order by rand()테이블에서는 .

php 스크립트에서 다음과 같은 회피책을 찾았습니다.

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

그런 다음 php에서 랜덤으로 수행합니다.

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

그리고나서

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

꽤 빠른 것 같은데...

오늘 JOINs와 함께 DISTINCT를 사용하려고 했는데 LAND가 각 JOIND 행을 구별하기 때문에 중복이 생긴 것 같습니다.잠시 뒤적거리다가 다음과 같은 효과적인 해결책을 찾았습니다.

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

열을 만들거나 랜덤 번호(예: php)를 사용하여 선택 항목에 조인하고 이 열을 기준으로 순서를 지정합니다.

사용하고 있는 솔루션은 다음 링크에도 게재되어 있습니다.MySQL의 ORDER BY RAND() 함수를 최적화하려면 어떻게 해야 합니까?

사용자 테이블은 프로파일 테이블보다 클 것으로 예상됩니다.그렇지 않으면 1:1 카디널리티가 됩니다.

이 경우 프로파일테이블에 참여하기 전에 사용자 테이블에서 랜덤으로 선택합니다.

첫 번째 작업 선택:

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

그런 다음 이 풀에서 계산된 확률을 통해 랜덤 행을 선택합니다.표에 M개의 행이 있고 N개의 랜덤 행을 선택하려면 랜덤 선택 확률이 N/M이어야 합니다.이 때문에,

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)

여기서 N은 $limit입니다.Count 및 M은 테이블 행 수를 계산하는 서브쿼리입니다.다만, 개연성을 검토하고 있기 때문에, $limit 미만으로 할 수 있습니다.반환된 행 수입니다.따라서 N에 계수를 곱하여 랜덤 풀 크기를 증가시켜야 합니다.

예:

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)

보통 $factor = 2로 설정합니다.요인을 낮은 값으로 설정하여 랜덤 풀 크기(예: 1.5)를 더 줄일 수 있습니다.

현시점에서는 M사이즈 테이블은 약 2N사이즈로 한정되어 있습니다.여기서부터는 JOIN을 하고 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

큰 테이블에서는 이 쿼리는 일반 ORDER by RAND() 쿼리를 능가합니다.

이게 도움이 됐으면 좋겠네요!

SELECT
    a.id,
    mod_question AS modQuestion,
    mod_answers AS modAnswers 
FROM
    b_ask_material AS a
    INNER JOIN ( SELECT id FROM b_ask_material WHERE industry = 2 ORDER BY RAND( ) LIMIT 100 ) AS b ON a.id = b.id

오늘도 같은 문제가 있었습니다.한계 및 오프셋을 사용하여 수정했습니다.임의 오프셋 세트에 대해 18회 반복할 수 있습니다.

• 파이썬에서는 수 .sample(range(1, rows_count), random_rows_count)
• 그런 다음 각 오프셋에 대해 OFFSET 및 LIMIT 1을 사용하여 해당 행을 가져와 목록에 추가합니다.
• rows_count를 캐시하여 성능 문제를 방지하고 각 요청의 총 행 수를 카운트할 수 있습니다.

편집 https://stackoverflow.com/a/40398306/3045926 이 투고에서 제안하는 내용입니다.

언급URL : https://stackoverflow.com/questions/1823306/mysql-alternatives-to-order-by-rand