인덱스 사용이 불가능한 경우
- 인덱스 변형함
가공, 함수 씌우기, 형변환 등 - 부정형
where id != '123'
null 조건 있을 때
단일 인덱스인 경우
오라클은 단일 인덱스 칼럼에 null값을 허용하지 않는다. 그래서...
where idx is null: 검색이 되지 않는다.
where idx is not null: 필터링 효과가 전혀 없다.
결합 인덱스인 경우
where idx1 is null and idx2 = sth
- 선두 칼럼을 기준으로 index range scan 가능
where idx1 is not null
- 다른 칼럼으로 INDEX FULL SCAN 하면서 해당 칼럼이 Null 아닌 거 필터링
인덱스 칼럼을 가공해야 할 때 대안
인덱스 칼럼을 가공해버리면 인덱스로써 사용이 불가능하기 때문에, 대안이 필요하다.
묵시적 형변환을 조심하자
본인이 가공을 직접 안 해도 인덱스 칼럼과 조건절에 쓰인 값의 자료형이 다르면 한 쪽을 자료형 변환해서 다른 쪽이랑 맞춰주는데, 이를 묵시적 형변환이라고 한다.
인덱스 칼럼 반대 쪽의 값이 변환되면 상관이 없는데, 인덱스 칼럼이 변환되면 인덱스를 사용할 수 없다.
묵시적 형변환은 다음 우선순위를 따른다.
- Datetime and interval datatypes
- BINARY_DOUBLE
- BINARY_FLOAT
- NUMBER
- Character datatypes
- All other built-in datatypes
인덱스 칼럼 idx의 자료형 number일 때, where idx = '123' 과 같은 조건절을 사용하면 문자열이었던 '123'이 숫자 123으로 바뀌어서 비교가 된다.
이 경우 인덱스 칼럼이 가공되지 않았으므로 인덱스를 의도대로 사용할 수가 있다.
다만 idx가 문자열이고 조건절이 where idx = 123이라면 TO_NUMBER(idx) = 123과 같은 변환이 일어나버려서 인덱스를 사용할 수가 없다.
이 경우 TABLE FULL SCAN을 할 수도 있으니, 아래와 같이 명시적 형변환을 해야 한다.
where idx = TO_CHAR(some_number)
Random 액세스 최소화
인덱스를 쓴다고 해서 무조건 성능이 좋은 것이 아니다.
쿼리를 최적화한다는 것은 결국 블록 I/O를 최소화한다는 것이고 인덱스를 쓸지 말지는 그 방법일 뿐이다.
또한 인덱스가 어떻게 구현됐느냐에 따라 같은 쿼리를 실행해도 읽는 블록의 수가 달라질 수 있다.
결론적으로 인덱스를 쓰더라도 블록의 Random Access 를 최소화해야 한다.
인덱스의 수직적, 수평적 탐색을 다 해서 리프블록의 rowid 하나를 얻었다고 하자.
rowid는 위와 같이 생겼는데, 블록 번호까지의 정보들을 조합해서 데이터 블록의 주소(Data Block Address, DBA)를 얻을 수 있다. DBA로 캐시 버퍼에 접근해서 DBA에 해당하는 블록 헤더(블록의 첫 포인터)를 얻을 수 있다.
그러나 그 과정은 상당히 복잡하기 때문에, rowid에 의한 블록 액세스를 최소화하는 전략을 취할 필요가 있다.
인덱스 손익분기점을 올리자
인덱스 손익분기점은 Index Range Scan이 Table Full Scan보다 느려지는 지점이다.
예를 들어 인덱스 손익분기점이 10%면 1000개 중 100개 이상의 레코드를 읽으면 인덱스 쓰는 것보다 전체를 보는 게 빠르다는 뜻이다. 그 말은 인덱스를 쓰면 쓸 수록 접근해야 하는 블록이 많다는 것이고, 같은 인덱스 칼럼 값을 가지는 레코드들이 여러 블록에 흩어져있다는 뜻이다.
이를 극복하기 위해선 다음 세 가지 전략이 있다.
1. 클러스터형 인덱스/IOT 사용
3. 클러스터 테이블 사용
인덱스 칼럼 추가
deptno, job으로 인덱스가 구성된 테이블에서 deptno, sal로 검색할 일이 잦으면 곤란하다. deptno로 찾아서 어찌어찌 결과를 줄여도 sal로 탐색할 블록이 많아지므로 차라리 결합 인덱스에 sal을 추가하자.
특히 테이블 액세스할 필요 없이 쿼리 결과에서 필요한 칼럼이 전부 인덱스 칼럼이라면 그 쿼리를 covered 쿼리라고 부르며 그런 인덱스를 covered 인덱스라고 한다.
오라클엔 없는 기능이지만, SQL Server에선 Include 인덱스라고 해서 인덱스로 쓰는 칼럼 말고도 자주 쓰는 칼럼을 따로 리프노드에 추가할 수 있다.
covered 쿼리와 같이 테이블에 접근 자체를 안 할 수 있다.
클러스터형 인덱스/IOT, 클러스터 테이블 사용
클러스터형 인덱스/IOT를 사용하면 인덱스/테이블 두 번 접근 안 하고 미리 정렬된 값에 바로 접근할 수 있다.
또한 클러스터 테이블은 같은 키 값을 가지는 레코드들을 같은 블록에 미리 넣어놨기 때문에 클러스터링 팩터가 좋을 수 밖에 없다.
클러스터 테이블을 구현하는 방법은 인덱스와 해시가 있는데, 해시 클러스터는 인덱스 칼럼들의 해시값이 같은 것들끼리 한 블록에 몰아넣는 것을 말한다.
인덱스의 경우엔 상관 없지만 해시의 경우 전부 '=' 조건으로 찾을 때에만 가능하다.
인덱스 바꾸기
상관관계가 높은 인덱스들(직급과 급여 등)을 결합하거나 인덱스 칼럼을 하나만 쓴다면 클러스터링 팩터가 좋아질 수 있다.
Sequential Access의 범위 최소화
선행 칼럼에 범위 조건 자제
인덱스 선행 칼럼이 조건절에 누락되거나 between, 부등호, like 같은 범위검색 조건이 사용되면 인덱스를 스캔하는 단게에서 비효율이 발생한다.
범위로 검색해야 하는 칼럼이라면 인덱스 선두로 두지 않고 최대한 맨 뒤로 두는 것이 좋으며, 만약 그랬더라면 in-list로 하는 것이 좋다.
이때 in-list의 크기는 적은 것이 좋다. 원소 개수만큼 인덱스 수직 탐색이 일어나기 때문이다.
범위 하나만 쓰기 지향
조건절에서 결합 인덱스 칼럼들에 범위검색 조건을 2개 이상 사용하면 첫 번째가 인덱스 스캔 범위를 결정하고 두 번째부터는 필터 조건 역할만 하기 때문에 성능상 불리해질 수 있다.
결합 인덱스 구성
- 조건절에 항상 또는 자주 사용되는, 그리고 그게 주로 '=' 조건인 칼럼을 선두로 둔다.
- 자주 정렬 기준이 되는 칼럼을 추가한다. order by 뒤에 인덱스의 결합 순서대로 칼럼들을 기술하면 소트 오퍼레이션을 대체할 수 있다.
- 선택도가 높은 것은 인덱스로 생성해봐야 효용가치가 별로 없다. 또한 개별 칼럼의 선택도보다는 조건절에서 어떤 형태로 자주 사용되는지, 그 빈도는 어떤지, 데이터 검색엔 어떤 게 유리한지 판단해야 한다.
이 외에도 쿼리 수행 빈도, 업무상 중요도, 클러스터링 팩터, 데이터량, DML 부하, 저장 공간, 인덱스 관리 비용 등 고려해야 할 사항이 여러가지 있다.
