SQL이 성능을 보장받기 위해서 어떻게 해야 하는가? 그 정답은 인덱스에 있을 것이다. 그렇다면 모든 SQL이 인덱스를 이용하면 성능을 보장받을 수 있을까?

SQL이 인덱스를 이용하는지 이용하지 않는지를 가지고 성능을 예측하는 것은 우리에게 많은 시행 착오를 가져다 줄 것이다.

물론, 해당 SQL이 인덱스를 이용해야 할 지 아니면 인덱스를 이용하지 말아야 할지를 결정하는 것이 SQL 최적화의 첫 걸음임에는 틀림 없다. 하지만, 이것만으로 SQL을 최적화할 수 없는 것이 현실이다.

우리는 인덱스를 이용해야 하는 SQL을 구분하여 해당 SQL은 최적의 인덱스를 이용해야 하며 이와 같은 최적의 인덱스를 이용하기 위해서는 결합 인덱스를 선정하는 기준을 이해해야 할 것이다.

지난 강의의 연산자에 의한 결합 인덱스 컬럼 순서 선정에이어 결합 인덱스를 구성하는 두 번째 우선 순위를 확인해 보자.

랜덤 액세스를 이해하자

결합 인덱스의 컬럼 순서는 네 개의 우선 순위에 의해 생성해야 할 것이다. 우선 순위를 어긋나게 인덱스를 생성한다면 우리는 더 이상 해당 인덱스를 이용하여 성능 향상을 기대하기는 어렵다.

• - 1순위 : 컬럼이 사용한 연산자에 의한 인덱스 컬럼 선정
• - 2순위 : 랜덤 액세스를 고려한 인덱스 컬럼 선정
• - 3순위 : 정렬 제거를 위한 인덱스 컬럼 선정
• - 4순위 : 단일 컬럼의 분포도를 고려한 인덱스 컬럼 선정

두 번째 우선 순위인 랜덤 액세스는 무엇인가? 랜덤 액세스는 데이터를 저장하는 블록을 한번에 여러 개 액세스하는 것이 아니라 한 번에 하나의 블록만을 액세스하는 방식이다.

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

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

그렇다면 과연 어떤 작업에서 랜덤 액세스가 발생하는 것일까? 바로 인덱스를 액세스하여 확인한 ROWID를 이용하여 테이블을 액세스하는 경우 랜덤액세스가 발생하게 된다.

ROWID는 해당 데이터를 찾아가는 유일한 주소 값이며 우리가 인덱스를 이용한다는 것은 인덱스로부터 조건을 만족하는 인덱스 값을 액세스한 후 ROWID를 확인하여 ROWID 값으로 테이블을 액세스하는 것을 의미한다.

이와 같이 인덱스 액세스 후 테이블을 액세스하는 경우에 발생하는 I/O는 한 번에 하나의 블록만 액세스하는 랜덤 액세스가 발생하게 된다.

랜덤 액세스의 종류를 분석하자

인덱스 액세스 후 테이블을 액세스하는 것은 랜덤 액세스를 발생시키게 된다.

이와 같이 발생하는 랜덤 액세스는 모두동일한 것인가? 그렇다면 랜덤 액세스에는 어떤 종류가 존재하는가? 우리가 보기에는 동일한 랜덤 액세스로 보이지만 실제로는 랜덤 액세스는 다음과 같이 세 가지로 구분된다.

• - 확인 랜덤 액세스
• - 추출 랜덤 액세스
• - 정렬 랜덤 액세스

첫 번째로 확인 랜덤 액세스는 무엇인가? 확인 랜덤 액세스는 WHERE 조건에 의해 발생하게 된다.

만약, WHERE 조건에는 카드번호 조건과 거래일자 조건이 함께 있으며 인덱스는 카드번호 컬럼으로만 구성되어 있다고 가정하자. 이와같다면 카드번호 컬럼에 의해 인덱스를 액세스하게 될 것이다.

결국, 카드번호 컬럼에 의해 처리 범위가 감소하게 된다. 그렇다면 거래일자 컬럼은 어떤 역할을 수행하게 되는가?

분명 카드번호 조건을 만족하는 데이터 중 거래일자 조건을 만족하는 데이터만을 결과로 추출해야 할 것이다. 하지만, 카드번호 컬럼으로만 인덱스가 구성되므로 거래일자 조건을 인덱스에서 확인할 수 없게 된다.

따라서, 카드번호 조건을 만족하는 모든 데이터에 대해 테이블을 액세스하여 거래일자 컬럼의 값을 확인해야 할 것이다.

이와 같이 WHERE 조건의 컬럼이 인덱스에 존재하지 않아 테이블을 액세스하는 랜덤 액세스를 확인 랜덤 액세스하고 한다.

확인 랜덤 액세스의 특징은 랜덤 액세스의 횟수보다 최종 결과가 동일하거나 더 적게추출된다는 것이다. 확인 랜덤 액세스는 랜덤 액세스가 발생한 후 버려지는 데이터가 존재하기 때문이다.

애써서 테이블을 액세스한 후 버려진다는 것은 매우 안타까운 일이다. 인덱스를 선정 함에 있어 확인 랜덤 액세스의 제거는 성능에 있어매우 중요한 역할을 수행하게 된다.

두 번째로 추출 랜덤 액세스를 확인해 보자.

WHERE 절의컬럼이 아니라 SELECT 절의 컬럼을 확인해 보자. WHERE절의 컬럼은 모두 인덱스에 존재하지만 SELECT 절의 컬럼이 인덱스에 존재하지 않는다면 어떤 현상이 발생하는가?

SELECT 절의 컬럼을 결과로 추출하기 위해서는 반드시 인덱스 액세스 후 테이블을 액세스해야 할 것이다. 이와 같은 현상이 추출 랜덤 액세스에 해당한다.

추출 랜덤 액세스의 특징은 랜덤 액세스의 횟수와 추출되는 데이터의 양이 동일하게 된다. SELECT 절의 컬럼들은 추출되는 데이터를 감소시키거나 증가시키지 못한다.

따라서, 추출 랜덤 액세스는 발생한만큼 결과로 추출된다.

세 번째로 정렬 랜덤 액세스는 추출 랜덤 액세스와 거의 동일하다.

ORDER BY 절 등에 사용된 컬럼이 인덱스에 존재하지 않아 테이블을 액세스하여 정렬을 수행하기 위해 데이터 를 액세스하는 랜덤 액세스를 발생시키는 경우를 정렬 랜덤 액세스라고 한다.

정렬 랜덤 액세스 또한 랜덤 액세스의 횟수와 추출되는 데이터의 건수가 동일하게 된다.

앞서 확인한 것과 같이 추출, 정렬 랜덤 액세스는 랜덤 액세스의 횟수와 추출되는 데이터의 건수에는 변화가 없다.

하지만, 확인 랜덤 액세스는 추출되는 데이터의 건수가 감소할수 있으며 그렇기 때문에 버려지는 데이터가 존재할 수 있다.

이와 같은 이유에서 확인 랜덤 액세스가 랜덤 액세스 중 가장 많은 부하를 발생시키며 우리는 최우선적으로 확인 랜덤 액세스를 제거하기 위해 노력해야 할 것이다.

효과적인 인덱스 선정을 통해 확인 랜덤 액세스를 제거하자

추출 또는 정렬 랜덤 액세스를 제거하는 것도 성능을 향상시키게 되지만 가장 먼저 확인 랜덤 액세스를 제거해야 할 것이다.

SELECT 카드번호, 사용액
  FROM 거래내역
 WHERE 카드번호 = '111'
   AND 거래일자 BETWEEN '20080501' 
                   AND '20080510';

거래내역 테이블에는 카드번호+가맹점 인덱스가 존재한다고 가정하자. 이와 같으며 카드번호+거래일자 인덱스는 추가로 생성할 수 없다고 가정하자.

인덱스의 추가 또는 삭제가 운영 중인 시스템에서는 매우 위험한 작업일 수 있다. 또한, 카드번호 컬럼은 인덱스의 첫 번째 컬럼이므로 인덱스에서 액세스하는 처리 범위를 감소시키게 된다.

하지만, 거래일자 컬럼은 인덱스에 존재하지 않기 때문에 인덱스에서 그 값을 확인할 수 없게 된다. 따라서, 거래일자 컬럼의 값을 확인하기 위해서 테이블을 액세스해야 하며 액세스한 데이터 중거래일자 조건을 만족하는 데이터만을 결과로 추출하게 된다.

위와 같은 랜덤 액세스는 확인 랜덤 액세스에 해당할 것이다.

이 경우에 기존 인덱스에 거래일자 컬럼을 추가한다면 어떻게 되는가?

카드번호+가맹점+거래일자 인덱스를 생성한다면 거래일자 컬럼 앞에 가맹점 컬럼이 존재하므로 거래일자 컬럼은 처리 범위를 감소시키는 역할을 수행하지는 못하지만 거래일자 컬럼이 인덱스에 존재하게 되므로 확인 랜덤액세스는 발생하지 않게 된다.

이처럼 인덱스를 조정하여 확인 랜덤 액세스를 제거할 수 있을 것이다. 이와 같이 랜덤 액세스를 제거하는 방법이야 말로 해당 SQL의 성능을 향상시킬 수 있는 방법이 될 것이다.

다음 강의에서는 추출 랜덤 액세스와 정렬 랜덤 액세스를 제거하는 방법에 대해 인덱스 선정을 통해 확인해 보자.