SQLP - 2. 친절한 SQL 튜닝 3장 정리

3장 인덱스 튜닝

• SQL이 참조하는 컬럼을 인덱스가 모두 포함하는 경우가 아니면, 인덱스를 스캔한 후에 반드시 테이블을 엑세스한다. TABLE ACCESS BY INDEX ROWID라고 표시된 부분이 여기에 해당한다.
• 인덱스 ROWID 는 물리적 주소보다 논리적 주소에 가깝다. 물리적으로 직접 연결되지 않고 테이블 레코드를 찾아가기 위한 논리적 주소 정보를 담고 있기 때문이다.
• 프로그래밍 언어에서 포인터는 메모리 주소값을 담는 변수를 말한다. 물리적으로 직접 연결된 구조나 다름없다. 메모리 상에서의 위치 정보인 포인터를 생각하며 인덱스 ROWID를 물리적 주소로 이해했다면 잘못 이해한 것이다. 인덱스 ROWID는 포인터가 아니다. 인덱스는 ROWID는 논리적 주소다. 디스크 상에서 테이블 레코드를 찾아가기 위한 위치 정보를 담는다. 포인터가 아니며, 테이블 레코드와 물리적으로 직접 연결된 구조는 더더욱 아니다.
• 메인 메모리 DB(MMDB)는 데이터를 모두 메모리에 로드해 놓고 메모리를 통해서만 I/O를 수행하는 DB라고 할 수 있다.
• 잘 튜닝된 OLTP성 데이터베이스 시스템이라면 버퍼캐시 히트율이 99% 이상이지만 그래도 MMDB만큼 빠르지는 않다. MMDB의 경우 인스턴스 가동 시 디스크에 저장된 데이터를 버퍼캐시로 로딩하고 이어서 인덱스를 생성한다. 이 때 인덱스는 디스크 상의 주소가 아닌 메모리상의 주소정보, 즉 포인터를 갖는다. 따라서 인덱스를 경유해 테이블을 엑세스하는 비용이 오라클에 비해 현저히 낮다. 
• 오라클은 테이블 블록이 수시로 버퍼캐시에서 밀려났다가 캐싱되며, 그때마다 다른 공간에 캐싱되기 때문에 인덱스에서 포인터로 직접 연결할 수 없는 구조다. 메모리 주소 정보(포인터)가 아닌 디스크 주소 정보(DBA, Data Block Address)를 이용해 해시 알고리즘으로 버퍼 블록을 찾아간다. 
• 읽고자 하는 DBA를 해시 함수에 입력해서 해시 체인을 찾고 거기서 버퍼 헤더를 찾는다. 거기서 얻은 포인터로 버퍼 블록을 찾아간다. 
• 인덱스로 테이블 블록을 엑세스할 때는 리프 블록에서 얻은 ROWID를 분해해서 DBA 정보를 얻고, 테이블을 Full Scan할 때는 익스텐트 맵을 통해 읽을 블록들의 DBA 정보를 얻는다.
• 모든 데이터가 캐싱돼 있더라도 테이블 레코드를 찾기 위해 매번 DBA 해싱과 래치 획득 과정을 반복해야 한다. 동시 엑세스가 심할 때는 캐시 버퍼 체인 래치와 버퍼 Lock에 대한 경합까지 발생한다. 이처럼 인덱스 ROWID를 이용한 테이블 엑세스는 생각보다 고비용 구조이다.
• 클러스터링 팩터(Clustering Factor, CF)는 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미한다. CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 매우 좋다. 즉, 테이블 엑세스량에 비해 블록 I/O가 적게 발생한다.
• 인덱스 ROWID로 테이블을 엑세스할 때, 오라클은 래치 획득과 해시 체인 스캔 과정을 거쳐 어렵게 찾아간 테이블 블록에 대한 포인터(메모리 주소값)를 바로 해제하지 않고 일단 유지한다. 이를 '버퍼 Pinning' 이라고 한다. 이 상태에서 다음 인덱스 레코드를 읽었는데, 마침 직전과 같은 테이블 블록을 가리킨다. 그러면 래치 획득과 해시 체인 스캔 과정을 생략하고 바로 테이블 블록을 읽을 수 있다. 논리적인 블록 I/O 과정을 생략할 수 있는 것이다. 
• 인덱스 ROWID를 이용한 테이블 엑세스는 생각보다 고비용 구조다. 따라서 읽어야 할 데이터가 일정량을 넘는 순간, 테이블 전체를 스캔하는 것보다 오히려 느려진다.
• 추출 건수가 늘면서 인덱스 스캔량이 느는 데서도 영향을 받지만, 테이블 랜덤 엑세스가 미치는 영향에 비교할 바가 아니다.
• 인덱스를 이용한 테이블엑세스가 Table Full Scan보다 더 느려지게 만드는 핵심 요인 두가지는 다음과 같다.
  • Table Full Scan은 스퀀셜 엑세스인 반면, 인덱스 ROWID를 이용한 테이블 엑세스는 랜덤 엑세스 방식이다.
  • Table Full Scan은 Multiblock I/O인 반면, 인덱스 ROWID를 이용한 테이블 엑세스는 Single Block I/O 방식이다.
• 인덱스 사용 시 손익분기점 개념은 온라인 트랜잭션을 처리하는 프로그램과 DW/OLAP/배치 프로그램 튜닝의 특징을 구분 짓는 핵심 개념이다.
• 온라인 프로그램은 보통 소량 데이터를 읽고 갱신하므로 인덱스를 효과적으로 활용하는 것이 무엇보다 중요하다. 조인도 대부분 NL 방식을 이용한다. NL조인은 인인덱스를 이용하는 조인 방식이다.
• 대량 데이터를 읽고 갱신하는 배치(batch) 프로그램은 항상 전체범위 처리 기준으로 튜닝해야 한다. 대량 데이터를 빠르게 처리하려면, 인덱스와 NL 조인보다 Full Scan과 해시 조인이 유리하다.
• 대량 배치 프로그램에선 인덱스보다 Full Scan이 효과적이지만, 초대용량 테이블을 Full Scan 하면 상당히 오래 기다려야 하고 시스템에 주는 부담도 적지 않다. 따라서 배치 프로그램에서는 파티션 활용 전략이 매우 중요한 튜닝 요소이고, 병렬 처리까지 더할 수 있으면 금상첨화다.
• 관리적 측면을 배제하고 성능 측면에서만 보면 테이블을 파티셔닝하는 이유는 결국 Full Scan을 빠르게 처리하기 위해서다. 모든 성능 문제를 인덱스로 해결하려 해선 안 된다. 큰 테이블에서 아주 적은 일부 데이터를 빨리 찾고자 할 때 주로 사용한다.
• 테이블 엑세스 단계 필터 조건에 의해 버려지는 레코드가 많을 때, 기존 인덱스에 WHERE절에 있는 컬럼을 추가하는 것만으로 큰 효과를 얻을 수 있따. 인덱스 스캔량은 줄지 않지만, 테이블 랜덤 엑세스 회수를 줄일 수 있다.
• 인덱스만 읽어서 처리하는 쿼리를 Covered 쿼리하고 부르며 그 쿼리에 사용한 인덱스를 Covered 인덱스라고 부른다.
• SQL Server 2005 버전에 추가된 Include 인덱스 기능이 있다. 인덱스 키 외에 미리 저장한 컬럼을 리프 레벨에 함께 저장하는 기능이다. 인덱스를 생성할 때 include (col) 옵션을 추가해주면 된다. 테이블 랜덤 엑세스 횟수를 줄이는 용도로만 사용한다.
• IOT(Index-Organized Table)는 테이블을 인덱스 구조로 생성한 것이다. MS-SQL Server는 Clustered Index라고 부른다.
• 일반 테이블은 힙 구조 테이블이며 대개 생략하지만 테이블 생성 시 organization heap 옵션을 명시할 수도 있다. 반면, IOT는 인덱스 구조 테이블이므로 정렬 상태를 유지하며 데이터를 입력한다. IOT는 인위적으로 클러스터링 팩터를 좋게 만드는 방법 중 하나다.
• 예를 들어, 어떤 회사에 영업사원이 100명이라고 가정하자. 영원사업들의 일별 실적을 집계하는 테이블이 있는데 한 블록에 100개 레코드가 담긴다. 그러면 매일 한 블록씩 1년이면 365개 블록이 생긴다. 사번+일자로 SUM을 수행하는 쿼리가 자주 수행된다고 했을 때 사번 기준의 클러스터링 팩터가 안 좋으므로 조회건 수 만큼 블록 I/O가 발생하게 된다. 이 때 사번이 첫 번째 정렬 기준이 되도록 IOT를 구성해주면 적은 블록만 읽고 처리할 수 있다. oraganization index 옵션을 사용하여 테이블을 생성하면 된다.
• 클러스터 테이블에는 인덱스 클러스터와 해시 클러스터 두 가지가 있다.
• 인덱스 클러스터 테이블은 클러스터 키 값이 같은 레코드를 한 블록에 모아서 저장하는 구조다.
• 여러 테이블 레코드를 같은 블록에 저장할 수도 있는데, 이를 '다중 테이블 클러스터'라고 부른다. 클러스터에 테이블을 담기 전에 클러스터 인덱스를 반드시 정의해야 한다. 클러스터 인덱스는 데이터 검색 용도로 사용할 뿐만 아니라 데이터가 저장될 위치를 찾을 때도 사용하기 때문이다.
• 일반  테이블에 생성한 인덱스 레코드는 테이블 레코드와 1:1 대응 관계를 갖지만, 클러스터 인덱스는 테이블 레코드와 1:M 관계를 갖는다. 따라서 클러스터 인덱스의 키값은 항상 Unique하다.(=중복값이 없다.)
• 클러스터에 도달해서는 시퀀셜 방식으로 스캔하기 때문에 넓은 범위를 읽더라도 비효율이 없다는 게 핵심원리다.
• 해시 클러스터는 인덱스를 사용하지 않고 해시 알고리즘을 사용해 클러스터를 찾아간다는 점만 다르다.

 

3.2 부분범위 처리 활용

• DBMS가 클라이언트에게 데이터를 전송할 때 일정량씩 나누어 전송한다. 전체 결과집합 중 아직 전송하지 않은 분량이 많이 남아있어도 서버 프로세스는 클라이언트로부터 추가 Fetch Call을 받기 전까지 그대로 멈춰 서서 기다린다.
• DBMS가 데이터를 모두 읽어 한 번에 전송하지 않고 먼저 읽는 데이터부터 일정량(Array Size)을 전송하고 멈추기 때문이다. 데이터를 전송하고 나면 서버 프로세스는 CPU를 OS에 반환하고 대기 큐에서 잠을 잔다. 다음 Fetch Call을 받으면 대기 큐에서 나와 그다음 데이터부터 일정량을 읽어서 전송하고 또다시 잠을 잔다. 이처럼 전체 쿼리 결과집합을 쉼 없이 연속적으로 전송하지 않고 사용자로부터 Fetch Call이 있을 때마다 일정량씩 나누어 전송하는 것을 이른바 '부분범위 처리'라고 한다. 데이터를 전송하는 단위인 Array Size는 클라이언트 프로그램에서 설정한다. JAVA에서는 기본적값이 10이며, Statement 객체 setFetchSize 메소드를 통해 설정을 변경할 수 있다.
• rs.next() 호출 시 Fetch Call을 통해 전송받은 데이터 10건을 클라이언트 캐시에 저장하고 이후 캐시에 저장한 데이터를 모두 소진한 후 rs.next()가 호출되면 추가 Fetch Call을 통해 10건을 전송받는다. 따라서 rs.next() 사이에 있는 JAVA 프로그램 로직이 오래 걸릴 이유는 없다. 
• order by가 있고 해당 컬럼이 선두인 index가 없으면 정렬을 마치고서야 클라이언트에게 데이터 전송을 시작한다. 즉 전체범위 처리다. 정렬 컬럼이 선두인 index가 있으면 부분범위 처리가 가능하다. 인덱스는 항상 정렬된 상태를 유지하므로 전체 데이터를 정렬하지 않고도 정렬된 상태의 결과집합을 바로 전송할 수 있기 때문이다.
• 대량 데이터를 파일로 내려받는다면 어차피 데이터를 모두 전송해야 하므로 가급적 Array Size를 크게 설정해야한다. 그러면 Fetch Call을 줄일 수 있다. 앞쪽 일부 데이터만 Fetch하다가 멈추는 프로그램이라면 Array Size를 작게 설정하는 것이 유리하다.
• Fetch Call의 특성을 이용해 중간에 멈췄다가 사용자의 추가 요청이 있을 때마다 데이터를 가져오도록 구현하고 안 하고는 클라이언트 프로그램을 개발하는 개발자의 몫이다.
• OLTP 시스템은 말 그대로 온라인 트랜잭션을 처리하는 시스템을 말한다., 온라인 트랜잭션은 일반적으로 소량 데이터를 읽고 갱신한다.
• OLTP 시스템이 수천수만 건을 조회하는 경우도 있다. 이 때 부분범위처리로 성능 개선을 할 수 있다. 문제는 앞쪽 일부만 출력하고 멈출 수 잇는가이다. 토드나 오렌지같은 쿼리 툴은 이미 그렇게 구현돼 있다. 이들처럼 클라이언트 프로그램이 DB 서버에 직접 접속하는 2-Tier 환경에서는 그렇게 구현할 수 있다. 그런데 클라이언트와 DB 사이에 WAS, AP 서버 등이 존재하는 n-Tier 아키텍처에서는 클라이언트가 특정 DB 커넥션을 독점할 수 없다. 단위 작업을 마치면 DB 커넥션을 곧바로 커넥션 풀에 반환해야 하므로 그 전에 SQL 조회 결과를 클라이언트에게 '모두' 전송하고 커서(cursor)를 닫아야 한다.(JAVA에서 Statement, ResultSet 객체). 그래서 구현하기 어렵다. 그래도 여전히 유효한데 5장에서 자세한 사항을 배운다.
• 부분범위 처리 원리를 활용해 상위 N개 집합을 빠르게 출력하도록 구현할 수 있다면, 인덱스로 엑세스할 전체 대상 레코드가 아무리 많아도 빠른 응답속도를 낼 수 있다. 그러기 위해선 인덱스를 이용해 소트 연산을 생략할 수 있어야 한다. 배치 I/O는 읽는 블록마다 건건이 I/O Call을 발생시키는 비효율을 줄이기 위해 고안한 기능이다. 이 기능이 작동하면 테이블 블록에 대한 디스크 I/O Call을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리한다.
• 배치 I/O 기능이 작동하면 인덱스를 이용해서 출력하는 데이터 정렬 순서가 매번 다를 수 있다는 사실에 주목해야 한다. 실제 배치 I/O가 작동할 때는 데이터 출력 순서가 인덱스 정렬 순서와 다를 수 있다. 소트 생략 가능한 인덱스를 사용하더라도 배치 I/O 기능이 작동하면 데이터 정렬 순서를 보장할 수 없다. 애초에 인덱스로 소트 연산을 생략할 수 없거나 SQL에 ORDER BY가 없으면, 랜덤 I/O 성능을 향상하는 이 기능을 옵티마이저가 기본적으로 사용하여 배치 I/O를 선택한다. 따라서 인덱스를 믿고 ORDER BY를 쓰지 않는 쿼리는 사용하지 않아야 한다. 옵티마이저가 Batch I/O를 채택할 수 있기 때문이다.

3.3 인덱스 스캔 효율화

• 운영환경에서 가능한 일반적인 튜닝 기법은 인덱스 컬럼 추가다.
• 인덱스 엑세스 조건은 인덱슼 스캔 범위를 결정하는 조건절이다. 인덱스 수직적 탐색을 통해 스캔 시작점을 결정하는 데 영향을 미치고 인덱스 리프 블록을 스캔하다가 어디서 멈출지를 결정하는 데 영향을 미치는 조건절이다.
• 인덱스 필터 조건은 테이블로 엑세스할지를 결정하는 조건절이다.
• 인덱스를 이용하든, 테이블을 Full Scan하든, 테이블 엑세스 단계에서 처리되는 조건절은 모두 필터 조건이다. 테이블 필터 조건은 쿼리 수행 다음 단계로 전달하거나 최종 결과집합에 포함할지를 결정한다.
• 옵티마이저의 비용 계산 원리
  • 비용 = 인덱스 수직적 탐색 비용 + 인덱스 수평적 탐색 비용 + 테이블 랜덤 엑세스 비용 = 인덱스 루트와 브랜치 레벨에서 읽는 블록 수 + 인덱스 리프 블록을 스캔하는 과정에 읽는 블록 수 + 테이블 엑세스 과정에 읽는 블록 수
• 선행 컬럼이 모두 '=' 조건인 상태에서 첫 번째 나타나는 범위검색 조건이 인덱스 스캔 범위를 결정한다. 이들 조건이 인덱스 엑세스 조건이다. 나머지 인덱스 컬럼 조건은 모두 인덱스 필터 조건이다.
• 아래 몇 가지 케이스를 제외하면, 인덱스 컬럼에 대한 조건절은 모두 엑세스 조건에 표시된다. 첫 번째 나타나는 범위검색 조건 이후 조건절 컬럼은 스캔 범위를 줄이는 데 큰 역할을 못 하는데도 말이다.
  • 좌변 컬럼을 가공한 조건절
  • 왼쪽 '%' 또는 양쪽 '%' 기호를 사용한 like 조건절
  • 같은 컬럼에 대한 조건절이 두 개 이상일 때, 인덱스 엑세스 조건으로 선택되지 못한 조건절
  • OR Expansion 또는 INLIST ITERATOR로 선택되지 못한 OR 또는 IN 조건절
• 따라서, 실행 계획과 상관없이 첫 번째 범위검색 조건까지가 인덱스 엑세스 조건이고 나머지는 필터 조건이라고 이해하자.
• BETWEEN 조건을 IN-LIST로 전환하면 IN-LIST 개수만큼 UNION ALL 브랜치가 생성되고 각 브랜치마다 모든 컬럼을 '=' 조건으로 검색하므로 앞서 선두 컬럼에 BETWEEN을 사용할 때와 같은 비효율이 사라진다. 
• Index Skip Scan 방식으로 유도해도 비슷한 효과를 얻을 수 있다.
• IN-List 값들을 코드 테이블로 관리하고 있을 때는 NL방식 조인이나 서브쿼리로 구현하면 된다. NL 조인을 학습하고 나면 이해됨(4장)
• BETWEEN 조건 때문에 리프 블록을 많이 스캔하는 비효율보다 IN-List 개수만큼 브랜치 블록을 반복 탐색하는 비효율이 더 클 수도 있다. 따라서 인덱스 스캔 과정에 선택되는 레코드들이 서로 멀리 떨어져 있을 때만 유용하다는 사실을 기억해야 한다. BETWEEN 조건 때문에 인덱스를 비효율적으로 스캔하더라도 블록 I/O 측면에서는 대개 소량에 그치는 경우가 많다. 인덱스 리프 블록에는 테이블 블록과 달리 매우 많은 레코드가 담기기 때문이다.
• Index Skip Scan을 활용하면 BETWEEN 조건을 굳이 조건절을 바꿔가며 IN-List 조건으로 변환하지 않아도 같은 효과를 낼 수 있다.
• IN조건은 '='가 아니다. IN 조건이 '='이 되려면 IN-List Iterator 방식으로 풀려야만 한다. 그렇지 않으면 IN 조건은 필터 조건이다. IN조건이 엑세스 조건으로서 의미있는 역할을 하려면, 해당 컬럼의 데이터가 아주 많아야 한다. 그렇지 않은 상황에서는 필터 방식으로 처리되는 것이 오히려 낫다.
• num_index_keys 힌트를 사용하여 IN-List를 엑세스 조건 또는 필터 조건으로 유도할 수 있다. 세 번째 인자 'n'은 n번째 컬럼까지만 엑세스 조건으로 사용하라는 의미이다.
• LIKE와 BETWEEN은 둘 다 범위 검색 조건으로서, 비효율 원리가 똑같이 적용되지만 데이터 분포와 조건절 값에 따라 인덱스 스캔량이 서로 다를 수 있다. LIKE보다 BETWEEN이 낫다.
• 날짜의 경우 LIKE '2025%' 202500 이 있을까봐, 202513이 있을까봐 더 스캔하게 되므로 BETWEEN으로 좀 더 범위를 제한하는 것이 낫다.
• 옵티마이저에 의한 OR Expansion 쿼리 변환이 기본적으로 작동하지 않으므로 인덱스 선두 컬럼에 대한 옵션 조건에 (is null)OR 조건을 사용해선 안된다. 옵션 조건 컬럼을 선두에 두고 인덱스를 구성해도 이를 사용할 수 없다. 이 방식의 유일한 장점은 옵션 조건 컬럼이 NULL 허용 컬럼이더라도 결과집합을 보장한다는 것 뿐이다. (옵션조건이란 필수값이 아닌 조회조건을 의미한다. 즉 사용자 입력에 따라 파라미터가 null일 수도 있는 조건)

SELECT * 
FROM 고객 
WHERE (:cust_id IS NULL OR 고객ID = :cust_id) 
AND 거래일자 BETWEEN :dt1 AND :dt2;
-- 이 경우 고객ID에 null이 들어오면 table full scan을 할 가능성이 높다.

• 즉, 이 얘기는 인덱스 선두 컬럼에 OR 조건을 사용하면 안 좋다는 이야기지. OR 조건을 쓸 때 성능 개선을 위해 어떻게 해야하는가에 대한 이야기가 아니다. 이런 경우에는 거래일자가 선두인 인덱스가 있는 게 좋다.
• 인덱스에 포함되지 않은 컬럼에 대한 옵션 조건은 어차피 테이블에서 필터링할 수 박에 없으므로 그럴 때는 OR 조건을 사용해도 무방하다. OR 조건을 활용한 옵션 조건 처리를 정리하면 다음과 같다.
  • 인덱스 엑세스 조건으로 사용 불가
  • 인덱스 필터 조건으로도 사용 불가
  • 테이블 필터 조건으로만 사용 가능
  • 단, 인덱스 구성 컬럼 중 하나 이상이 Not Null 컬럼이면(모든 테이블 레코드가 인덱스에 저장되어 있음이 보장), 18c 부터 인덱스 필터 조건으로 사용 가능