index

What

인덱스 장/단점

• 장점
• 조회 속도 개선으로 인해 전반적인 시스템 부하 감소 효과를 기대할 수 있다.

• 단점
• 추가적인 저장공간이 필요하다. (10%)
• 관리하기 어렵다
• 잘못 오용될 경우 오히려 성능이 좋지 않아진다.
• 삽입, 삭제, 변경이 자주 일어나는 컬럼에는 오히려 좋지 않다.
why ❓
삽입 : 새로운 데이터에 대한 인덱스 추가 하는 비용 발생
수정 : 기존의 인덱스를 사용하지 않는다라고 표시하고 새롭게 인덱스 추가
삭제 : 삭제하는 데이터의 인덱스 사용하지 않겠다고 표시

언제 사용해야 적절할까?

• 규모가 작지 않은 테이블

• 삽입, 수정, 삭제가 자주 일어나지 않는 컬럼

• JOIN이나 WHERE, ORDER BY를 자주 수행하는 컬럼

• 데이터의 카티널리티가 높은 컬럼 ( 중복도가 낮은 컬럼 )

인덱스 자료 구조 [B+Tree]

• 해쉬 테이블이 아니라 왜 B+Tree를 선택했을까?
• 해쉬 테이블은 하나의 데이터 검색에 있어 O(1)이라는 시간복잡도를 가지고 있으므로 매우 빠르다. 하지만 부등호 연산이 자주 사용되는 검색을 위해서는 적합하지 않기 때문이다.

• B+Tree 상세 설명 [InnoDB]

• 인덱스의 자료구조로서 자식 노드가 2개 이상인 B-Tree를 개선시키는 자료구조로서 말단 리프노드만 인덱스와 함께 데이터를 가지고 있고 나머지 노드들은 데이터를 위한 인덱스(key)만을 갖는다.

• 또다른 특징으로서 리프노드들은 LinkedList 구조로 연결되어 있다.

인덱스의 종류

• 클러스터드 인덱스
• 테이블 당 한개만 생성이 가능하며 기본키를 생성하면 자동으로 클러스터드를 생성해 준다.
• 리프 페이지가 곧 데이터 이다.
• 데이터의 입력, 수정, 삭제 시 항상 정렬 상태를 유지해야 한다.
• 이것도 하나의 부담이 될 수 있지만, 리프페이지가 곧 데이터 노드로서 상대적으로 넌 클러스터드 인덱스 보다 빠르다는 이점이 있다.
• 정렬 상태를 유지하는 부담으로 인해 입력, 수정, 삭제시에 느리다.
• 30% 이내에서 사용해야 좋은 선택도를 가진다.



 

• 넌 클러스터드 인덱스
• 여러개의 인덱스를 선언할 수 있다.
• 인덱스 페이지는 로그파일에 저장된다.
• 즉 수용공간을 더 차지한다.
• 레코드의 원본은 정렬되지 않고, 인덱스 페이지만 정렬된다.
• 리프페이지가 데이터가 아니라 가르키는 위치 RID이기 때문에 클러스터드 보다 검색속도가 느리지만, 입력, 수정, 삭제는 더 빠르다.
• 3% 이내에서 사용해야 좋은 선택도를 가진다.



 

☠️ 인덱스 고려사항

• 입력, 수정, 삭제가 빈번한 컬럼에는 유의해야 한다.

• 인덱스 컬럼에서는 NULL 값을 사용하지 않는다.

• 타입 형식에 유의해야 한다.
그렇지 않으면, 인덱스를 타지 않는다.
• INT 타입이면 NUM = 1000
• VARCHAR 타입이면 NUM = ‘1000’

• 복합 컬럼을 인덱스로 설정할 때는 자주 사용되는 컬럼 순으로 인덱스를 생성하는 것이 좋다! 즉, 우선순위를 잘 고려해야 한다. (필터링 많이 되어질 컬럼을 앞쪽에 배치하는 것이 좋다)

• 카디널리티 선택도 차이
• 카디널리티 : 특정 데이터 집합의 유니크한 값의 개수이다.
• 선택도 : 카디널리티로부터 계산할 수 있는 값으로서 데이터 집합에서 특정 값을 얼마나 잘 골라낼수 있는지에 대한 지표이다.

⚡인덱스 주의사항

• Like 검색에 유의 한다. 🌠
• %문자%로 많이 검색하는 것 보다는 문자%로 앞 prefix로 먼저 끊어서 검색할 때가 성능이 더 좋다.

• NULL 관련
• NULL 구분으로 인덱스 컬럼을 검색하면 인덱스를 타지 않는다.
• IS NULL 등 인덱스가 있어도 타지 않음에 유의 한다.

• 부정 연산자 🌠
• ≠, <>, NOT IN, NOT EXIST 등 이런 경우에는 인덱스를 타지 않는다.

• 형 변환 혹은 값 변환 🌠 [가공해서 사용하면 안된다]
• 인덱스가 있는 컬럼에 형 변환 혹은 값 변환이 있으면 인덱스를 타지 않는다.

NUM + 1 =100 , ISNULL(NUM,1) = 100

이런것 처럼 조건을 주면 타지 않음에 유의 한다.

• OR 구문
• 인덱스가 걸린 컬럼과 OR 구문으로 연동된 다른 컬럼으로 조회할 경우 테이블 전체 스캔을 하며 인덱스를 타지 않는다.

• 왼쪽 절 기준
• WHERE로 조건문을 수행할 때, 왼쪽의 조건에만 인덱스를 탄다.

A.NAME = B.NAME

이런 조건을 검색을 한다면, A테이블의 NAME에 인덱스가 설정되어 있으면 인덱스를 타지만 A테이블에는 인덱스가 없고 B에만 인덱스가 존재하는 경우 인덱스를 타지 않는다.
• 즉 검색 시 왼쪽에 있는 테이블에 인덱스가 걸린 컬럼을 줘야 성능향상을 기대할 수 있다.
 
 
 

Why

• 결국 update, delete를 하기 위해서는 사전에 select가 필요하다. 그러므로 인덱스가 없는 컬럼으로 수정과 삭제가 이루어지면 느려짐을 유의해야 한다.

Refer