인덱스 기본

1. 인덱스 구조 및 탐색

1.1 미리보는 인덱스 튜닝

• 데이터를 찾는 두가지 방법
  • 테이블 전체 스캔
  • 인덱스 이용
• 온라인 트랜잭션 처리 (OLTP)시스템에서는 소량 데이터를 주로 검색하므로 인덱스 튜닝이 무엇보다 중요
• 인덱스 튜닝의 두 가지 핵심요소
  • 인덱스 스캔 효율화 튜닝(비효율을 줄이는것)
    EX) 학생명부에서 시력이 1.0~1.5인 홍길동 학생을 찾을 경우, 이름-시력순정렬과
    시력-이름순 정렬을 하면 스캔하는 데이터량의 차이가 크다
  • 테이블 엑세스 횟수를 줄이는것
    인덱스 스캔후 테이블 액세스시 랜덤 I/O방식을 사용하므로 이를 '랜덤 엑세스 최소화 튜닝'이라고한다
  • 둘중 랜덤엑세스 최소화 튜닝이 더중요하다, 성능에 미치는 영향이 더 크기 때문
  • SQL 튜닝은 랜덤 I/O와의 전쟁
  
  
• SQL 튜닝은 랜덤 I/O와의 전쟁
  • DB성능이 느린 이유는 디스크 I/O때문이다. 읽어야할 데이터량이 많고, 그과정에서 디스크 I/O가 많이 발생할때 느리다.
  • DBMS가 제공하는 많은기능이 느린 랜덤I/O를 극복하기 위해 개발됐다.
    (IOT, 클러스터, 파티션부터 테이블 Prefetch, Batch I/O등등)
  • NL조인이 대량데이터 조인할때 느린이유도 랜덤 I/O때문

1.2 인덱스 구조

• 인덱스는 대용량 테이블에서 필요한 데이터만 빠르게 효율적으로 엑세스하기 위해 사용하는 오브젝트
• DB에서 인덱스 없이 데이터 검색하려면, 테이블을 처음부터 끝까지 모두 읽어야한다.
• 인덱스 이용시 일부만 읽고 멈출 수있다, 범위스캔이 가능하다, 인덱스가 정렬되어있기 때문
• DBMS는 일반적으로 B*Tree인덱스사용, 루트와 브랜치 블록에 각 레코드는 하위블록에대한 주소값을 갖는다.
• 인덱스레코드의 키 값은 하위 블록에 저장된 키 값의 범위
• 수직적 탐색 : 인덱스 스캔 시작지점을 찾는 과정
• 수평적 탐색 : 데이터를 찾는 과정

1.3 인덱스 수직적 탐색

• 정렬된 인덱스 레코드 중 조건을 만족하는 첫번째 레코드를 찾는 과정, 인덱스 스캔 시작지점을 찾는 과정
• 인덱스 수직탐색시 루트포함 브랜치블록은 '조건을 만족하는 첫번재 레코드'가 목표지점.

1.4 인덱스 수평적 탐색

• 스캔 시작점을 찾았으면, 찾고자하는 데이터가 더 안나타날때까지 인덱스 리프블록을 수평으로 스캔
• 본격적으로 데이터를 찾는 과정
• 인덱스 수평적 탐색하는 이유
  • 조건절을 만족하는 데이터를 모두 찾기 위해
  • ROWID를 얻기 위해
  • 필요한 컬럼을 인덱스가 모두 갖고있으면 인덱스만 스캔하고 끝나지만, 일반적으로 인덱스스캔후 테이블도 엑세스한다. 이때 ROWID가 필요.

1.5 결합 인덱스 구조와 탐색

• 두개이상 컬럼을 결합해서 인덱스를 만들 수도 있다.
• 결합인덱스 컬럼순서에 따라 성능차이가 크게날수있다.
• 선택도가 낮은 컬럼을 앞쪽에 두고 결합인덱스를 생성해야 검사횟수를 줄일수있어 성능에 유리하다
• B*Tree인덱스에서는 delete작업에의한 인덱스 불균형상태에 놓이지않는다 B가 Balanced임
• 'Balanced'는 어떤 값으로 탐색해도 인덱스 루트에서 리프블록까지 도달하기까지 읽는 블록수가 같음을 의미
• 루트부터 모든 리프블록까지 높이(height)를 항상 같다.
  
  

2. 인덱스 기본 사용법

2.1 인덱스를 사용한다는 것

• 인덱스 컬럼을 순서대로(asc든 desc든) 정렬하여 모여있기때문에 찾기쉽다.
• 인덱스 컬럼(정확히는 선두컬럼)을 가공하지 않아야 인덱스를 정상적으로 사용할 수 있다.
• 인덱스를 정상적으로 사용한다는 것 > 리프블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는것. = 리프블록 일부만 스캔하는 Index Range Scan
• 정확한 시작점을 찾아 사용할수없으면 전체를 탐색해야한다.

2.2 인덱스를 Range Scan할 수 없는 이유

• 인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용(Range Scan)할 수 없다.
• 인덱스 컬럼을 가공했을때 인덱스를 정상적으로 사용할 수 없는 이유는 인덱스 스캔지점을 찾을수 없기 때문
• USE_CONCAT힌트를 사용하여 OR조건을 내부적으로 UNION ALL로바꾸지않는이상 RANGE SCAN이 불가능하다
• IN조건에서는 옵티마이저가 IN-List Iterator방식을 사용한다 > IN-List개수만큼 Index Range Scan을 반복

2.3 더 중요한 인덱스 사용 조건

• 인덱스를 Range Scan하기 위한 첫번째 조건은 인덱스 선두 컬럼이 조건절에 있어야 한다.(가공하지 않은 상태로)
• 인덱스 선두컬럼이 가공되지않은 상태로 조건절에 있으면 인덱스 Range Scan은 무조건 가능
• 문제는 Range Scan한다고 항상 성능이 좋은것은 안디ㅏ.
  
  

  인덱스 잘탄고 튜닝이끝난게 아니다.

  • 주문상품_N1인덱스는 (주문일자+상품번호)순으로 구성됐고, 테이블에 쌓이는 데이터량은 하루 평균 100만건
  • 조건절에는 인덱스 선두컬럼인 주문일자가 있고, 가공하지않은 상태여서 Range Scan하는데 문제가 없다.
  • 하지만 정말 인덱스를 잘타는지는 인덱스 리프 블록에서 스캔하는 양을 따져봐야 알 수 있다.
  • 상품번호 LIKE '%PING%' 이나 SUBSTR(상품번호,1,4)='PING' 같은 조건은 스캔범위를 줄이는데 전혀 도움이되지않는다 > 스캔데이터량은 주문일자 조건을 만족하는 100만건. > 과연 인덱스를 잘탄다고 말할수있는가?
  
  

2.4 인덱스를 이용한 소트 연산 생략

• PK컬럼순으로 정렬을할경우 별도의 정렬연산을 따로 수행하지 않는다. PK인덱스를 스캔하면서 출력한 결과집합은 어차피 PK컬럼순으로 정렬되기때문.(실행계획에 SORT ORDER BY가 없다)
• 만약 정렬 연산은 생략할 수 있게 인덱스가 구성돼 있지 않다면, 실행계획에 SORT ORDER BY 연산 단계가 추가된다.

2.5 ORDER BY 절에서 컬럼 가공

• 조건절이 아닌 ORDER BY 또는 SELECT-LIST에서 컬럼을 가공함으로써 인덱스를 정상적으로 사용할 수 없는 경우도 종종 있다.
• [장비번호+변경일자+변경순번] 인덱스가 있는데 아래와 같이 쿼리를 작성했다면 정렬연산이 발생한다.
  인덱스에는 가공안한 컬럼순으로 정렬했는데, ORDER BY 절에서 가공해버렸기 때문이다.

  SELECT *
  FROM 상태변경이력
  WHERE 장비번호 ='C'
  ORDER BY 변경일자 || 변경순번

• 유사한 케이스 인덱스는[주문일자+주문번호] - SORT ORDER BY STOPKEY 발생
  ORDER BY의 주문번호는 SELECT절의 TO_CHAR로 가공한 컬럼을 가르킨다.

  SELECT *
  FROM (
           SELECT TO_CHAR(A.주문번호, 'FM00000') AS 주문번호, A.업체번호, A.주문금액
           FROM 주문 A
           WHERE A.주문일자 = :DT
           AND A.주문번호 > NVL(:NEXT_ORD_NO, 0)
           ORDER BY 주문번호
  )
  WHERE ROWNUM <= 30

2.6 SELECT-LIST에서 컬럼 가공

• 인덱스에 포함된 컬럼의 MIN MAX값을 구할때는 인덱스 리프블록의 왼쪽(MIN) 또는 오른쪽(MAX)에서 레코드하나(FIRST ROW)만 읽고 멈춘다
• 인덱스 [장비번호+변경일자+변경순번]

  SELECT MIN(변경순번)
  FROM 상태변경이력
  WHERE 장비번호 = 'C'
  AND 변경일자 = '20180316'

• 인덱스 사용못하는경우.

  SELECT NVL(MAX(TO_NUMBER(변경순번)), 0)
  FROM 상태변경이력
  WHERE 장비번호 = 'C'
  AND 변경일자 = '20180316'

• 아래와같이 바꾸면 정렬연산 없이 최종변경순번을 얻을수 있다.
  SELECT NVL(TO_NUMBER(MAX(변경순번)), 0)
• 애초에 변경순번 데이터를 숫자형으로 설계했다면 이런 튜닝할 일도 안 생긴다.

2.7 자동 형변환

• 조건컬럼을 가공하지않더라도, 조건의 데이터 타입이 조건컬럼의 데이터 타입과 다를경우, 자동형변환 발생
• 형변환이 발생하면서 인덱스를 타지못하고 풀스캔이 발생할 수 있음.
• 조건절에 정확한 데이터 타입의 조건이나, 형변환을 명시해주는것이 좋다.
• 형변환함수를 쓴다고 성능이 안좋아지지 않는다(내부적으로 수행하기 때문), 블록I/O를 줄이는데 힘써야한다.