인덱스 튜닝 기초
범위 스캔이 불가능하거나 인덱스 사용이 아예 불가능한 경우
- 인덱스 선두 컬럼을 조건절에서 가공하면 (FBI 인덱스를 정의하지 않는 한) 정상적으로 인덱스를 사용할 수 없다.
FBI 인덱스 : 함수 기반 인덱스. 컬럼을 가공한 형태로 인덱스 생성하기
- 부정형 비교(<>)를 사용해도 인덱스를 사용할 수 없다.
- is not null 조건도 부정형 비교에 해당하므로 정상적인 인덱스 사용은 어렵다.
위 세 경우 모두 정상적인 인덱스 범위 스캔이 불가능할 뿐이지 인덱스 사용 자체가 불가능한 것은 아니다.
인덱스에 null이 포함될 때
- 오라클
단일컬럼 인덱스 : null값 저장 안함.
결합 인덱스 : 모든 인덱스 구성 컬럼이 null인 경우 저장 안함. - SQL Server
모든 컬럼이 null인 로우도 인덱스에 저장 됨.
따라서 Oracle에서는 단일컬럼 인덱스를 통해 is null 검색 시에는 인덱스 사용이 불가능하지만 SQL Server에서는 가능하다.
묵시적 형변환
인덱스 컬럼을 사용자가 명시적으로 가공하지 않더라도 조건절에서 비교되는 두 값의 데이터 타입이 다르면 내부적으로 형변환이 일어난다. 하지만 묵시적 형변환은 쿼리 수행 도중 에러를 발생시키거나(Oracle에서는 자동으로 형변환을 시켜주긴 하지만... 바로 컴파일 시점에서 에러를 내는 DBMS이 있다.) 결과 집합을 틀리게 만드는 요인이 될 수 있기 때문에 인덱스 컬럼과 비교되는 반대쪽을 인덱스 컬럼 데이터 타입에 맞춰주는 것이 좋다.(좌변 기준으로 우변의 값에 형변환이 일어날 경우에 성능에 문제가 없다. 그러므로 우변의 값에 포맷을 정확히 지정해 주는 습관이 필요하다.)
- 문자형 숫자형과 만나면 숫자형으로 변환된다.
단, 연산자가 LIKE일 때는 다르다. LIKE 자체가 문자열 비교 연산자이므로 이때는 문자형 기준으로 숫자형 컬럼이 변환된다.
2. 문자형과 날짜형이 만나면 날짜형으로 변환된다.
자동 형변환 주의
자동 형변환 시 애플리케이션 품질 측면에도 문제가 발생할 수 있다.
예를 들어 숫자형 컬럼(n_col)과 문자형 컬럼(v_col)을 비교하면 문자형 컬럼이 숫자형으로 변환되는데, 만약 문자형 컬럼에 숫자로 변환할 수 없는 문자열이 입력되면 커리 수행 도중 에러가 발생한다.
실행 에러가 아니라 결과 오류가 생기는 사례도 있다.
오라클에서 decode(a, b, c, d)를 처리하는 방법을 생각해보자. a=b면 c를 반환하고, 아니면 d를 반환한다. 이때 반환값의 데이터 타입은 세 번째 인자 c에 의해 결정된다. 따라서 c가 문자형이고 d가 숫자형이면 d는 문자형으로 변환된다. 또한, 세 번째 인자 c가 null이면 varchar2로 취급한다.
<EMP>
| EMPNO | JOB | SAL |
|---|---|---|
| 1 | CLERK | 300 |
| 2 | STUDENT | 99 |
| 3 | STUDENT | 100000 |
이런 테이블이 있다고 가정하자. 여기서 JOB이 'CLERK'이 아닌 사람들 중 SAL이 가장 높은 사람을 찾기 위해 이렇게 SQL을 작성했다.
select max(decode(job, 'CLERK', NULL, sal)) max_sal2
from emp;100000을 예상하고 쿼리를 작성했지만 결과는 99가 나온다. 위에서 말한 것처럼 현재 decode의 세 번째 인자가 NULL이고 varchar2로 취급되기 때문에 문자열 '99'와 문자열 '100000'중에서 최대값을 찾게 되는 것이다. '99'와 '100000'중에서는 '100000'이 크니까 결과가 100000이 되는 것이다.
원래 의도대로 쿼리를 작성하기 위해서는
select max(decode(job, 'CLERK', TO_NUMBER(NULL), sal)) max_sal2
from emp;이렇게 세 번째 인자를 형변환 해줘야 한다.
SQL 성능은 블록 I/O를 줄일 수 있느냐 없느냐에서 결정되기 때문에 TO_CHAR, TO_DATE, TO_NUMBER와 같은 형변환 함수는 필요할 때 명시해주는 것이 좋다.
테이블 Rabdom 액세스 최소화
인덱스 ROWID에 의한 테이블 Random 액세스
쿼리에서 참조되는 컬럼이 인덱스에 모두 포함된 경우가 아니라면, '테이블 Random 액세스'가 일어난다.
인덱스에 저장되어 있는 rowid는 흔히 '물리적 주소정보'라고 일컬어지는데, 오브젝트 번호, 데이터 파일 번호, 블록 번호같은 물리적 요소들로 구성되어 있기 때문일 것이다. 하지만 '논리적 주소정보'가 좀 더 맞는 표현일 듯 하다. rowid가 물리적 위치 정보로 구성되지만 인덱스에서 테이블 레코드로 직접 연결되는 구조는 아니기 때문이다.
인덱스 ROWID에 의한 테이블 액세스 구조
- 인덱스에서 하나의 rowid를 읽고 DBA(디스크 상의 블록 위치 정보)를 해시 함수에 적용해 해시 값을 확인한다.
- 해시 값을 이용해 버킷을 찾아간다.
- 해시 버킷에 연결된 해시 체인을 스캔하면서 블록 헤더를 찾는다.
- 해시 체인에서 블록 헤더를 찾으면 거기 저장된 포인터를 이용해 버퍼 블록을 읽는다. (버퍼 캐시에 적재된 상태)
- 해시 체인을 스캔하고도 블록 헤더를 찾지 못하면, LRU 리스트를 스캔하면서 Free 버퍼를 찾는다. 디스크에서 읽은 블록을 적재하기 위해 빈 캐시 공간을 찾는 것이다.
- LRU 리스트에서 Free 버퍼를 얻지 못하면 Dirty 버퍼를 디스크에 기록해 Free 버퍼를 확보한다.
- Free 버퍼를 확보하고 나면 디스크에서 블록을 읽어 캐시에 적재한다.
위 과정을 처리하는 중 래치, 버퍼 Lock과 같은 Internal Lock을 획득하거나 다른 백그라운드 프로세스의 선처리 결과를 기다리는 내부 메커니즘이 작동하는데, 그런 과정에 경합까지 발생한다면 블록 하나를 읽더라도 생각보다 큰 비용을 치르게 된다. 따라서 인덱스 rowid에 의한 테이블 액세스가 생각만큼 빠르지 않다.
Oracle은 '클러스터링 팩터'라는 개념을 사용해 인덱스 ROWID에 의한 테이블 액세스 비용을 평가한다. 여기서 클러스터링 팩터는 '군집성 계수'쯤으로 번역이 가능하며, 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미한다. 인덱스 레코드 정렬 순서와 테이블 레코드 정렬 순서가 같을수록 클러스터링 팩터가 좋은 상태라고 할 수 있다.
인덱스 손익분기점
인덱스 rowid에 의한 테이블 액세스는 생각보다 고비용 구조이고, 따라서 일정량을 넘는 순간 테이블 전체를 스캔할 때보다 오히려 더 느려진다. Index Range Scan에 의한 테이블 액세스가 Table Full Scan보다 느려지는 지점을 흔히 '손익 분기점'이라고 부른다.
ex) 인덱스 손익분기점이 10%다 = 레코드 1000개 중 100개 이상을 읽을 때는 인덱스를 이용하는 것보다 Table Full Scan을 하는 것이 더 빠르다.
클러스터링 팩터가 나쁘면 손익분기점이 5% 미만에서 결정되며 심할 때는 1% 미만으로 떨어진다, 반대로 클러스터링 팩터가 아주 좋을 때는 손익분기점이 90% 수준까지 올라가기도 한다.
인덱스에 의한 액세스가 Full Table Scan보다 더 느리게 만드는 핵심적인 요인
- 인덱스 rowid에 의한 테이블 액세스는 Random 액세스인 반면, Full Table Scan은 Sequential 액세스 방식으로 이루어진다.
- 디스크 I/O 시, 인덱스 rowid에 의한 테이블 액세스는 Single Block Read 방식을 사용하는 반면, Full Table Scan은 Multiblock Read 방식을 사용한다.
손익분기점 원리에 따르면 선택도가 높은 인덱스는 효용가치가 낮지만, 그렇다고 테이블 전체를 스캔하는 것은 부담스러울 때가 많다.
손익분기점 극복하기
- SQL Server의 클러스터형 인덱스와 Oracle의 IOT
: 테이블 자체가 인덱스 구조이므로 항상 정렬된 상태를 유지하며 인덱스 리프 블록이 곧 데이터 블록이어서 인덱스를 수직 탐색한 다음에 테이블 레코드를 읽기 위한 추가적인 Random 액세스가 불필요하다. - SQL Server의 Include Index
: 인덱스 키 외에 미리 지정한 컬럼을 리프 레벨에 함께 저장하는 기능으로서. 테이블 Random 액세스 횟수를 줄이도록 돕는다. - Oracle의 클러스터 테이블
: 키 값이 같은 레코드를 같은 블록에 저장하기 때문에 클러스터 테이블에 대한 클러스터 인덱스를 이용할 때는 테이블 Random 액세스가 키 값별로 한 번씩만 발생한다. 클러스터에 도달해서는 Sequential 방식으로 스캔하기 때문에 넓은 범위를 읽더라도 비효율이 없다. - 파티셔닝
: 대량 범위검색 조건으로 자주 사용되는 컬럼 기준으로 테이블을 파티셔닝하면 Full Table Scan 하더라도 일부 파티션만 읽고 멈추도록 할 수 있다. 클러스터는 기준 키 값이 같은 레코드를 블록 단위로 모아 저장하지만 파티셔닝은 세그먼트 단위로 저장하는 점이 다르다. - 부분범위 처리
: 인덱스 스캔 비효율이 없도록 잘 구성된 인덱스를 이용해 부분범위처리 방식으로 프로그램을 구현한다면 그 인덱스의 효용성은 100%가 된다.
테이블 Random 액세스 최소화 튜닝
인덱스 컬럼 추가
인덱스 구성을 함부로 바꾸기 어려울 뿐더러 인덱스를 새로 추가하다 보면 테이블마다 인덱스가 수십 개씩 달리는 현상이 발생한다. 이럴 때 기존 인덱스에 특정 컬럼을 추가하는 것만으로도 큰 효과를 거둘 수 있다. 인덱스 스캔량은 그대로지만 테이블 Random 액세스 횟수를 줄여주기 때문이다.
Covered Index
테이블 Random 액세스가 아무리 많더라도 필터 조건에 의해 버려지는 레코드가 거의 없을 경우 아예 테이블 액세스가 발생하지 않도록 필요한 모든 컬럼을 인덱스에 포함시키는 방법을 고려해볼 수 있다. SQL Server에서는 그런 인덱스를 'Covered Index'라고 부르며, 인덱스만 읽고 처리하는 쿼리를 'Covered 쿼리'라고 부른다.
Include Index
인덱스 키 외에 미리 지정한 컬럼을 리프 레벨에 함께 저장하는 기능이다. (Oracle에는 아직 없는 기능이다.)
create index emp_x01 on emp (deptno) include (sal)include 옵션으로 지정한 컬럼은 그 값을 리프 블록에만 저장하여 수직적 탐색에는 사용되지 못하고 수평적 탐색을 위한 필터 조건으로만 사용한다.
IOT, 클러스터형 인덱스, 클러스터 테이블 활용
해시 클러스터 테이블은 해시 함수에서 반환된 값이 같은 데이터를 물리적으로 함께 저장하는 구조다. 해시 함수가 인덱스 역할을 대신하는 것이며, 해싱 알고리즘을 이용해 클러스터 키 값을 데이터 블록 주소로 변환해준다. 별도의 인덱스 구조를 생성하지 않는 장점에도 불구하고 해시 클러스터의 활용성을 떨어뜨리는 중요한 제약사항은, '=' 검색만 가능하다는 점이다.
수동으로 클러스터링 팩터 높이기
해당 인덱스를 기준으로 테이블을 재생성함으로써 클러스터링 팩터를 인위적으로 좋게 만드는 방법이다. 주의할 점은, 인덱스가 여러 개인 상황에서 특정 인덱스를 기준으로 테이블을 재정렬한다면 다른 인덱스의 클러스터링 팩터가 나빠질 수 있다는 점이다. 따라서 인위적으로 클러스터링 팩터를 높일 목적으로 테이블을 Reorg할 때는 가장 자주 사용되는 인덱스를 기준으로 삼아야 하며, 혹시 다른 인덱스를 사용하는 중요한 쿼리 성능에 나쁜 영향을 주지 않는지 반드시 체크해봐야 한다. 그리고 이 작업을 주기적으로 수행하려면 데이터베이스 관리 비용이 증가하고 가용성에도 영향을 미치므로 테이블과 인덱스를 Rebuild하는 부담이 적고 그 효과가 확실할 때만 사용하는 것이 바람직하다.
인덱스 스캔범위 최소화
I/O 튜닝의 핵심 원리
- Random 액세스 발생량을 줄인다.
- Sequential 액세스에 의한 선택 비중을 높인다.
인덱스 선행 컬럼이 범위조건일 때의 비효율
인덱스 구성 컬럼이 조건절에서 모두 등치 조건(=)으로 비교되거나, 인덱스 컬럼 중 일부가 '='조건이 아니거나 조건절에서 생략되더라도 그것이 뒤쪽 컬럼일 때는 비효율이 없다. 하지만 인덱스 선행 컬럼이 조건절에 누락되거나 between, 부등호, like같은 범위검색 조건이 사용되면 인덱스를 스캔하는 단계에서 비효율이 발생한다.
범위 조건을 In-List르 전환
인덱스의 구성을 바꾸기 어려울 때 between 조건을 In-List로 바꿔주면 큰 효과를 얻을 수 있는 경우가 있다.
select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
from 매물아파트매매
where 인터넷매물 between '1' and '2' and 아파트시세코드='A01011350900056' and 평형 = '59' and 평형타입 = 'A' order by 입력일 desc;물론 between 조건에 해당하는 컬럼을 인덱스의 맨 뒤로 보내면 되겠지만 구성을 바꾸기는 쉽지 않다.
select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
from 매물아파트매매
where 인터넷매물 in ('1', '2') and 아파트시세코드='A01011350900056' and 평형 = '59' and 평형타입 = 'A' order by 입력일 desc;이렇게 바꿔줬을 경우
훨씬 인덱스 스캔 범위가 줄어든다. 만약 between 그대로 사용했다면 회색으로 칠해진 두 행 사이의 행들도 스캔 범위에 해당하게 된다. 대신, 이렇게 조건을 바꾸면 인덱스의 수직적 탐색이 두 번 발생하게 된다.
또한, 이렇게 작성한 것과도 같은 결과를 가져오게 된다.
select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
from 매물아파트매매
where 인터넷매물 = '1' and 아파트시세코드='A01011350900056' and 평형 = '59' and 평형타입 = 'A'
union all
select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
from 매물아파트매매
where 인터넷매물 = '2' and 아파트시세코드='A01011350900056' and 평형 = '59' and 평형타입 = 'A'
order by 입력일 desc;인덱스 선두 컬럼의 between 조건을 In-List 조건으로 바꿀 때 주의할 점은, In-List 개수가 많지 않아야 한다는 것이다. 필요 없는 범위를 스캔하는 비효율은 사라지겠지만 인덱스 수직 탐색이 여러 번 발생하기 때문이다. In-List 개수가 많을 때는, between 조건 떄문에 리프 블록을 추가로 스캔하는 비효율보다 In-List 조건 때문에 브랜치 블록을 반복 탐색하는 비효율이 더 클 수 있고, 인덱스 높이(height)가 높을 때 특히 그렇다. 이럴 때 Index Skip Scan이 유용할 수 있는데, 이는 인덱스 선두컬럼이 누락됐을 때 뿐만 아니라 부등호, between, like같은 범위검색 조건일 때도 사용될 수 있다.
범위조건을 두 개 이상 사용할 때의 비효율
인덱스 구성 : [ 회사 + 지역 + 상품명 ]
select *
from 가입상품
where 회사 = :com and 지역 like :reg || '%' and 상품명 like :prod || '%';이렇게 범위검색 조건을 두 개 이상 사용하면 첫 번째 인덱스 스캔 범위는 거의 결정되고, 두 번째는 필터 조건 역할만 하기 때문에 성능상 불리해질 수 있다.
만약 지역 컬럼에 대한 검색 조건이 입력되지 않을 수도 있어 LIKE 연산자를 사용한거라면 SQL을 두 개 만들어 사용하는 것이 좋다.
select * from 가입상품 where 회사 = :com and 상품명 like :prod || '%';
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을 기준으로 위의 쿼리에는 비효율이 발생하겠지만 아래의 쿼리에는 최상으로 수행될 수 있다. 만약 위의 쿼리까지 최적화하려면 [회사 + 상품명] 인덱스를 추가하는 방향으로 생각할 수 있다. 하지만 인덱스 추가가 부담스럽다면 [회사 + 상품명 + 지역] 으로 인덱스 컬럼 순서를 바꾸는 방법도 생각할 수 있겠지만 이번에는 반대로 아래의 쿼리를 처리할 때 불리해진다.
따라서 이는 상품명 조건에 입력되는 값의 선택도에 따라 결정할 사항이다.
인덱스 설계
결합 인덱스 구성을 위한 기본 공식
- 조건절에 항상 사용되거나 적어도 자주 사용되는 컬럼들을 선정한다.
- 선정된 컬럼 중 '=' 조건으로 자주 조회되는 컬럼을 앞쪽에 둔다.
- 소트 오퍼레이션을 생략하도록 하기 위해 컬럼을 추가한다.
인덱스를 이용해 소트 연산을 대체하려면, 인덱스 컬럼 구성과 같은 순서로 누락없이(뒤쪽 컬럼이 누락되는 것은 상관없음) order by절에 기술해 주어야 한다. 단, 인덱스 구성 컬럼이 조건절에서 '=' 연산자로 비교된다면, 그 컬럼은 order by절에서 누락되거나 인덱스와 다른 순서로 기술하더라도 상관없다.
또한, 인덱스 생성 여부를 생성할 때는 선택도가 충분히 낮은지가 중요한 판단 기준이 된다. 선택도가 높은 인덱스는 생성해봐야 효용가치가 별로 없다. 결합 인덱스 컬럼 간 순서를 정할 때도 개별 컬럼의 선택도가 고려사항은 될 수 있지만 어느 쪽이 유리한지는 상황에 따라 다르기 때문에 개별 컬럼의 선택도보다는 조건절에서 어떤 형태로 자주 사용되는지. 사용빈도는 어느 쪽이 높은지, 데이터를 빠르게 검색하는 데에 어느 쪽 효용성이 높은지 등이 더 중요한 판단기준이 된다.
추가적인 고려사항
- 쿼리 수행 빈도
- 업무상 중요도
- 클러스터링 팩터
- 데이터량
- DML 부하(= 기존 인덱스 개수, 초당 DML 발생량, 자주 갱신되는 컬럼 포함 여부 등)
- 저장 공간
- 인덱스 관리 비용 등
개별 쿼리 성능을 높일 뿐만 아니라 생성되는 인덱스 개수를 최소화함으로써 DML 부하를 줄이는 것이 중요한 목표여야 한다.
