DB 인덱스(Index) - 생성 방법 및 컬럼순서

DB 인덱스에 대한 개념이 궁금하다면 이전 포스팅 이전 포스팅을 확인 바란다.

---

인덱스 생성 방법

CREATE INDEX TEST_IDX ON TEST(학년, 학급, 성별)

---

인덱스 컬럼 선택

• WHERE 절에 자주 등장하는 컬럼
• Equal('=') 으로 비교되는 컬럼
• JOIN 조건으로 자주 사용되는 컬럼
• ORDER BY 절에서 자주 사용되는 컬럼

---

인덱스 컬럼 순서 결정

인덱스를 구성하는 컬럼들의 순서는 매우 중요하기에 신중하게 결정하여야 한다.

컬럼의 순서를 잘못 배열하면 인덱스의 발동 확률이 매우 낮아질 수 있다.

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

 

1) 공통적으로 사용하는 필수 조건절 컬럼을 우선

인덱스의 첫 번째 컬럼을 WHERE절에 사용하지 않는다면 해당 인덱스는 사용되지 않는다.

많은 쿼리에서 공통적으로 사용된 조건절의 컬럼을 인덱스 선행 칼럼에 주로 사용한다

공통적으로 사용된다는 것은 필수 조건절이라는 의미와 동일하다. 

 

인덱스 사용 X

SELECT *
FROM USER
WHERE 학급 = 5

인덱스 사용 O

SELECT *
FROM USER
WHERE 학년 = 1

 

2) Equal('=') 컬럼을 다른 연산자 컬럼보다 우선

조건(WHERE) 절에서 Equal('=')이 아닌 다른 연산자(BETWEEN, LIKE, <, >)의 첫 번째 컬럼까지만 인덱스를 사용하고 그 이후 컬럼들은 인덱스를 사용하지 않는다.

 

인덱스 컬럼순서가 COLUMN1,2,3,4 순서로 되어있다고 가정하자

WHERE COLUMN1 = ?
AND   COLUMN2 = ?
AND   COLUMN3 BETWEEN ? AND ? -- 인덱스에서 '='이 아닌 연산자를 사용하는 첫 번째 
AND   COLUMN4 = ?

COLUMN3에서 BETWEEN을 사용했기에 COLUMN3 까지만 인덱스를 사용하고 COLUMN4는 인덱스를 사용하지 않는다.

이렇게 사용하고 싶다면 인덱스를 COLUMN1,2,4,3 순서로 작성해야 한다.

 

3) 대분류 -> 중분류 -> 소분류 컬럼순으로 구성

분포도가 좋은 컬럼이 처리 범위를 줄여주므로 인덱스의 선행 칼럼으로 순서를 구성해야 한다.

인덱스를 사용하는 이유 중 하나가 하나의 컬럼만으로는 분포도가 좋지 않지만 여러 개의 컬럼으로 분포도를 향상해 처리 범위를 줄여주는 데에 있기 때문이다.

인덱스에서는 선행 칼럼에서 걸러진 범위에서 그다음 컬럼이 걸러지고 연속해서 걸러진 범위에서 값들이 걸러지는 것이다. 그렇기에 걸러지는 범위는 큰 범위부터 점점 작은 범위로 걸러지는 것이 더 좋은 효율을 낼 수 있는 것이다.

 

 

4) WHERE 조건절 컬럼은 ORDER BY조건절 컬럼보다 우선

인덱스는 기본적으로 위치(조건) 정보와 순서(정렬) 정보의 특성을 동시에 갖고 있다. 따라서 인덱스에는 위치정보를 갖고 있는 컬럼도 있고, 순서 정보를 갖고 있는 컬럼도 있다. 순서 정보 즉, 정렬에 사용되는 컬럼으로는 처리범위가 그대로인 상태로 다음 컬럼으로 넘어가는 것이다. 그렇기에 위치 정보 즉, 조건절에 사용되는 컬럼에 우선순위를 둬서 처리 범위를 줄이는 것이 더 좋을 것이다. 

 

---

인덱스를 사용한 조회시 조회컬럼 순서는 신경쓰지 않아도된다.

조회조건에 포함되었는지가 중요한 것이지 인덱스 순서와 조회조건의 순서를 맞출 필요는 없다.