[DB] DB Index 란?

Index란?

테이블에 저장된 데이터를 빠르게 조회하기 위한 데이터베이스 객체

•  대개 B-Tree, B+Tree구조를 가짐
• Index는 논리적/물리적으로 테이블과 독립적임

Index를 왜 사용할까?

SQL서버에서 테이블을 만들고 데이터를 추가, 수정, 삭제 할 때 데이터의 레코드는 내부적으로 아무런 순서 없이 저장된다. 이때 데이터 저장영역을 Heap이라고 한다. Heap에서는 인덱스가 없는 테이블의 데이터를 찾을 때 무조건 전체 데이터 페이지의 처음 레코드부터 끝 페이지의 마지막 레코드까지 다 읽어서 검색조건과 비교하게 된다. 이런 식의 데이터 검색방법을 테이블 스캔(table scan) 또는 풀 스캔(full scan)이라고 한다. 이럴 경우 양이 많은 테이블에서 일부분의 데이터만 불러 올 때 풀 스캔을 하면 처리 성능이 떨어진다. 즉 인덱스는 데이터를 select 할 때 빨리 찾기 위해 사용된다. 

Index 생성 시, 고려할 점

인덱스를 생성 시에는 where 절과 join, order by 등과 관련된 칼럼중 사용 빈도가 높고 키 값의 선별도가 좋은 칼럼에 사용해야 한다. 반대로 사용 빈도가 낮고 칼럼의 선별도가 나쁜, 예를 들어 한 칼럼의 값이 true/false, 성별(M/F) 등에는 인덱스를 사용하지 않는 것이 좋다. 또 테이블이 작거나 자주 갱신 될 때도 사용하지 않는 것이 좋다. 

선택한 키의 검색 일치율이 10%미만일 경우는 Index를 사용하는 것이 좋으나, 그 이상일 경우는 풀 스캔이 더 나을 수 있다. 

•  예) 테이블 내 100개 데이터 중 pk=1을 검색했을 때, 1개의 데이터가 나올 경우 1/100 = 0.01 즉 1%의 일치율을 보인다.

책에 비유를 할 때, 클러스터 인덱스는 찾고자 하는 페이지를 바로 찾는 것이고 논 클러스터 인덱스는 목차를 먼저 확인한 다음, 목차에서 찾고자 하는 페이지를 찾아가는 것입니다. 또한 테이블 스캔은 처음부터 한 장씩 넘기면서 찾는 방식입니다.

select * from test where name like'이%' select * from test where name like'%이%' -- 처음 %가 붙으면 table scan이 되어 속도가 느리게 됩니다.

팁

아래의 내용을 주의, 명시해서 index를 생성하도록 합니다.

1. where절에서 자주 사용하는 컬럼

2. like '%~~~~~~~~~'는 조심 (table scan이여서 성능 감소) %는 뒤에만 사용하도록 해야함 

3. between A and B (클러스터 인덱스 유리) - 범위 쿼리문에서는 클러스터 인덱스가 유리하지만 클러스터 인덱스는 테이블 당 1개만 가질 수 있다는 단점 존재

4. order by에 항상 또는 자주 사용되는 컬럼

5. join으로 자주 사용되는 컬럼

6. Foreign key (1:1 매핑)이 많을 때 -> 클러스터, 논클러스터 인덱스 둘 다 상관 없음 (상황에 따라 클러스터 인덱스 사용)

7. Foreign key (1:N 매핑)이 많을 때 -> 클러스터 인덱스 사용

8. 100만건의 데이터 중 10건의 데이터 조회 -> 찾는 건이 적은 컬럼에 인덱스를 걸어주는 것이 상책

• 중복이 많은 컬럼 (예를 들어, 성별)에는 인덱스를 거는 것이 아님
• 조회되는 건 수가 많으면 인덱스를 걸지 않고 table scan이 더 나은편

9. not 연산자는 긍정문으로 변경

10. insert, delete 등 데이터의 변경이 많은 컬럼은 인덱스를 걸지 않은 편이 좋음

• 인덱스를 만드는데 시간과 저장공간이 소비되고 만들고 난 후에도 추가적인 공간이 필요. 데이터를 변경 (insert, update, delete)를 하면 (특히 insert) 인덱스를 다시 조정해야하기 때문에 자원이 많이 소모됨

Index 생성

인덱스에는 크게 clustered와 nonclustered 인덱스로 나눌 수 있다. clustered 인덱스는 물리적 정렬로 DB에 데이터를 입력 시 이것을 기준으로 입력이 된다. 따라서 한 테이블에 오직 하나만 존재 할 수 있으며 table을 열었을 때 order by를 사용하지 않아도 데이터가 clustered 인덱스에 따라 정렬이 되어 있는 것을 확인 할 수 있다. 물리적으로 정렬이 되어 있는 만큼 가장 빠른 처리를 한다. nonclustered 인덱스는 clustered 인덱스와는 달리 중복된 값을 가지면 한 테이블에 여러 개를 생성 할 수 있다. 이 외에 unique에 대해 알아보겠다. unique는 말 그대로 중복을 허용하지 않는 값을 보호 할 때 사용한다. 예를 들어 회원 관리 프로그램에서 아이디가 중복되는 것을 막고자 한다면 이 옵션을 사용하면 된다. 우리가 자주 사용하는 primary key의 경우에는 clustered와 unique 특성을 갖게 하는 제약키이다. 

Index를 생성할 때 clustered가 되어있을 때, index scan이 유리하다.