SQL 최적화 기본 원리

인덱스 기본

인덱스 스캔 효율화

• 여러 개의 칼럼으로 이루어진 복합 칼럼 인덱스를 생성 시 각 칼럼의 순서에 따라 복합 칼럼 인덱스가 특정 SQL에 효율적일 수도 있고 비효율적일 수도 있음

• 먼저 오는 칼럼으로 데이터를 더 유일하게 찾아낼 수 있는 상황이 인덱스 효율이 좋은 상황
• 예) 시력 + 이름 + 학년반번호 < 이름 + 시력 + 학년반 번호 (이름으로 데이터를 좁혔을 때 더 적은 데이터를 골라낼 수 있기 때문에)

랜덤 액세스 최소화

• 인덱스 스캔 후 추가 정보를 가져오기 위해 리프 블록의 ROW ID를 가지고 테이블에서 해당 행을 찾기 위한 테이블 랜덤 액세스를 수행함

• 테이블 랜덤 액세스는 DBMS 성능 부하의 주 요인. 인덱스 스캔 효율이 좋다면 인덱스 스캔의 범위가 좁아져 테이블 랜덤 액세스 횟수가 적어지게 되고, SQL 문의 성능 향상이 있게 됨

• 인덱스 스캔 효율이 좋지 않다면 인덱스 스캔 범위가 넓어지며, 이로 인해 테이블 랜덤 액세스의 횟수가 많아지기에 SQL 문 성능이 저하됨

• 비용 기반 옵티마이저는 인덱스 스캔 시 비효율이 발생하여 과도한 테이블 랜덤 액세스가 일어날 것으로 예측되면, 차라리 테이블 풀 스캔을 하게 됨
• 테이블 풀 스캔은 전체 테이블의 데이터를 읽기는 하지만 랜덤 액세스는 발생하지 않음

Single Block I/O vs Multi Block I/O

버퍼 캐시

Single Block I/O

• 한 번의 I/O 콜을 통해 단 하나의 데이터 블록만을 읽어 메모리에 적재함

• 인덱스 스캔을 할 때는 각각의 블록을 읽을 때 단 1개의 블록씩만 읽게 됨

Multi Block I/O

• 테이블의 데이터를 읽을 때 한 번의 I/O 콜을 통해 테이블을 구성하는 블록과 인접하는 블록들을 한꺼번에 읽어 메모리에 적재하는 것

• 테이블 풀 스캔 시, 한번에 여러 개의 블록을 읽게 됨

테이블 풀 스캔과 인덱스 스캔

인덱스 스캔 종류

인덱스 범위 스캔(Index Range Scan)

• 인덱스를 이용하여 1건 이상의 데이터를 추출하는 방식. 인덱스 스캔으로 특정 범위를 스캔하면서 대상 레코드를 하나하나 리턴함

• B*Tree 인덱스의 가장 일반적이고 정상적인 형태의 액세스 방식

• 인덱스 루트에서 리프 블록까지 수직적으로 탐색한 후 필요한 범위(Range)만 스캔

인덱스 유일 스캔(Index Unique Scan)

인덱스 풀 스캔

SELECT /*+ INDEX(A IDX_TB_SUBWAY_STATN_TK_GFF_TMP_02) */ -- SQL INDEX 힌트로 특정 인덱스 타도록 유도
       /* SELECT.TB_SUBWAY_STATN_TK_GFF_TMP.003 */
       A.STD_YM
     , A.BEGIN_TIME
  FROM TB_SUBWAY_STATN_TK_GFF_TMP A
  WHERE A.BEGIN_TIME >= '0200'
  ORDER BY A.STD_YM
;

• 인덱스가 (STD_YM, BEGIN_TIME) 에 대해서 적용되어 있어, ORDER BY가 STD_YM에 대해 적용되는 순간 인덱스 전체를 읽어서 정렬작업을 회피하고 싶어함(옵티마이저의 특성)

• 인덱스 풀 스캔은 성능상 불리한 점이 많아 성능이 안 좋은 악성 SQL문의 실행계획으로 자주 나타남

• 일반적으로 인덱스 풀스캔은 “인덱스를 탔다”라고 표현하지 않음. 비효율적인 인덱스 스캔

• SQL 힌트인 INDEX 힌트 이용

인덱스 스킵 스캔

• 인덱스 선두 칼럼이 조건절에 없어도 인덱스를 활용하는 스캔 방식임

• 조건절에 빠진 인덱스 선행 칼럼의 변별력이 좋지 않고 후행 칼럼이 ‘=’ 조건으로 오는 칼럼의 변별력이 좋을 때 인덱스 스킵 스캔을 함

• 루트 또는 브랜치 블록에서 읽은 칼럼값 정보를 이용해 조건절에 부합하는 레코드를 포함할 가능성이 있는 리프 블록만을 스캔

SELECT /*+ INDEX_SS(A IDX_TB_SUB_STA_TK_GFF_TMP_03) */
       /* SELECT.TB_SUBWAY_STATN_TK_GFF_TMP.004 */
       A.SUBWAY_STATN_NO
     , A.STD_YM
     , A.BEGIN_TIME
     , A.END_TIME
     , A.TK_GFF_SE_CD
     , A.TK_GFF_CNT
  FROM TB_SUBWAY_STATN_TK_GFF_TMP A
  WHERE A.SUBWAY_STATN_NO = '000376'
;

• INDEX_SS SQL 힌트를 사용하여 인덱스 스킵 스캔을 유도함. 만일 INDEX_SS 힌트를 주지 않으면 오라클 옵티마이저가 다른 인덱스를 사용할 수 있음

인덱스 고속 풀 스캔

• 인덱스 자체를 처음부터 끝까지 Multi Block I/O 방식으로 한 번에 I/O Call 해서 여러 개의 블록을 읽어옴

• 하지만 인덱스 자체를 처음부터 끝까지 한번에 읽기 때문에 결과집합의 정렬은 보장하지 않음.

--코드 7-26 인덱스 생성
CREATE INDEX IDX_TB_SUB_STA_TK_GFF_TMP_04
ON TB_SUBWAY_STATN_TK_GFF_TMP(STD_YM, BEGIN_TIME, END_TIME);

--코드 7-27 통계정보 생성
ANALYZE INDEX IDX_TB_SUB_STA_TK_GFF_TMP_04 ESTIMATE STATISTICS;

--코드 7-28 통계레벨 설정
ALTER SESSION SET STATISTICS_LEVEL = ALL;

--코드 7-29 SQL문 실행
SELECT /*+ INDEX_FFS(A IDX_TB_SUB_STA_TK_GFF_TMP_04) */
       /* SELECT.TB_SUBWAY_STATN_TK_GFF_TMP.005 */
        A.STD_YM
      , MAX(A.BEGIN_TIME) AS BEGIN_TIME
      , MAX(A.END_TIME) AS END_TIME
   FROM TB_SUBWAY_STATN_TK_GFF_TMP A
  WHERE A.STD_YM >= '00000000'
  GROUP BY A.STD_YM
;

인덱스 역순 범위 스캔

--코드 7-30 인덱스 생성
CREATE INDEX IDX_TB_SUB_STA_TK_GFF_TMP_05
ON TB_SUBWAY_STATN_TK_GFF_TMP(STD_YM, END_TIME);


--코드 7-31 통계정보 수집
ANALYZE INDEX IDX_TB_SUB_STA_TK_GFF_TMP_05 ESTIMATE STATISTICS;

--코드 7-32 통계레벨 설정
ALTER SESSION SET STATISTICS_LEVEL = ALL;

--코드 7-33 SQL문 실행
SELECT /*+ INDEX_DESC(A IDX_TB_SUB_STA_TK_GFF_TMP_05) */
       /* SELECT.TB_SUBWAY_STATN_TK_GFF_TMP.006 */
       A.SUBWAY_STATN_NO
     , A.STD_YM
     , A.BEGIN_TIME
     , A.END_TIME
     , A.TK_GFF_SE_CD
     , A.TK_GFF_CNT
  FROM TB_SUBWAY_STATN_TK_GFF_TMP A
 WHERE A.STD_YM = '202010'
   AND A.END_TIME <= '1800'
 ORDER BY A.STD_YM, END_TIME DESC
;

• INDEX_DESC 힌트를 이용하여 인덱스를 뒤에서부터 스캔하도록 유도

• 이 경우 옵티마이저는 인덱스를 뒤에서부터 읽음

조인 수행 원리

조인이란

NL(Nested Loops) 조인

• 첫 번째 집합의 대상 건수만큼 반복하면서 두 번째 집합을 조회하여 매칭되는 행을 리턴하는 조인 기법

• 한 레코드(행)씩 순차적으로 진행하기 때문에 부분 범위만 처리하는 것으로 유도해야 효율적으로 수행됨

• 첫 번째 집합의 처리 범위에 따라 전체 성능이 결정되기 때문에 첫 번째 집합의 수가 적어야 성능상 유리함

• NL 조인은 부분 범위처리가 가능한 OLTP(Online Transaction Processing) 환경에 적합하다고 할 수 있음. OLTP 환경이란 우리가 일반적으로 사용하는 영화 예매 시스템과 같이 최신 데이터 위주로 조회하고, 실시간으로 영화를 예매하는 특성을 지닌 시스템을 의미

소트머지 조인

• 2개의 테이블(집합)을 조인 칼럼 기준으로 모두 정렬한 후 두 집합을 병합(Merge) 하면서 결과집합을 도출

• 소트 머지 조인은 인덱스 유무에 영향을 받지 않음. 어차피 2개의 집합을 모두 정렬하기 때문에. 단 필요한 인덱스가 이미 있는 경우에는 해당 인덱스를 이용해서 정렬 작업을 생략하기도 함

• 양쪽 집합을 모두 개별적으로 각각 따로 읽고 나서 조인을 수행하기 때문에 조인 칼럼에 인덱스가 없는 상황에서 두 테이블을 독립적으로 읽은 후, 각각의 집합끼리 조인 연산을 수행할 때 유리

해시조인

• 해시 조인은 첫 번째 테이블(집합)을 기준으로 해시 테이블을 생성하고 두 번째 집합을 조회하면서 해시 테이블과의 해시 FUNCTION 비교를 통해 매칭되는 건들을 결과집합에 포함시킴

• 여기서 중요한 것은 첫 번째 집합의 용량이 HASH AREA 메모리 공간에 전부 다 담길 수 있을 정도로 작으면 성능 상 매우 유리해진다는 것

• 해시 조인은 소트 머지 조인처럼 정렬부하가 없으며 NL 조인처럼 테이블 랜덤 액세스에 대한 부하가 없음
• 이러한 장점으로 인해 대량의 데이터 처리 시 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 조인할 때(배치 프로그램, DW, OLAP성 쿼리) 사용함

조인 기법 비교

조인 순서의 중요성