[mysql] MySQL의 ORDER BY RAND () 함수를 어떻게 최적화 할 수 있습니까?

Program / 글쓴이

쿼리를 최적화하고 싶습니다. mysql-slow.log .

내 느린 쿼리의 대부분에는 ORDER BY RAND(). 이 문제를 해결할 실제 해결책을 찾을 수 없습니다. MySQLPerformanceBlog에 가능한 해결책이 있지만 이것만으로는 충분하지 않다고 생각합니다. 제대로 최적화되지 않은 (또는 자주 업데이트되고 사용자가 관리하는) 테이블에서는 작동하지 않거나 PHP생성 된 임의 행을 선택하기 전에 두 개 이상의 쿼리를 실행해야합니다 .

이 문제에 대한 해결책이 있습니까?

더미 예 :

SELECT  accomodation.ac_id,
        accomodation.ac_status,
        accomodation.ac_name,
        accomodation.ac_status,
        accomodation.ac_images
FROM    accomodation, accomodation_category
WHERE   accomodation.ac_status != 'draft'
        AND accomodation.ac_category = accomodation_category.acat_id
        AND accomodation_category.acat_slug != 'vendeglatohely'
        AND ac_images != 'b:0;'
ORDER BY
        RAND()
LIMIT 1

답변

이 시도:

SELECT  *
FROM    (
        SELECT  @cnt := COUNT(*) + 1,
                @lim := 10
        FROM    t_random
        ) vars
STRAIGHT_JOIN
        (
        SELECT  r.*,
                @lim := @lim - 1
        FROM    t_random r
        WHERE   (@cnt := @cnt - 1)
                AND RAND(20090301) < @lim / @cnt
        ) i

이것은 MyISAM( COUNT(*)즉시 적이기 때문에 ) 특히 효율적 이지만, InnoDB그것 10보다 훨씬 더 효율적입니다.ORDER BY RAND() .

여기서 주요 아이디어는 정렬하지 않고 대신 두 개의 변수를 유지하고 running probability 하고 현재 단계에서 선택할 행을 입니다.

자세한 내용은 내 블로그에서이 기사를 참조하십시오.

최신 정보:

하나의 무작위 레코드 만 선택해야하는 경우 다음을 시도하십시오.

SELECT  aco.*
FROM    (
        SELECT  minid + FLOOR((maxid - minid) * RAND()) AS randid
        FROM    (
                SELECT  MAX(ac_id) AS maxid, MIN(ac_id) AS minid
                FROM    accomodation
                ) q
        ) q2
JOIN    accomodation aco
ON      aco.ac_id =
        COALESCE
        (
        (
        SELECT  accomodation.ac_id
        FROM    accomodation
        WHERE   ac_id > randid
                AND ac_status != 'draft'
                AND ac_images != 'b:0;'
                AND NOT EXISTS
                (
                SELECT  NULL
                FROM    accomodation_category
                WHERE   acat_id = ac_category
                        AND acat_slug = 'vendeglatohely'
                )
        ORDER BY
                ac_id
        LIMIT   1
        ),
        (
        SELECT  accomodation.ac_id
        FROM    accomodation
        WHERE   ac_status != 'draft'
                AND ac_images != 'b:0;'
                AND NOT EXISTS
                (
                SELECT  NULL
                FROM    accomodation_category
                WHERE   acat_id = ac_category
                        AND acat_slug = 'vendeglatohely'
                )
        ORDER BY
                ac_id
        LIMIT   1
        )
        )

이것은 당신이 ac_id어느 정도 균등하게 분포되어 있다고 가정합니다 .

답변

그것은 당신이 얼마나 무작위 적이어야 하는가에 달려 있습니다. 연결 한 솔루션은 IMO에서 잘 작동합니다. ID 필드에 큰 간격이 있지 않는 한 여전히 무작위입니다.

그러나 다음을 사용하여 하나의 쿼리에서 수행 할 수 있어야합니다 (단일 값 선택 용).

SELECT [fields] FROM [table] WHERE id >= FLOOR(RAND()*MAX(id)) LIMIT 1

기타 솔루션 :

  • random테이블에 호출 된 영구 부동 필드를 추가하고 난수로 채 웁니다. 그런 다음 PHP에서 임의의 숫자를 생성하고"SELECT ... WHERE rnd > $random"
  • 전체 ID 목록을 가져 와서 텍스트 파일에 캐시합니다. 파일을 읽고 임의의 ID를 선택하십시오.
  • 쿼리 결과를 HTML로 캐시하고 몇 시간 동안 보관합니다.

답변

방법은 다음과 같습니다.

SET @r := (SELECT ROUND(RAND() * (SELECT COUNT(*)
  FROM    accomodation a
  JOIN    accomodation_category c
    ON (a.ac_category = c.acat_id)
  WHERE   a.ac_status != 'draft'
        AND c.acat_slug != 'vendeglatohely'
        AND a.ac_images != 'b:0;';

SET @sql := CONCAT('
  SELECT  a.ac_id,
        a.ac_status,
        a.ac_name,
        a.ac_status,
        a.ac_images
  FROM    accomodation a
  JOIN    accomodation_category c
    ON (a.ac_category = c.acat_id)
  WHERE   a.ac_status != ''draft''
        AND c.acat_slug != ''vendeglatohely''
        AND a.ac_images != ''b:0;''
  LIMIT ', @r, ', 1');

PREPARE stmt1 FROM @sql;

EXECUTE stmt1;

답변

(예, 여기 고기가 충분하지 않다는 이유로 물을 찌르 겠지만 하루 동안 비건 채식을 할 수 없나요?)

케이스 : 간격없는 연속 AUTO_INCREMENT, 1 행 리턴
케이스 : 간격없는 연속 AUTO_INCREMENT, 10 행
케이스 : 간격이있는 AUTO_INCREMENT, 리턴 1 행
케이스 : 랜덤 화를위한 추가 FLOAT 열
케이스 : UUID 또는 MD5 열

이 5 가지 경우는 큰 테이블에 대해 매우 효율적으로 만들 수 있습니다. 보다 내 블로그 를하십시오.

답변

이것은 인덱스를 사용하여 임의의 ID를 얻는 단일 하위 쿼리를 제공하고 다른 쿼리는 조인 된 테이블을 가져옵니다.

SELECT  accomodation.ac_id,
        accomodation.ac_status,
        accomodation.ac_name,
        accomodation.ac_status,
        accomodation.ac_images
FROM    accomodation, accomodation_category
WHERE   accomodation.ac_status != 'draft'
        AND accomodation.ac_category = accomodation_category.acat_id
        AND accomodation_category.acat_slug != 'vendeglatohely'
        AND ac_images != 'b:0;'
AND accomodation.ac_id IS IN (
        SELECT accomodation.ac_id FROM accomodation ORDER BY RAND() LIMIT 1
)

답변

더미 예제에 대한 해결책은 다음과 같습니다.

SELECT  accomodation.ac_id,
        accomodation.ac_status,
        accomodation.ac_name,
        accomodation.ac_status,
        accomodation.ac_images
FROM    accomodation,
        JOIN
            accomodation_category
            ON accomodation.ac_category = accomodation_category.acat_id
        JOIN
            (
               SELECT CEIL(RAND()*(SELECT MAX(ac_id) FROM accomodation)) AS ac_id
            ) AS Choices
            USING (ac_id)
WHERE   accomodation.ac_id >= Choices.ac_id
        AND accomodation.ac_status != 'draft'
        AND accomodation_category.acat_slug != 'vendeglatohely'
        AND ac_images != 'b:0;'
LIMIT 1

대안에 대해 자세히 알아 보려면 이 문서를ORDER BY RAND() 읽어야 합니다 .

답변

내 프로젝트에서 많은 기존 쿼리를 최적화하고 있습니다. Quassnoi의 솔루션은 쿼리 속도를 크게 높이는 데 도움이되었습니다! 그러나 모든 쿼리, 특히 여러 대형 테이블에서 많은 하위 쿼리를 포함하는 복잡한 쿼리에 대해 상기 솔루션을 통합하기가 어렵다는 것을 알았습니다.

그래서 덜 최적화 된 솔루션을 사용하고 있습니다. 기본적으로 Quassnoi의 솔루션과 동일한 방식으로 작동합니다.

SELECT  accomodation.ac_id,
        accomodation.ac_status,
        accomodation.ac_name,
        accomodation.ac_status,
        accomodation.ac_images
FROM    accomodation, accomodation_category
WHERE   accomodation.ac_status != 'draft'
        AND accomodation.ac_category = accomodation_category.acat_id
        AND accomodation_category.acat_slug != 'vendeglatohely'
        AND ac_images != 'b:0;'
        AND rand() <= $size * $factor / [accomodation_table_row_count]
LIMIT $size

$size * $factor / [accomodation_table_row_count]무작위 행을 선택할 확률을 계산합니다. rand ()는 난수를 생성합니다. rand ()가 확률보다 작거나 같으면 행이 선택됩니다. 이것은 테이블 크기를 제한하기 위해 무작위 선택을 효과적으로 수행합니다. 정의 된 제한 개수보다 적게 반환 될 가능성이 있으므로 충분한 행을 선택하도록 확률을 높여야합니다. 따라서 $ size에 $ factor를 곱합니다 (일반적으로 $ factor = 2로 설정하고 대부분의 경우 작동 함). 마지막으로 우리는limit $size

이제 문제는 accomodation_table_row_count를 해결하는 것 입니다. 테이블 크기를 알고 있다면 테이블 크기를 하드 코딩 할 수 있습니다. 이것은 가장 빠르게 실행되지만 분명히 이상적이지 않습니다. Myisam을 사용하는 경우 테이블 수를 얻는 것이 매우 효율적입니다. innodb를 사용하고 있기 때문에 간단한 count + selection을하고 있습니다. 귀하의 경우에는 다음과 같습니다.

SELECT  accomodation.ac_id,
        accomodation.ac_status,
        accomodation.ac_name,
        accomodation.ac_status,
        accomodation.ac_images
FROM    accomodation, accomodation_category
WHERE   accomodation.ac_status != 'draft'
        AND accomodation.ac_category = accomodation_category.acat_id
        AND accomodation_category.acat_slug != 'vendeglatohely'
        AND ac_images != 'b:0;'
        AND rand() <= $size * $factor / (select (SELECT count(*) FROM `accomodation`) * (SELECT count(*) FROM `accomodation_category`))
LIMIT $size

까다로운 부분은 올바른 확률을 찾는 것입니다. 다음 코드에서 볼 수 있듯이 실제로 대략적인 임시 테이블 크기 만 계산합니다 (사실 너무 대략적입니다!). (select (SELECT count(*) FROM accomodation) * (SELECT count(*) FROM accomodation_category))그러나이 논리를 구체화하여 테이블 크기 근사치를 제공 할 수 있습니다. 행을 과소 선택하는 것보다 과도하게 선택하는 것이 좋습니다. 즉, 확률이 너무 낮게 설정되면 충분한 행을 선택하지 않을 위험이 있습니다.

이 솔루션은 테이블 크기를 다시 계산해야하므로 Quassnoi의 솔루션보다 느리게 실행됩니다. 그러나이 코딩이 훨씬 더 관리하기 쉽다는 것을 알았습니다. 이것은 정확성 + 성능 대 코딩 복잡성 사이의 균형 입니다. 큰 테이블에서는 Order by Rand ()보다 훨씬 빠릅니다.

참고 : 쿼리 논리가 허용하는 경우 조인 작업 전에 가능한 한 빨리 임의 선택을 수행하십시오.