[친절한SQL튜닝 요약 정리] 3-1. 테이블 액세스 최소화

친절한SQL튜닝 책 요약 정리

 

 

1. 테이블 랜덤 액세스

 

 (1) 인덱스에 대한 맹신

     : 인덱스는 생각보다 느리다!

 

 (2) 인덱스에 갖는 ROWID는 물리적 주소? 논리적 주소? ( ROWID = DBA(Data Block Address) + 로우 번호 )

     : 논리적 주소이다.

 

 (3) 메인 메모리DB와 비교

       메인 메모리 DB : 데이터를 모두 메모리에 로드해놓고 메모리를 통해서만 I/O를 수행하는 DB

                                  인덱스는 물리적주소(포인터)를 갖는다. 그렇기 때문에 빠르다. 비용이 0에수렴

 

 (4) I/O 메커니즘 복습

     : 디스크에서 블록을 읽기 전 버퍼캐시부터 찾아본다.

       읽고자 하는 DBA를 해시함수에 입력 후 해시 체인을 찾고 거기서 버퍼헤더를 찾는다.

       이처럼 인덱스 ROWID를 이용한 테이블 액세스는 생각보다 고비용 구조다.

 

 

2. 인덱스 클러스터링 팩터

   : Clustering Factor(CF)는 비슷한 데이터가 서로 얼마나 모여있는지를 나타낸다.

     그러면 속도가 빨라지는데 다음 인덱스 레코드를 읽을 때 직전 테이블과 같은 블록을 읽는데

     ( 블록 : 레코드가 모여있는 단위 ) 해시 체인을 찾는 과정을 생략하게 된다.

     더 적은 블록만 조회하면 되므로 속도가 향상된다.

 

 

3. 인덱스 손익분기점

   : ROWID는 생각보다 고비용 구조다.

     따라서 읽어야할 데이터가 일정량을 넘는 순간, 테이블 전체를 스캔하는 것보다 오히려 느려진다.

     테이블 전체 스캔은 성능이 일정한 편이다. 10만건과 1000만 건의 큰 차이가 없다.

     인덱스를 이용한 테이블 액세스는 전체 건중 몇 건을 추출하느냐가 중요하다. 많을수록 느려진다.

     CF에 따라 달라지지만 보통 5~20%의 낮은 손익 분기점을 갖는다.

     하지만 이것도 10만, 많다고 했을 때 100만 이내 테이블에 적용되는 수치이다. 그보다 많아지만

     손익분기점이 더 낮아진다. 만건만 넘어도 Table Full Scan 방식으로 읽는게 더 빠를 수 있다.

     * 인덱스가 만능이 아니란 것을 설명하기 위한것이고, 손익분기점을 높이라는 것은 아니다.

 

 

4. 인덱스 컬럼 추가

   : Ex)인덱스가 <이름 + 부서>로 되어 있을 때, 이름이 홍길동이고 나이가 30이상인 사람을 찾는다면

     1. 인덱스 구성을 <이름 + 나이> 로 변경하고 싶을 것이다. 

     2. 하지만 다른 사람이 짜놓은 SQL이 기존 인덱스 구성을 사용할 수도 있기때문에 안된다.

     3. 그러면 인덱스를 새로 만들어 추가하게 된다.

     4. 테이블마다 인덱스가 수십 개씩 달려 배보다 배꼽이 더 커지게 된다.

     이럴 때 사용하는 방법이 컬럼 추가이다.

     인덱스 자체에 컬럼을 같이 저장하도록 하면 테이블 액세스를 하지 않아도 된다.

     하지만 인덱스 수직적 탐색에 해당컬럼 값이 기준으로 사용될 수는 없다.

     참고) SQL Server 2005 에는 Include 인덱스라고 인덱스와 함께 모든 컬럼을 저장하는 기능이 있다.

              지정 컬럼을 리프 레벨에 함께 저장하는 기능이다. 최대 1023개 컬럼까지 가능하다.

 

 

5. 인덱스 구조 테이블

   : 인덱스를 통해 테이블을 찾아가는 것이 아니라, 인덱스 자체를 테이블로 만든다.

     즉, 데이터 블록에 있어야 할 데이터를 인덱스 리프 브록에 모두 저장하고 있다.

     이런 기능을 지원하는데, 오라클은 'IOT(Index Organized Table)' 라고 부르고

     MS-SQL Server는 '클러스터형 인덱스'라고 부른다.

     IOT는 인위적으로 클러스터링 팩터를 좋게 만드는 방법 중 하나이다.

 

 

6. 클러스터 테이블

  : 클러스터를 만드는 두가지 방법

 

  (1) 인덱스 클러스터 테이블

     : 인덱스의 값이 같은 레코드를 한 블록에 모아서 저장하는 구조. 더많으면 옆 블록을 할당해서 연계

       심지어 여러 테이블 레코드를 같은 블록에 저장할 수도 있음.(다중 테이블 클러스터)

       * SQL 서버의 클러스터 인덱스보다는 오라클의 IOT에 가깝다. 

         오라클 클러스터는 키 값이 같은 데이터를 같은 공간에 저장해 둘뿐,

         IOT나 SQL Server의 클러스터형 인덱스처럼 정렬하지는 않는다.

 

  (2) 해시 클러스터 테이블

     : 인덱스를 사용하지 않고 해시 알고리즘을 사용해 클러스터를 찾아간다는 점만 다르다.