랜덤액세스 이해

SQL이 인덱스를 이용해야 할 지 아니면 인덱스를 이용하지 말아야 할지를 결정하는 것이 SQL 최적화의 첫걸음

인덱스를 이용해야 하는 SQL을 구분하여 해당 SQL은 최적의 인덱스를 이용해야한다.

랜덤액세스란 ?

데이터 저장 블록을 한번에 여러 개 액세스 하는 것이 아니라 한번에 하나의 블록만을 액세스하는 방식

 

전체테이블 스캔

테이블 전체 스캔의 경우는 여러개의 블록을 한번에 액세스하기 때문에 여러 블록을 액세스하는 다중 블록 I/O 수행

한번에 여러개의 블록을 액세스하며 같은 양에 데이터에 대해 적은 횟수의 디스크I/O 발생하여 성능 향상 

 

랜덤 액세스 발생이유

• 인덱스를 액세스하여 확인한 ROWID를 이용하여 테이블을 액세스 하는 경우 랜덤 액세스 발생
• ROWID는 해당 데이터를 찾아가는 유일한 주소 값
• 인덱스를 이용하는 것은 인덱스로부터 조건을 만족하는 인덱스 값을 액세스 한 후 ROWID를 확인하여 ROWID 값으로 테이블을 액세스 하는 것을 의미
  • 인덱스 액세스 후 테이블을 액세스하는 경우에 발생하는 I/O는 한번에 하나의 블록만 액세스하는 랜덤 액세스 발생

 

랜덤 액세스 종류

1. 확인 랜덤 액세스
2. 추출 랜덤 액세스
3. 정렬 랜덤 액세스

 

1. 확인 랜덤 액세스

• WHERE절의 조건에 의해 발생
• WHERE절 조건의 칼럼이 인덱스에 존재하지 않아 테이블을 액세스하는 랜덤 액세스를 확인 랜점 액세스라고 함
• 단일 인덱스로 구성시 인덱스를 사용한 칼럼에 의해서만 처리범위가 감소

<EX>

SELECT 카드번호, 거래일자
  FROM 거래내역
 WHERE 카드번호 ='111'
   AND 거래일자 = '20230921';

카드번호로만 인덱스 구성되어 있을시

1. 카드번호로만 인덱스로 액세스 
2. 추출한 데이터에 대하여 거래일자 모두 액세스
3. 카드 번호 조건에 만족하는 모든 데이터에 대해 테이블을 액세스하여 거래일자 칼럼 칼럼 값 확인

 

-> 카드번호로만 처리범위 감소

-> 최종결과는 랜덤 액세스의 횟수보다 동일하거나 더 적게 추출됨

-> 확인 랜덤 액세스가 발생한 후 버려지는 데이터 존재

 

추출 랜덤 액세스

• WHERE절의 칼럼은 모두 인덱스에 존재하지만 SELECT 절의 칼럼이 인덱스에 존재하지 않음
• SELECT 절의 칼럼을 결과로 추출하기 위해서는 반드시 인덱스 액세스 후 테이블을 액세스
• SELECT 절의 칼럼들은 추출된느 데이터를 감소시키거나 증가시키지 못함
• 랜덤 액세스의 횟수 = 추출되는 데이터의 양

 

정렬 랜덤 액세스

• ORDER BY 절 등에 사용된 칼럼이 인덱스에 존재하지 않아 테이블을 액세스하여 정렬을 수행하기 위해 데이터를 액세스하는 랜덤 액세스를 발생시키는 경우를 정렬 랜덤 액세스라 함
  • 인덱스는 항상 정렬되어있음
• 정렬 랜덤 액세스 = 추출 데이터 건수

 

 

-> 확인 랜덤 액세는 추출되는 데이터 건수가 감소할 수 있어 버려지는 데이터 존재 할 수 있음

-> 확인 랜덤 액세스가 랜덤 액세스 중 가장 많은 부화를 발생시켜 최우선적으로 확인 랜덤 액세스 제거하기 위해 노력 필요

 

확인 랜덤 액세스 제거

• 확인 랜덤 액세는 추출되는 데이터 건수가 감소할 수 있어 버려지는 데이터 존재 할 수 있음
• 확인 랜덤 액세스가 랜덤 액세스 중 가장 많은 부화를 발생시켜 최우선적으로 확인 랜덤 액세스 제거하기 위해 노력 필요
• 추출 또는 정렬 랜덤 액세스를 제거하는 것도 성능을 향상시키지만 가장 먼저 확인 랜덤 액세스 제거 필요

<EX>

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

• 거래내역 테이블에 카드번호 + 가맹점 인덱스 존재<카드번호 + 거래일자 인덱스 추가 생성 불가>
• 인덱스 추가, 삭제가 운영중인 시스템에 큰 영향을 줄 수 있음
  •  인덱스는 추가나 삭제시 물리적 삭제가 아닌 논리적 삭제

 

1. 카드번호 칼럼은 인덱스의 첫번째 칼럼이므로 인덱스에서 액세스하는 처리 범위 감소
2. 거래일자 칼럼은 인덱스에 존재하지 않아 인덱스에서 값 확인 불가
3. 거래일자 칼럼의 값을 확인하기 위해서 테이블을 액세스해야 하며 액세스한 데이터 중 거래일자 조건을 만족하는 데이터만을 결과로 추출

 

카드번호+가맹점+거래일자 인덱스 생성시 거래일자 칼럼 앞에 가맹점 칼럼이 존재해 처리범위 감소시키지 못하지만 거랭리자 칼럼이 인덱스에 존재하게 되므로 확인 랜덤 액세스는 발생하지 않음

->확인 랜덤 액세스를 제거함으로 성능 향상에 도움

 

 

출처 : http://www.gurubee.net/lecture/2230

랜덤 액세스의 이해