[친절한 SQL 튜닝] 2장. 인덱스 기본

2.0 미리 알아보는 DB에서 데이터를 찾는 방법

• '2.1.1 미리 보는 인덱스 튜닝'에서 설명하는 내용을 따로 내 방식대로 설명

 

• 학교에서 이름이 '홍길동'이라는 학생의 교실을 찾는 방법
  • 모든 교실을 돌며 찾는다.
    • Table Full Scan
  • 이름으로 소팅된 명부를 보고 '홍길동'이 있는 교실을 알아낸다.
    • 이름을 인덱스로 지정한 Index Scan 하여 교실을 알아낸다.
    • 이름으로 된 인덱스만으로 교실을 알 수 있기 때문에 이 경우는 테이블 액세스가 없음. 

 

• 학교에서 이름이 '홍길동'이고 출생월이 '5월~6월'인 학생의 교실을 찾는 방법
  • 모든 교실을 돌며 찾는다.
    • Table Full Scan
  • 이름으로 소팅된 명부를 보고 '홍길동'이 있는 교실을 돌며 생월이 '5월~6월'인지 물어보며 찾는다.
    • 이름을 인덱스로 지정한 Index Scan후, 실제 테이블 액세스.
    • 테이블 액세스 후, 생월이 '5월~6월'인지 확인함.
    • 이 경우는 '5월~6월'이 아닌 '홍길동'이 있을 수 있으므로 테이블 액세스를 통해 생월이 확인해야 하므로 테이블 액세스가 발생. => 랜덤 액세스(테이블 액세스)가 발생하므로 '랜덤 액세스 최소화 튜닝'이 필요. 아래와 같이 인덱스를 튜닝하면 랜덤 액세스가 발생하지 않음.
  • 생월, 이름 순으로 소팅된 명부를 보고 교실을 알아낸다.
    • 생월, 이름을 인덱스로 지정한 Index Scan 하여 교실을 알아낸다.
    • 생월, 이름으로 된 인덱스만으로 교실을 알 수 있기 때문에 이 경우는 테이블 액세스가 없음.
    • 단, '5월~6월' 생이 100명이고 '홍길동'이 5명이라면 생월이 먼저 소트 되어 있기 때문에 불필요한 인덱스 스캔이 많아짐 => 인덱스 스캔의 효율이 적기 때문에 '인덱스 스캔 효율화 튜닝'이 필요. 아래와 같이 인덱스를 튜닝하면 스캔의 효율이 좋아진다.
  • 이름, 생월 순으로 소팅된 명부를 보고 교실을 알아낸다.
    • 이름, 생월 인덱스로 지정한 Index Scan 하여 교실을 알아낸다.
    • 이름, 생월로 된 인덱스만으로 교실을 알 수 있기 때문에 이 경우는 테이블 액세스가 없음.
    • '5월~6월' 생이 100명이고 '홍길동'이 5명이라면, 이름이 먼저 소트 되어 있기 때문에 불필요한 인덱스 스캔이 많지는 않음

 

• SQL 튜닝은 랜덤 액세스를 줄이기 위한 '랜덤 액세스 최소화 튜닝'과 인덱스 스캔을 줄이기 위한 '인덱스 스캔 효율화 튜닝'을 의미한다.

 

• 1장에서 말했듯이 SQL이 느린 이유는 거의 무조건 디스크 I/O 이기 때문에 '인덱스 스캔 효율화 튜닝'보다는 '랜덤 액세스 최소화 튜닝'이 중요하다. SQL 튜닝은 랜덤 I/O와의 전쟁이다.

 

2.1 인덱스 구조 및 탐색

• 인덱스는 B*Tree 구조로 되어있다.
  • 대학교 때 배웠었는데 완전히 까먹었다..
  • B Tree의 단점을 내가 좋아하는 도널드 커누스가 새롭게 제안한 트리
  • 루트 노드가 아닌 노드는 최대 저장 공간의 2/3 이상의 자료가 저장되어야 함
    (B-트리는 ⌈m/2⌉ - 1 조건에 의해 최소 1/2 이상의 자료가 저장되어야 함)
  • 노드에 저장되는 자료가 넘치는 경우 재분배
    (B-트리는 항상 중간값을 가지는 자료를 부모 노드로 올려 보내고 분할하였음)

 

• 최종적으로 하단에 있는 리프 블록에는 테이블 레코드를 가리키는 ROWID를 갖는다.
  • ROWID = 데이터 블록 주소 (DBA) + 로우 번호
  • DBA = 데이터 파일 번호 + 블록 번호
  • 블록 번호 = 데이터 파일 내에서 부여한 상대적 순번
  • 로우 번호 = 블록 내 순번

 

• 인덱스는 수직적 탐색으로 인덱스 스캔의 시작점을 찾고, 이후 수평적 탐색으로 본격적으로 데이터를 찾음
  • 수직적 탐색 : B*Tree 루트에서 시작해 리프 블록까지 수직적 탐색을 통해 조건을 만족하는 첫 번째 레코드를 찾는 과정
  • 수평적 탐색 : 리프 블록끼리는 양방향 링크드 리스트이다. 따라서 수직적 탐색을 통해 첫 번째 레코드인 리프에 도달한 후, 해당 조건을 만족하는 다른 리프 블록을 찾기 위해 수평적으로 탐색하는 과정 

 

• 결합 인덱스 생성 시, 컬럼 순서에 따른 인덱스의 구조(즉 B*Tree의 구조)에는 영향이 없다.
  • 이름 + 성별이나 성별 + 이름 순으로 인덱스를 구성해도 인덱스 구조는 동일하기 때문에 읽는 인덱스 블록 개수는 동일하다.
  • 따라서 '결합 인덱스 생성 시 컬럼 배치 순서'에 따른 일 량에는 차이는 없다.
  • 이 부분은 여기서까지만 읽으면 이해는 되지만, 이 책을 다 읽고 오면 좀 헷갈린다. 실제 인덱스 선두 컬럼에 따른 I/O 차이 때문에.. 
• 데이터를 Delete 하면 인덱스의 구조가 변경되는데, 이때 불균형이 일어난다고 말하는 사람들이 있지만, B Tree의 B는 'Balanced'를 의미하는 것처럼 B Tree는 데이터의 추가, 삭제가 될 경우 항상 Balanced 되게 트리의 구조를 수정한다.
  
  • Index Skew : 불균형은 생길 수 없지만 인덱스 엔트리가 왼쪽 또는 오른쪽에 치우침.
  • Index Spares : 인덱스 블록에 밀도가 떨어지는 현상

 

2.2 인덱스 기본 사용법

• 인덱스를 정상적으로 사용(Index Range Scan) 하기 위한 가장 첫 번째 조건은 인덱스 선두 컬럼이 조건절에 있어야 함.
• 두 번째 조건은 인덱스의 선두 컬럼을 가공하지 않은 상태로 사용해야 함.
• 즉, 인덱스를 정상적으로 사용한다라는 의미는 조건절에 인덱스 선두 컬럼을 가공하지 않을 상태로 사용해야 함을 의미한다.

 

• 인덱스 컬럼을 가공해서 사용하면 인덱스 스캔 시작점을 찾을 수 없으므로 Range Scan이 불가능
  • '이름' 컬럼을 인덱스 컬럼으로 가진 인덱스에서 이름이 '홍길동'이라는 사람을 찾는다면 수직적 탐색을 통해 '홍길동' 리프를 찾고 수평적 탐색을 통해 '홍길동'이 나올 때까지 스캔하면 종료.
  • 동일한 조건에서 이름에 '길동'을 포함한 사람을 찾는다면 수직적 탐색을 통해 시작점을 알 수도 없고 스캔의 끝도 알 수 없음. 

 

• 따라서 데이터를 가공하는 SUBSTR, NVL, LIKE을 사용하면 인덱스를 정상적으로 사용하지 않음.

 

• OR도 일반적으로 인덱스를 정상적으로 사용하지는 않지만 use_concat 힌트를 사용하여 UNION ALL로 변환하여 Range Scan 가능
  • OR 조건에 해당하는 값을 여러 문장으로 나눈 후 UNION ALL로 표현하면 각각 Rance Scan을 한다. 

 

• IN도 OR가 동일한 기능을 하는데, 마찬가지로 UNION ALL로 표현을 하면 된다.
  • IN은 보통 옵티마이저가 알아서 IN-List Iterator 방식으로 Range Scan을 사용 가능하도록 함.
  • IN에서 use_concat 힌트를 사용하면 어떻게 될까? 
    • IN도 use_concat을 사용하면 UNION ALL로 변환함
  • OR은 왜 IN-List Iterator로 안되나?
    • OR도 IN-List Iterator 방식으로 옵티마이저가 변환해줄 수 도 있다.

 

• 인덱스는 정렬된 상태로 존재하므로 Range Scan 인덱스를 사용한다는 것은 데이터를 정렬된 상태로 스캔을 한다는 것을 의미함. 따라서 수평적 탐색을 통해 언제 멈춰야 할지 알 수가 있는 것. 
  • 인덱스를 정상적으로 사용한다면 ORDER BY를 SQL 문장에 사용해도 실행계획에 SORT OREDER BY가 표현되지 않음. 즉, 소트 연산을 하지 않아 더 효율적임.
  • 만약 인덱스가 ASC로 되어있는 상태에서 ORDER BY를 DESC로 해야 한다고 해도 옵티마이저가 인덱스를 디센딩 하여 스캔함. (단 제대로 안될 경우, 힌트를 주는 것이 좋음)
  • 당연하겠지만 ORDEY BY에 들어간 인덱스 컬럼을 가공할 경우 문제 됨. 
  • -- 인덱스 : ID + 변경일자 + 변경순번 -- SORT ORDER BY 연산 생략 SELECT * FROM 변경이력 WHERE 장비번호 = '1' ORDER BY 변경일자, 변경순번 -- 가공하여 SORT ORDER BY 연산 필요 SELECT * FROM 변경이력 WHERE 장비번호 = '1' ORDER BY 변경일자 || 변경순번​

 

 

• SELECT 절에서 MAX나 MIN을 인덱스가 컬럼이 포함된 컬럼으로 조회하고 Range Scan을 탈 경우 정렬 연산을 수행하지 않고 바로 찾으러 감.
  • 실행 계획에 FIRST ROW로 표현됨
  • 마찬가지로 인덱스 컬럼을 가공할 경우 되지 않음
  • 인덱스의 맨 마지막 것만 MAX/MIN 조회할 경우 해당되는 건가?
  • 조건절 없이 하면 어떻게 될까?

    -- 인덱스 : ID + 변경일자 + 변경순번
    
    -- 정렬 연산 없이 FIRST ROW로 빠르게 찾음
    SELECT MAX(변경순번)
    FROM 변경이력
    WHERE 장비번호 = '1'
    AND 변경일자 = '20210612'
    
    
    -- 가공할 경우 SORT ORDER BY 연산 필요
    SELECT NVL(MAX(TO_NUMBER(변경순번)), 0)
    	* 
    FROM 변경이력
    WHERE 장비번호 = '1'
    AND 변경일자 = '20210612'
    

 

 

• 컬럼의 타입과 다른 타입으로 검색할 경우, 자동형 변환에 의해서 인덱스를 타지 않음
  • TO_CHAR, TO_DATE, TO_NUMBER를 사용하지 않아도 필요한 경우 옵티마이저가 자동으로 형 변환하므로 주의

 

2.3 인덱스 확장기능 사용법

• Index Range Scan : 가장 일반적인 인덱스 스캔 방식. 수직적 탐색 + 수평적 탐색

 

• Index Full Scan : 수직적 탐색 없이 리프 맨 처음부터 수평적 탐색하는 방식.
  • 인덱스 선두 컬럼이 조건절에 존재하지 않을 경우 옵티마이저가 차선책으로 사용하는 방법
  • 옵티마이저는 Table Full Scan을 고려하여 만약 좋지 않다면 Index Full Scan을 고려함
  • 조건에 해당하는 로우가 극히 적으면 Index Full Scan이 효율적이므로 이를 사용
  • 조건에 해당하는 로우가 많아도 극히 일부 데이터만 fetch 하여 소트 한 데이터를 보여줘야 하는 경우 first_row라는 힌트를 주어 Index Full Scan을 사용하는 방법도 있다.

 

• Index Unique Scan : 유니크한 인덱스의 모든 인덱스 컬럼을 '='을 줄 경우 수직적 탐색만으로 바로 찾는 방식.

 

• Index Skip Scan : 인덱스의 선두 컬럼이나 중간 컬럼이 조건절에 존재하지 않을 경우, 사용할 수 있는 방식
  • 오라클 9i부터 Index Full Scan방식 보다 좀 더 나은 차선책
  • 단, 존재하지 않는 인덱스 컬럼의 Disticnt 개수가 적고, 후자 컬럼은 많을 경우에 사용해야지 좋음.
  • 수직적 탐색으로 시작점을 찾은 후, 조건절에 해당하는 가능성이 있는 블록만 스캔하는 방식
  • index_ss 힌트를 주어서 사용 가능

 

• Index Fast Full Scan : 논리적 구조로 스캔하는 방식이 아닌 디스크에 저장된 순서대로 스캔
  • Multiblock I/O를 사용하고, 병렬 스캔이 가능하여 빠름
  • 단, 순서 보장이 되지 않음
  • 인덱스에 포함된 컬럼으로만 조회할 때 사용 가능?
  • index_ffs 힌트를 주어서 사용 가능

 

• Index Range Scan Descending : 현재 정렬 방식의 반대로 스캔하는 방식

 

추가 내용

• 인덱스를 정상적으로 사용한다고 해서(Index Range Scan) 쿼리가 빨리 수행되는 것은 아님.
• 수직적 탐색 이후 스캔할 데이터가 100만 건이라면? 인덱스를 타봤자 100만 건을 스캔해야 하므로 문제 됨.