3. 인덱스 튜닝

3.1 테이블 액세스 최소화

• sql 튜닝은 랜덤 I/O와의 전쟁이다. 그만큼 랜덤 I/O 줄이는 것이 중요
• 테이블 랜덤 액세스 최소화 하는 구체적인 방법을 소개

3.1.1 테이블 랜덤 액세스

• 인덱스로 검색하는데 왜 느릴까?
• 인덱스로 검색해도 빠른데, 왜 굳이 파티셔닝을할까?

인덱스 ROWID는 물리적 주소? 논리적 주소?

• sql이 참조하는 컬럼을 인덱스가 모두 포함하는 경우가 아니면, 인덱스를 스캔한 후에 반드시 테이블을 액세스한다.
  TABLE ACCESS BY INDEX ROWID
• 인덱스를 스캔하는 이유 : 검색 조건을 만족하는 소량의 데이터를 인덱스에서 빨리 찾고 테이블 레코드를 팢아기기 위한 주소값, ROWID를 얻으러고 하는데 있다.
• rowId는 물리적 주소일까, 논리적 주소일까? 논리적 주소에 가깝다.
  • 물리적 주소 : 오브젝트 번호, 상대파일번호, 블록 번호,  데이터 파일 번호 로 구성
  • 논리적 주소 : 물리적으로 직접 연결되지 않고 테이블 레코드를 찾아가기 위한 논리적 주소 정보를 담고 있기 때문

 

• I/O 메커니즘 복습
  • DBA : 데이터파일번호 + 블록번호 - 디스크상에서 블록을 찾기 위한 주소 정보이다.
  • 매번 디스크에서 블록을 읽을 수는 없기 때문에 I/O 성능을 높이기 위해서 버퍼 캐시를 활용한다.
  • 캐시에 적재할 때 읽을 때 같은 해시 함수를 사용하므로 버퍼 헤더는 항ㅅ아 같은 해시 체인에 연결된다.
  • 반면 실제 데이터가 담긴 버퍼 블록은 매번 다른 위치에 캐싱되는데, 그 메모리 주소값을 버퍼 헤더가 가지고 있다.
  • 해싱 알고리즘으로 버퍼 헤더를 찾고, 버퍼 헤더에서 얻은 포인터로 버퍼 블록을 찾는다.
  • 인덱스 rowId는 포인터가 아니다. 논리적 주소 정보다
    1. rowid가 기리키는 테이블 블록을 버퍼캐시에서 먼저 찾고
    2. 못 찾을 때만 디스크에서 블록을 읽는다.
    3. 물론 버퍼캐시에 적재한 후에 읽는다.
  • 인덱스 rowId를 이용한 테이블 액세스는 고비용 구조다.

3.1.2 인덱스 클러스터링 팩터

• 클러스터링 : 하나의 데이터를 여러개의 부분집합으로 분할 하는것
• 팩터 : 특정 주제 || 분야에서 주용한 요소, 변수를 의미
  
  
• 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도
• 동일한 정보의 block끼리 뭉쳐져 있기 때문에 table 액세스량에 비해 block I/O가 적게 발생한다.
  - 10개 block에 흩어져 있는 정보가 클러스터링을 통해서 4개의 block만 접근할 수 있게 정보가 뭉쳐지게 된다.
  
  
• 하지만 가능한 쓰면 안되는 방법이다. cf) cold data(집계 방법)일 때는 상관이 없다.
  • 수동으로 처리하기 때문에 변경이 발생할 때마다 새로 디스크 모음을 해야하는 불편함이 존재

3.1.3 인덱스 손익분기점

• 결과집합이 10 ~ 15% 일 때는 index가 빠리고 이보다 클 경우에는 table full scan이 빠르다.
• 10~15%의 table scan과 index scan의 효율성이 변경되는 부분을 손익 분기점이라고 한다.
• 참고로 CF(클러스터링 팩터)가 좋으면 손익 분기점이 90%까지 상승한다.

 

• 손익분기점 개념 취지 : 테이블 스캔이 항상 나쁜것도 아니고, 인덱스 스캔이 항상좋은 것도 아니다를 보여줄 뿐 index 손익분기점을 높이기 위해 어떤 조치를 해야 한다는 뜻이 아니다.
• oltp(온라인 트랜젝션, hot data)을 처리하는 프로그램과 dw / olap / 배치 프로그램 튜닝의 특징을 구분 짓는 핵심 개념

 

온라인 프로그램 튜닝 vs 배치 프로그램 튜닝

• 온라인 프로그램 튜닝 : hot data, 소량 데이터를 읽고 갱신하므로 인덱스를 효과적으로 활용 가능, 조인도 대부분 NL 방식 사용
• 배치 프로그램 튜닝 : 대량 데이터를 읽고 갱신하는 배치 프로그램은 항상 전체범위 처리 기준으로 튜닝한다. table full scan, 해시 조인이 유리하다.

 

3.1.4 인덱스 컬럼 추가

• 가장 일반적으로 사용하는 튜닝 기법은 인덱스에 컬럼을 추가
• 실 운영 환경에서는 결합 인덱스 구성을 임의로 변경하기가 절대 쉽지 않다. - 기존 인덱스를 사용하는 sql이 있을 수 있기 때문
• 인덱스를 새로 만들자니 인덱스를 추가하다 보면 테이블마다 인덱스가 수십 개씩 달려 배보다 배꼽이 커지게 된다.
• 따라서 기존 결합 인덱스에 인덱스가 필요한 컬럼을 뒤에 추가하는것이 큰 효과를 발휘

 

3.1.6 인덱스 구조 테이블

• = IOT
• 테이블을 인덱스 구조로 생성하는 방법 - 일반적으로 인덱스로 rowId 얻고 랜덤 액세스를 돌리는 과정을 없애기
• 화살표를 극단적으로 줄인 케이스
• 테이블 블록에 있어야 할 데이터를 인덱스 리프 블록에 모두 저장하고 있는 것
• 인덱스 리프 블록이 곧 데이터 블록

# 인덱스 구조로 만드는 구문

create table index_org_t (a number, b varchar(10),
constraint index_org_t_pk primary key (a))
orgamization index;

• 일반 힙 구조 테이블에 데이터를 입력할 때는 랜덤 방식 사용
• 반면, IOT는 인덱스 구조 테이블이므로 정렬 상태를 유지하며 데이터를 입력한다.
• IOT는 인위적으로 클러스터링 팩터를 좋게 만드는 방법 중 하나다.
  -> 같은 값을 가진 레코드들이 100% 정렬된 상태로 모여 있으므로 랜덤 액세스가 아닌 시퀀셜 방식으로 데이터를 액세스한다.
  따라서 between || 부등호 조건으로 넓은 범위를 읽을 때 유리
• 데이터 입력과 조회 패턴이 서로 다른 테이블에도 유용
• ex) 실적 등록은 일자별로 진행 but 실적조회는 주로 사원별로 이뤄진다.
  사원별 실적 확인시, 365개의 block을 뒤져야하지만 사번이 첫번째 정렬 기준이 되도록 IOT 구성하면 4개 block만 읽으면 된다.

 

3.1.7 클러스터 테이블

• 인덱스 클러스터와 해시 클러스터 2가지 존재
  1. 인덱스 클러스터 테이블
  2. 해시 클러스터 테이블

 

인덱스 클러스터 테이블 : table을 data별로 쪼개 놓은 것

• 클러스터 키 값이 같은 레코드를 한 블록에 모아서 저장하는 구조
• 클러스터에 테이블을 담기전에 반드시 클러스터 인덱스를 정의해야한다.
• 클러스터 인덱스는 데이터 검색 용도로 사용할 뿐만 아니라 데이터가 저장될 위치를 찾을 때도 사용하기 때문

# 인덱스 클러스터 테이블 구성
create cluster c_dept# (deptno number(2) ) index;

# 클러스터 인덱스 만들기
create index c_dept#_idx on clluster c_dept#;

# 클러스터 인덱스를 만들었으면 아래와 같이 클러스터 테이블을 생성
create table dept (
deptno number(2) not null
, dname varchar(14)  not null
, loc varchar(13) )
cluster c_dept# (deptno);

• 클러스터 인덱스는 테이블 레코드와 1:M 관계를 갖는다.
• 따라서 클러스터 인덱스의 키 값은 항상 Unique == 중복 값이 없다.
• 시퀀셜 방식으로 스캔하기 때문에 넓은 범위를 읽더라도 비효율이 없다는 게 핵심원리

 

해시 클러스터 테이블

• 인덱스를 사용하지 않고 해시 알고리즘을 사용해 클러스터를 찾아간다 차이만 존재

 

3.2 부분범위 처리 활용

select * from table; 을 할 경우 딱 보이는 view 까지만 보여주고 스크롤 내리면 그 때 또 가져옴

 

3.2.1 부분범위 처리

• 전체 결과집합 중 아직 전송하지 않은 분량이 많이 남아있어도 서버 프로세스는 클라이언트로부터 추가 fetch call (스크롤)을 받기 전까지 그대로 멈춰 서서 기다린다.
• 1억 건짜리 테이블인데도 결과를 빨리 출력할 수 있는 이유는, 먼저 읽는 데이터부터 일정량을 전송하고 멈추기 때문dlek.
• 데이터를 전송하고 나면 서버 프로세스는 CPU를 OS에 반환하고 대기 큐에서 잠을 잔다.

 

• 전체 쿼리 결과 집합을 쉼 없이 연속적으로 전송하지 않고 사용자로부터 Fetch Call 이 잇을 때마다 일정량씩 나눠서 전송하는 것을 부분범위 처리라고 한다.

정렬 조건이 있을 때 부분범위 처리

• 만약 부분범위 처리를 할 때 order by를 추가할 경우, 원래는 모든 데이터를 다 읽어 정렬을 마친 후에야 client에게 데이터 전송을 시작해야 하는 전체범위처리를 해야한다.
• 하지만 created 컬럼이 선두인 인덱스가 존재시, 부분 범위 처리가 가능하다. - 인덱스는 항상 정렬된 상태를 유지하기 때문이다.

 

ArraySize 조정을 통한 Fetch Call 최소화 

• 부분범위 처리 원리에서 네트워크를 통해 전송해야 할 데이터량에 따라 배열 사이즈 조절을 할 수 있다.
• 쿼리 툴인 오렌지에서도 배열 사이즈를 조절할 수 있다.

 

* 배치 I/O

• index를 이용해 sort 연산을 생략할 때 사용
• 배치(Batch) : 모았다가 한번에 처리
• 배치 I/O는 읽는 블록마다 건건이 I/O Call을 발생시키는 비효율을 줄이기 위해 고안한 기능
• 테이블 블록에 대한 디스크 I/O Call을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리

 

3.3 인덱스 스캔 효율화

• 중요
• 일반적인 튜닝 기법인 인덱스 컬럼 추가 외에 다양한 튜닝 기법 소개
• 케이스 별로 기억하기

3.3.1 인덱스 탐색

• 인덱스 조건절 처리시 속도 비교
• 173p ~179p 실습 자주 하기

3.3.2 인덱스 스캔 효율성

• 결합 index여서 b* tree는 1개이지만 조건절에서는 각각 작성해야한다. 이때, 중간에 조건절이 빠지게 되면 index 효과를 받지 못한다.
• 물론 선두 index 컬럼은 무조건 있어야한다.
• ex) 성 - 능 - 감 - 시   |    성 - 능 -   -  시
• 선두 컬럼, 선행 컬럼
  • 선두 컬럼 : 인덱스 구성상 맨 앞쪽에 있는 컬럼을 지칭할 때 사용
  • 선행 컬럼 : 상대적으로 앞쪽에 놓인 컬럼을 칭할 때 사용

 

3.3.3 액세스 조건과 필터 조건

• 액세스 조건 : 결정적 조건
• 필터 조건 : 있으나 마나한 조건
• index 튜닝 기법
  1. where 절에 자주 들어가는 조건 : index하기
  2. 결합 인덱스 순서
  3. 등치( = ) 여야한다. between 같은 건 안된다.
  4. 중간 조건 비어있을 때 안됨
• index 튜닝 기법의 조건을 만족하면 index access 조건, 만족하지 못하면 index filter 조건

 

3.3.6 between을 IN-List로 전환

• between의 경우 index 성능을 낮추기 때문에 in을 사용한다.
• 옵티마이저가 자동으로 Union all로 변환하기 때문에 성능을 높일 수 있다.

 

3.3.7 index skip scan 활용

• 이건 어려워서 나중에 볼것

3.3.9 Between과 Like 스캔범위 비교

• Like 보다는 Between을 사용하는 것이 났다. 무조건 그렇게 할 것
• like보다 범위가 더 좁을 수 밖에 없기 때문 

 

3.3.12 함수 호출부하 해소를 위한 인덱스 구성

• 개발자가 직접 만든 sql 함수 사용은 지양하는 것이 좋다.
• 내장 함수와 sql은 동일 context에서 수행되지만 생성자 함수는 다른 context에서부터 수행되므로 성능이 무조건 떨어진다.

 

3.4 인덱스 설계

• 인덱스 튜닝, 더 나아가 SQL 튜닝의 하이라이트라고 할 수 있다.
• 많은 경험과 고도의 기술력이 요구되는 매우 전문적인 설계 영역
• 세밀한 인덱스 원리와 이론을 바탕으로 많은 시행책오를 겪어야 하는데 시행착오를 줄길 노하우를 소개

3.4.1 인덱스 설계가 어려운 이유 

• 추가하는 건 쉬워도 수정, 삭제가 빡세다
  인덱스 추가는 시스템에 부하를 주고, 인덱스 변경은 운영 리스크가 크다
• 최적화된 인덱스를 마음껏 생성할 수 있다면, sql 튜닝과 인덱스 설계만큼 쉬운 일도 없다.
• 하지만 인데스가 많으면 문제가 생긴다.
  1. DML 성능 저하 (TPS - transaction per sec 저하)
  2. 데이터베이스 사이즈 증가 - 디스크 공간 낭비
  3. 데이터베이스 관리 및 운영 비용 상승
• 참고로 인덱스를 추가하는 문제점에서는 cold data == 집계 table은 상관이 없다.
  - 오래된 data는 변할 가능성이 없기 때문

 

3.4.2 가장 중요한 2가지 선택 기준

• default 기준: 인덱스 선두 컬럼을 조건절에 반드시 사용해야한다.
• 첫 번째 기준: 조건절에 항상 사용하거나, 자주 사용하는 컬럼을 선정하는 것
• 두 번째 기준: '=' 조건으로 자주 조회하는 컬럼을 앞쪽에 두어야 한다.

 

• 기술적으로 위의 공식이 끝이지만 업무를 모르면 수정이 불가 하기 때문에 어려운 것

3.4.3 스캔 효율성 이외의 판단 기준

• 수행 빈도 - 1년에 1번 건들이는건 튜닝할 필요가 없다
• 업무상 중요도 - 해당 업무의 쿼리는 n 초안에 끝나야만 하는 경우 튜닝 필요
• 클러스터링 팩터
• 데이터량
• DML 부하
• 저장공간
• 인덱스 관리 비용

 

3.4.4 공식을 초월한 전략적 설계

• 최적을 달성해야 할 가장 핵심적인 액세스 경로 한두 개를 전략적으로 선택해서 최적 인덱스를 설계하고, 나머지 액세스 경로는 약간의 비효율이 있더라도 목표 성능을 만족하는 수준으로 인덱스를 구성할 수 있어야 한다.
• 단순히 공식에 의한 결정이 아니라, 업무 상황을 이해하고 나름의 판단 기준을 가지고 결정을 내려야 한다.
• 236p 에는 between을 써야하는 예시를 보여준다.
  
  
• 핵심은 index sceen 효율보다 테이블 액세스가 더 큰 부하요소라는 것을 인지해야한다.
• 그리고 일자 조회구간(between)이 길지 않으면 인덱스 스캔 비효율이 성능에 미치는 영향이 크지 않다는 점이다.

 

3.4.5 소트 연산을 생략하기 위한 컬럼 추가

• 조건절에 사용하지 않는 컬럼이더라도 소트 연산을 생략할 목적으로 인덱스 구성에 포함시킴으로써 성능 개선을 도모할 수 있다.
• I/O 최소화 및 sort 연산 생략을 위한 인덱스 구성 공식
  1. '=' 연산자로 사용한 조건절 컬럼 선정
  2. order by 절에 기술한 컬럼 추가
  3. '=' 연산자가 아닌 조건절 컬럼은 데이터 분포를 고려해 추가 여부 결정

 

3.4.6 중복 인덱스 제거

• 동일 column들의 결합 인덱스여도 순서가 다르면 중복이 아니다.

 

3.4.8 인덱스 설계도 작성

• 전반적인 업무에 관한 인덱스 설계도를 만들어야지 인덱스 튜닝을 할 수 있다.