[SQL] 효율적인 DB INDEX(인덱스) 설정

인덱스(INDEX)

검색 속도를 높이기 위한 색인 기술이다.

보통 인덱스는 일반적으로 SELECT 쿼리의 WHERE에 사용할 컬럼에 대해 효율적인 검색을 위해 사용하거나, 다른 테이블과의 JOIN에 사용된다. (주로 효율적인 검색을 위해 사용된다.)

일반적으로 SQL 서버에 데이터를 저장할 때는 내부적으로 아무런 순서없이 저장한다. 이때, 데이터 저장영역은 Heap이다.

 

Heap에서는 인덱스가 없는 테이블의 데이터를 찾을 때, 전체 데이터 페이지의 처음 레코드부터 끝 페이지 마지막 레코드까지 모두 조회하게 된다.

이러한 방식을 풀 스캔(Full Scan) 또는 테이블 스캔(Table Scan) 검색 방식이라고 한다.

 

검색 속도 향상을위해서 인덱스를 사용해야 한다.

 

[ 어떤 컬럼에 Index를 설정해야 하는가? ]

1. 핵심 기준 4가지

- 카디널리티(Cardinality)

카디널리티가 높으면(한 컬럼이 갖고 있는 값의 중복도가 낮으면) 인덱스 설정에 좋은 컬럼이다. 

- 선택도 (Selectivity)

선택도가 낮으면(한 컬럼이 갖고 있는 값 하나로 적은 row가 찾아지면) 인덱스 설정에 좋은 컬럼이다.

- 조회 활용도

조회 활용도가 높으면 인덱스 설정에 좋은 컬럼이다.

- 수정 빈도

수정 빈도가 낮으면 인덱스 설정에 좋은 컬럼이다. (인덱스도 테이블이기 때문에 인덱스로 지정된 컬럼의 값이 바뀌게 되면 인덱스 테이블도 새롭게 갱신되어야 한다.)

 

2. 그 밖의 Index 명시 사항

- WHERE 절에 자주 사용되는 컬럼에 사용하기

- LIKE와 사용할 경우 %를 뒤에 사용하기

- ORDER BY 에 자주 사용되는 컬럼에 사용하기

- JOIN에 자주 사용되는 컬럼에 사용하기

- 데이터 변경이 잦은 컬럼에는 사용하지 않기

 

[ Index를 무조건 많이 설정하면? ]

1. 인덱스 설정 시, 데이터 베이스에 할당된 메모리를 사용하여 테이블 형태로 저장하게 된다. 즉, 인덱스가 많아지면 데이터베이스의 메모리를 많이 잡아먹게 된다.

2. 인덱스로 지정된 컬럼의 값이 바뀌게 되면 인덱스 테이블이 갱신되어야 하므로 느려질 수 있다.

 

전체적인 데이터베이스의 성능 부하를 초래할 수 있다.

 

[ 설정된 Index가 DML에 미치는 영향 ] 

* SELECT

Index는 주로 SELECT 쿼리에서 성능이 잘 나온다.

* UPDATE, DELETE

인덱스로 설정된 컬럼에 대해 조건(WHERE)을 사용할 수도 있는 UPDATE, DELETE 사용 시 조회에서는 성능이 크게 저하되지 않는다.

* INSERT

INSERT의 경우 효율이 좋지 않다. 새로운 데이터를 추가하면서 인덱스가 설정되어 있던 컬럼의 테이블도 같이 수정되어야 하기 때문이다.

 

[ Single Column Index와 Multi Column Index의 비교 ]

- Multi Column Index의 장점

질의(SQL) 컬럼이 모두 조합 인덱스에 있는 경우, 물리적인 데이터 블록을 읽을 필요가 없다. (인덱스 테이블만 읽으면 된다.)

- Multi Column Index를 고려해야 하는 경우

WHERE 절에서 AND 연산자에 의해 자주 같이 질의되는 컬럼인 경우

 

[ Index 특징 요약 ]

- 검색 (SELECT) 속도 향상

- 인덱스 테이블을 위한 추가 공간과 시간 필요

- INSERT, UPDATE, DELETE가 경우에 따라 성능 하락 발생

 

 출처 

https://velog.io/@jwpark06/%ED%9A%A8%EA%B3%BC%EC%A0%81%EC%9D%B8-DB-index-%EC%84%A4%EC%A0%95%ED%95%98%EA%B8%B0