[DBMS] 과연 Index scan(Random Acces)가 빠른것인가? (Random Access vs Sequential Access & Index Scan vs Full Scan)

Database의 IO Access 유형으로는 Sequential  , Random 이 있다.

random Access는 OLTP성 DB에서 성능에 미치는 영향이 크다 

 

Disk Access Time (Seek Time)

- 데이터를 읽고 저장하기 위해. HDD head 위치를 이동시키는데 소요된 시간 

 

Sequential Access (순차scan)

- HDD에서 순서대로 검색, 스캔 방식 

- table full scan 일 경우 , DB_FILE_MULTIBLOCK_READ_COUNT 설정으로 HDD 에서 한번 읽을 떄 몇개의 block을 읽을건지 정할 수 있다. 해당 파라미터값은 임계치가 있어서 크다고 좋은것이 아니다.

 

Random Access (임의 Access)

- 원하는 데이터가 산발적으로 흩어져있을 경우 

- 원하는 레코드만을 직접 Access 

- 검색될 레코드에 바로 접근하기 위해는 key가 필요 

- index 경유할 경우, index key에 mapping된 rid로만으로 찾을 수 있다. 

- 한번 읽을때 마다 block 단위로 가져온다 

 

인프런- 권철민 강사 강의자료에서 발췌

index를 사용할 경우, index가 정렬이 되어있기에 조건에 맞는 데이터의 끝을 알 수있다 (breakpoint존재)

반면, full scan 일 경우, 조건에 맞는 데이터를 이미 다 찾았음에도 불구하고 테이블의 끝까지 돌아야한다  

 

그럼 Random Access(Index경유) 가 성능(IO횟수)에 항상 유리한것일까?

 

 

Quiz-1 .

Case1. 1만건의Record를 Access 하기 위해서 Random I/O 시 1,000 Block 이 필요하고 Full Scan 시 십만 Block 필 요할경우어떤Access 가 더 빠른가?

Case2. 백만건의 Record를 Access 하기 위해서 Random I/O 시 십만 Block 이 필요하고 Full Scan 시 천만 Block 필요 할경우어떤Access 가 더 빠른가?

 

두 조건 모두 Random I/O : Full Scan block수는 1:100 이다 ,

case2에서  Random I/O도 Full Scan도 100배 늘어났다  

 

답은

Case1 . Random IO

Case2 . Full Scan 

 

** Random I/O는 절대량이 늘어날 수록 , 수행 성능 급격하게 하락 

 

 

Quiz-2.

[조건-1] 특정 TABLE의 컬럼값 분포도가 균일하게 10% 일경우,

가령 컬럼값 10개의 종류가 모두 균일하게 같은 건 수라고 가정한다

[문제-1 ] 1억건이 넘는 대용량테이블에 해당컬럼으로 단독인덱스를 생성할것인가?

 

[조건-2]  만일 동일테이블에 해당 컬럼값 분포도가 균일하게 1%라면,

가령 컬럼값이 100개의 종류가 모두 균일하게 같은 건 수라고 가정한다 

[문제-2 ]  1억건이 넘는 대용량테이블에 해당컬럼으로 단독인덱스를 생성할것인가?

 

 

분포도가 좋으면 index를 달면 끝나는걸까?

[조건-1]  분포도 10% / 1억건의 데이터를 10개의 종류로만 나눈다면 한 컬럼값당 1000만개 

 - 단독 index 할 경우, 한번 읽을때마다 1000만건씩 random io를 하게된다 - 느려짐 / (composite index 으론 해결할 수 있다)

- Random io 절대량이 크다

[조건-2] 분포도 1% / 1억건의 데이터를 100개의 종류로만 나눈다면 한 컬럼값당 100만개 

- 단독 index 할 경우, 한번 읽을때마다 10만건씩 random io를 하게된다 - 느려짐 / (composite index 으론 해결할 수 있다)

- Random io 절대량이 크다

 

Point는 분포도가 작다! 고 해서 Index 생성을 하는게 좋다! 라고 바로 결론이 나서는 안된다. 

Random IO는 절대량이 늘수록 성능이 하락되기떄문에 , 

 

Access Record 수가 적을 땐  full scan 보단 Random Access 가 유리하지만 , 

Reocord 수가 많을 경우 , Random Access 의 성능은 급격히 하락하게 되어 full scan 이 효과적일 경우가 있다. 

 

"많은 index 가 오히려 악이 될 수 있다."

range가 넓은 index도 악. 

무분별하게 많은 index는 DML 성능 하락.