1. HOME
  2. [종료]코어 오라클 데이터베이스 스터디
  3. 7차 코어 오라클 데이터베이스 스터디
  4. 16장. 임의의 선택

16장. 임의의 선택 (by xsoft) [2011.12.14]

목차

XVI. 임의의 선택

1. 목표 - 샘플 행 가져오기

2. 안티패턴 - 데이터를 임의로 정렬하기

3. 안티패턴 인식 방법

4. 안티패턴 사용이 합당한 경우

5. 테이블 전체 정렬 피하기

XVI. 임의의 선택

  • 광고를 표시하는 웹 어플리케이션 개발 시, 매번 무작위로 광고를 선택해 모든 광고주의 광고가 균등한 기회로 보이게 하고 사용자는 같은 광고를 반복적으로 보이지 않게 해야 함.
  • 런칭 후 며칠동안은 잘 돌아갔지만, 시간이 지날수록 웹사이트가 느려지는 현상이 발생됨.
  • 웹 사이트 사용자와 광고주를 모두 잃기 전에, 무작위로 광고를 선택하는 쿼리를 어떻게 개선해야 하나?

1. 목표 - 샘플 행 가져오기

  • 얼마나 자주 임의의 결과를 리턴하는 SQL 쿼리가 필요한지 알 수 있는 사례
    • 광고나 강조하고 싶은 뉴스 스토리와 같은 순환 컨텐츠를 표시할 때
    • 레코드의 부분집합을 조사할 때
    • 걸려오는 전화를 가용한 오퍼레이터에 할당할 때
    • 테스트 데이터를 생성할 때
  • 전체 데이터 집합을 애플리케이션으로 가져와 샘플을 고르는 것보다, DB에 샘플을 요청하는 것이 좋음.
  • 목표는 임의의 샘플 데이터만 리턴하는 효율적인 SQL 쿼리를 작성하는 것.

2. 안티패턴 - 데이터를 임의로 정렬하기

  • 쿼리에서 임의의 행을 고르는 가장 흔한 SQL 기법은 RANDOM 함수를 이용하여 첫 행을 고르는 것임.
  • 샘플 데이터 생성(MySQL 기준)

SELECT *
FROM   BUGS
ORDER BY RAND() LIMIT 1;
  • 이 방법은 인기 있는 방법이지만, 전체 집합을 정렬하는 비용이 많이 들고,
    인덱스를 사용할 수 없기 때문에 데이터가 늘어날 경우 악성 SQL로 변함.

3. 안티패턴 인식 방법

  • 안티패턴 인식 방법
    • "SQL에서 임의의 행을 리턴하는 건 정말 느려." 임의의 샘플을 선택하기 위한 쿼리는, 개발 기간이나 테스트 기간이 얼마 안 되는 샘플을 데이터에서 실행하면 잘 동작함. 그러나 실 환경에서 데이터가 늘어나기 시작하면 점점 느려짐. 서버를 튜닝하고 인덱스를 달고, 캐싱을 늘려서 해결할 수 있는 문제가 아님.
    • "애플리케이션에서 사용할 메모리를 어떻게 늘릴 수 있지? 모든 행을 가져와서 그 중 임의로 한 개를 선택해야 해" 모든 데이터를 애플리케이션으로 가져올 필요가 없어야 한다. 이렇게 하는 것은 대부분 낭비다. 게다가 데이터베이스는 애플리케이션 메모리에서 처리할 수 없을 정도로 커지는 게 보통이다.
    • "어떤 항목이 다른 것보다 좀 더 자주 나오는 것 같아 보이지 않니? 랜덤이 별로 랜덤 같지 않은데?" 난수가 데이터베이스의 PK 값의 간격과 동기화되지 않았다.

4. 안티패턴 사용이 합당한 경우

  • 임의로 정렬하는 방법의 비효율성은 데이터 집합이 적을 때는 참을만함.
  • 예를들어, 미국의 50개 주 중 임의로 하나를 선택하는 경우며, 이런 식으로 데이터가 늘어날 일이 거의 없는 경우는 상관없음.

5. 테이블 전체 정렬 피하기

  • 임의로 정렬하는 방법은 테이블 스캔과 비용이 많이 드는 수동정렬을 수반함.
  • 이를 해결하기 위해서, 아래의 5가지 방법으로 해결 모색 가능

1) 1과 MAX 사이에서 임의의 키 값 고르기


SELECT B1.*
FROM   BUGS AS B1
JOIN   (SELECT CEIL(RAND() * (SELECT MAX(BUG_ID)
                              FROM   BUGS)) AS BUG_ID) AS B2
WHERE  B1.BUG_ID >= B2.BUG_ID
ORDER  BY B1.BUG_ID LIMIT 1;
  • 이 방법은 PK 값이 1부터 시작해 연속적으로 존재한다고 가정함.
  • 즉, 1과 최대값 사이에 빈 값이 없다는 뜻이며, 빈 값이 존재할 경우, 임의로 선택한 값이 테이블의 행과 매치되지 않을 수 있음.
  • 키가 1과 최대값 사이의 모든 값을 사용하는 경우에는 이 방법 사용할 수 있음.

2) 다음으로 큰 키 값 고르기


SELECT B1.*
FROM   BUGS AS B1
JOIN   (SELECT CEIL(RAND() * (SELECT MAX(BUG_ID)
                              FROM   BUGS)) AS RAND_ID) AS B2 ON (B1.BUG_ID = B2.RAND_ID);
  • 이 방법은, 1과 최대값 사이에 빈틈이 있는 경우 사용 가능.
  • 임의의 수가 키 값과 대응되지 않은 경우에 대한 문제를 해결하지만, 빈틈 바로 앞에 있는 키 값이 더 자주 선택되는 단점 존재함. 그 이유는, 랜덤 값은 거의 균일하게 분포하지만, BUG_ID 값은 그렇지 않기 때문.
  • 빈틈이 드물게 존재하고 모든 키 값이 동일한 빈도로 선택되는 것이 중요하지 않을 때 이 방법 사용 가능

3) 모든 키 값의 목록을 구한 다음, 임의로 하나 고르기

  • 결과 집합의 PK 값 하나를 고르는 애플리케이션 코드를 사용할 수 있으며, 이후 PK 값을 이용해 데이터베이스에서 전체 행을 조회함.
  • 아래는 PHP로 구현한 내용

<?php
$bug_id_list = $pdo->query("SELECT bug_id FROM Bugs")->fetchAll();

$rand = random( count($bug_id_list) );
$rand_bug_id = $bug_id_list[$rand]["bug_id"];

$stmt = $pdo->prepare("SELECT * FROM Bugs WHERE bug_id = ?");
$stmt->execute( array($rand_bug_id) );
$rand_bug = $stmt->fetch();
  • 이 방법은 테이블 정렬을 피하고, 각 키 값을 거의 같은 확률로 선택하지만 다른 비용이 추가됨
    • 데이터베이스로부터 모든 BUG_ID 값을 불러올 때 리스트 크기가 엄청나게 클 수 있음. 심지어 애플리케이션 메모리 자원을 넘어서 다음과 같은 에러 발생 가능
      • Fatal error : Allowed memory SIZE OF 16777216 bytes exhausted
    • 쿼리를 두 번 해야 한다. 한 번은 PK의 목록을 생성하기 위해, 두 번째는 임의의 행을 가져오기 위해서다. 쿼리가 지나치게 복잡하고 비용이 많이 든다면, 이게 문제가 될 수 있음.

4) 오프셋을 이용해 임의로 고르기

  • 앞에서 살펴본 방법에 존재하는 문제를 피하는 다른 방법이 있다. 데이터 집합에서 행의 개수를 세고 0과 행 개수 사이의 임의의 수를 고른 다음, 데이터 집합을 쿼리할 때 이 수를 오프셋으로 사용하는 것

<?php

$rand = "SELECT ROUND(RAND() * (SELECT COUNT(*) FROM Bugs))";
$offset = $pdo->query($rand)->fetch(PDO::FETCH_ASSOC);

$sql = "SELECT * FROM Bugs LIMIT 1 OFFSET :offset";
$stmt = $pdo->prepare($sql);
$stmt->execute( $offset );
$rand_bug = $stmt->fetch();
  • 이 방법은 표준이 아닌 LIMIT 절에 의존하고 있다. LIMIT절은 MySQL, PostgreSQL, SQLite에서 지원함.
  • 다른 대안은 Oracle, MSSQL, DB2에서 동작하는 ROW_NUMBER() 분석함수를 사용하는 것.

<?php
$rand = "SELECT 1 + MOD(ABS(dbms_random.random()),
  (SELECT COUNT(*) FROM Bugs)) AS offset FROM dual";
$offset = $pdo->query($rand)->fetch(PDO::FETCH_ASSOC);

$sql = "WITH NumberedBugs AS (
  SELECT b.*, ROW_NUMBER() OVER (ORDER BY bug_id) AS RN FROM Bugs b
) SELECT * FROM NumberedBugs WHERE RN = :offset";
$stmt = $pdo->prepare($sql);
$stmt->execute( $offset );
$rand_bug = $stmt->fetch();

5) 밴더 종속적인 방법

  • Oracle은 약간 다르게 SAMPLE 절을 사용함. 예를 들어, 다음 쿼리는 테이블에서 1%의 행을 가져온 다음, 임의의 순서로 정렬한 후 한 행만 가져옴.

SELECT *
FROM   (SELECT *
        FROM   BUGS SAMPLE(50)
        ORDER BY DBMS_RANDOM.VALUE)
WHERE  ROWNUM = 1

어떤 쿼리든 최적화할 수 없다. 이 경우에는 다른 접근방법을 취해야 한다.

문서에 대하여

  • 최초작성자 : ~xsoft
  • 최초작성일 : 2011년 12월 17일
  • 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
  • {*}이 문서의 내용은 인사이트(insight) 에서 출간한 'SQL AntiPatterns : 개발자가 알아야 할 25가지 SQL 함정과 해법'를 참고하였습니다.*
  1. HOME
  2. [종료]코어 오라클 데이터베이스 스터디
  3. 7차 코어 오라클 데이터베이스 스터디
  4. 16장. 임의의 선택