• 티스토리 홈
  • 프로필사진
    31514
  • 방명록
  • 공지사항
  • 태그
  • 블로그 관리
  • 글 작성
31514
  • 프로필사진
    31514
    • 분류 전체보기 (109)
      • Book (66)
        • Learning SQL (9)
        • SQL 레벨업 (8)
        • 견고한 데이터 엔지니어링 (5)
        • 운영체제 (2)
        • 스파크 완벽 가이드 (9)
        • 파이썬 코딩의 기술 (29)
        • 분산 컴퓨팅 (4)
      • 개발 (25)
      • 기타 (10)
        • 출퇴근 공부 간단 정리 (7)
      • ELK (6)
  • 방문자 수
    • 전체:
    • 오늘:
    • 어제:
  • 최근 댓글
      등록된 댓글이 없습니다.
    • 최근 공지
      • 31514의 이전 블로그는 여기로!
      등록된 공지가 없습니다.
    # Home
    # 공지사항
    #
    # 태그
    # 검색결과
    # 방명록
    • 7장 - 서브쿼리
      2024년 09월 25일
      • 31514
      • 작성자
      • 2024.09.25.:59
      • 테이블 : 영속적인 데이터를 저장
      • 뷰 : 영속적이지만 데이터는 저장하지 않음. 따라서 접근할 때마다 SELECT 구문이 실행됨
      • 서브쿼리 : 비영속적인 생존 기간이 SQL 구문 실행 중으로 한정

      서브쿼리의 문제점

      서브쿼리의 성능적 문제는 결과적으로 서브쿼리가 실체적인 데이터를 저장하고 있지 않다는 점에서 기인한다.

      이에 따라 다음과 같이 세 가지 문제가 발생한다.

       

      <연산 비용 추가>

      실체적인 데이터를 저장하고 있지 않은 서브쿼리에 접근할 때마다 SELECT 구문을 실행해서 데이터를 만들어야 한다는 뜻이다.

      따라서 구문 실행에 발생하는 비용이 추가된다.

       

      <데이터 I/O 비용 발생>

      SELECT 구문의 연산 결과를 사용하기 위해서 메모리에 저장해야 한다.

      만약 메모리의 공간이 충분하다면 상관 없지만, 아닐 경우 저장소에 저장할 때도 있다.

      이를 TEMP 탈락 현상의 일종이라고 말할 수 있는데, 저장소에 접근하는 경우 속도가 크게 저하된다.

       

      <최적화를 받을 수 없음>

      명시적인 제약 또는 인덱스가 설정되어 있는 테이블과 달리, 서브쿼리는 그러한 메타 정보가 존재하지 않는다.

      따라서 옵티마이저가 쿼리를 해석하기 위한 정보를 서브쿼리에서 얻을 수 없다.

       

      <결론>

      서브쿼리는 유연성으로 인해 코딩할 때 편리하지만, 복잡한 연산을 수행하거나 결과 크기가 큰 서브쿼리를 사용할 때는 성능 리스크를 고려해야 한다.

       

      서브쿼리 의존증

      -- 서브쿼리 예시
      -- 고객별 최소 순번 레코드를 구하는 쿼리
      
      select r1.cust_id, r1.seq, r1.price
      from receipts r1
      inner join (select cust_id, min(seq) min_seq
                  from receipts
                  group by cust_id) r2
            on r1.cust_id = r2.cust_id
            and r1.seq = r2.min_seq;

      위 쿼리는 가독성이 좋지 않고 성능을 저하시키는 4가지 요인이 있다.

      1. 서브쿼리를 메모리 또는 디스크에 확보하므로 오버헤드가 발생한다.
      2. 서브쿼리는 인덱스 또는 제약 정보를 가지지 않기 때문에 최적화되지 못한다.
      3. 위 쿼리는 결합을 필요로 하기 때문에 비용이 높고 실행 계획 변동 리스크가 발생한다.
      4. Receipts 테이블에 스캔이 두 번 필요하다.
      -- 상관 서브쿼리로 변경
      select cust_id, seq, price
      from receipts r1
      where seq = (select min(seq)
                   from receipts r2
                   where r1.cust_id = r2_cust_id);

      상관 서브쿼리로 변경하면 결합이 사라지고 R2 접근에 기본 키의 인덱스 온리 스캔을 사용할 가능성도 있다.

      하지만 그렇다고 해도 Receipts 테이블에 1회 접근하고 기본 키의 인덱스에 1회 접근한다.

      그럼 어떻게 더 좋은 코드를 작성할까?

      일단 Receipts 테이블에 대한 접근을 1회로 줄일 것이다.

      SQL 튜닝에서 가장 중요한 부분이 I/O를 줄이는 것인데, 이를 위해 윈도우 함수를 사용할 수 있다.

      -- row_number 윈도우 함수를 사용한 쿼리
      select cust_id, seq, price
      from (select cust_id, seq, price,
              row_number() over (partition by cust_id order by seq) row_seq
            from receipts) work
      where work.row_seq = 1;

      윈도우 함수에서 정렬을 사용하지만, 다른 쿼리에서도 MIN 함수를 사용했었으므로 큰 비용 차이가 발생하지 않는다.

      리스크 관리

      윈도우 함수를 사용한 쿼리가 얼마나 성능이 좋은지는, 사용하는 DBMS 또는 데이터베이스 서버의 성능, 매개 변수나 인덱스와 같은 환경 요인에 의해 크게 바뀔 수 있다.

      하지만 저장소의 I/O 양을 감소시키는 것이 SQL 튜닝의 가장 기본 원칙이는 점에는 변함이 없다.

       

      결합을 사용하면 실행 계획에 변동이 생길 수 있고, 상관 서브쿼리를 사용하면 ~

      하지만 우리는 실행 계획이 단순할수록 성능이 안정적인 사실을 알아야 한다.

      정말 서브쿼리 사용이 더 나은 경우는 없을까?

      결합할 때는 최대한 결합 대상 레코드 수를 줄이는 것이 중요하다.

      그런데 옵티마이저가 이러한 것을 잘 판별하지 못할 때는, 사람이 직접 명시해주면 성능적으로 좋은 결과를 얻을 수 있다.

       

      예를 들어, 두 개의 테이블을 존재할 때 다음과 같은 선택지가 있다고 하자.

      1. 결합부터 하고 집약하는 방법
      2. 집약부터 하고 결합하는 방법

      1번의 경우 2번에 비해 I/O 비용이 더 발생하고, 집약의 비용이 더 적게 소모된다.

      하지만 TEMP 탈락이 발생하지 않는다면 집약의 비용은 괜찮은 트레이드오프가 될 수 있다.

       

      따라서 다음과 같이 서브쿼리를 사용하여 먼저 집약을 하고, 결합하는 방법을 추천한다.

      select c.co_cd, c.district, sum_emp
      from companies c
      inner join (select co_cd,
                    sum(emp_nbr) sum_emp
                  from shops
                  where main_flg = 'Y'
                  group by co_cd) csum
      on c.co_cd = csum.co_cd;

      'Book > SQL 레벨업' 카테고리의 다른 글

      9장 - 갱신과 데이터 모델  (2) 2024.09.30
      8장 - SQL의 순서  (0) 2024.09.27
      6장 - 결합  (0) 2024.09.24
      5장 - 반복문  (0) 2024.09.23
      4장 - 집약과 자르기  (0) 2024.09.20
      다음글
      다음 글이 없습니다.
      이전글
      이전 글이 없습니다.
      댓글
    조회된 결과가 없습니다.
    스킨 업데이트 안내
    현재 이용하고 계신 스킨의 버전보다 더 높은 최신 버전이 감지 되었습니다. 최신버전 스킨 파일을 다운로드 받을 수 있는 페이지로 이동하시겠습니까?
    ("아니오" 를 선택할 시 30일 동안 최신 버전이 감지되어도 모달 창이 표시되지 않습니다.)
    목차
    표시할 목차가 없습니다.
      • 안녕하세요
      • 감사해요
      • 잘있어요

      티스토리툴바