source

MySQL의 ORDER BY RAND() 함수를 최적화하려면 어떻게 해야 합니까?

goodcode 2022. 9. 23. 00:14
반응형

MySQL의 ORDER BY RAND() 함수를 최적화하려면 어떻게 해야 합니까?

하겠습니다.mysql-slow.log.

느린 질의의 대부분은 다음을 포함합니다.ORDER BY RAND()이 문제를 해결할 수 있는 진정한 해결책을 찾을 수 없습니다.MySQL Performance Blog에서 가능한 솔루션이지만, 이것만으로는 충분하지 않다고 생각합니다.최적화되지 않은(또는 자주 업데이트되는) 테이블에서는 작동하지 않거나 두 개 이상의 쿼리를 실행해야 합니다.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

은 특히 이은특특 this this this this this this this this에 효과적입니다.MyISAM)COUNT(*)만, 「즉석에서도 마찬가지입니다.InnoDB10이 높다ORDER BY RAND().

두 입니다.running probability★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★

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

업데이트:

임의의 레코드를 1개만 선택할 필요가 있는 경우는, 다음과 같이 시험해 주세요.

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 , (AUTO_INCREMENT(「」), 1행 반환)
를 위한 : FLOAT
또는 ('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

현재 문제는 apacement_table_row_count를 해결하는 것입니다.테이블 사이즈를 알면 하드코드로 테이블 사이즈를 만들 수 있습니다.이것이 가장 빨리 실행되지만, 분명히 이것은 이상적이지 않습니다.Myisam을 사용한다면 테이블 카운트를 얻는 것이 매우 효율적입니다.innodb를 사용하고 있기 때문에 간단한 카운트+선택만 하고 있습니다.이 경우 다음과 같습니다.

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()보다 훨씬 빠릅니다.

주의: 쿼리 로직이 허용하는 경우 조인 조작 전에 가능한 한 빨리 랜덤 선택을 수행하십시오.

UUID(버전 4) 또는 기타 랜덤 값을 가진 컬럼을 고유 인덱스(또는 프라이머리 키)로 추가하는 것이 좋습니다.

그런 다음 쿼리 시 랜덤 값을 생성하고 생성된 값보다 큰 행을 랜덤 열로 정렬하여 선택할 수 있습니다.

예상 행 수보다 적은 행을 수신한 경우 greater than 절 없이 쿼리를 반복합니다(결과 집합의 "시작"에서 행을 선택합니다).

uuid = generateUUIDV4()

select * from foo
where uuid > :uuid
order by uuid
limit 42

if count(results) < 42 {
  select * from foo
  order by uuid
  limit :remainingResultsRequired
}

function getRandomRow(){
    $id = rand(0,NUM_OF_ROWS_OR_CLOSE_TO_IT);
    $res = getRowById($id);
    if(!empty($res))
    return $res;
    return getRandomRow();
}

//rowid is a key on table
function getRowById($rowid=false){

   return db select from table where rowid = $rowid; 
}

언급URL : https://stackoverflow.com/questions/1244555/how-can-i-optimize-mysqls-order-by-rand-function

반응형

'source' 카테고리의 다른 글

불변형 및 가변형  (0) 2022.10.06
mysql.sock을 통한 고부하 시 PHP/MYSQL 연결 실패  (0) 2022.10.06
npm start 실행 시 Start 스크립트 누락 오류  (0) 2022.09.23
Java에 auto type이 있나요?  (0) 2022.09.23
Keras LSTM의 개요  (0) 2022.09.23

'source'의 다른글

  • 현재글MySQL의 ORDER BY RAND() 함수를 최적화하려면 어떻게 해야 합니까?

관련글

댓글

티스토리툴바