MySQL 랜덤으로 열을 뽑을 때, order by rand() 보다 빠른 방법

출처 : https://blog.naver.com/nwind7/100193752809

보통 랜덤으로 목록을 뽑을 때

select rnd_id, rnd_value from t_random_innodb order by rand() limit 10;

이런식으로 쿼리하시죠? 이게 간단해서 좋기는 한데 대상 컬럼수가 1만줄이 넘으면 느린걸 느낍니다. 10만 줄 쯤 되면 테이블 구조에 따라 1초 넘어가는 경우도 있고.. 해서 다른 방법을 찾아보았습니다.

1. 조건절에 random 확율연산만 넣는 방법

이 방법의 핵심은 아래쪽에 select 절에 있는 @lim := @lim -1 와 where 조건절에 있는 @cnt :=@cnt-1 입니다.

select 절에 있는 저 문장은 select 하는 결과레코드가 있을 때만 동작합니다. 즉 레코드가 하나 나타날때는 @lim 은 9로 줄고... 해서 10개의 레코드가 보이면 @lim은 0이 되죠.

아래 @lim/@cnt 의 random 확율에서 @lim 이 0이 되면 확률은 0이 되겠죠? 결과 레코드를 하나 뽑을 때마다 결과로 뽑힐 확률을 낮추는 효과가 있습니다.

where 조건절에 있는 @cnt :=@cnt-1  문장은 모든 대상열에 대해서 동작합니다. 즉 레코드 수가 1000개라면 처음에는 @cnt가 1000이 되고 마지막 레코드에서는 1이 됩니다. 커서가 뒤로 갈 수록 뽑힐 확률을 높여주는 역할을 합니다. 

이 방법의 장점은 order by rand() 보다 빠르다는 것입니다.  어떤 경우에든 목표로 하는 결과값이 다

나오며, 그 확률은 랜덤확률에 거의 근접합니다. (약간 차이가 있지만 무시할 수 있습니다)

500만건이 있는 테이블에서 10건을 뽑아내는데 order by rand()로 4.4초가 걸리는 쿼리가 이 방법을 쓰면 3.2초에 끝나게 됩니다.

단점도 있습니다. PK가 있는 테이블은 PK순서대로 결과값이 나옵니다. 만일 11개의 대상열이 있는데 10개를 뽑는다면? 거의 같은 목록이 똑같은 순서로 계속 나오게 됩니다. 단, 이건 * 로 뽑을때의 이야기이고, pk만 뽑아낼때는 랜덤하게 뽑아집니다.

또 CPU자원을 많이 사용합니다. 대상열이 100만줄 있으면 저 변수 변경, rand() 연산 및 비교연산을 100만번 반복해야 합니다...

상대적으로 innodb 보다는 myisam에서 빠른 쿼리입니다.

http://explainextended.com/2009/03/01/selecting-random-rows/ 요기서 본 내용입니다.

2. 임시테이블을 만들어 원하는 값을 insert 하는 방법

자동증가하는 PK가 있을 때 유용한 방법입니다.  프로시저로 보여드릴께요.

DELIMITER $$

CREATE PROCEDURE `pSelect_Random_Test`(
     In in_RecordCnt  int
)
BEGIN
    declare v_max_idx int default 0;
    declare v_idx int default 0;
    declare v_cnt int default 0;
    declare v_t_cnt int default 0;

-- 임시테이블을 만들고

    DROP TEMPORARY TABLE IF EXISTS tmp_random; 

    create temporary table tmp_random (rnd_id int not null, rnd_value int not null, primary key (rnd_id) ) engine=memory;

-- key의 max값을 뽑은 다음

    select max(rnd_id) from t_random_innodb into v_max_idx;

 loop_me : LOOP

    -- 임시테이블에 원하는 레코드가 들어갔거나 원하는 레코드에 대해 10배수의 insert 시도를 했을 경우 루프에서 벋어난다.
    select count(*) from tmp_random into v_cnt;

    if (v_t_cnt>=in_RecordCnt*10) or (v_cnt >= in_RecordCnt) then
       LEAVE loop_me;
    end if;

    -- 최대 key값에 random 값을 곱해서 최대키값보다 작은 어떤 임의의 키값을 하나 임시테이블에 입력한다.

    set v_idx = v_max_idx * rand() + 1;
    insert into tmp_random (rnd_id, rnd_value)
       select rnd_id, rnd_value from t_random_innodb where rnd_id <= v_idx order by id desc limit 1 on duplicate key update rnd_value=values(rnd_value);

  set v_t_cnt = v_t_cnt + 1;
 END LOOP loop_me;

 -- 임시테이블에 들어있는 값을 정렬 random 으로 쿼리한다.

 select * from tmp_random order by rand();
END $$
DELIMITER ; 

insert 건수가 많을 수록 2번이 1번보다 훨~씬 빨라진다.

500만건 기준으로 방법 1은 3.2초가 걸리는 쿼리가 방법 2로 하면 0.1초가 걸립니다^^