INDEX 튜닝

SQL의 성능은 처리범위에 좌우된다.

이를 인덱스를 잘 설정하면 처리범위를 최소화 할 수 있게 되고, 결국 성능을 향상시킬수 있다.

0. 인덱스를 거는 이유

1. 처리범위의 양을 줄이기위해

2. 랜덤액세스의 양을 줄이기위해

3. 정렬횟수를 줄이기위해

1. 처리범위의 양을 줄이기위해

WHERE 절에서 점조건( IN , =  연산자를 이용한 조건)을 먼저 기술하고, 다름 선분 조건(LIKE, BETWEEN, <, > 등과 같이 점 조건을 제외한 연산자)를 기술 하는것이 처리범위를 감소시키는 방법이다.

단일인덱스 뿐만 아니라 결합컬럼인덱스를 사용하면 처리범위를 더욱 감소시킬수 있다. 결합인덱스를 사용할때 주의할 점은 점 조건과 선분 조건의 순서에 의해서 처리범위가 변한다는 것이다.

1.1. 결합인덱스를 구성하는 컬럼의 순서

1순위 : 컬럼이 사용한 연산자에 의한 인덱스 컬럼 선정

2순위 : 랜덤 액세스를 고려한 인덱스 컬럼 선정

3순위 : 정렬 제거를 위한 인덱스 컬럼 선정

4순위 : 단일 컬럼의 분포도를 고려한 인덱스 컬럼 선정

위의 규칙이 생긴 이유는 디스크 I/O를 최소화하는 가장 최적의 방법이기 때문이다.

2. 랜덤 액세스의 양을 줄이기위해

랜덤 액세스란, 데이터를 저장하는 블록을 한번의 I/O에 대해 여러번 액세스 하는 것이 아니라 한번의 I/O에 대해 하나의 블록만을 액세스 하는방식을 말한다.

레코드간 논리적, 물리적인 순서를 따르지 않고, 한건을 읽기 위해 한 블록씩 접근하는 방식

데이터를 추출하기 위해 한번의 I/O에 대해 여러개의 블록을 액세스 한다면 같은 양의 데이터에 대해 적은 횟수의 디스크 I/O가 발생하기 때문에 성능이 향상 될 수있다.

테이블을 처음부터 끝까지 액세스하는 테이블 전체스캔(Table Full Scan)의 경우에는 한 번의 I/O에 대해 여러 개의 블록을 액세스할 수 있기 때문에 한 번에 여러 블록을 액세스하는 다중 블록 I/O를 수행하게 된다.

2-1. 랜덤 액세스의 종류

2.1.1. 확인 랜덤 액세스 

WHERE, HAVING조건의 컬럼이 인덱스에 존재하지 않아 발생하는 랜덤 액세스

- 점조건, 선분조건으로 인덱스를 걸어준다.

2.1.2 추출 랜덤 액세스 

SELECT절의 컬럼이 인덱스에 존재하지 않아 발생하는 랜덤 액세스

- 매우 자주 사용하는 SQL에 대해서는 추출 랜덤 액세스 제거 고려

- 하나의 인덱스로 많은 SQL에 대해 추출 랜덤 액세스를 제거할 수 있다면 컬럼이 많더라도 인덱스에 컬럼 추가 고려

- 인라인 뷰를 통해 데이터가 감소하는 경우 ROWID 이용하여 추출 랜덤 액세스 감소 고려

※ ROWID?

행을 찾아가는 가장 빠른 방법

인덱스의 활용과도 연관성이 있다.

인덱스는 인덱스 컬럼과 rowid를 가지고 있다.

2.1.3 정렬 랜덤 액세스

ORDER BY, GROUP BY절에 사용될 컬럼이 존재하지 않아 발생하는 랜덤 액세스

- ORDER BY나 GROUP BY절에 있는 컬럼을 인덱스에 추가 해야한다.

2.1.4 요약

종류	발생 위치	추출된 데이터의 갯수
확인 랜덤 액세스	WHERE 절/HAVING 절	감소 또는 동일
정렬 랜덤 액세스	ORDER BY 절/GROUP BY 절	동일
추출 랜덤 액세스	SELECT 절	동일

정렬 랜덤액세스나 추출랜덤액세스는 랜덤액세스의 횟수와 추출되는 데이터 건수에는 변화가 없다. 하지만, 확인 랜덤 액세스는 추출되는 데이터의 건수가 감소될수있다(WHERE문 실행후 데이터 필터링되기 때문에)

그러므로, 가장먼저 확인 랜덤 액세스를 줄이는 방법을 고려해야한다.

3. 정렬횟수를 줄이기위해

ORDER BY를 사용하지 않더라도, 인덱스에 의해서 정렬이 되기 때문에, 인덱스를 잘 활용하면 정렬의 양을 줄일수 있다.

DBMS에서 SQL의 정렬을 제거할 수 있다면, SQL의 단순화와 성능 향상이라는 매우 큰 혜택을 얻을 것이다.

테이블 - 데이터가 INSERT되는 순서에 의해 저장되므로 어떤 컬럼에 의해 정렬된 데이터가 저장되지않는다.

인덱스 - 인덱스를 구성하는 컬럼에 의해 정렬된 데이터가 저장된다. 인덱스의 첫번째 컬럼에 의해 정렬되며, 첫번째 컬럼의 값이 동일한 데이터에 대해서는 인덱스의 두번째 컬럼에 의해 정렬된다.

인덱스 조건 : 점조건~ ORDER BY절의 컬럼순서대로 ~ 선분조건~

출처 : http://www.gurubee.net/expert/kwontra