[RDBMS] 인덱스 생성전략

[RDBMS] 인덱스 생성전략

회사에서 로그 서버를 개발하면서, 아직은 경험하지는 못했지만 다른 제품의 로그 서버를 개발하시는 분들이 성능과 관련된 장애로 고생하시는 모습을 많이 보았다. 이 때문에 추후에 나도 대용량으로 쌓이는 로그라는 데이터의 특성에 맞게 인덱싱을 잘해야겠다고 생각을 해서 인덱스 관련 공부를 해보았다.

오늘 정리할 내용을 바탕으로 회사 제품에 적용해보고, 추후에 데이터가 많이 쌓였을 때 실제로 성능 비교를 해보고 포스팅 해볼 예정이다.

 

인덱스 선정 기준

• 모든 액세스 형태와 분석을 토대로 이상적인 컬럼 구성과 순서 결정을 통해 최소의 인덱스로 모든 액세스 형태를 만족할 수 있도록 해야한다.
• 가능한 실측자료(액세스 형태 수집, 분석, 엑세스의 빈도, 처리범위의 크기, 분포도, 테이블의 크기, 액세스 유형 등)를 활용하여 종합적으로 전략적인 결정을 해야한다.
• 여기서 분포도란 쉽게 말해 사용 빈도로 이해를 하면 될 것 같다. 즉, 분포도가 좋다는 의미는 한 컬럼에 해당하는 값이 희소성이 높다는 뜻이고 분포도가 나쁘다는 의미는 그 반대다.

 

저장용 대형 테이블의 적용기준

• 로그성 데이터를 관리할 목적으로 생성된 테이블을 저장용 대형 테이블이라고 한다.
• 이러한 테이블의 특징으로는 대량의 데이터가 지속적으로 입력이 된다는 것이다.
• 따라서 테이블에서 기본키(PK)를 가지는 것은 입력시 부담이 될 수 있으므로 이를 사용하지 않는 것도 고려해야 한다. 대신 UNIQUE INDEX 생성을 고려해야 한다.
• 또한, 테이블에 파티션을 만들고 파티션마다 필요한 인덱스(Local Index)를 생성하는 것이 좋다.
• 파티션 인덱스 사용시, 사용 파티션의 인덱스만 사용하는 것도 고려해야 한다 => 즉, 전체 Local Index를 UNUSABLE 상태로 만든 후, 이후 필요한 파티션의 인덱스만 생성하는 방식

 

Composite Index (결합 인덱스)

• 여러 컬럼을 모아 하나의 인덱스로 만드는 방식. 주로 SQL 문장에서 WHERE 절의 조건 컬럼이 2개 이상의 AND 로 연결되어 함께 사용되는 경우에 많이 사용된다.
• 단, OR로 조회되는 경우는 결합 인덱스를 만들면 안된다.
• 특징
  • 인덱스의 첫 번째 컬럼이 조건절에 없다면 일반적으로 인덱스가 사용되지 않는다.
  • Equal 연산이 아닌 검색 조건이 들어오는 경우(범위 연산), 처리 범위가 크게 증가하여 효율이 크게 저하될 수 있다.

 

결합 인덱스를 구성하는 컬럼의 순서

성능을 향상시키기 위해서는 결합 인덱스를 구성하는 컬럼은 반드시 다음의 순서에 맞도록 생성해야 한다.

• 1순위 : 컬럼이 사용한 연산자에 의한 인덱스 컬럼 선정
• 2순위 : 랜덤 액세스를 고려한 인덱스 컬럼 선정
• 3순위 : 정렬 제거를 위한 인덱스 컬럼 선정
• 4순위 : 단일 컬럼의 분포도를 고려한 인덱스 컬럼 선정

 

위 우선 순위를 지키지 않는다면 애당초 해당 인덱스를 이용한 성능 향상은 기대하기 조차 힘들어진다.

 

예시1

SELECT 카드번호, 사용금액
  FROM 거래내역
 WHERE 카드번호 = '111'
   AND 거래일자 BETWEEN '20080501' AND '20080510';

• 카드번호 컬럼의 분포도는 매우 좋으며 거래일자 컬럼의 분포도는 매우 안좋다고 가정 후, (카드번호, 거래일자) 순으로 결합 인덱스를 구성하였을 경우,
• 위와 같은 쿼리문에서 카드번호 컬럼의 값에 의해 처리 범위는 감소하게 되며 또한 거래일자 컬럼에 의해서라도 처리 범위가 감소하게 되므로 원하는 데이터에 대해 최소의 액세스로 결과를 추출할 수 있게 된다.

 

예시2

SELECT 카드번호, 사용금액
  FROM 거래내역
 WHERE 카드번호 BETWEEN '111' AND '555'
   AND 거래일자 = '20080515';

• 컬럼의 분포도만을 생각하여 카드번호 + 거래일자 인덱스를 생성한다면 카드번호 컬럼에 의해서만 처리 범위가 감소하게 되어 성능 저하가 발생한다.
• 그 이유는 111 카드번호부터 555 카드번호까지 2008년 5월 15일 데이터만을 액세스하는 것이 아니라 '111' 카드번호부터 '555' 카드 번호까지 모든 데이터를 액세스하기 때문이다. => 즉, 거래일자 컬럼은 처리 밤위를 감소키지 못한다.
• 따라서 결합 인덱스를 구성할 경우, 다음과 같은 조건에 따라 결합 인덱스를 구성해야 한다.
  • 점 조건(=, IN) + 점 조건(=, IN) : 두 조건에 의해 처리 범위 감소
  • 점 조건(=, IN) + 선분 조건(LIKE, BETWEEN) : 두 조건에 의해 처리 범위 감소
  • 선분 조건(LIKE, BETWEEN) + 선분 조건(LIKE, BETWEEN) : 앞의 선분 조건에 의해 처리 범위 감소
  • 선분 조건(LIKE, BETWEEN) + 점 조건(=, IN) : 앞의 선분 조건에 의해서만 처리 범위 감소

 

따라서 위와 같이 연산자에 의해 처리 범위가 결정되며 최소의 처리 범위를 보장받기 위해서는 점 조건 앞에 선분 조건이 존재하면 안된다.

---

Reference

• 인덱스 전략 : http://wiki.gurubee.net/pages/viewpage.action?pageId=12189750
• PCTFREE : https://12bme.tistory.com/304
• 결합 인덱스 : http://www.gurubee.net/lecture/2229