MySQL에서 Random row 가져오기

MySQL 테이블에 랜덤한 row를 가져오기 위해서 아래와 같은 질의문을 쓸 수 있습니다. 

SELECT name FROM random ORDER BY RAND() LIMIT 1;

하지만 row수가 몇만건이 되면 굉장히 느려지는 방법입니다. 왜냐하면 실제로 EXPLAIN으로 검사해 보면 type이 ALL이기 때문입니다. 전체 row를 스켄하는 방법으로 index보다도 못해서 굉장히 느려집니다. 정말 비추되는 방법이지요. 

그럼 아래처럼 생각할 수 있습니다. 

SELECT name FROM random WHERE id = (SELECT CEIL(RAND() * (SELECT MAX(id) FROM random));

이 코드도 문제입니다. EXPLAIN 검사해보면 type이 ALL입니다. 문제는 WHERE절 뒤에 id = (SELECT....)부분이죠. 

결국 최적화를 위해 JOIN을 사용할 수 있습니다. 

SELECT name FROM random JOIN

  (SELECT CEIL(RAND() *

     (SELECT MAX(id)

     FROM random)) AS id

   ) AS r2

USING (id);

이걸 검사해보면 type이 system, const로 나옵니다. 이렇게 하면 JOIN하는 테이블은 상수(const) 임시테이블로서 나오기 때문에 1개의 row만 비교하는 형태가 되어 system이 되는 겁니다. 

단지 주의할 사항은 MAX(id)라는 것은 Count(*)와는 다르다는 점입니다. 만약 row가 중간에 삭제되는 경우가 아니라면 id가 PK라는 조건내에서 서로 같은 값이 되겠지만 삭제된다면 서로 다른 값입니다. 그뿐 아니라 삭제가 되면 아에 random row를 찾지 못하는 경우도 발생합니다 .

글쓴이 : 지돌스타 (http://blog.jidolstar.com/861)