SQL 튜닝 - INDEX

* 목적: DB의 객체로써 조회의 속도를 빠르게 하기 위해 사용. 단, 오라클의 경우 인덱스를 생성할 수 있는 수의 제한은 없지만, 너무 많은 인덱스를 제작할 경우 SQL문의 실행 속도가 떨어질 수 있다.

 

* 인덱스는 그 대상인 테이블과느 별도의 형태로 독립적인 저장 공간을 가지고 존재한다.

1. B*TREE INDEX

* 구조

https://ko.wikipedia.org/wiki/B_%ED%8A%B8%EB%A6%AC#%EC%82%BD%EC%9E%85

B 트리 - 위키백과, 우리 모두의 백과사전

 

* 장점

- 모든 Leaf 노드가 같은 깊이에 존재하기 때문에 어떤 위치에 있는 자료를 접근하더라도 거의 동일한 시간이 걸림

- 빠른 자료의 조회를 위해 Key 값이 정렬되도록 유지됨

- 자동적으로 노드 간에 균형 상태 유지

- 모든 블록은 평균적으로 3/4 정도의 자료가 입력되므로 구조 자체의 손실이 적음

- 특정 값 질의, 일정 범위 조회 질의 등 질의에 대해 최상의 성능 유지

- 테이블의 크기가 증가하더라도 수행 속도가 저하되는 현상이 적음

 

* 실제 구성

(약 90만건의 SALES 데이터를 가진 SALES 테이블로 활용)

CREATE INDEX SALES_IDX_PROD_CUST_ID ON SALES(PROD_ID, CUST_ID);

ANALYZE INDEX SALES_IDX_PROD_CUST_ID COMPUTE STATISTICS;

SELECT INDEX_NAME, UNIQUENESS, BLEVEL, LEAF_BLOCKS
FROM USER_INDEXES
WHERE INDEX_NAME = 'SALES_IDX_PROD_CUST_ID';

깊이(LEVEL)이 2이고, LEAF BLOCK이 총 2375개인 구조로 생성

- 구성된 인덱스 정보 확인

SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME, COLUMN_POSITION
FROM USER_IND_COLUMNS
WHERE TABLE_NAME = 'SALES'
ORDER BY INDEX_NAME, COLUMN_POSITION;

* Rowid In Oracle

오라클에서 사용하는 자료형 중의 하나, DB 내부에 있는 자료의 저장 위치

- 확장형 Rowid

SELECT ROWID, ENAME FROM EMP WHERE DEPTNO = 20;

Rowid는 다음과 같이 구성되어있다.

AAAAAA	FFF	BBBBBB	RRR
데이터 오브젝트 번호	데이터 파일 번호(유일)	데이터 블록 번호	블록 내부의 ROW 번호(0부터 시작)

 

- 제한형 Rowid

이진수의 표시형식을 사용

데이터블록(8자리)

Row 번호(4자리)

데이터 파일(4자리)

2. 인덱스의 선정

인덱스는 일반적으로 10~15% 이내의 테이블 자료를 액세스 할 경우 효율적, 그 이상의 자료를 액세스 할 경우에는 FTS(Full Table Scan)이 더 좋은 성능을 보여준다.

 

* 인덱스 선정 절차

1) 프로그램 개발에 이용된 모든 테이블에 대하여 Access Path 조사

 

2) 인덱스 칼럼 선정 및 분포도 조사

: 분포도(Cardinality)는 하나의 칼럼에 얼마나 다양한 값이 어떻게 분포되어 있는지를 나타낸다. 분포도가 클수록(다양한 값으로 구성되어 있을 때) 인덱스에 적합한 칼럼이다.

 

★ 인덱스에 포함 / 미포함해야 하는 칼럼의 예시

포함	미포함
- Where 절에 자주 사용되는 컬럼 - 테이블 조인에 자주 사용되는 컬럼 - 분포도가 좋거나 유일한 칼럼 - Select 문장의 Min, Max의 기준이 되거나 정렬에 자주 사용되는 칼럼	- 분포도가 좋지 않은 칼럼 - 테이블의 Row 수가 적은 칼럼 - 자주 수정하는 칼럼

 

3) Critical Access Path 결정 및 우선 순위 선정

 

4) 인덱스 칼럼의 조합 및 순서 결정

: '부서 내의 어떤 사원'의 인덱스를 제작할 경우, 부서번호, 사원번호 순서로 인덱스를 제작해야 한다.

 Equal(=) 연산자로 사용되는 칼럼을 앞에 두어 폭을 줄여 속도를 향상할 수 있다.

 

5) 시험 생성 및 테스트

- 테이블이 변경되는 정도에 따라 PCTFREE 옵션이 지정되어야 한다. PRIMARY KEY와 같이 거의 수정되지 않을 인덱스에 대해서는 값을 낮게 지정한다.

- 인덱스를 생성할 때 반드시 테이블스페이스를 지정해야 한다. 지정하지 않은 경우 SYSTEM TABLESPACE에 저장된다.

- 인덱스를 생성할 때 INITRANS, MAXTRANS 옵션을 지정한다.

- 인덱스를 생성하는 속도를 증가시키기 위해 UNRECOVERABLE 옵션을 사용하여 REDO LOG를 생략할 수 있습니다. 이 방법은 인덱스의 생성이 로그에 남지 않기 때문에 반드시 DB 백업을 수행해야 한다.

 

6) 결정된 인덱스를 기준으로 프로그램 반영

 

7) 실제 적용

 

* 인덱스 선정 기준

- 분포도가 좋은 칼럼은 단독적으로 생성하여 활용도 향상(10 ~ 15% 이내)

- 자주 조합되어 사용되는 칼럼의 경우에는 결합 인덱스 생성 고려

- 인덱스 간의 역할 정의(가능한 모든 Access Path를 만족시킬 수 있는)

- 수정이 빈번하지 않은 칼럼 사용

- 외부 키로 사용된 칼럼에 대하여 생성

- 정렬 기준으로 자주 사용되는 칼럼에 대한 인덱스 생성

 

* 사용 케이스

1) 고유 인덱스의 Equal(=) 검색

SELECT * FROM EMP WHERE EMPNO = 7788;

EMP_EMPNO_IX로 ROWID를 얻고, 랜덤 액세스를 통해 ROW를 조회

2) 고유 인덱스의 범위(Range) 검색

SELECT * FROM EMP WHERE EMPNO <= 7654;

조건과 일치하는 첫 위치부터 아래로 검색하여 값이 일치하지 않는 곳에서 검색을 종료

3) 중복 인덱스의 범위 검색

SELECT * FROM EMP WHERE JOB LIKE 'SALE%';

범위 검색으로 INDEX RANGE SCAN이 일어난다.

4) OR & IN 조건

SELECT * FROM EMP2 WHERE EMPNO = 7654 OR EMPNO = 7788;

SELECT * FROM EMP2 WHERE EMPNO IN (7654, 7788);

INLIST ITERATOR

3. 인덱스를 사용하지 못하는 경우

1) NOT 연산자 사용: FTS가 효율이 더 좋음

2) IS NULL, IS NOT NULL 사용: 아직 정의되지 않은 미지의 값인 NULL 값은 인덱스에 저장하지 않기 때문

3) 옵티마이저의 선택

4) 외부 변형

- 서프레싱(Suppressing): 칼럼을 변형함으로써 인덱스를 사용하지 못하게 하는 것

- 서프레싱에는 외부적인 변형(사용자가 의도적으로 칼럼 값에 변경을 가한 것), 내부적인 변형(데이터베이스에 의해 자동적으로 수행되는 변형)

- 칼럼에 NVL, RTRIM과 같은 작업을 할 경우 칼럼이 변형되어 인덱스의 값과 다르다고 옵티마이저가 판단한다.

 

(예시 1) - 불필요한 함수를 사용한 경우

Q. 사원정보 테이블에서 이름이 'M'으로 시작하는 모든 사원의 정보를 조회

-- 인덱스 사용 X
SELECT * 
FROM EMP
WHERE SUBSTR(ENAME, 1, 1) = 'M';

-- 인덱스 사용 O
SELECT *
FROM EMP
WHERE LIKE 'M%';

 

 (예시2) - 문자열 결합

-- INDEX X
SELECT * 
FROM EMP 
WHERE JOB||DEPT='MANAGER10';

-- INDEX O
SELECT *
FROM EMP
WHERE JOB = 'MANAGER'
AND DEPT = 10;

 

(예시 3) - DATE 변수의 가공

-- INDEX X
SELECT *
FROM EMP
WHERE TO_CHAR(HIREDATE, 'YYMMDD') = '021016';

-- INDEX O
SELECT *
FROM EMP
WHERE HIREDATE >= TO_DATE('021016', 'YYMMDD')
AND HIREDATE < TO_DATE('021016', YYMMDD')+1;

 

(예시 4) - 불필요한 NULL 처리

-- INDEX X
SELECT *
FROM EMP
WHERE NVL(JOB, 'NOJOB') = 'MANAGER';

-- INDEX O
SELECT *
FROM EMP
WHERE JOB = 'MANAGER';

 

(예시 5) - 산술식의 적용

-- INDEX X
SELECT *
FROM EMP
WHERE SAL*12 > 40000;

-- INDEX O
SELECT *
FROM EMP
WHERE SAL > 40000 /12;

※ 칼럼의 분포도가 좋지 않은 경우, 고의적으로 변형시켜 RANGE가 아닌 FULL SCAN으로 유도할 수 있다.

※ 예를 들어 2개의 컬럼으로 검색을 하는데, 하나의 칼럼의 분포도는 좋고, 다른 하나의 칼럼의 분포도가 좋지 않다면 고의적으로 분포도가 좋지 않은 칼럼은 변형시켜 하나의 인덱스만 사용하도록 할 수 있다.

※ 두 개의 테이블을 조인할 때, 다른 한쪽의 결과가 적은 경우 적은 테이블의 인덱스를 사용하게 유도할 수 있다.

 

5) 내부적인 변형

- 서로 다른 자료형을 비교나 할당하는 경우 발생

4. 결합 인덱스(Composite Index)

* Index Merge

- 결합 인덱스: 2개 이상의 칼럼을 하나의 인덱스로 생성하여 액세스 경로를 구성하는 것

- 인덱스 머지: 개별 칼럼에 인덱스가 생성되어 있으면서 모두 WHERE절에 Equal(=) 조건으로 사용되었다면, 각각의 인덱스 조합으로 자료를 접근하는 것

- 결합 인덱스가 인덱스 머지보다 좋은 액세스 경로를 제공하지만, 제한이 있다.

SELECT 과정ID, 회사NO FROM TAB1
WHERE 과정ID = 700 AND 회사NO = '100';

위 SQL문에 인덱스 머지가 적용되는 경우, 실행 과정은 다음과 같다.

1) 과정ID와 회사NO에 생성되어 있는 인덱스를 동시 검색

2) 각 ROWID가 속한 블록의 위치가 빠른 것으로 검색을 시작(과정 ID의 ROWID가 회사 NO보다 작으면 과정 ID로 검색을 시작)

3) 상대방의 ROWID보다 작으면 계속 검색

4) 절차 반복

5) 검색 종료

 

* 결합 인덱스(Composite Index)

한 테이블의 여러 칼럼을 조합하여 인덱스를 생성하는 것, 최대 32개의 칼럼까지 포함할 수 있고, 순서에 상관이 없다. 하지만 배열 순서는 중요하다.

 

- 결합 인덱스 칼럼 선택

WHERE 절에서 AND 조건으로 자주 결합되어 사용하면서, 각각의 분포도 보다 두 개 이상의 칼럼이 결합되었을 때 분포도가 양호한 경우 사용

다른 테이블과 조인의 연결고리로 자주 사용되는 칼럼

하나 이상의 키 칼럼 조건으로 같은 집합의 칼럼들이 자주 조회될 때

- 결합 순서

1) Access Path 조건에 많이 사용되어지는 칼럼을 우선

2) '='로 사용되는 칼럼을 우선

3) 분포도가 좋은 칼럼을 우선

4) 자주 이용되는 Sort의 순서로 결정

5. 기타 인덱스

* 함수 기준 인덱스(FBI, Function Based Index)

- 정의: 인덱스로 사용되는 칼럼의 변환된 모습이나 연산식의 결과 값을 인덱스에 저장하는 것

- 옵티마이저가 인덱스를 사용하여 실행계획을 수립하기 위해 테이블에 ANALYZE 명령을 수행해 통계 값을 설정해야 한다. 그리고 Init 파일에서 OPTIMIZER_MODE를 FIRST_ROWS, ALL_ROWS로 지정하거나 Hint를 사용해 CBO가 사용되도록 한다.

* 클러스터 인덱스(Cluster Index)

- 클러스터: 테이블의 데이터를 저장하는 방법으로써 클러스터 키라고 부르는 공통된 칼럼을 기준으로 하나 이상의 테이블 데이터를 동일한 데이터 블록에 모아서 저장할 수 있는 선택적 기능

- Regular 인덱스는 인덱스에서 찾고자 하는 Row의 Rowid를 읽어 들여 테이블에서 해당 Row를 랜덤 액세스로 읽어 들인다. Cluster 인덱스는 클러스터 키 값에 의하여 해당 데이터가 저장된 첫 번째 블록의 위치를 찾는다.(물리적인 I/O 성능이 좋아짐)

- 비교

일반 인덱스(Regular Index)	클러스터 인덱스(Cluster Index)
Null 저장 X	Null 저장
KEY - ROWID 저장	KEY - 1st Data Block 저장
Rowid를 통한 Row 액세스	Key에 대한 데이터 Block 액세스
Row별로 독립적인 엔트리	클러스터 키당 하나의 엔트리
생성은 선택적	클러스터 사용을 위한 필수