개발자를 위한 인덱스 생성과 SQL작성 노하우 : 인덱스

 

1. 인덱스는 분류이다.

 

분류의 특징

• 분류의 역사는 인류의 역사와 궤를 같아한다.
• 분류는 인간 세계뿐만 아니라 동물의 세계에서도 존재한다.
• 수많은 자료를 정리,관리,검색한다는 의미에서 분류는 DB 그 자체이다.
• 과거의 계층적인 분류부터 최근의 컴퓨터정보 분류에 이르기까지 분류는 지속적으로 발전했다.
• 체계적으로 정리된 자료는 검색 시간을 줄여 비용 절감으로 연결괸다. 이것이 분류의 힘이다.

 

물리적 분류의 특징

• 직접적이고 시각적인 분류 방식
• 인류 역사와 함께 널리 사용됨
• 현실 세계의 분류에서 적합
• 분류 대상과 분류 정보 일체형
• 동일한 시공간에서 오직 한 분류만 가능
• 분류 대상과 분류 정보는 1:1 관계

 

물리적 분류 예

논리적 분류의 특징

• 간접적이고 추상적인 분류 방식
• 컴퓨터 사용과 더불어 폭넓게 사용
• 디지털 세상의 분류에 적합
• 분류 대상과 분류 정보 분리
• 동일한 시공간에서 동시에 다양한 분류 가능
• 분류 대상과 분류 정보는 1:N 관계

 

논리적 분류 예

 

DB에서 인덱스는 논리적 분류의 특성을 갖고 있다.

 

논리적 분류는 '분류 대상과 분류 정보의 분리(종속적)'의 특징을 갖는다. 여기서 분류 대상은 테이블을 의미하고, 분류 정보는 인덱스를 의미한다. DB에서 인덱스(분류 정보)는 테이블(분류 대상)의 위치 정보를 갖고 있으므로, 테이블을 삭제하면 인덱스는 자동으로 삭제된다. 따라서 테이블과 인덱스는 종속적 관계를 갖는다.

 

또한 논리적 분류에는 '동일한 시공간에 동시에 다양한 분류 가능'의 특징이 존재한다. 이것은 하나의 테이블에 여러 개의 인덱스가 종속적으로 존재한다는 것을 의미한다.

 

분류 대상과 분류 정보를 분리하지 않으면 여러 문제가 발생할 수 있다.

• 저장 공간을 분류의 특성에 맞게 미리 할당해야 한다.
• 상시적으로 공간 이동을 해야 하고, 분류를 유지하는 데 많은 비용이 든다.
• 오직 하나의 분류만 가능하다.

 

인덱스

• 인덱스는 책의 목차와 같다.
• 인덱스는 테이블의 원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조다.
• 인덱스는 SQL 명령문의 처리 속도를 높이기 위해 컬럼에 대해 생성하는 객체다.
• 오라클의 인덱스 내부 구조는 B-tree 구조다.
• 인덱스는 논리적 분류이다.

 

2. 결합인덱스의 컬럼 순서 결정 방법

 

1. 항상 WHERE 조건에 사용되는 컬럼인가?
2. 항상 '='로 사용되는가?
3. 분포도가 좋은 컬럼인가?
4. SORT에 사용되는 컬럼인가?

 

첫째, 항상 WHERE 조건에 사용되는 컬럼인가?

결합인덱스의 첫 번째 컬럼을 조건에서 사용하지 않는다면, 그 인덱스는 사용되지 않는 경우가 대부분이다. 따라서 많은 쿼리에서 공통적으로 사용되는 조건절의 컬럼을 인덱스의 선행컬럼에 주로 사용한다. 다수의 쿼리에서 공통적으로 사용된다는 것은 필수 조건절이라는 의미이다. 필수 조건절은 결합인덱스의 선행컬럼으로 사용돼야 한다.

 

둘째, 항상 '='로 사용되는가?

결합인덱스 = 컬럼1 + 컬럼2 + 컬럼3 + 컬럼4 + 컬럼5

 

결합인덱스에서 선행컬럼이 '='조건이 아니라면 후행컬럼이 조건에서 '='을 사용하더라도 처리범위가 줄어들지 않는다. 조건절에서 '='이 아닌 연산자를 사용하는 첫 번째 컬럼까지만 인덱스를 타고, 그 다음 후행컬럼부터는 인덱스를 타지 않고 필터만 한다.

WHERE 컬럼1 = ?
AND       컬럼2 = ?
AND       컬럼3 BETWEEN ? AND ? -- 결합인덱스에서 '='이 아닌 연산자를 사용하는 첫 번째 컬럼
AND       컬럼4 = ?
AND       컬럼5 LIKE ?

 

따라서 구간 조건으로 많이 사용되는 컬럼은 후행컬럼으로 주로 사용된다.

결합인덱스 = 컬럼1 + 컬럼2 + 컬럼4 + 컬럼3 + 컬럼5

 

셋째, 분포도가 좋은 컬럼인가?

'분포도가 좋은 컬럼은 처리 범위를 줄여주므로 결합인덱스의 선행컬럼으로 사용해야 한다' => X

그 이유는, 결합인덱스는 여러 컬럼을 합쳐서 처리 범위를 줄여주는 인덱스인데, 이미 첫 번째 선행컬럼이 분포도가 좋아서 처리 범위가 많이 줄어들었으므로 굳이 결합인덱스로 만들지 않고 단일인덱스로 사용해도 된다.

 

결합인덱스에서 선행컬럼의 순서는 분포도로 접근하기보다는, 분류의 개념으로 접근하는 것이 좋은 결과를 가져올 수 있다.

 

결합인덱스 : 선행컬럼(분포도 나쁨) + ··· + 중간컬럼(분포도 보통) + ··· + 후행컬럼(분포도 좋음)        

                                       ↓

결합인덱스 : 선행컬럼(대분류) + ··· + 중간컬럼(중분류) + ··· + 후행컬럼(소분류)

 

넷째, 소트에 사용되는 컬럼인가?

소트에 사용됐다고 결합인덱스의 선행컬럼으로 반드시 사용돼야 하는 것은 아니다. 결합인덱스는 결합된 컬럼의 순서로 정렬,저장되므로 소트에 사용되는 컬럼은 결합인덱스의 일부분일 수는 있으나, 선행컬럼의 조건일 수는 없다. 오히려 소트에 사용되는 컬럼은 결합인덱스 후행컬럼에 자주 사용된다.

인덱스는 기본적으로 위치(조건)정보와 순서(정렬) 정보의 특성을 동시에 갖고 있고, 따라서 결합인덱스에는 위치정보를 갖는 컬럼과 순서 정보를 갖는 컬럼들이 존재할 수 있다. 결합인덱스는 아래와 같은 규칙으로 구성돼야 하고, 결론적으로 결합인덱스의 컬럼 순서에서 순서정보 컬럼은 위치정보 컬럼의 후행에 있어야 한다.

 

1. 위치정보 컬럼만으로 구성할 수 있다. 혹은 순서정보 컬럼만으로 구성할 수 있다.
2. 위치정보 컬럼과 순서정보 컬럼의 순으로 구성할 수 있다.
3. 위치정보 컬럼과 순서정보 컬럼의 순서는 혼재돼서는 안되며 뒤바뀌어도 안된다.

 

추천하는 결합인덱스의 컬럼 순서

1. 공통적으로 사용하는 필수 조건절 컬럼을 우선한다.
2. '=' 조건의 컬럼을 다른 연산자 컬럼보다 우선한다.
3. 대분류 -> 중분류 -> 소분류 컬럼순으로 구성한다.
4. 위치(조건) 정보 컬럼은 순서(SORT) 정보 컬럼보다 우선한다.

 

3. 인덱스 컬럼 선정 기준

 

인덱스는 테이블에서 찾고자 하는 레코드를 빨리 찾아가는 색인 또는 목차의 개념이기도 하고, 분류 대상(테이블)에 대한 논리적인 분류 정보(인덱스)이기도 하다. 테이블에 존재하는 컬럼들을 모두 인덱스 컬럼으로 사용할 수 있는 것은 아니다. 5가지 규칙에 따라서 인덱스 대상 후보 컬럼을 결정해서 사용할 수 있다.

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

 

분포도가 좋은 컬럼인가?

분포도란 전체 레코드에서 식별 가능한 수에 대한 백분율을 의미한다. 식별 가능한 수가 클수록 분포도는 낮으며, 분포도가 낮을수록 분포도가 좋다고 한다. 분포도가 좋은 컬럼은 인덱스 후보 컬럼이 될 수 있다.

 

분포도(%) = (1 / 식별 가능한 수) x 100

 

좋은 분포도의 기준은 보통 전체 레코드의 10% 이내 또는 3% 이내를 의미하지만, 기본 원리로만 이해하고 무조건 적용하지 않아야 한다. 또한, 논리적으로 분포도를 판단했을 때 좋지 않은 것이 물리적으로 판단했을 때 분포도가 좋은 경우가 존재하기 때문에, 분포도는 논리적으로 판단하기보다는 물리적으로 판단하는 것이 좋다. 그리고 특정 데이터를 찾기 위해서 1000만 번의 풀스캔을 해야할 경우가 있을 때는 약간의 부하가 있더라도 인덱스를 만들어 놓으면, 한 번의 랜덤 엑세스로 원하는 정보를 얻을 수 있다.

 

단일인덱스에서는 컬럼의 분포도가 중요한 요소이지만, 결합인덱스에서는 컬럼들의 순서가 중요한 요소다. 하지만 결합인덱스도 각각의 컬럼은 분포도가 나쁠지라도 전체 컬럼의 분포도는 좋아야 한다.

 

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

인덱스 컬럼이 자주 갱신(update)되면, 인덱스 밸런싱이 깨진다. 테이블에 처리(insert, update, delete) 작업을 할 때, 인덱스에 대한 정보도 반영해야 하기 때문에 가급적 update 항목에서 사용되는 컬럼은 인덱스 후보컬럼에서 배제하는 것이 좋다. 만약 update를 유발하는 컬럼이라도 인덱스에 반드시 사용해야 하는 경우가 있을 때는 단일인덱스로 사용되기보다는 결합인덱스의 후행컬럼으로 주로 사용된다.

 

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

조건절에서 자주 사용한다는 의미는 중요한 조건절이라는 의미도 있지만, 범용적인 조건절이라는 의미가 더 크다. 테이블에 종속적인 인덱스가 많으면 많을수록 데이터 처리 시 부하도 높을 것이다. 따라서 필요한 만큼의 인덱스만 만들도록 해야한다. 

한 테이블에 3개의 쿼리가 존재하는 경우

 

쿼리 일일 구동 횟수 : 쿼리3(1000번) > 쿼리1(100번) > 쿼리2(10번)

조건절에서 자주 사용 : 컬럼3(3회) > 컬럼2(2회) = 컬럼4 = 컬럼6 > 컬럼1(1회) = 컬럼5 = 컬럼7

 

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

 

가장 빈번히 구동되는 쿼리를 기준으로 인덱스를 만든다. 그리고 다른 쿼리도 범용적으로 사용할 수 있도록 조건절에서 자주 사용하는 컬럼을 결합인덱스의 선행으로 둔다.

 

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

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

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

 

1. 1번 위치에 인덱스를 생성할 경우 : 주문 테이블에서 고객 테이블로 접근(조인 연결)

2. 2번 위치에 인덱스를 생성할 경우 : 고객 테이블에서 주문 테이블로 접근(조인 연결)

3. 1번, 2번 위치에 모두 다 있을 경우 : 오라클에서 통계 정보를 바탕으로 테이블간 방향을 결정

4. 1번, 2번 위치에 모두 다 없을 경우 : Sort Merge Join 방식이나 Hash Join 방식으로 실행계획 결정

 

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

인덱스는 기본적으로 위치정보 + 순서정보로 구성된다. 조건절에서 사용하는 컬럼 외에 ORDER BY 절에 있는 컬럼도 인덱스 후보컬럼으로 사용할 수 있다. 결합인덱스에 조건절 컬럼 이외에 ORDER BY절 컬럼을 추가할 때 발생하는 인덱스 부하와, 추가하지 않을 떄 발생하는 SORT 부하를 비교해서 결정한다. 만약 소트 부하가 더 크다고 판단되면 ORDER BY 절 컬럼도 인덱스 후보로 선정할 수 있다. 

조건에 따른 결과 데이터를 가져오는 데에 필요한 부하보다는, 가져온 데이터를 소트하는 데 필요한 부하가 수십 배 이상인 경우가 흔하게 존재한다. 가져온 데이터의 건수가 많으면 많을수록 부하는 급격하게 증가하기 때문에 인덱스를 생성할 때 소트 발생을 제거하는 컬럼인지 여부가 중요하다.