[최종프로젝트] MySQL에서 ORDER BY RAND() 사용 시 성능 문제 및 해결 전략 번역 및 요약

https://jan.kneschke.de/projects/mysql/order-by-rand/

 

요약 및 정리 

 

 

MySQL에서 ORDER BY RAND()를 사용할 때 발생하는 성능 저하 문제를 근본적으로 해결하는 실용적인 데이터베이스 튜닝 가이드입니다. 이 콘텐츠는 대량의 데이터에서 무작위 행을 추출할 때 ORDER BY RAND() 대신 MAX(id)와 RAND()를 조합하여 쿼리 속도를 획기적으로 개선하는 구체적인 SQL 기법과, 데이터 삭제 시 발생하는 ID 공백(Holes) 문제를 관리하는 트리거 설정 방법까지 상세히 다룹니다. 데이터베이스 성능 최적화에 관심 있는 개발자라면, 이 글을 통해 실제 적용 가능한 고성능 랜덤 추출 전략을 즉시 습득할 수 있습니다.

 

1. MySQL에서 ORDER BY RAND() 사용 시 성능 문제 및 해결 전략

이 문서는 MySQL에서 대량의 데이터에서 무작위 행을 추출할 때 발생하는 ORDER BY RAND()의 성능 저하 문제를 해결하고, 고성능 랜덤 추출 전략을 제시하는 타임라인 노트입니다.

1.1. ORDER BY RAND()의 기본 사용법과 한계

1. ORDER BY RAND()의 기본 사용: MySQL 매뉴얼에 따르면, ORDER BY RAND()와 LIMIT 1을 사용하여 무작위 행을 추출할 수 있습니다.
   1. 예시 쿼리: SELECT name FROM random ORDER BY RAND() LIMIT 1;

1. 성능 문제 발생 시점: 이 방법은 데이터가 적을 때(예: 1,000개 행)는 잘 작동하지만, 행 수가 증가하면(예: 10,000개 행) 정렬(sorting)에 대한 오버헤드가 중요해집니다.
   1. 문제의 핵심은 정렬을 수행한 후 거의 모든 행을 버린다는 점입니다.

1. 대안의 전제 조건: 정렬 없이 랜덤 추출을 수행하기 위한 대안은 숫자 형태의 기본 키(ID)가 존재한다는 것을 가정합니다.
   1. 첫 번째 예시에서는 ID가 1부터 시작하며, 최대값까지 ID 공백(Holes)이 없다고 가정합니다.

1.2. 애플리케이션에서 작업 분리하기 (ID 사전 계산)

1. 첫 번째 아이디어: 전체 작업을 애플리케이션에서 ID를 미리 계산하여 단순화할 수 있습니다.
   1. 1단계: 데이터베이스에서 최대 ID를 조회합니다: SELECT MAX(id) FROM random;
   2. 2단계: 애플리케이션에서 1과 MAX(id) 사이의 무작위 숫자를 생성합니다.
   3. 3단계: 생성된 무작위 ID를 데이터베이스에 전달하여 해당 행을 검색합니다: SELECT name FROM random WHERE id = [random_id];

1. 성능 이점: 이 방식은 첫 번째 SELECT가 최적화되어 무시되며, 두 번째 SELECT는 상수 값에 대한 eq_ref 조회가 되어 매우 빠릅니다.

1.3. 데이터베이스 내에서 랜덤 ID 생성하기

1. 데이터베이스 내 작업 시도: 애플리케이션이 아닌 데이터베이스 내에서 랜덤 ID를 생성할 수 있는지 확인합니다.

1. 랜덤 ID 생성 쿼리: MAX(id)를 사용하여 랜덤 ID를 생성하는 시도입니다.
   1. 초기 시도 (결과가 double): SELECT RAND() * MAX(id) FROM random;
   2. 정수(int)를 얻기 위한 개선 시도: SELECT CEIL(RAND() * MAX(id)) FROM random;

1. 성능 분석 (EXPLAIN):
   1. 위 쿼리를 EXPLAIN으로 분석하면 인덱스 스캔(index)이 발생하여 MAX()에 대한 최적화 이점을 잃는 것으로 나타납니다.
   2. 성능 회복: 서브쿼리를 사용하여 MAX(id)를 먼저 계산하면, EXPLAIN 결과 No tables used로 표시되며 성능이 회복됩니다. SELECT CEIL(RAND() * (SELECT MAX(id) FROM random));

1. 행 검색 시도 및 문제점: 생성된 랜덤 ID를 사용하여 행을 검색하는 시도입니다.
   1. 쿼리: SELECT name FROM random WHERE id = (SELECT CEIL(RAND() * (SELECT MAX(id) FROM random));
   2. 심각한 성능 저하: 이 방식은 가장 잘못된 방법으로 지적됩니다. WHERE 절의 서브쿼리가 외부 SELECT가 가져오는 모든 행에 대해 실행되기 때문에 성능이 매우 나빠집니다.

1. 성능 최적화된 해결책: 랜덤 ID가 한 번만 생성되도록 보장해야 합니다.
   1. 최적화된 쿼리: JOIN을 사용하여 랜덤 ID를 상수처럼 처리합니다.

SELECT name FROM random JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM random)) AS id ) AS r2 USING (id);

1. 결과: 내부 SELECT가 상수 TEMPORARY 테이블을 생성하고, JOIN은 단 하나의 행만 선택하므로 완벽합니다. 정렬이 없고, 쿼리의 대부분이 최적화됩니다.

1.4. ID 공백(Holes)이 있을 경우의 일반화된 해결책

1. 공백 문제 도입: DELETE 작업 등으로 인해 ID에 공백이 발생했을 때를 대비하여 이전 해결책을 일반화합니다.

1. 공백 처리 쿼리: = 대신 >=를 사용하여 공백을 처리하고 CEIL을 제거합니다.
   1. 쿼리: SELECT name FROM random AS r1 JOIN (SELECT (RAND() * (SELECT MAX(id) FROM random)) AS id) AS r2 WHERE r1.id >= r2.id ORDER BY r1.id ASC LIMIT 1;
   2. 작동 방식: 이 쿼리는 랜덤 값보다 크거나 같은 모든 ID를 조인하고, 직접 일치하는 항목이 없으면 바로 다음 이웃 항목을 선택합니다. LIMIT 1 덕분에 한 행만 찾으면 즉시 중단되며, ORDER BY id ASC를 통해 인덱스 순서대로 읽습니다.

1. 균등 분포(Equal Distribution) 문제: ID 분포가 균등하지 않으면 행 선택의 무작위성도 균등하지 않게 됩니다.
   1. 예시 데이터(holes 테이블)에서 ID 9~15가 모두 다음으로 큰 ID인 16으로 매핑되는 문제가 발생합니다.

1. 해결책: 매핑 테이블 사용: 데이터가 비교적 상수일 경우, 매핑 테이블을 만들어 행 번호(hole-free)를 실제 ID에 매핑할 수 있습니다.
   1. 매핑 테이블 생성: holes_map 테이블을 생성하고, row_id (구멍 없는 순번)와 random_id (실제 ID)를 매핑합니다.
      • 예시: row_id 3은 실제 random_id 4에 매핑됩니다.

1. 매핑 테이블을 이용한 랜덤 쿼리: 구멍 없는 row_id를 기반으로 랜덤 쿼리를 실행합니다.
   1. 쿼리: SELECT name FROM holes JOIN (SELECT r1.random_id FROM holes_map AS r1 JOIN (SELECT (RAND() * (SELECT MAX(row_id) FROM holes_map)) AS row_id) AS r2 WHERE r1.row_id >= r2.row_id ORDER BY r1.row_id ASC LIMIT 1) as rows ON (id = random_id);

1. 결과: 1,000회 페치 후, 분포가 다시 균등해지는 것을 확인할 수 있습니다.

1.5. ID 공백 유지 관리 (트리거 사용)

1. 목표: r2 테이블(실제 데이터)이 변경될 때마다 r2_equi_dist 테이블(매핑 테이블)을 자동으로 업데이트하여 공백 없는 구조를 유지합니다.

1. INSERT 시 트리거 (tai_r2):
   1. AFTER INSERT 트리거를 생성합니다.
   2. 새 레코드가 삽입되면, r2_equi_dist의 최대 ID보다 1 큰 ID를 할당하여 삽입합니다.

1. DELETE 시 트리거 (tad_r2):
   1. AFTER DELETE 트리거를 생성합니다.
   2. 삭제된 OLD.id에 해당하는 r2_id를 r2_equi_dist에서 삭제합니다.
   3. 삭제된 ID보다 큰 모든 r2_id의 id 값을 1씩 감소시켜 공백을 메웁니다.

1. UPDATE 시 트리거 (tau_r2):
   1. AFTER UPDATE 트리거를 생성합니다.
   2. r2_equi_dist 테이블에서 r2_id를 OLD.id에서 NEW.id로 업데이트하여 외래 키 제약을 유지합니다.

1.6. 한 번에 여러 행 가져오기

여러 행을 한 번에 가져오는 방법은 다음과 같습니다.

1. 방법 1: 쿼리를 여러 번 실행합니다.
2. 방법 2: 쿼리를 실행하고 결과를 임시 테이블에 저장하는 저장 프로시저를 작성합니다.
3. 방법 3: UNION을 사용합니다.

저장 프로시저 예시 (get_rands):

1. cnt (가져올 개수)를 입력받습니다.
2. 임시 테이블 rands를 생성합니다.
3. LOOP 구조를 사용하여 cnt가 0이 될 때까지 1.3절에서 최적화된 랜덤 추출 쿼리를 반복 실행하고 결과를 rands 테이블에 삽입합니다.
4. 개선 필요 사항: 이 예시에는 동적 SQL 사용 및 중복 결과를 제거하기 위한 UNIQUE 인덱스 및 위반 처리 로직 수정이 필요합니다.

1.7. 성능 비교 분석

성능 비교를 위해 세 가지 쿼리 유형을 정의하고 100개부터 1,000,000개 행까지 1,000회씩 실행하여 비교했습니다.

1. Q1: ORDER BY RAND()
2. Q2: RAND() * MAX(ID) (애플리케이션 방식과 유사)
3. Q3: RAND() * MAX(ID) + ORDER BY ID (DB 내 최적화 방식)

예상 비용: Q1은 $N \cdot \log_2(N)$의 비용이 예상되며, Q2와 Q3는 거의 상수 비용이 예상됩니다.

실행 결과 (시간):

행 수 (N)	Q1 (ORDER BY RAND())	Q2 (RAND() * MAX(ID))	Q3 (RAND() * MAX(ID) + ORDER BY ID)
100	0:00.718s	0:00.519s	0:00.570s
1,000	0:02.092s	0:00.607s	0:00.607s
10,000	0:18.684s	0:00.614s	0:00.614s
100,000	2:59.081s	0:00.628s	0:00.628s
1,000,000	58:20.000s	0:00.637s	0:00.637s

결론: 테이블에 100개 행만 있어도 순수한 ORDER BY RAND() (Q1)는 최적화된 쿼리(Q2, Q3)보다 성능이 뒤처지기 시작합니다.

• 추가 분석: 이러한 쿼리에 대한 더 자세한 분석은 [analyzing-complex-queries](/projects/mysql/analyzing-complex-queries)에서 확인할 수 있습니다.