랜덤액세스(랜덤 I/O)와 인덱스

쿼리에서 참조되는 컬럼이 인덱스에 모두 포함되는 경우가 아니라면 인덱스 스캔 이후 '테이블 Random 액세스'가 발생합니다.

이는 잦은 블록 I/O를 발생시켜 성능 원인이 될 수 있으며 확인, 추출, 정렬 랜덤 액세스를 없애기 위해 노력해야 합니다.

그중에서 확인 랜덤 액세스를 줄이는 것이 가장 중요합니다.

 

랜덤액세스?

랜덤 액세스는 데이터를 저장하는 블록을 한 번에 여러 개 액세스 하는 것이 아니라 한 번에 하나의 블록만을 액세스 하는 싱글 블록 I/O 방식입니다.

• 블록 : hdd나 sdd에 저장되는 물리적인 단위. 일반적으로 몇 KB~ 몇 MB 크기.
• 반대로 테이블 풀 스캔(Table Full Scan)의 경우에는 한 번에 여러 개의 블록을 액세스 하는 멀티 블록 I/O 방식을 사용합니다.

랜덤 액세스는 순차 액세스(Sequential Access)와는 달리, 데이터베이스에서 원하는 데이터를 빠르게 찾을 수 있지만, 블록 I/O 비용이 더 크기 때문에 시스템 자원을 더 많이 사용하기 때문에 DB성능 확보를 위해서 랜덤 액세스를 최소화하여 성능을 향상시키려고 노력해야 합니다.

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

• 랜덤 액세스는 데이터베이스에서 특정 레코드를 찾는 작업이나 인덱스를 사용하는 작업에 많이 사용됩니다.

 

랜덤 액세스가 발생되는 시점

쿼리에서 참조되는 컬럼이 인덱스에 모두 포함되는 경우가 아니라면 인덱스 스캔 이후 '테이블 Random 액세스'가 반드시 일어남.
실행계획에서는 Table Access by index ROWID라고 표시됩니다.

• 인덱스를 액세스하고, 확인한 ROWID를 이용하여 다시 테이블을 액세스하는 경우 랜덤 액세스가 발생.

• B-Tree INDEX 구조 - 출처 : 개발자를 위한 오라클 SQL 튜닝

위에 보이는 그림은 인덱스 B-Tree 구조입니다.

최하단 영역인 리프(leaf) 노드에 해당 테이블의 행을 가리키는 ROWID 가 존재합니다.

• Rowid는 데이터베이스에서 각 레코드를 식별하는 고유한 값을 말합니다. 이 값은 실제 데이터 레코드를 찾아가기 위한 주소 값 정보를 포함하고 있어 Rowid를 통해 데이터를 직접 찾아서 가져올 수 있습니다.
• B-TREE로 된 인덱스 스캔이 완료되면 해당 데이터를 찾아가는 유일한 주소 값인 ROWID를 확인하여 테이블에 액세스(접근) 하게 됩니다.

 

조건(where)을 만족하는 인덱스에 액세스 후 ROWID를 이용하여 테이블을 액세스(접근)하는 경우에 발생하는 I/O가 블록을 하나씩 접근하는 것을 랜덤 액세스가 발생한다고 합니다.

 

인덱스 스캔 후 테이블 Random 액세스가 발생할 수 있는 경우는 다음과 같습니다.

1. 인덱스 컬럼의 일부만 참조하는 경우 : 인덱스를 스캔한 후 해당 레코드의 나머지 컬럼을 조회하기 위해 테이블의 블록을 Random 액세스.
2. 인덱스가 커서를 사용하는 경우 : 커서(Cursor)는 인덱스 스캔 결과를 처리하기 위한 데이터베이스 객체. 커서를 사용하는 쿼리에서는 인덱스 스캔 후에도 테이블 Random 액세스가 발생할 수 있습니다. 이는 커서가 인덱스의 모든 블록을 스캔하면서 해당 레코드의 나머지 컬럼을 가져오기 위해 테이블의 블록을 Random 액세스하기 때문입니다.
3. 인덱스만으로 쿼리 결과를 만족시킬 수 없는 경우 : 이는 인덱스가 쿼리 결과를 제공하기 위한 커버링 인덱스(Covering Index)가 아닌 경우에 해당합니다.
   • 커버링 인덱스란 쿼리에서 참조하는 모든 컬럼이 인덱스에 포함된 경우를 말합니다.

랜덤 액세스의 종류

인덱스 액세스 후 테이블을 액세스하는 것은 랜덤 액세스를 발생시키게 됩니다. (인덱스만 액세스하고 테이블을 안뒤지는것이 중요.)

1. 확인 랜덤 액세스

• 개념: WHERE, HAVING 조건의 컬럼이 인덱스에 존재하지 않아 테이블을 액세스하는 랜덤 액세스
• 특징 : 랜덤액세스의 횟수보다 최종 결과가 동일하거나 적게 추출
• 고려 사항 : 단일 블록 I/O를 증가시키며 여러 인덱스에 액세스 후 버려지는 데이터 발생, 확인 랜덤액세스 제거가 성능을 좌우

WHERE 이름 = 'ys' and 성 = 'k'

예를 들어, WHERE 조건에 '이름', '성' 2개의 조건이 있고 해당 테이블의 인덱스는 '이름' 컬럼에만 존재합니다.

SQL이 실행되게 되면 이름 칼럼에 의해 인덱스를 액세스하고 처리 범위가 좁혀질 것입니다.

그러나, '성'은 인덱스로 설정이 되어있지 않기 때문에

결국 '이름' 조건을 만족하는 모든 데이터에 대해 테이블을 액세스 하여 '성' 컬럼의 값을 확인하여 조건을 부합하는 값을 찾게 됩니다.

• where 조건 : '이름' 조건을 만족하는 모든 데이터 중 '성' 을 만족하는 데이터

이처럼 WHERE 조건의 칼럼이 인덱스에 존재하지 않아 테이블 랜덤 액세스를 발생시키는 것을 확인 랜덤 액세스라고 합니다.

 

 

확인 랜덤 액세스의 특징은 랜덤 액세스의 횟수보다 최종 결과가 동일하거나 더 적게 추출됩니다.

열심히 테이블을 액세스 한 후 버려지는 데이터가 존재하기 때문에,

랜덤 액세스의 세 가지 종류 중에서도 확인 랜덤 액세스의 제거는 성능에 있어 매우 중요합니다.

2. 추출 랜덤 액세스

• 개념 : 인덱스 액세스 후 SELECT 절의 컬럼을 결과로 추출 위해 추가로 테이블에 액세스
• 특징 : 랜덤액세스 횟수와 추출 데이터양 동일, SELECT 절에서 발생
• 고려 사항 : 자주 사용 SQL, 인라인 뷰를 통한 RowID 이용, 컬럼이 많은 결합 인덱스

WHERE절의 컬럼들은 모두 인덱스에 존재하지만, SELECT절의 컬럼들에 인덱스에 포함되지 않는 컬럼이 있다면 인덱스 액세스 이후 테이블에 추가로 액세스 해야 합니다. 이와 같은 현상이 추출 랜덤 액세스라고 합니다.

 

추출 랜덤 액세스의 특징은 결과의 양이 랜덤 액세스의 횟수와 동일합니다. SELECT 절의 컬럼들은 추출되는 데이터를 감소시키거나 증가시키지 못하므로 발생한 만큼 결과로 추출한다. 따라서 추출 랜덤 엑세스는 where절의 조회 결과만큼 발생하게 됩니다.

3. 정렬 랜덤 액세스

• 개념 : ORDER BY, GROUP BY절 컬럼이 인덱스에 존재하지 않아 추기 테이블 액세스
• 특징 : 랜덤액세스와 추출 데이터 양 동일, ORDER BY, GROUP BY절에서 발생
• 고려 사항 : ORDER/GROUP BY절 컬럼 인덱스 추가, 불필요한 ORDER/GROUP BY절 제거

select 이후 ORDER BY절이나 GROUP BY절에 사용되는 컬럼에 인덱스가 존재하지 않을때 발생합니다.

정렬 랜덤 액세스의 양도 추출 랜덤 액세스와 마찬가지로 결과의 양과 동일합니다.

랜덤액세스 중 추출되는 데이터를 감소시키는 확인랜덤액세스를 감소시키는 방안이 성능 측면에서 가장 중요합니다.

확인 랜덤 액세스는 블록 I/O를 증가시키면서 열심히 테이블을 액세스 하고 나서 버려지는 데이터가 존재하기 때문에 최우선적으로 사용하지 않는 데이터를 조회하여 가장 많은 부하를 발생시킬 수 있는 확인 랜덤 액세스를 제거하기 위해 노력해야 합니다.

확인 랜덤 액세스는 단일 블록 I/O를 증가 시킵니다.

랜덤액세스 최소화 사례

유형	사례	개선 사항
확인랜덤 액세스	SELECT 사번 FROM 사원 WHERE 부서=2 AND 나이=25;	– “나이” 컬럼 인덱스 추가 → 1회씩 액세스
추출랜덤 액세스	SELECT 이름 FROM 사원 WHERE 성별=”남”;	– “이름” 컬럼 인덱스 추가 → “이름” 자주 사용
정렬랜덤 액세스	SELECT 사번 FROM 사원 ORDER BY 나이 ASC;	– “나이” 컬럼 인덱스 추가 → 성능 개선

확인 랜덤 액세스 제거

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

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

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

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

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

하지만, 거래일자 컬럼은 인덱스에 존재하지 않기 때문에 거래일자 컬럼의 값을 확인하기 위해서 테이블을 액세스해야 하며 그 중 거래일자 조건을 만족하는 데이터만을 결과로 추출하게 됩니다. -> 확인 랜덤 액세스

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

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

이처럼 인덱스를 조정하여 확인 랜덤 액세스를 제거하여 해당 SQL의 성능을 향상시킬 수 있습니다.

추출 랜덤 액세스 제거

추출 랜덤 액세스 : SELECT 절의 데이터를 추출하기 위해 인덱스 액세스 후 테이블을 액세스해야 하는 랜덤 액세스

추출 랜덤 액세스는 SELECT 절에 의해 발생하게 되며 추출랜덤 액세스를 제거하기 위해서는 SELECT 절의 모든 컬럼을 인덱스에 추가해야 합니다. 잘못하면 매우 많은 컬럼으로 구성된 인덱스가 생성될 수도 있을 것이다.

그렇다면 추출 랜덤 액세스를 감소시키기 위해 어떻게 해야 하는가? 다음의 세 가지를 고려할 수 있습니다..

• 매우 자주 사용하는 SQL에 대해서는 추출 랜덤 액세스 제거 고려
  • 많은 컬럼으로 인덱스를 구성하는 것도 고려할 수 있다는 의미
• 하나의 인덱스로 많은 SQL에 대해 추출 랜덤 액세스를 제거할 수 있다면 컬럼이 많더라도 인덱스에 컬럼 추가 고려
• 인라인 뷰를 통해 데이터가 감소하는 경우 ROWID 이용하여 추출 랜덤 액세스 감소 고려

참조

• http://www.gurubee.net/lecture/2230 ~ 2236
• https://sas-study.tistory.com/448
• https://12bme.tistory.com/138
• https://debaeloper.tistory.com/m/3