mysql에서 무작위 값 추출 하기

예시) 응모권 신청 받아서 n명 당첨, 게임에서 친구 찾기에서 무작위 리스트가 필요할떄

order by rand()

동작 방식은 임의의 값을 각 레코드에 부여 하고 그 값으로 정렬을 한다

int형 pk(ai on), string col 1개로 구성되어 있고 row 수는 천만개

select * from table order by rand() limit 30;

쿼리의 실행 계획을 살펴 보면 아래와 같다

테이블 풀 스캔이 나오며 쿼리 코스트 비용을 보니 무시무시하다 😱

데이터의 수가 적거나 1회성 호출인 경우에는 써보겠으나 수가 많고 잦은

호출을 하는 경우에는 기피하는 게 좋을듯..

Using filesort

order by 절이 인덱스를 사용하지 못할 때 등장 한다 👀

조회된 레코드를 정렬용 메모리 버퍼에 복사해 다시 퀵 소트 알고리즘으로 정렬 한다

단순 인덱스를 타기 위한 방법?

select * from table as t1
inner join
(
select id from table order by rand() limit 30
) as t2
on t1.id = t2.id;

스택오버플로우에서 본 방법인데 여전히 filesort이고 풀 인덱스 스캔이여서 그런지

실행속도는 마찬가지로 매우 느리기 떄문에 큰 메리트가 없음

정렬 값을 미리 만들어두는 방법

책에 나온 방법을 그대로 실행해보았음

테이블의 인서트할 때 하나의 컬럼에 정렬 값(index 설정 ok)를 저장 한다

insert into table(col, rand_val) values(val, floor(rand() * 10000000));

set @rnd = floor(rand() * 10000000);

select * from table as t1 inner join
(
(select id from table where rand_val >= @rnd limit 30)
union all
(select id from table where rand_val < @rnd limit 30)
) as t2
on t1.id = t2.id;

인덱스로 조회된 결과를 합쳐서 나오기 때문에 만족스러운 결과가 나옴

그 외 방법?

정렬 값을 미리 만들지 않고 할 수 있는 방법을 조금 고민해 보았다

방식은 위와 비슷한데 pk가 (int형 ai)이고 레코드가 삭제되지 않는다는

전제 조건이여야 가능한듯 😂

set @rnd = floor(rand() * (select max(id) from test.table_1));

select * from (
(select * from table where id >= @rnd
limit 30)
union all
(select * from table where id <
@rnd limit 30)
) as table limit 30;

아직 못해본것

어플리케이션과 조금 나누어서 처리를 했을 때 어떤 게 더 빠른지 확인해볼것

위의 @rnd값을 먼저 어플리케이션에서 가지고 와서 이후 쿼리 실행 해보기

참조

• How does MySQL’s ORDER BY RAND() work?
• Real MySQL