Index를 잘 사용하는 방법 - 중급(TIL)
1. 인덱스 스캔량과 필터 조건
이전 글에서 쿼리 실행 계획과 관련된 설명을 하였다. 그 글에서는 인덱스를 사용한 실행 계획을 세울 때, 인덱스 범위 스캔처럼 인덱스를 통해 스캔하는 시작점과 끝점의 범위를 줄이는 게 중요함을 이야기했었다. 그래야 찾아 가는 블록의 수가 적어지며 랜덤 엑세스 횟수가 줄어들기 때문이다. 그런데, 인덱스를 설계하는 단계에서 이것이 가능하지만 이미 만들어진 인덱스를 확장하는 단계에서 이는 불가능하다.
책에 나온 예시는 아래와 같았는데, 만약에 인덱스를 dept_id와 job으로 만들었다고 해보자. 그러면 아래의 쿼리 문에서 탐색하는 인원은 둘리부터 마이콜까지 총 4명이다. salary 조건을 인덱스 자체로는 거를 수 없기 때문에, 해당 레코드의 salary 값을 얻기 위해 블록에 무조건 접근해야 한다.
위 문제의 가장 좋은 해결방법은 쿼리 문에 맞게 dept_id와 salary 조합으로 인덱스를 만들어서 사용하는 것인이다. 쿼리가 늘어남에 따라 개별 쿼리에 맞는 인덱스를 무작정 늘리면 메모리와 성능이 저하된다. 따라서, 기존 인덱스에 컬럼을 늘려주는 방법이 가장 좋은 방법이다.
기존에 (dept_id, job)인 인덱스를 (dept_id, job, salary)로 바꿔주면 중간에 job이 조건이 없어 인덱스 스캔 범위는 바뀌지 않는다. 하지만, 인덱스 내에 저장된 salary 정보와 쿼리 내 salary 정보를 통해 블록에 접근할 지 말지는 고를 수 있다. 이런 경우, 랜덤 엑세스 횟수가 줄어듦으로서 성능이 개선된다.
이런 방법을 더 적극적으로 활용해 인덱스를 하나의 테이블 축소판으로 보고 데이터를 가져오는 방법도 있다. 이는 select 문 내의 모든 컬럼이 인덱스에 추가된 경우에 가능하다. 이 때, 인덱스만 읽어 처리하는 쿼리를 Covered 쿼리, 그 쿼리에 사용된 인덱스를 Covered 인덱스라고 한다.
2. 부분 범위 처리와 배치 처리
데이터가 수 억건이 된다고 해보자. 이 때 아무런 조건없이 수 억건을 한 번에 퍼올리는 상황이 배치처리에서는 필요할 수 있다. 하지만 일반적인 서비스에서 사용자들은 그런 정보를 다 확인하지 않는다. 당장 블로그 글 목차만 보더라도 내가 설정해놓은 15개의 글만 페이지별로 확인할 수 있다.
이 내용을 착안해 만드는 방법이 '부분범위 처리'이다. 사용자가 추가적인 요청을 하기 전까지 미리 데이터 일부만 퍼놓고 더이상 읽지 않고 대기하는 방법이다. 그래서 초기 데이터부터 추가 요청이 있을 때만 설정해놓은 값만큼 퍼올린다. 그리고 해당 처리를 담당하는 프로세스는 다음 요청까지 sleep 상태로 들어간다.
인덱스가 있는 경우, 인덱스로 정렬된 정보를 활용해 sort by를 생략할 수 있다. 특히나 풀테이블 스캔으로 정렬을 하려면 일부 데이터만 퍼올려서 보여주는 게 불가능하다. 왜냐하면 일부 데이터가 전체 데이터를 기준으로 조건에 맞게 정렬되어 있는게 보장되지 않기 때문이다. 그래서 일부 데이터만 퍼올리는 경우, 인덱스가 있으면 더 효율적으로 가져올 수 있다. 또한 이 구현은 쿼리 툴 개발자의 마음이라고 한다.
그런데, 이 내용을 보면서 이해가 안되는 부분이 몇 가지 있었다. 이런 기능을 OLTP 환경에서 사용한다고 하는데, 그럼 스프링에서는 저렇게 동작하면 커넥션 풀을 계속 물고 있는거 아닌가라는 생각이 들었다. 가뜩이나 커넥션 풀 마르지 말라고 트랜잭션 내부에 시간이 오래 걸리거나 외부 시스템이랑 연결해야 되는 경우라면, 커넥션 풀 반납하고 사용하라는 등 뭔가 추가적인 게 붙는데 저건 더 심각한 것 같았다. 애초에 HTTP 요청 쓰레드가 고정된 것도 아닌데, 불가능한 게 아닌가라는 물음이 있었다.
그 다음 물음은 JPA에서는 부분 처리랑 비슷한 개념으로 페이징과 슬라이싱을 사용하는데, 그러면, 페이징과 슬라이싱을 할 때 찾는 위치에 따라 블록을 읽는 개수가 다르지 않을까 하는 물음이 생겼다.
다행히 "친절한 SQL 튜닝" 책의 뒤편에 이 내용을 다루는 부분이 있었다. 결과적으로는 지속적으로 커넥션을 물고 요청마다 반복하는 방식은 아니다. 대신 이 개념을 이용해, TopN 혹은 페이징에서 정렬 조건과 일치하는 경우에는, 부분처리와 비슷한 효과를 낼 수 있게 처리를 돕는다.
하지만, 이 때 주의할 점은 시작점과 끝점을 between 절로만 처리하지 않으면 된다. between절로 페이징을 해버리면 SQL을 짜는 사람 입장에서는 보기 좋지만, DBMS 입장에서는 특정 범위까지만 스캔하고 멈추는 ROWNUM이라는 조건절이 없어져 전체 테이블을 스캔해야겠구나로 인식한다.
추가적으로 데이터를 조회할 때에, 인덱스 구성으로 정렬을 보장할 수 없는 경우에는 위의 알고리즘이 정상 작동하지 않는다. 이럴 때는 인덱스 칼럼을 추가하거나 의미없는 정렬 조건을 빼주는 방법을 고려해보는 것이 좋다.
반대로 배치 I/O의 경우는, 부분처리 범위가 아니라 모든 데이터가 필요한 경우 사용된다. 이 때 I/O 성능을 높이기 위해서 최대한 디스크 I/O 내용이 일정량이상 쌓일 때까지 스캔한다. 그리고 넘어가면 디스크에서 한 번에 쌓인 데이터를 한꺼번에 가져오는데, 이 때 특징은 인덱스를 사용하더라도 데이터 정렬 순서가 매번 다르다. 따라서 이 방법은 정렬이 필요없는 경우, 랜덤 엑세스를 줄여 성능을 높이는 방법 중 하나이다.
단, 힌트를 통해서 유도하는 게 아니라면, Oracle의 경우에는 버전마다 작동 방식이 다르다. 또한, 이 배치 I/O의 특징은 인덱스를 사용하더라도 순서가 항상 보장되지 않을 수 있는 이유에 관해서 설명해준다. 따라서, 인덱스를 타더라도 꼭 순서 보장이 안될 수 있으므로 Order by 구를 생략하는 습관이 없어야 한다.
3. Between과 IN-List 방식
Between 절로 처리를 할 때에 범위가 한정되어 있는 경우, IN-LIST로 바꾸어보는 것을 고려할 수 있다. 특히 인덱스를 바꿀 수 없는 경우에 해볼 법한 시도이다. 이 때 IN-LIST 방식은 IN절에 있는 내부 요소를 범위 스캔해서 필터 조건으로 사용하는 것이 아닌 엑세스 조건으로 사용하는 방법이다. 그러면 마치 인덱스 스킵 스캔 방식처럼 동작한다. 아래는 BETWEEN에서 IN-LIST로 바뀌었을 때 인덱스를 이용해서 테이블을 스캔하는 것을 비교하는 방식을 이미지화했다.
이미지를 보면 알 수 있는 내용은 두 가지이다. 우선 BETWEEN을 IN-LIST로 변경하는 경우, IN절에 들어가는 원소의 개수가 매우 적어야지 효율적이다. 왜냐하면 수평적 탐색은 줄어들 수 있겠으나 수직적 탐색의 회수가 증가하기 때문이다. 또한 인덱스 정보를 기준으로 둘 사이의 정보가 매우 멀어야지 효율적이다. 이 또한 내부적인 데이터 정보에 관해 고민을 많이 해보고 서브 쿼리를 통한 조인으로 풀 지를 고민해봐야 한다.
앞에서 인덱스 엑세스 조건에 관해 이야기를 했는데, 위의 IN-LIST 방식은 마치 SQL문을 짤 때, IN절 내부를 '='로 바꾸고 UNIONALL로 합친 것과 동일하다. 하지만 인덱스에서 해당 BETWEEN절로 들어간게 선두 컬럼이면 이상적으로 동작하지만 선두 컬럼에서 벗어난 경우에는 필터 조건으로 사용된다. 직접 메모리까지 가서 데이터를 확인하는 게 아닌 인덱스에 있는 해당 정보로 순회하며 대상인지 아닌지만을 확인한다는 것이다. 이 때, 해당 BETWEEN 절로 데이터가 모여있어 선두 컬럼과 시작점을 정확하게 찾을 수 있는 경우에는 안 쓰는게 나을 수도 있다.
위의 정보를 통해 IN-LIST로 변경할 지를 고려하고 옵티마이저가 자동으로 바꾸는 경우를 대비해 적절한 힌트를 주는 걸 고려해보아야 한다.
[참고 자료]
친절한 SQL 튜닝 - 조시형
SQL 레벨업 - 미크