MySQL 랜덤으로 열을 뽑을 때, order by rand() 보다 빠른 방법
컨텐츠 정보
- 10,749 조회
- 0 추천
- 목록
본문
출처 : 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 확율연산만 넣는 방법
01.
SELECT
rnd_id, rnd_value
02.
FROM
(
03.
SELECT
@cnt :=
COUNT
(*) + 1,
04.
@lim := 1005.
FROM
t_random_innodb
06.
) vars07.
STRAIGHT_JOIN08.
(09.
SELECT
r.*,
10.
@lim := @lim - 111.
FROM
t_random_innodb r
12.
WHERE
(@cnt := @cnt - 1)
13.
AND
RAND(20090301) < @lim / @cnt
14.
) i
이 방법의 핵심은 아래쪽에 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초가 걸립니다^^
관련자료
-
링크