인덱스의 물리적 분류와 논리적 분류
분류는 논리적 분류와 물리적 분류로 나눌 수 있다. 물리적 분류는 다른 말로 시각적 분류이기도 한데, 분류 대상과 1:1 관계에 있고 일체형이다. 음반을 분류한다고 했을 때, 가수별*연도별 분류, 테마별*가수별 분류 등 여러가지 분류들이 있을 수 있는데 동일한 시공간에서는 오로지 하나의 물리적 분류만 가능하다. 따라서 여러가지의 분류로 나눌 수 있도록 하기 위해서는 분류 대상(음반)과 분류 정보(색인)를 분리하는 것이다. 이것이 논리적 분류로 디지털 세계에서 대량의 복잡한 자료를 다양하게 분류하는 데 적합하다.
분류 대상과 분류 정보를 분리함으로써, 그동안 할 수 없었던 다양한 분류가 동시에 가능해졌다. DB에서 인덱스는 논리적 분류의 특성을 가지고 있다. DB에서 테이블은 분류 대상과 동일한 의미이고, 인덱스는 분류 정보와 동일한 의미이다.
흔하게 인덱스는 다음과 같은 특징을 갖는다고 말할 수 있다.
1. 인덱스는 책의 목차와 같다.
2. 인덱스는 테이블의 원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조이다.
3. 인덱스는 SQL 명령문의 처리 속도를 높이기 위해 컬럼에 대해 생성하는 객체이다.
4. 오라클의 인덱스 내부 구조는 B-Tree 구조이다.
개발자는 인덱스를 어떻게 잘 만들 것인지가 중요하므로 위처럼 B-Tree구조인지, 다른 구조인지는 중요하지 않다.
인덱스에 대한 오해와 진실
1. 항상 WHERE 조건에 사용되는 컬럼인가 ?
결합인덱스의 첫 번째 컬럼을 조건에서 사용하지 않는다면, 그 인덱스는 사용되지 않는 경우가 대부분이다. 따라서 많은 쿼리에서 공통적으로 사용하는 조건절의 컬럼을 인덱스의 선행컬럼에 주로 사용한다. 다수의 쿼리에서 공통적으로 사용되는 것은 필수 조건절이라는 의미와 동일하다.
2. 항상 '='로 사용되는가 ? (회의 필요)
결합인덱스에서 선행컬럼이 '=' 조건이 아니라면 후행컬럼 조건에서 '='을 사용하더라도 처리 범위는 줄어들지 않는다. 조건절에서 '='이 아닌 연산자를 사용하는 첫 번째 컬럼까지만 인덱스를 타고, 그 다음 후행컬럼부터는 인덱스를 타지 않고 필터만 한다. 다른 말로는 체크한다고도 한다.
결합인덱스 = 컬럼1 + 컬럼2 + 컬럼3 + 컬럼4
컬럼3 조건에서 BETWEEN을 사용했다면 컬럼3까지만 인덱스를 타고 컬럼4는 인덱스를 타지 않고 필터만 한다. 따라서 일반적으로 구간 조건으로 많이 사용되는 컬럼은 후행컬럼으로 주로 사용한다.
3. 분포도가 좋은 컬럼인가 ?
분포도가 좋은 선행컬럼이라면, 굳이 결합인덱스로 사용할 필요는 없고 단일인덱스로 사용하면 된다. 결합인덱스는 여러 컬름을 합쳐서 처리 범위를 줄여주는 인덱스이다. 따라서 결합인덱스의 컬럼 순서와 분포도의 관계는 다음과 같다.
결합인덱스 : 선행컬럼(분포도 나쁨) + .. + 중간컬럼(분포도 보통) + ... + 후행컬럼(분포도 좋음)
결합인덱스는 여러 컬럼을 합쳐서 처리 범위를 줄인다는 의미도 있지만, 다수의 단일 인덱스를 대체하는 공통의 인덱스라는 의미도 있다.
4. 소트에 사용되는 컬럼인가 ?
소트에 사용되었다고 선행컬럼으로 반드시 사용돼야 하는 게 아니다. 결합인덱스는 결합된 컬럼의 순서로 정렬, 저장되므로 소트에 사용되는 컬럼은 결합인덱스의 일부분일 수는 있으나, 선행컬럼의 조건일 수는 없다. 인덱스는 기본적으로 위치(조건) 정보와 순서(정렬) 정보의 특성을 동시에 갖고 있다. 따라서 인덱스에는 위치정보를 갖고 있는 컬럼도 있고, 순서 정보를 갖고 있는 컬럼도 있다. 따라서 결합인덱스는 아래와 같은 규칙으로 만들어야 한다.
- 위치정보 컬럼만으로 구성할 수 있고, 순서정보 컬럼만으로 구성할 수 있다.
- 위치정보 컬럼과 순서정보 컬럼 순으로 구성할 수 있다. (반대는 안된다.)
인덱스 컬럼 선정 기준
인덱스는 테이블에서 찾고자 하는 레코드를 빨리 찾아가는 색인 또는 목차의 개념이기도 하고, 분류 대상에 대한 논리적인 논리적인 분류 정보이기도 하다. 테이블에 존재하는 수많은 컬럼을 모두 인덱스 컬럼으로 사용할 수 있는 것은 아니다. 컬럼 중에서도 인덱스로 사용할 수 있는 컬럼이 있고, 그렇지 않은 컬럼도 있다. 인덱스 대상 후보 컬럼은 아래와 같이 결정할 수 있다.
1. 분포도가 좋은 컬럼인가?
2. 갱신이 자주 발생하지 않는 컬럼인가?
3. 조건절에서 자주 사용되는 컬럼인가?
4. 조인의 연결고리에 사용되는 컬럼인가?
5. 소트 발생을 제거하는 컬럼인가?
분포도가 좋은 컬럼인가?
분포도란 전체 레코드에서 식별 가능한 수에 대한 백분율을 의미한다. 예를 들어 성별 컬럼은 식별 가능한 수가 2개이고, 분포도는 50%이다. 나이 컬럼은 식별 가능한 수가 100개(1세~100세)이고 분포도는 1%이다. 식별 가능한 수가 클수록 분포도가 낮으며, 분포도가 낮을수록 분포도가 좋다고 한다. 테이블 풀스캔 시의 멀티 블록으로 운반됨을 감안해야 하고, 인덱스 랜덤 접근시의 Leaf Block 접근도 감안해야 한다. 따라서 인덱스 후보컬럼으로 가능한 기준치는 분포도 1% 이내이다.
분포도는 논리적으로 판별하면 안되고 물리적으로 판단하는게 좋다.
갱신이 자주 발생하지 않는 컬럼인가 ?
인덱스 컬럼이 자주 갱신되면, 인덱스 밸런싱이 깨지게 되므로 인덱스 자료구조도 수정이 되기때문에 적절하지 않다. 따라서 가급적 update 항목에서 사용하는 컬럼은 인덱스 후보컬럼에서 배제하는 것이 좋다.하지만 update 유발 컬럼이라도 인덱스에 반드시 사용해야하는 경우도 있는데, 배송여부, 진행상태코드 등 컬럼이 그 예시이다. 이러한 컬럼은 단일인덱스에 사용되기보다는 결합인덱스의 후행컬럼으로 주로 사용된다.
조건절에서 자주 사용되는 컬럼인가 ?
조건절에서 자주 사용되는 의미는 범용적인 조건절이라는 의미도 있기 때문에, 조건절에서 자주 사용하는 컬럼을 결합인덱스의 선행으로 두는게 좋다.
조인의 연결고리에 사용되는 컬럼인가 ? (몰라서 일단 패스)
인덱스는 조건절에서 사용되기도 하고 조인절에서 사용되기도 하는데, 조건절에서 사용하는 인덱스는 최초로 접근하는 테이블을 결정하는 중요한 인덱스이며, 테이블 접근 범위를 줄여주는 역할을 한다. 조인절에서 사용하는 인덱스는 테이블 간의 관계를 맺는 인덱스로서, 데이터 접근 범위를 항상 줄여주는 것은 아니다.
소트 발생을 제거하는 컬럼인가 ?
인덱스는 기본적으로 위치정보 + 순서정보로 구성되는데, 조건절에서 사용하는 컬럼만 인덱스 후보컬럼이 되는것은 아니다. ORDER BY 절에 있는 컬럼도 인덱스 후보컬럼으로 사용할 수 있는데, 하나의 테이블에 인덱스가 많으면 많을수록 부하가 점점 증가 하듯이 결합인덱스도 컬럼 수가 많으면 많을수록 부하가 점점 증가한다.
SELECT *
FROM 주문
WHERE 상품코드 = '텐트'
AND 배송여부 = 부
ORDER BY 주문일자 DESC위의 쿼리에서 상품코드와 배송여부는 위치정보 컬럼이고, 주문일자 컬럼은 순서정보 컬럼이다. 이때는 다음과 같이 인덱스를 고려할 수 있다.
결합인덱스1 : 상품코드(위치) + 배송여부(위치)
결합인덱스2: 상품코드(위치) + 배송여부(위치) + 주문일자(순서)
소트 부하가 인덱스 부하보다 크다면 결합인덱스 2를 고려해야 하며, 적다면 결합인덱스 1을 고려해야 한다. 그런데 결합인덱스2로 작성하는게 개발자 입장에서는 편하다.
참조
http://www.yes24.com/Product/Goods/61583758
개발자를 위한 인덱스 생성과 SQL 작성 노하우 - YES24
『개발자를 위한 인덱스 생성과 SQL 작성 노하우』개발 현장에서 바로 통하는 인덱스 생성 및 쿼리 작성 노하우 제시관리를 쉽게 하고 개발 생산성을 끌어올리는 쿼리 작성의 정석 ‘공정쿼리’
www.yes24.com
'DB' 카테고리의 다른 글
| 오라클의 조인 방식 (0) | 2022.08.01 |
|---|---|
| 공정쿼리 (0) | 2022.07.31 |
| 인덱스 작성 노하우와 인덱스 생성도 (0) | 2022.07.26 |
| 커넥션과 서버 프로세스의 생성 (0) | 2022.05.30 |
| 오라클 구조 (0) | 2022.05.01 |