-
1. 인덱스 ROWID 에 의한 테이블 랜덤 엑세스
2. 테이블 엑세스 최소화 튜닝
인덱스 기본 원리에서 살폈듯이, B*Tree 인덱스를 정상적으로 사용하려면 범위 스캔 시작지점을 찾기 위해 루트 블록부터 리프 블록까지의 수직적 탐색 과정을 거쳐야 한다. 만약 인덱스 선두 컬럼이 조건절에 사용되지 않으면, 범위 스캔을 위한 시작점을 찾을 수 없어 옵티마이저는 인덱스 전체를 스캔하거나 테이블 전체를 스캔하는 방식을 선택한다.
인덱스 선두 컬럼이 조건절에 사용되더라도 범위 스캔이 불가능하거나 인덱스를 아예 사용 못하는 경우들을 살펴본다.
1. 범위 스캔이 불가능하거나 인덱스 사용이 불가능한 경우
아래 세가지 경우는 인덱스 범위 스캔이 불가능하지만 index Full Scan 은 가능하다.
마지막 쿼리문의 경우 오라클은 단일 컬럼 인덱스에 null을 저장하지 않기 때문에,
부서코드에 단일 컬럼 인덱스가 존재하면 그 인덱스 전체를 스캔하면서 얻은 레코드는 모두 부서코드 is not null 을 만족한다.
select * from 업체 where substr(업체명,1,2) = '대한'; select * from 고객 where 직업 <> '학생'; select * from 사원 whrer 부서코드 is not null;2. 인덱스 컬럼의 가공
인덱스 컬럼 가공 사례 튜닝 방안 select * from 업체
where substr(업체명,1,2)='대한'select * from 업체
where 업체명 like '대한%'select * from 사원
where 월급여 * 12 = 36000000select * from 사원
where 월급여 = 36000000/12select * from 주문
where to_char(일시,'yyyymmdd') = :dtselect * from 주문
where 일시 >= todate(:dt, 'yyyymmdd')
and 일시 < todate(:dt, 'yyyymmdd') +1select * from 고객
where 연령 || 직업 = '30공무원'select * from 고객
where 연령 = '30'
and 직업 = '공무원'select * from 회원사지점
where 회원번호 || 지점번호 = :strselect * from 회원사지점
where 회원번호 = substr(:str,1,2)
and 지점번호 = substr(:str,3,4)3. 묵시적 형변환
인덱스 컬럼을 사용자가 명시적으로 가공하지 않더라도 조건절에서 비교되는 두 값의 데이터 타입이 다르면 내부적으로 형변환이 일어난다.
예를 들어 emp 테이블 deptno 컬럼은 number 형이다. 이 컬럼에 대한 검색조건으로는 숫자형이 옳지만, 자칫 실수로 다음과 같이 문자형으로 코딩하는 경우가 종종 생긴다.
다행히 문자형과 숫자형이 만나면 옵티마이저가 문자형을 숫자형으로 변환해 인덱스도 사용할 수 있게 된다.
테이블 엑세스 최소화
1. 인덱스 ROWID 에 의한 테이블 랜덤 엑세스
쿼리에서 참조되는 컬럼이 모두 포함되는 경우가 아니라면, '테이블 랜덤 엑세스' 가 일어난다.
다음 실행 계획에서 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' (IDX)SQL 서버는 '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 PRDERED FORWARD가. 인덱스 ROWID 에 의한 테이블 엑세스 구조
인덱스에 저장되어 있는 ROWID 는 흔히 '물리적 주소정보' 라고 일컬어진다. 오브젝트 번호, 데이터 파일 번호, 블록 번호 같은 물리적 요소들로 구성돼 있기 때문이다.
하지만 보는 시각에 따라 '논리적 주소정보'라고 표현하기도 한다. ROWID 가 물리적 위치 정보로 구성되지만 인덱스에서 테이블 레코드로 직접 연결되는 구조는 아니기 때문이다.
어떤 것이 맞든 중요한 것은, ROWID 가 메모리 상의 위치정보가 아니라 디스크 상의 위치정보라는 사실이다.
그리고 데이터 블록을 읽을 때 항상 버퍼 캐시를 경유하므로 메모리 상에서 버퍼 블록을 찾기 위해 해시 구조와 알고리즘을 사용한다.
인덱스 ROWID 를 이용해 데이터 블록을 읽는 매커니즘을 요약하면 다음과 같다.
1. 인덱스에서 하나의 rowid 를 읽고 DBA (디스크 상 블록 위치 정보)를 해시 함수에 적용해 해시값을 확인한다.
2. 해시 값을 이용해 해시 버킷을 찾는다.
3. 해시 버킷에 연결된 해시 체인을 스캔하면서 블록 헤더를 찾는다.
4. 블록 헤더를 찾으면 거기 저장된 포인터를 이용해 버퍼 블록을 읽는다.
5. 블록 헤더를 찾지 못하면, LRU 리스트를 스캔하면서 Free 버퍼를 찾는다. 디스크에서 읽은 블록을 적재하기 위해 빈 캐시 공간을 찾는 것이다.
6. LRU 리스트에서 Free 버퍼를 얻지 못하면 Dirty 버퍼를 디스크에 기록해 Free 버퍼를 확보한다.
7. Free 버퍼를 확보하고 나면 디스크에서 블록을 읽어 캐시에 적재한다.
여기서 일일히 설명할 순 없지만, 위 처리 과정 중에는 래치(Latch), 버퍼 Lock 같은 Internal Lock 을 획득하거나
다른 백그라운드 프로세스의 선처리 결과를 기다리는 내부 매커니즘이 작동한다. 그런 과정에 경합까지 발생한다면 블록 하나를 읽더라도 생각보다 큰 비용을 치르게 된다.
오라클이나 SQL 서버같은 디스크 기반 DBMS 에서 인덱스 rowid 에 의한 테이블 엑세스가 생각만큼 빠르지 않은 이유가 여기에 있다. 특히 다량의 테이블 레코드를 읽을 때의 성능 저하가 심각하다.
나. 클러스터링 팩터
오라클은 클러스터링 팩터라는 개념을 이용해 인덱스 ROWID 에 의한 테이블 엑세스 비용을 평가한다.
SQL 서버는 공식적으로 이 용어를 사용하지는 않지만, 내부적인 비용 계산식에 이런 개념이 포함되어 있을 것이다.
클러스터링 팩터는 '군집성 계수(=데이터가 모여있는 정도)' 로 해석될 수 있으며, 특정 컬럼을 기준으로 값은 값을 갖는 데이터가 서로 모여있는 정보를 말한다.
클러스터링 팩터가 좋은 경우는 인덱스 레코드 정렬 순서와 거기서 가리키는 테이블 레코드 정렬 순서가 100% 일치하는 것을 볼 수 있다.
클러스터링 팩터가 안좋은 경우는 인덱스 레코드 정렬 순서와 거기서 가리키는 테이블 레코드 정렬 순서가 전혀 일치하지 않는 것이다.
다. 인덱스 손익분기점
Index Range Scan 에 의한 테이블 엑세스가 Table Full Scan 보다 느려지는 지점을 흔히 '손익분기점' 이라고 부른다.
예를 들어 손익분기점이 10% 라는 의미는 1000개 중 100개 레코드 이상을 읽을 때는 인덱스를 사용하는 것보다 테이블 전체를 스캔하는 것이 더 빠르다는 뜻이다.
인덱스 손익분기점은 5~20%의 낮은 수준에서 결정되지만, 클러스터링 팩터에 따라 크게 달라진다.
클러스터링 팩터가 나쁘면 손익분기점은 5% 미만에서 결정된다. 심할 때는 (BCHR 이 매우 안 좋을 때) 1% 미만으로 떨어진다. 반대로 클러스터링 팩터가 아주 좋을 때는 손익분기점이 90% 수준까지 올라가기도 한다.
인덱스에 의한 엑세스가 Full Table Scan 보다 더 느리게 만드는 가장 핵심적인 두 가지 요인은 다음과 같다.
- 인덱스 rowid 에 의한 테이블 엑세스는 랜덤 엑세스인 반면, Full Table Scan 은 시퀀셜 엑세스 방식으로 이뤄진다.
- 디스크 I/O 시 , 인덱스 rowid 에 의한 테이블 엑세스는 Single Block Read 방식을 사용하는 반면,
Full Table Scan 은 MultiBlock Read 방식을 사용한다.
라. 손익분기점 극복하기
손익분기점 원리에 따르면 선택도가 높은 인덱스는 효용가치가 낮다. 그렇더라도 테이블 전체를 스캔하는 것은 부담스러울 때가 많다. 이럴 때 DBMS 가 제공하는 기능을 잘 활용해 인덱스 손익분기점의 한계를 극복할 수 있다.
1. SQL 서버의 클러스터형 인덱스와 오라클의 IOT
테이블을 인덱스 구조로 생성하는 것이다. 테이블 자체가 인덱스 구조이므로 항상 정렬된 상태를 유지한다. 더불어 인덱스 리프 블록이 곧 데이터 블록이어서 인덱스를 수직 탐색한 다음 테이블 레코드를 읽기 위한 추가적인 랜덤 액세스가 불필요하다.
2. SQL 서버의 Include Index
인덱스 키 외에 미리 지정한 컬럼을 리프 레벨에 함께 저장하는 기능으로서, 테이블 랜덤 엑세스 횟수를 줄이도록 돕는다.
3. 오라클이 제공하는 클러스터 테이블
키 값이 같은 레코드를 같은 블록에 저장하기 때문에 클러스터 테이블에 대한 클러스터 인덱스를 이용할 때는 테이블 랜덤 엓세스가 키 값별로 한 번씩만 발생한다. 클러스터에 도달해서는 시퀀셜 방식으로 스캔하기 때문에 넓은 범위를 읽어도 비효율이 없다.
4. 파티셔닝
대량 범위 검색 조건으로 자주 사용되는 컬럼 기준으로 테이블을 파티셔닝한다면 Full Table Scan 을 하더라도 일부 파티션만 읽고 멈추게 할 수 있다.
클러스터는 기준 키 값이 같은 레코드를 블록 단위로 모아 저장하지만, 파티셔닝은 세그먼트 단위로 저장하는 점이 다르다.
5. 부분범위처리
2. 테이블 엑세스 최소화 튜닝
가. 인덱스 컬럼 추가
emp 테이블에 PK 외에 [deptno + job] 순으로 구성괸 emp_x01 인덱스만 있는 상태에서 아래 쿼리를 실행하는 경우,
조건에 만족하는 사원은 1명이지만 테이블 엑세스는 6번 발생한다.
select /*+index(emp emp_x01)*/ ename,job,sal from emp where deptno=30 and sal>3000
여기서 인덱스 구성은 [deptno + sal] 로 수정하면 좋겠지만, 운영환경에서 구성을 함부로 바꾸기 어려운 경우,
기존 인덱스에 추가해주면 [deptno+ job+sal] 인덱스 스캔량은 그대로지만, 테이블 랜덤 엑세스 횟수를 줄일 수 있다.
나. Covered Index
테이블을 엑세스하고서 필터 조건에 의해 버려지는 레코드가 많을 때, 인덱스에 컬럼을 추가함으로써 얻는 성능 효과를 살펴보았다. 그런데 테이블 랜덤 엑세스가 아무리 많아도 필터로 버리는 레코드가 없다면 비효율은 없다.
이럴 경우 아예 테이블 엑세스가 발생하지 않도록 필요한 모든 컬럼을 인덱스에 포함시키는 방법을 고려해 볼 수 있다.
SQL 서버에서는 그런 인덱스를 'Covered 인덱스'라고 부르며, 인덱스만 읽고 처리하는 쿼리를 'Covered 쿼리'라고 부른다.
다. Include Index
오라클엔 아직 없는 유용한 기능이 SQL 서버 2005에 추가됐다. 인덱스 키 외에 미리 지정한 컬럼을 리프 레벨과 함께 저장하는 기능이 바로 그것이다. 인덱스를 생성할 때 다음과 같이 include 옵션을 지정하면 되고, 컬럼을 최대 1023개 까지 지정할 수 있다.
create index emp_x01 on emp (deptno) include (sal)만약 인덱스를 [deptno+sal] 로 생성하면, sal 컬럼도 수직적 탐색에 사용할 수 있도록 그 값을 루프와 브랜치 블록에 저장한다. 하지만 위와 같이 sal 컬럼을 include 옵션으로만 지정하면 그 값은 리프 블록에만 저장한다.
따라서 수직적 탐색에는 사용되지 못하고 수평적 탐색을 위한 필터 조건으로만 사용된다. 그럼으로써 테이블 랜덤 엑세스 횟수를 줄이는 효과를 가진다.
라. IOT, 클러스터형 인덱스, 클러스터 테이블 활용
해시 클러스터 테이블은 해시 함수에서 반환된 값이 같은 데이터를 물리적으로 함께 저장하는 구조다.
클러스터 키로 데이터를 검색하거나 저장할 위치를 찾을 때 해시 함수를 사용한다. 해시 함수가 인덱스 역할을 대신하는 것이며, 해싱 알고리즘을 이용해 클러스터 키 값을 데이터 블록 주소로 변환해준다.
별도의 인덱스 구조를 생성하지 않는 장점에도 불구하도 해시 클러스터의 활욜성을 떨어뜨리는 중요한 제약사항은, '=' 검색만 가능하다는 것이다. 항상 '=' 조건으로만 검색되는 컬럼을 해시키로 선정해야하는 것이며, 이는 해시 함수를 사용하기 때문에 나타나는 어쩔 수 없은 제약이다.
마. 수동으로 클러스터링 팩터 높이기
테이블에는 데이터가 무작위로 입력되는 반면, 그것을 가리키는 인덱스는 정해진 키 순으로 정렬되기 때문에 대게 클러스터링 팩터가 좋지 않기 마련이다.
그럴 때 해당 인덱스 기준으로 테이블을 재생성함으로써 클러스터링 팩터를 인위적으로 좋게 만드는 방법을 생각해볼 수 있고, 효과는 매우 극적이다.
주의할 것은, 인덱스가 여러 개인 상황에서 특정 인덱스를 기준으로 테이블을 재정렬하면 다른 인덱스의 클러스터링 팩터가 나빠질 수 있다는 점이다. 다행히 두 인덱스 키 컬럼 간의 상관관계가 높다면 두 대 이상 인덱스의 클러스터링 팩터가 동시에 좋아질 수 있지만, 그런 경우를 제외하면 대개 클러스터링 팩터가 좋은 인덱스는 테이블당 하나뿐이다.
따라서 인위적으로 클러스터링 팩터를 높일 목적으로 테이블을 reorg 할 때는 가장 자주 사용되는 인덱스를 기준으로 삼아야 한다. 혹시 다른 인덱스를 사용하는 중요한 쿼리 성능에 나쁜 영향을 주지 않는지 반드시 체크해야 한다.
이 작업을 주기적으로 수행하려면 데이터베이스 관리비용이 증가하고 가용성에도 영향을 미치므로 테이블과 인덱스를 리빌드하는 부담이 적고 그 효과가 확실할 때에만 사용하는 것이 바람직하다.
바. 배치 I/O
앞서 인덱스 ROWID를 이용한 테이블 랜덤 엑세스는 고비용구조라는 것을 배웠다.
인덱스를 이용해 대량 데이터를 조회하면, 디스크 I/O 발생량도 함께 증가하므로 성능이 급격히 나빠진다. 지금까지 언급된 튜닝 기법을 잘 숙지하고 잘 활용해야하는 이유이다.
디스크 랜덤 I/O 성능을 높이기 위해 DBMS 업체들도 내부적으로 많은 기법을 개발하고 있다. 오라클의 배치 I/O 는 그 중 하나이다. 다른 기능들에 비해 이 기능은 쿼리 결과 집합에도 영향을 주기 때문에 잘 숙지해야한다.
배치 I/O 는 읽는 블록마다 건건이 I/O call 을 발생시키는 비효율을 줄이기 위해 고안한 기능이다.
인덱스를 이용해 테이블을 엑세스하다가 버퍼 캐시에서 블록을 찾지 못하면 일반적으로 디스크 블록을 바로 읽는다. 이 기능이 작동하면 테이블 블록에 대한 디스크 I/O Call 을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리한다. 오라클 11g 에서는 NL 조인 Inner 쪽 테이블을 엑세스할 때만 이 기능이 작동했지만, 12c 부터는 인덱스 ROWID로 테이블을 엑세스하는 어떤 부분에서든 이 기능이 작동할 수 있다.
배치 I/O 기능이 작동하면 인덱스를 이용해 출력하는 데이터 정렬순서가 매번 다를 수 있다는 사실에 주목해야 한다.
테이블 블록을 모두 버퍼 캐시에서 찾을 때는 (버퍼캐시히트율=100%) 기존처럼 인덱스 키값 순으로 데이터가 출력된다. 하지만 그렇지 않을 때 (버퍼캐시히트율<100%), 즉 실제 배치 I/O 가 작동할 때는 데이터 출력 순서가 인덱스 정렬 순서와 다를 수 있다.
create index emp_x01 on emp(deptno, job, empno); set autotrace traceonly exp; select /*+batch_table_access_by_rowid(e) */ * from emp e where deptno=20 order by job, empno; -------------------------------------------------- 0 SELECT STATEMENT 1 SORT ORDER BY 2 TABLE ACCESS BY INDEX ROWID BATCHED 3 INDEX RANGE SCAN실행계획에 배치 I/O 가 작동할 수 있다는 사실을 표현하기 위해 테이블 엑세스단계 뒤쪽에 'BATCHED' 가 추가됐다.
주목할 점은 소트 연산을 생략할 수 있는 인덱스 구성인데도 SORT ORDER BY 오퍼레이션이 나온 것이다.
배치 I/O 기능이 작동하면 데이터 정렬 순서를 보장할 수 없으므로 옵티마이저가 이런 선택을 한 것이다.
배치 I/O 가 작동하지 않는다면 실행계획에 SORT ORDER BY 는 생략된다.
인덱스를 이용하면 결과 집합이 자동으로 인덱스 키 값으로 정렬되므로, 과거에는 ORDER BY 를 생략해 ROWNUM 조건과 함께 index/index_desc 힌트를 적용하는 패턴을 많이 사용했지만, 12c로 업그레이드하면서 결과 집합의 정렬 순서가 달라질 수 있으므로 order by 문구를 함께 작성하는 것이 바람직하다.
인덱스 스캔 효율화
I/O 튜닝의 핵심 원리로 1. 랜덤 엑세스 발생량 줄이기 2. 시퀀셜 엑세스 의한 선택 비중을 늘리기 가 있다.
지금까지 설명은 모두 1번에 해당하며, 여기서부터는 2번, 그 중에서도 인덱스를 시퀀셜 방식으로 스캔하는 단계에서 발생하는 비효율 해소 원리를 다룬다.
1. 인덱스 선행 컬럼이 범위조건일 때 비효율
인덱스 구성 컬럼이 조건절에서 모두 등치(=) 조건으로 비교되면 리프 블록을 스캔하면서 읽은 레코드는 모두 테이블 엑세스로 이어진다. 읽고 버리는 레코드가 없으므로 인덱스 스캔단계에서 효율은 최상이다.
인덱스 컬럼 중 일부가 '=' 조건이 아니거나 조건절에서 생략되더라도 그것이 뒤쪽 컬럼일 때는 비효율이 없다.
예를 들어 인덱스가 [아파트시세코드 + 평형 + 평형타입 + 인터넷 매물] 순으로 구성됐을 때 조건절이 다음과 같은 경우를 말한다.
where 아파트시세코드 = :a where 아파트시세코드 = :a and 평행 = :b where 아파트시세코드 = :a and 평행 = :b and 평형타입 = :c where 아파트시세코드 = :a and 평행 = :b and 평형타입 between :c and :d반면 인덱스 선행컬럼이 조건절에서 누락되거나 between, 부등호, like 같은 범위검색 조건이 사용되는 인덱스를 스캔하는 모든 단계에서 비효율이 발생한다.
아래 쿼리를 실행할 때 [아파트시세코드 + 평형 + 평형타입 + 인터넷 매물] 인덱스를 실행하면 효율적이지만
[인터넷 매물 + 아파트시세코드 + 평형 + 평형타입 ] 순으로 바꾸면 인덱스 스캔 범위가 넓어지며 비효율적이라는 의미다.
select * from 매매 where 아파트시세코드 = '12345' and 평형 = '59' and 평형타입 = 'A' and 인터넷매물 between 1 and 2 order by 입력일 desc;2. 범위조건을 In-List 로 전환
인덱스가 이미 [인터넷 매물 + 아파트시세코드 + 평형 + 평형타입 ] 과 같이 선행 컬럼이 범위검색 컬럼이고,
운영중인 시스템이라 이를 바꾸기 어렵다면, between 조건을 In-List 조건으로 변경하면 가끔 큰 효과를 얻을 수 있다.
select * from 매매 where and 인터넷매물 in (1, 2) and 아파트시세코드 = '12345' and 평형 = '59' and 평형타입 = 'A' order by 입력일 desc; ----------------------------------------------- 0 SELECT STATEMENT 1 INLIST ITERATOR 2 TABLE ACCESS BY INDEX ROWID 3 INDEX RANGE SCANIn-List 개수가 많으면 필요없는 범위를 스캔하는 비효율은 사라져도, 인덱스 수직 탐색이 여러 번 발생해
between 조건으로 리프블록을 추가로 스캔하는 것보다 In-List로 브랜치 블록을 반복 탐색하는 비효율이 더 커질 수 있다. 인덱스 높이가 높을 때는 특히 더 그렇다. 이런 경우 오라클이라면 Index Skip Scan 이 유용할 수 있다.
3. 범위조건을 2개 이상 사용할 때의 비효율
인덱스 구성이 [회사+지역+상품명] 일 때, 다음과 같이 범위검색 조건을 2개 이상 사용하면 첫 번째가 인덱스 스캔 범위를 거의 결정하고, 두번째는 필터 조건 역할만 하기 때문에 성능상 불리할 수 있다.
select * from 가입상품 where 회사 = :com and 지역 like :reg || '%' and 상품명 like :prod || '%'스캔량이 소량일 때는 차이가 미미하지만 대량일 때는 상당한 성능 차이를 보일 수 있으므로 인덱스 컬럼에 대한 비교 연산자를 신중히 선택해야 한다.
만약 지역 컬럼에 대한 검색조건이 입력되지 않을 수 있어 위와 같이 like 연산자를 사용한 거라면 SQL 을 아래와 같이 2개 만들어 사용하는 것이 좋다.
1. select * from 가입상품 where 회사 = :com and 상품명 like :prod || '%'; 2. select * from 가입상품 where 회사 = :com and 지역 = :reg and 상품명 like :prod || '%';혹은 아래처럼 union all 을 이용하는 방법도 있다.
select * from 가입상품 where :reg is null and 회사 = :com and 상품명 like :prod || '%'; union all select * from 가입상품 where :reg is not null and 회사 = :com and 지역 = :reg and 상품명 like :prod || '%';기존 인덱스 구성에서 UNION ALL 상단 쿼리는 기존과 동일한 비효율을 안고 수행되겠지만, 하단쿼리는 최상으로 수행될 수 있다. 만약 UNION ALL 상단 쿼리까지 최적화하려면 [회사+상품명] 으로 인덱스를 추가해야한다.
인덱스를 새로 추가하는 것이 부담되면 [회사+상품명+지역] 순으로 인덱스 컬럼 순서 구성을 변경하는 것을 고려할 수 있는데 이는 UNION ALL 하단 쿼리를 처리할 때 불리해진다.
따라서 이는 상품명 조건에 입력되는 값의 선택도에 따라 결정할 사항이다.
출처
SQL 전문가 가이드 - 한국데이터산업진흥원
반응형