🌠3-3-2. 테이블 액세스 최소화

과목3. SQL 고급 활용 및 튜닝

제3장 인덱스 튜닝

제2절 테이블 액세스 최소화

1. 인덱스 ROWID에 의한 테이블 랜덤 액세스

가. 인덱스 ROWID에 의한 테이블 액세스 구조

쿼리에서 참조되는 칼럼이 인덱스에 모두 포함되는 경우가 아니라면, '테이블 random 액세스'가 일어난다. 아래 실행계획에서 'Table Access By Index ROWID'라고 표시된 부분을 말한다.

SELECT * FROM 고객 WHERE 지역 = '서울'; 

		Execution Plan
0		SELECT STATEMENT Optimizer=ALL_ROWS
1	0	TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE)
2	1	INDEX (RANGE SCAN) OF '고객_지역_IDX' (INDEX)

SQL Server는 'RID Lookup'이라는 표현을 사용하며, 아래 실행계획에서 알 수 있듯이 인덱스로부터 테이블을 NL 조인하는 것처럼 처리경로를 표현하고 있다.

StmtText
--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
| --Index Seek(OBJECT:([..].[dbo].[고객].[고객_지역_idx]), SEEK:([지역] = '서울')
| --RID Lookup(OBJECT:([..].[dbo].[고객]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

참고로, 2000 이하 버전에서는 아래 처럼 'Bookmark Lookup'이라고 표현했으며, 이것이 오히려 Oracle 실행계획과 같은 모습이다.

StmtText
--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([..].[dbo].[고객]))
| --Index Seek(OBJECT:([..].[dbo].[고객].[고객_지역_idx]), SEEK:([지역] = '서울'))

지금부터 'Table Access By Index Rowid' 또는 'RID(=Bookmark) Lookup'으로 표현되는 테이블 random 액세스의 내부 메커니즘을 자세히 살펴보자.

• 인덱스 ROWID에 의한 테이블 액세스 구조

인덱스에 저장돼 있는 rowid는 흔히 '물리적 주소정보'라고 일컬어지는데, 오브젝트 번호, 데이터 파일 번호, 블록 번호 같은 물리적 요소들로 구성돼 있기 때문일 것이다. 하지만 보는 시각에 따라서는 '논리적 주소정보'라고 표현하기도 한다. rowid가 물리적 위치 정보로 구성되지만 인덱스에서 테이블 레코드로 직접 연결되는 구조는 아니기 때문이다. 어떤 것이 맞든 중요한 것은, rowid가 메모리 상의 위치정보가 아니라 디스크 상의 위치정보라는 사실이다. 그리고 데이터 블록을 읽을 때는 항상 버퍼 캐시를 경유하므로 메모리 상에서 버퍼 블록을 찾기 위해 해시 구조와 알고리즘을 사용한다. 해시 키(Key) 값으로는 rowid에 내포된 데이터 블록 주소(Data Block Address, DBA)를 사용하다. 인덱스 ROWID를 이용해 테이블 블록을 읽는 메커니즘을 간단히 요약하면 다음과 같다.

• 인덱스 ROWID에 의한 테이블 액세스 구조

• 인덱스에서 하나의 rowid를 읽고 DBA(디스크 상의 블록 위치 정보)를 해시 함수에 적용해 해시 값을 확인한다.

• 해시 값을 이용해 해시 버킷을 찾아간다.

• 해시 버킷에 연결된 해시 체인을 스캔하면서 블록 헤더을 찾는다.

• 해시 체인에서 블록 헤더를 찾으면 거기 저장된 포인터를 이용해 버퍼 블록을 읽는다.

• 해시 체인을 스캔하고도 블록 헤더를 찾지 못하면, LRU 리스트를 스캔하면서 Free 버퍼를 찾는다. 디스크에서 읽은 블록을 적재하기 위해 빈 캐시 공간을 찾는 것이다.

• LRU 리스트에서 Free 버퍼를 얻지 못하면 Dirty 버퍼를 디스크에 기록해 Free 버퍼를 확보한다.

• Free 버퍼를 확보하고 나면 디스크에서 블록을 읽어 캐시에 적재한다.

여기서 일일이 설명할 순 없지만, 위 처리 과정 중에는 래치(Latch), 버퍼 Lock 같은 Internal Lock을 획득하거나 다른 백그라운드 프로세스의 선처리 결과를 기다리는 내부 메커니즘이 작동한다. 그런 과정에 경합까지 발생한다면 블록 하나를 읽더라도 생각보다 큰 비용을 치르게 된다. Oracle이나 SQL Server 같은 디스크 기반 DBMS에서 인덱스 rowid에 의한 테이블 액세스가 생각만큼 빠르지 않은 이유가 여기에 있다. 특히, 다량의 테이블 레코드를 읽을 때의 성능 저하가 심각하다. 앞으로 실행계획에서 아래와 같이 'Table Access By Index ROWID'나 'RID(=Bookmark) Lookup' 오퍼레이션을 볼 때면, [그림 Ⅲ-4-17]과 함께 방금 설명한 복잡한 처리 과정을 항상 떠올리기 바란다.

SELECT * FROM 고객 WHERE 지역 = '서울'; 

		Execution Plan
0		SELECT STATEMENT Optimizer=ALL_ROWS
1	0	TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE)
2	1	INDEX (RANGE SCAN) OF '고객_지역_IDX' (INDEX)

나. 클러스터링 팩터(Clustering Factor)

Oracle은 '클러스터링 팩터'라는 개념을 사용해 인덱스 ROWID에 의한 테이블 액세스 비용을 평가한다. SQL Server는 공식적으로 이 용어를 사용하진 않지만 내부적인 비용 계산식에 이런 개념이 포함돼 있을 것이다. 클러스터링 팩터는 '군집성 계수(= 데이터가 모여 있는 정도)' 쯤으로 번역될 수 있는 용어로서, 특정 칼럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미한다? 좋은 상태를 도식화한 것으로서, 인덱스 레코드 정렬 순서와 거기서 가리키는 테이블 레코드 정렬 순서가 100% 일치하는 것을 볼 수 있다.

반면 [그림 Ⅲ-4-19]는 인덱스 클러스터링 팩터가 가장 안 좋은 상태를 도식화한 것으로서, 인덱스 레코드 정렬 순서와 테이블 레코드 정렬 순서가 전혀 일치하지 않는다.

클??색 효율이 매우 좋은데, 예를 들어 「거주지역 = '제주'」에 해당하는 고객 데이터가 물리적으로 근접해 있다면 흩어져 있을 때보다 데이터를 찾는 속도가 빨라지게 마련이다.

다. 인덱스 손익분기점

앞서 설명한 것처럼 인덱스 rowid에 의한 테이블 액세스는 생각보다 고비용 구조이고, 따라서 일정량을 넘는 순간 테이블 전체를 스캔할 때보다 오히려 더 느려진다. Index Range Scan에 의한 테이블 액세스가 Table Full Scan보다 느려지는 지점을 흔히 '손익 분기점'이라고 부른다. 예를 들어, 인덱스 손익분기점이 10%라는 의미는 1,000개 중 100개 레코드 이상을 읽을 때는 인덱스를 이용하는 것보다 테이블 전체를 스캔하는 것이 더 빠르다는 것이다. 인덱스 손익분기점은 일반적으로 5~20%의 낮은 수준에서 결정되지만 클러스터링 팩터에 따라 크게 달라진다. 클러스터링 팩터가 나쁘면 손익분기점은 5% 미만에서 결정되며, 심할 때는(BCHR가 매우 안 좋을 때) 1% 미만으로 떨어진다. 반대로 클러스터링 팩터가 아주 좋을 때는 손익분기점이 90% 수준까지 올라가기도 한다. 인덱스에 의한 액세스가 Full Table Scan보다 더 느리게 만드는 가장 핵심적인 두 가지 요인은 다음과 같다.

• 인덱스 rowid에 의한 테이블 액세스는 random 액세스인 반면, Full Table Scan은 Sequential 액세스 방식으로 이루어진다.

• 디스크 I/O 시, 인덱스 rowid에 의한 테이블 액세스는 Single Block Read 방식을 사용하는 반면, Full Table Scan은 Multiblock Read 방식을 사용한다.

라. 손익분기점 극복하기

손익분기점 원리에 따르면 선택도(SELECTivity)가 높은 인덱스는 효용가치가 낮지만, 그렇다고 테이블 전체를 스캔하는 것은 부담스러울 때가 많다. 그럴 때 DBMS가 제공하는 기능을 잘 활용하면 인덱스의 손익분기점 한계를 극복하는 데 도움이 된다. 첫 번째는 SQL Server의 클러스터형 인덱스와 Oracle IOT로서, 테이블을 인덱스 구조로 생성하는 것이라고 앞서 설명하였다. 테이블 자체가 인덱스 구조이므로 항상 정렬된 상태를 유지한다. 그리고 인덱스 리프 블록이 곧 데이터 블록이어서 인덱스를 수직 탐색한 다음에 테이블 레코드를 읽기 위한 추가적인 random 액세스가 불필요하다. 두 번째는 SQL Server의 Include Index이다. 인덱스 키 외에 미리 지정한 칼럼을 리프 레벨에 함께 저장하는 기능으로서, 테이블 random 액세스 횟수를 줄이도록 돕는다. 잠시 후 좀 더 자세한 설명을 보게 될 것이다. 세 번째는 Oracle이 제공하는 클러스터 테이블(Clustered Table)이다. 키 값이 같은 레코드를 같은 블록에 저장하기 때문에 클러스터 테이블에 대한 클러스터 인덱스를 이용할 때는 테이블 random 액세스가 키 값별로 한 번씩만 발생한다. 클러스터에 도달해서는 Sequential 방식으로 스캔하기 때문에 넓은 범위를 읽더라도 비효율이 없다. 네 번째는 파티셔닝이다. 읽고자 하는 데이터가 많을 때는 인덱스를 이용하지 않는 편이 낫다고 하지만, 수천만 건에 이르는 테이블을 Full Scan해야 한다면 난감하기 그지없다. 그럴 때, 대량 범위검색 조건으로 자주 사용되는 칼럼 기준으로 테이블을 파티셔닝한다면 Full Table Scan 하더라도 일부 파티션만 읽고 멈추도록 할 수 있다. 클러스터는 기준 키 값이 같은 레코드를 블록 단위로 모아 저장하지만 파티셔닝은 세그먼트 단위로 저장하는 점이 다르다. 좀 더 자세한 내용은 5장에서 보게 될 것이다.

이런 기능 외에 1장에서 설명한 부분범위처리 원리를 잘 활용하는 것도 좋은 방법이다. 인덱스 스캔 비효율이 없도록 잘 구성된 인덱스를 이용해 부분범위처리 방식으로 프로그램을 구현한다면 그 인덱스의 효용성은 100%가 된다. 무조건 인덱스를 사용하는 쪽이 유리하다는 뜻이다.

2. 테이블 random 액세스 최소화 튜닝

가. 인덱스 칼럼 추가

emp 테이블에 현재 PK 이외에 [deptno + job] 순으로 구성된 emp_x01 인덱스 하나만 있는 상태에서 아래 쿼리를 수행하려고 한다.

SELECT /*+ index(emp emp_x01) */ 
      ENAME, JOB, SAL
 FROM EMP
WHERE DEPTNO = 30
  AND SAL >= 2000 

[그림 Ⅲ-4-20]을 보면 위 조건을 만족하는 사원이 단 한 명뿐인데, 이를 찾기 위해 테이블 액세스는 6번 발생하였다.

인덱스 구성을 [deptno + sal] 순으로 바꿔주면 좋겠지만 실 운영 환경에서는 인덱스 구성을 함부로 바꾸기가 쉽지 않다. 기존 인덱스를 사용하는 아래와 같은 SQL이 있을 수 있기 때문이다.

SELECT ENAME, JOB, SAL
 FROM EMP
WHERE DEPTNO = 30
  AND JOB = 'CLERK' 

할 수 없이 인덱스를 새로 만들어야겠지만 이런 식으로 인덱스를 추가하다 보면 테이블마다 인덱스가 수십 개씩 달려 배보다 배꼽이 더 커지게 된다. 이럴 때, [그림 Ⅲ-4-21]처럼 기존 인덱스에 sal 칼럼을 추가하는 것만으로 큰 효과를 거둘 수 있다. 인덱스 스캔량은 줄지 않지만 테이블 random 액세스 횟수를 줄여주기 때문이다.

나. Covered Index

테이블을 액세스하고서 필터 조건에 의해 버려지는 레코드가 많을 때, 인덱스에 칼럼을 추가함으로써 얻는 성능 효과를 살펴보았다. 그런데 테이블 random 액세스가 아무리 많더라도 필터 조건에 의해 버려지는 레코드가 거의 없다면 거기에 비효율은 없다. 이때는 어떻게 튜닝해야 할까? 이때는 아예 테이블 액세스가 발생하지 않도록 필요한 모든 칼럼을 인덱스에 포함시키는 방법을 고려해 볼 수 있다. SQL Server에서는 그런 인덱스를 'Covered 인덱스'라고 부르며, 인덱스만 읽고 처리하는 쿼리를 'Covered 쿼리'라고 부른다.

다. Include Index

Oracle엔 아직 없는 유용한 기능이 SQL Server 2005 버전에 추가되었는데, 인덱스 키 외에 미리 지정한 칼럼을 리프 레벨에 함께 저장하는 기능이다. 인덱스를 생성할 때 아래와 같이 include 옵션을 지정하면 되고, 칼럼을 최대 1,023개까지 지정할 수 있다.

CREATE INDEX EMP_X01 ON EMP (DEPTNO)

INCLUDE (SAL) 

만약 인덱스를 [deptno + sal] 순으로 생성하면 sal 칼럼도 수직적 탐색에 사용될 수 있도록 그 값을 루프와 브랜치 블록에 저장한다. 하지만 위와 같이 sal 칼럼을 include 옵션으로만 지정하면 그 값은 리프 블록에만 저장한다. 따라서 수직적 탐색에는 사용되지 못하고 수평적 탐색을 위한 필터 조건으로만 사용된다. 그??를 가져다 준다.

라. IOT, 클러스터형 인덱스, 클러스터 테이블 활용

1절에서 설명한 Oracle IOT나 SQL Server 클러스터형 인덱스을 이용하는 것도 테이블 random 액세스를 없애는 중요한 방법 중 하나다. Oracle이라면 클러스터 테이블을 이용할 수도 있다. IOT와 클러스터형 인덱스에 대해선 1절에서 이미 설명하였다. 인덱스를 이용하는 인덱스 클러스터도 이미 설명했으므로 생략하고, 여기서는 해시 클러스터에 대해서만 간단히 살펴보기로 하자. 해시 클러스터 테이블은 해시 함수에서 반환된 값이 같은 데이터를 물리적으로 함께 저장하는 구조다. 클러스터 키로 데이터를 검색하거나 저장할 위치를 찾을 때 해시 함수를 사용한다. 해시 함수가 인덱스 역할을 대신하는 것이며, 해싱 알고리즘을 이용해 클러스터 키 값을 데이터 블록 주소로 변환해 준다. 별도의 인덱스 구조를 생성하지 않는 장점에도 불구하고 해시 클러스터의 활용성을 떨어뜨리는 중요한 제약사항은, '=' 검색만 가능하다는 점이다. 항상 '=' 조건으로만 검색되는 칼럼을 해시 키로 선정해야 하는 것이며, 이는 해시 함수를 사용하기 때문에 나타나는 어쩔 수 없는 제약이다.

마. 수동으로 클러스터링 팩터 높이기

테이블에는 데이터가 무작위로 입력되는 반면, 그것을 가리키는 인덱스는 정해진 키(key) 순으로 정렬되기 때문에 대개 클러스터링 팩터가 좋지 않게 마련이다. 필자의 경험에 의하면, 클러스터링 팩터가 나쁜 인덱스를 이용해 많은 양의 데이터를 읽는 SQL 튜닝하기가 가장 어렵다. 그럴 때, 해당 인덱스 기준으로 테이블을 재생성함으로써 클러스터링 팩터를 인위적으로 좋게 만드는 방법을 생각해 볼 수 있고, 실제 그렇게 했을 때 나타나는 효과는 매우 극적이다. 주의할 것은, 인덱스가 여러 개인 상황에서 특정 인덱스를 기준으로 테이블을 재정렬하면 다른 인덱스의 클러스터링 팩터가 나빠질 수 있다는 점이다. 다행히 두 인덱스 키 칼럼 간에 상관관계가 높다면(예를 들어, 직급과 급여) 두 개 이상 인덱스의 클러스터링 팩터가 동시에 좋아질 수 있지만, 그런 경우를 제외하면 대개 클러스터링 팩터가 좋은 인덱스는 테이블당 하나뿐이다. 따라서 인위적으로 클러스터링 팩터를 높일 목적으로 테이블을 Reorg 할 때는 가장 자주 사용되는 인덱스를 기준으로 삼아야 하며, 혹시 다른 인덱스를 사용하는 중요한 쿼리 성능에 나쁜 영향을 주지 않는지 반드시 체크해 봐야 한다. 그리고 이 작업을 주기적으로 수행하려면 데이터베이스 관리 비용이 증가하고 가용성에도 영향을 미치므로 테이블과 인덱스를 Rebuild하는 부담이 적고 그 효과가 확실할 때만 사용하는 것이 바람직하다.

바. 배치 I/O //TODO

출처 : 데이터온에어 – 한국데이터산업진흥원(https://dataonair.or.kr)