이전 포스팅들의 예제들은 모두 정렬 연산을 생략하는 것에 초점이 맞춰져 있었다. ORDER BY 절에 사용된 컬럼의 인덱스가 있을 경우, 인덱스는 항상 정렬된 상태를 유지하기 때문에 해당 정렬 연산을 생략할 수 있다. 이를 통한 비용 절감이 이루어지는 것은 자명한 사실이다.
하지만 정렬이 없는 쿼리의 경우는 어떻게 튜닝할지 생각해봐야 한다. 인덱스를 위시한 쿼리 튜닝의 목적은 테이블 액세스를 줄이는 것에 있다. 인덱스를 효율적으로 활용하기 위해서는 인덱스의 탐색 범위 자체를 줄여야 한다. 그리고 이를 통해 테이블 액세스, 궁극적으로는 I/O를 줄여야한다. 이를 위해서 인덱스 컬럼을 올바르게 구성할 수 있어야하고, SQL 또한 이에 맞게 작성해야 한다.
이번 포스팅에서는 인덱스 탐색 범위와 테이블 액세스를 줄이는 것에 초점을 맞춰서 글을 작성해보려고 한다.
수직적 탐색, 수평적 탐색
앞서 인덱스에 대해 알아본 포스팅에서 수직적 탐색과 수평적 탐색에 대해 알아본 적이 있다.
인덱스 트리의 탐색은 수직적 탐색을 통해 리프 노드의 인덱스 스캔 시작 지점을 찾고, 해당 지점부터 수평적 탐색을 통해 조건에 맞는 인덱스를 탐색하면서 이루어진다.
더 적은 테이블 액세스를 위해서는 목표로 하는 인덱스를 더 효율적으로 탐색할 수 있도록 탐색 범위를 줄여나가야 한다. 가령 3개의 인덱스를 찾기 위해서 3개의 인덱스만을 탐색했다면, 이는 최상의 시나리오가 되겠다. 이를 위해서는 작성한 SQL에 따라 인덱스를 어떻게 탐색하게 되는지 이해할 필요가 있다.
액세스 조건, 필터 조건
실행할 SQL의 조건절에 따라서 인덱스에 접근하는 방식, 탐색한 인덱스를 통해서 테이블에 접근할지 여부, 테이블에서 얻은 결과의 활용 여부를 따지게 된다.
친절한 SQL 튜닝에서는 인덱스 스캔 시에 SQL의 조건절이 적용되는 방식을 아래와 같이 분류하고 있다.
인덱스 액세스 조건 : 인덱스 스캔 범위를 결정하는 조건
인덱스 필터 조건 : 테이블에 액세스할지 결정하는 조건
테이블 필터 조건 : 테이블에서 얻은 결과 집합을 다음 단계나 최종 결과 집합에 포함할지 결정하는 조건
이 포스팅에서 생성했던 스키마를 활용해서 위 조건들을 이해해보겠다.
USER 테이블에서 성별 + 닉네임 의 복합 인덱스가 존재한다고 하면, 다음 쿼리는 어떤 식으로 인덱스에 접근하게 될까?
SELECT * FROM USER
WHERE gender = 'MAN'
AND nickname BETWEEN 'nickname100' and 'nickname200'
AND age >= 20;인덱스 액세스 조건
USER 테이블에 생성된 성별+닉네임 인덱스는 다음과 같이 정렬될 것이다.
| 성별 | 닉네임 |
|---|---|
| MAN | nickname003 |
| MAN | nickname019 |
| MAN | nickname022 |
| MAN | nickname024 |
| WOMAN | nickname005 |
| WOMAN | nickname016 |
| WOMAN | nickname017 |
| WOMAN | nickname029 |
수직적 탐색
결합 인덱스이기 때문에 수직적 탐색 과정에서 성별 컬럼이 MAN이면서 닉네임 컬럼이 nickname100과 nickname200사이인 구간을 탐색할 것이다. 헷갈리면 안되는 것은 복합 인덱스의 경우 수직적 탐색 과정에서 하위 레벨의 노드를 찾아가기 위해 거쳐가는 범위 조건
필터링 과정에서 인덱스 컬럼 값이 모두 사용된다는 것이다. 성별만으로 하위 노드에 대한 키 값을 얻거나, 닉네임만으로 하위 노드에 대한 키 값을 얻을 수 없다는 얘기다.
결과적으로 성별 컬럼이 MAN이면서 닉네임 컬럼이 nickname100인 인덱스가 포함된 인덱스 리프 블록(MySQL의 경우 페이지)이 수평적 탐색의 시작지점이 된다.
수평적 탐색
수평적 탐색은 수직적 탐색을 통해 찾은 시작 지점부터 시작된다. 성별 컬럼이 MAN이고, 닉네임 컬럼이nickname100인 인덱스가 포함되어있는 인덱스 리프 블록부터 탐색을 시작하여 성별 컬럼이 MAN이고 닉네임 컬럼이 nickname200인 인덱스가 포함되어있는 블록까지 탐색을 한다.
정리
정리를 하면, 위 쿼리 문에서 인덱스 액세스 조건은 WEHRE 절에서 gender = 'MAN'과 nickname BETWEEN 'nickname100' and 'nickname200'이 되는 것을 알 수 있다.
인덱스 필터 조건
인덱스 필터 조건은, 액세스한 인덱스 중에서 테이블에 접근할지 말지를 결정하는 조건이다. 이번 예제에서는 nickname100부터nickname200까지가 인덱스 필터 조건이 되겠다.
테이블 필터 조건
테이블 필터 조건은 인덱스 액세스 조건 + 인덱스 필터 조건에 포함되지 않은 조건으로, 테이블에서 탐색한 레코드를 최종 결과 집합에 포함할지에 대해 결정하게 된다. 이번 예제에서는 age>=20이 조건으로 테이블 필터 조건이 되겠다.
정리
이전 포스팅에서 알아보았듯이, 인덱스는 구성된 컬럼의 순서대로 정렬이 되어있는 구조다. 만약 선행 컬럼의 조건절이 모두=로 이루어져 있을 경우, 해당 인덱스들은 모두 액세스 조건에 해당된다. 또한 후속 인덱스 컬럼에 대한 조건이 등호 이외의 조건일 경우, 해당 인덱스까지가 인덱스 액세스 조건에 포함되게 된다.
가령 TEST 테이블에 a, b, c, d, e 컬럼이 있고, a, b,c의 순서대로 복합 인덱스가 생성되어있다고 하자. a, b에 대한 조건이 등호이고, c 에 대한 조건이 BETWEEN, IN, LIKE, 부등호 등일 경우 a, b, c는 모두 액세스 조건에 해당되고 c의 조건으로 인해 INDEX RANGE SCAN이 발생할 확률이 높다. 또한 c는 인덱스 필터 조건이 된다. d와 e에 사용된 조건절은 모두 테이블 필터 조건이 된다.
정리하면, 복합 인덱스가 있는 테이블에서 SQL의 조건절의 내용에 따라서 인덱스 액세스 조건이 바뀌게 된다. 이 때 선행 인덱스 컬럼의 조건절이 모두 등호일 경우, 등호가 아닌 후속 인덱스 컬럼의 조건에 따라서 인덱스 리프 노드의 탐색 범위가 결정되며 이는 테이블 액세스의 수를 결정하게 된다.
마무리
개인적으로 난이도가 있는 내용이고, 이 분야를 다루는 상황에 따라 튜닝 또한 천차만별이다. 그래서 일단은 탐색 범위를 줄이기 위한 개념만 정리하는 방식으로 포스팅을 하게 됐다. 좋은 예제나 관련된 실제 상황이 발생하면 해당 내용을 토대로 본 포스팅을 전면 수정해볼 계획이다.
다음은 좋은 인덱스를 설계하는 법에 대해서 다뤄보고자 한다.
참고
