[친절한 SQL 튜닝] Table Full Scan VS Index Range Scan

I/O 튜닝이 곧 SQL 튜닝이다 !

SQL이 느린 이유

SQL이 느린 이유는 십중팔구 I/O 때문이다. 구체적으로 말해, 디스크 I/O 때문이다.

 

I/O는 잠(SLEEP) 이다. OS 또는 I/O 서브시스템이 I/O를 처리하는 동안 프로세스는 잠을 자기 때문이다. 

프로세스는 실행중인 프로그램이다. 여러 프로세스가 하나의 CPU를 공유할 수 있지만, 특정 순간에는 하나의 프로세스만 CPU를 사용할 수 있다. 열심히 일해야 할 프로세스가 한가하게 잠을 자고 있으니 I/O가 많으면 성능이 느릴 수 밖에 없다.

 

DB버퍼캐시

디스크 I/O 가 SQL 성능을 결정한다. SQL을 수행하는 과정에 계속해서 데이터 블록을 읽는데, 자주 읽는 블록을 매번 디스크에서 읽는 것은 매우 비효율적이다. 모든 DBMS 에 데이터 캐싱 메커니즘이 필수인 이유이다. 

 

• 라이브러리 캐시 = 코드 캐시 = SQL과 실행계획, DB 저장형 함수/프로시저 등을 캐싱
• DB버퍼 캐시 = 데이터 캐시 = 디스크에서 어렵게 읽은 데이터 블록을 캐싱
  • 같은 블록에 대한 반복적인 I/O Call 을 줄임

 

서버 프로세스와 데이터파일 사이에 버퍼캐시가 있어 데이터 블록을 읽을 땐 항상 버퍼캐시부터 탐색한다.

캐시에서 블록을 찾는다면 프로세스는 잠을 자지 않아도 된다. 

---

Table Full Scan vs Index Range Scan

테이블에 저장된 데이터를 읽는 방식은 두 가지다. 테이블 전체를 스캔해서 읽는 방식과 인덱스를 이용해서 읽는 방식이다.

 

Table Full Scan

• 테이블에 속한 블록 전체를 읽어서 사용자가 원하는 데이터를 찾는 방식이다. 

 

Index Range Scan

• 인덱스에서 일정량을 스캔하면서 얻은 ROWID로 테이블 레코드를 찾아가는 방식이다. 
  • ROWID는 테이블 레코드가 디스크 상에 어디 저장됐는지를 가리키는 위치 정보다. 

 

언제나 인덱스를 사용하는게 올바를까 ? 그렇지 않다. 

Table Full Scan 은 피해야한다는 많은 개발자의 인식과 달리 인덱스가 SQL 성능을 떨어뜨리는 경우도 상당히 많다. 

• 한번에 많은 데이터를 처리하는 집계용 SQL
• 배치 프로그램

위와 같은 경우(다 그런건 아니지만) 상당 수가 Table Full Scan 으로 유도하면 성능이 빨라진다. 

 

인덱스를 이용하는데 왜 성능이 더 느릴까 ? 

Table Full Scan 은 한 블록에 속한 모든 레코드를 한 번에 읽어 들이고, 캐시에서 못 찾으면 한 번의 수면(I/O Call)을 통해 인접한 수십~수백 개 블록을 한꺼번에 I/O 하는 메커니즘이다. 수십~수백 건의 소량 데이터를 찾을 때 수백만~수천만건의 데이터를 스캔하는 건 비효율적이다. 큰 테이블에서 소량 데이터를 검색할 때는 반드시 인덱스를 이용해야 한다. 

 

Index Range Scan 을 통한 테이블 액세스는 캐시에서 블록을 못 찾으면, 레코드 하나를 읽기 위해 매번 잠을 자는 I/O 매커니즘이다. 따라서 많은 데이터를 읽을 때는 Table Full Scan 보다 불리하다.

게다가 이 방식은 읽었던 블록을 반복해서 읽는 비효율이 있다. 한 블록에 평균 500개 레코드가 있으면, 같은 블록을 최대 500번 읽는다. 만약 인덱스를 이용해 전체 레코드를 액세스한다면, 모든 블록을 평균 500번씩 읽게 되는 셈이다. 각 블록을 단 한 번 읽는 Table Full Scan 보다 훨씬 불리하다. 

 

인덱스의 중요성은 아무리 강조해도 지나치지 않는다. 그러나 인덱스에 대한 맹신은 금물이다. 인덱스가 항상 옳은 것은 아니며, 바꿔말해 Table Full Scan 이 항상 나쁜 것도 아니다. 인덱스는 큰 테이블에서 아주 적은 일부 데이터를 빨리 찾기 위한 도구일 뿐이므로 모든 성능 문제를 인덱스로 해결하려 해선 안 된다. 읽을 데이터가 일정량을 넘으면 인덱스보다 Table Full Scan 이 유리하다.