[DB] INDEX란?

실무를 진행하면서 DB 설계를 많이 진행해보았는데 INDEX 설계는 진행해본 적이 없어서 이해도가 떨어지는 것 같아 찾아보게 되었다.

1. INDEX란?

INDEX란 테이블의 검색 성능을 높혀주기 위한 자료구조이다. 하나 또는 여러 개 컬럼에 대하여 설정할 수 있다.

INDEX가 걸려 있어도, WHERE절 없이 단순히 컬럼을 조회하는 경우에는 인덱스가 사용되지 않기 때문에 성능 향상에 도움이 되지 않는다.
(예를 들어 ‘홍길동’이라는 단어를 찾고싶으면 색인 페이지에서 ‘홍’으로 시작하거나 ‘ㅎ’으로 시작하는 색인을 찾아보면 빠르게 찾을 수 있다.)

2. INDEX의 장단점

장점

1. 적절한 인덱스를 생성하고 사용하면 조회 성능을 획기적으로 개선할 수 있다.

단점

1. INSERT, UPDATE, DELETE의 속도가 저하된다.
2. 대략 테이블 크기의 10% 공간이 추가로 필요하다.
3. 잘못 사용할 경우 검색 성능이 저하된다.

3. INDEX 설계 방법

1. 규모가 작지 않은 테이블에서 사용한다.
2. INSERT, UPDATE, DELETE가 자주 발생하지 않는 컬럼을 사용한다.
3. JOIN, WHERE, ORDER BY에 자주 사용되는 컬럼을 사용한다.
4. 데이터의 중복도가 낮은 컬럼을 사용한다.

4. INDEX 생성 문법

• 인덱스 생성 및 추가 시 UNIQUE(중복 값을 허용하지 않는 인덱스)로 설정하고 싶을 경우 작성한다.

인덱스 생성

-- 단일 인덱스
CREATE [UNIQUE] INDEX 인덱스 명 ON 테이블 명(필드 명1);

-- 다중 컬럼 인덱스
CREATE [UNIQUE] INDEX 인덱스 명 ON 테이블 명(필드 명1, 필드 명2, ...);

인덱스 조회

SHOW INDEX FROM 테이블 명;

인덱스 정렬(인덱스 생성 시점에 필드의 정렬방식 설정)

CREATE INDEX 인덱스 명 ON 테이블 명 (필드 명 DESC);
CREATE INDEX 인덱스 명 ON 테이블 명 (필드 명 ASC);

인덱스 삭제

ALTER TABLE 테이블 명 DROP INDEX 인덱스 명;

인덱스 추가

ALTER TABLE 테이블 명 ADD [UNIQUE] INDEX 인덱스 명(컬럼 명1, 컬럼 명2...);

5. ORDER BY 와 GROUP BY에 대한 INDEX

INDEX는 ORDER BY와 GROUP BY에도 영향을 끼치는데 다음과 같은 경우에는 INDEX를 타지 않는다.

1. ORDER BY 인덱스 컬럼 명1, 컬럼 명2 : 복수의 키에 대해서 ORDER BY를 사용한 경우
2. WHERE 컬럼 명1=’값’ ORDER BY 인덱스 컬럼 명 : 연속하지 않은 컬럼에 대해 ORDER BY를 실행한 경우
3. ORDER BY 인덱스 컬럼 명1 DESC, 인덱스 컬럼 명2 ASC : DESC와 ASC를 혼합해서 사용한 경우
4. GROUP BY 컬럼 명1 ORDER BY 컬럼 명2 : GROUP BY와 ORDER BY의 컬럼이 다른 경우
5. ORDER BY ABS(컬럼 명) : ORDER BY 절에 다른 표현을 사용한 경우

---