인덱스 컬럼 선정 기준

July 2nd, 2022

원문

[개발자를 위한 인덱스 생성과 SQL 작성 노하우 이병국 글봄크리에이티브 - 교보문고 (kyobobook.co.kr)](http://www.kyobobook.co.kr/product/detailViewKor.laf?ejkGb=KOR&mallGb=KOR&barcode=9788996560081)

indexBook

다음과 같은 순서로 소개한다.

  • 인덱스를 잘 만들기 위한 기본 원리들에 대한 이해
  • 인덱스 생성도와 실전 인덱스
  • 인덱스의 종류와 사용 목적에 대한 이해

성능 제고를 위해 우선 고려할 것, 인덱스

인덱스는 목차나 색인처럼 빠르게 찾기 위한 용도로 알려있지만 분류 정보이기도 하다. 인덱스의 특징은 다음과 같다.

  • 인덱스는 책의 목차와 같다.
  • 인덱스는 테이블의 원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조다.
  • 인덱스는 SQL 명령문의 처리 속도를 향상시키기 위해 컬럼에 대해서 생성하는 객체다.
  • 오라클에서 인덱스 내부 구조는 B-tree 구조다.
  • 분류 대상과 분류 정보를 분리함으로써 다양한 분류가 동시에 가능하게 됐다.
  • 테이블은 분류 대상이고 인덱스는 분류 정보이다.
  • 인덱스는 논리적 분류다.
  • 분류는 물리적 분류와 논리적 분류로 나눌 수 있다.

인덱스 대상 후보컬럼 선정 기준

아래 5가지 규칙에 따라 인덱스 대상 후보 컬럼을 결정한다.

  1. 분포도가 좋은 컬럼인가?
  2. 갱신이 자주 발생하지 않는 컬럼인가?
  3. 조건절에서 자주 사용되는 컬럼인가?
  4. 조인의 연결고리에 사용되는 컬럼인가?
  5. 소트 발생을 제거하는 컬럼인가?

분포도가 좋은 컬럼인가?

분포도란 전체 레코드에서 식별 가능한 수에 대한 백분율을 의미한다. 남자 여자만 저장된다면 분포도는 50%이다. 나이 컬럼은 100개라 가정하면 분포도는 1%이다. 식별 가능한 수가 클수록 분포도가 낮고 분포가 낮을수록 분포도가 좋다고 한다. 분포도가 좋은 컬럼은 인덱스 후보 컬럼이 될 수 있다.

인덱스보다 풀스캔이 유리할 때

분포도가 좋으면 인덱스 후보 컬럼이 될 수 있다. 테이블 풀스캔시의 멀티 블록으로 운반됨을 감안해야 하고, Index Random 접근시의 Leaf Block 접근도 감안해야 한다. 인덱스 생성으로 인한 부하까지도 고려한다면, 인덱스 후보 컬럼으로 허용 가능한 기준치는 1% 이내이다. 분포도가 1% 이상이면 인덱스의 효율성이 떨어진다. 오히려 테이블 풀스캔이 더 좋은 경우도 있다.

논리적 분포도로 판단할 때의 위험

인덱스 후보컬럼 선정 기준은 분포도가 1% 이내 조건 외에도 한 가지를 더 추가할 수 있다. 컬럼의 분포도가 좋으나 업무적으로 봤을 때 조건절에 사용되지 않을 컬럼의 경우 인덱스 후보컬럼으로 선정할 수 없다. 예를들어 고객으 생년월일이 분포도가 좋다고 한다면 고객의 생년월일이 중요해지는 기능이 있는지 점검해야한다.

갱신이 자주 발생하지 않는 컬럼인가?

인덱스 컬럼이 자주 갱신(update)된다면 인덱스 밸런싱이 깨진다. 인덱스는 조회를 빨리 하기 위해 만들어졌지만 그만큼 희생해야 하는 부분도 있다. 테이블에 처리(insert, update, delete) 작업을 할 때, 인덱스에 대한 정보도 반영해야 하기 때문이다. 가급적 update 항목에서 사용하는 컬럼은 인덱스 후보컬럼에서 배제하는 것이 좋다. 수정일자, 종료일자, 취소일자 등이 있다. 이러한 update 유발 컬럼이라도 인덱스에 반드시 사용해야 하는 경우가 있다. 이러한 컬럼은 단일 인덱스에 사용하기 보다는 결합인덱스의 후행컬럼으로 주로 사용된다. 갱신이 자주 발생한다면 더 엄격한 기준으로 대해야 한다.

조건절에서 자주 사용된는 컬럼인가?

조건절에 자주사용된다는건 중요한 조건절이라는 의미도 있지만 범용적인 조건이라는 의미가 더 크다.

index04

그림과 같이 세개의 쿼리가 존재한다고 가정한다. 내용을 요약하자면 다음과 같다.

  • 쿼리 사용횟수 : 쿼리3 > 쿼리1 > 쿼리2
  • 조건절에서 자주 사용하는 컬럼 : 컬럼3 > 컬럼2 = 컬럼4 = 컬럼6 > 컬럼1 = 컬럼5 = 컬럼7

이럴경우 추천되는 인덱스는 다음과 같다.

  • 결합인덱스 = 컬럼3 + 컬럼4 + 컬럼6 + 컬럼7

제일 먼저 빈번히 사용되는 쿼리3 기준으로 인덱스를 만들고 다른 쿼리에서도 범용적으로 사용할 수 있도록 조건절에서 자주 사용하는 컬럼을 결합인덱스의 선행으로 두었다.

조인의 연결고리에 사용되는 컬럼인가?

인덱스는 조건절에서 사용되기도 하고 조인절에서 사용되기도 한다. 조건절에서 사용하는 인덱스는 최초로 접근하는 테이블을 결정하는 중요한 인덱스이며, 데이터 접근 범위를 줄여주는 역할을 한다. 조인절에서 사용하는 인덱스는 테이블간의 관계를 맺는 인덱스로서, 데이터 접근 범위를 항상 줄여주는 것은 아니다. 1:N 관계에서는 오히려 접근 범위가 커지기도 한다.

오라클 쿼리에서 조인의 방법에는 다음과 같이 3가지가 있다.

  • Nested Loop Join: 온라인 쿼리에서 90% 이상을 차지한다. 조인절에 인덱스가 반드시 있어야 한다.
  • Sort Merge Join: 거의 발견할 수 없다. 조인절에 인덱스가 반드시 있어야 하는 것은 아니다.
  • Hash Join: 배치 쿼리에서 30% 이상을 차지한다. 조인절에 인덱스가 반드시 있어야 하는 것은 아니다.

대부분의 개발자들이 접하는 방식은 Nested Loop Join 이며 이경우에는 조인절에 반드시 인덱스가 있어야한다. 인덱스 존재 여부에 따라서 3가지 조인방법이 결정되는 경욱 많다.

index05

그림처럼 주문 테이블이 고객번호 컬럼으로 조인돼 있다고 가정한다. 인덱스를 생성해야할 위치는 다음 4가지 경우에서 가장 저비용의 방법을 선택하면 된다.

  • 1번 위치에 인덱스를 생성할 경우: 주문 테이블에서 고객 테이블로 접근(조인 연결)
  • 2번 위치에 인덱스를 생성할 경우: 고객 테이블에서 주문 테이블로 점근(조인 연결)
  • 1번, 2번 위치에 모두 다 있을 경우: 오라클에서 통계 정보를 바탕으로 테이블간 방향을 결정
  • 1번, 2번 위치에 모두 다 없을 경우: Sort Merge Join 방식이나 Hash Join 방식으로 실행계획(plan)이 결정

소트 발생을 제거하는 컬럼인가?

인덱스는 기본적으로 위치정보 + 순서 정보로 구성된다. 조건절에 사용하는 컬럼만 인덱스 후보컬럼이 되는 것은 아니다. ORDER BY 절에 있는 컬럼도 인덱스 후보컬럼으로 사용 할 수 있다. 하나의 테이블에 인덱스가 많으면 많을수록 부하가 점점 증가 하듯이 결합인덱스도 컬럼 수가 많으면 많을수록 부하가 점점 증가한다.

결국 소트 발생 제거를 위해 인덱스에 해당 컬럼을 포함시킬지 말지는 비용을 따져봐야 한다. 결합인덱스에 조건절 컬럼 이외에 ORDER BY절 컬럼을 추가할 때 발생하는 인덱스 부하와 추가하지 않을 때 발생하는 SORT 부하를 비교해 결정한다. 만일 소트 부하가 더 크다고 판단되면 ORDER BY 절 컬럼도 인덱스 후보로 선정할 수 있다.

ORDER BY 절 컬럼도 인덱스 후보일 수 있다.

책의 저자는 다음과 같은 기준으로 결정한다. 위치정보 컬럼만으로 죟한 건수가 수백 건이라면 SORT 컬럼은 결합인덱스로 지정하지 않는다. 수천건 단위라면 결합인덱스에 포함시킨다. 쿼리 구동횟수가 많으면 포함시킨다.