인덱스 (INDEX)

인덱스(INDEX)란?

• 테이블의 조회 속도를 높여주는 자료구조
• 데이터의 위치를 빠르게 찾아주는 역할을 한다.
  • 문서의 목차같은 역할이다.
• 인덱스를 사용하지 않으면 Table Full Scan이 일어나 성능이 저하되거나 치명적인 장애가 발생한다.

인덱스의 특징

• 하나 이상의 속성에 대해 설정할 수 있다.
  • 최대 15가지의 속성에 대해 설정할 수 있다.
• WHERE절을 사용하지 않고 인덱스가 걸린 속성을 조회하는 것은 성능에 아무런 영향이 없다.
• 인덱스 생성 시 사용되는 속성이 많아질수록 INDEX, UPDATE, DELETE를 비효율적으로 수행한다.
  • 페이지를 정렬하는 작업때문이다.
• 다중 속성을 사용한 인덱스의 경우에 인덱스 생성 시 명시한 속성의 순서대로
  WHERE절에 선언할 때 조회 속도가 빨라진다.
• 테이블 크기의 약 10% 정도의 추가 공간이 필요하다.
  • 인덱스 페이지가 차지하는 공간이다.
• WHERE 절에서 명시하는 속성에 연산을 가하면 해당 속성은 인덱스를 사용하지 않는다.

인덱스의 장점

• SELECT의 속도가 빨라진다.

인덱스의 단점

• INSERT, UPDATE, DELETE의 속도는 저하된다.
  • Table의 index 색인 정보를 갱신하는 추가적인 비용이 소요된다.

인덱스가 사용되지 않는 경우

• 복수의 키에 대해서 ORDER BY를 사용한 경우
• 연속하지 않은 속성에 대해 ORDER BY를 실행한 경우
• DESC와 ASC를 혼합해서 사용한 경우
• GROUP BY와 ORDER BY의 속성이 다른 경우
• ORDER BY 절에 IF나 CASE같은 다른 표현을 사용한 경우
• LIKE 키워드 사용 시 %가 앞에 위치하는 경우
  • 예시
    • SELECT * FROM MEMBER WHERE NAME LIKE '%동';

인덱스 유형

• 클러스터 인덱스 (Clustered Index)
  • 테이블당 1개만 존재할 수 있다.
  • 기본 키로 지정된 속성은 자동으로 클러스터 인덱스가 생성된다.
  • 실제 저장된 데이터와 같은 페이지 구조를 가진다.
  • 클러스터 인덱스를 기준으로 데이터가 자동 정렬된다.
    • 기본 키를 변경하면 클러스터 인덱스가 변경되기 때문에
      변경된 기본 키를 기준으로 자동 정렬된다.
    • 대용량의 데이터가 존재하는 테이블에서
      클러스터 인덱스 변경 시 심각한 시스템 부하를 줄 수 있다.
  • 보조 인덱스에 비해 검색 속도가 빠르다.
  • 보조 인덱스에 비해 INSERT, UPDATE, DELETE 연산이 느리다.
  • 특정 조건에 따라서 자동 생성된다.
    1. PK 존재 시 PK를 클러스터 인덱스로 지정한다.
    2. PK 부재 시 UK && NN인 속성 존재 시 해당 속성을 클러스터 인덱스로 지정한다.
    3. 1단계와 2단계에 해당하는 속성이 없다면 임의로 보이지 않는 속성을 생성하여
       해당 속성을 클러스터 인덱스로 지정한다.
• 보조 인덱스 (Secondary Index 또는 non-Clustered Index)
  • 한 테이블에 여러개 설정 가능하다.
  • UNIQUE 키워드로 고유 속성 지정시 자동으로 보조 인덱스가 생성된다.
  • 실제 저장된 데이터와 다른 무리의 별도의 페이지 구조를 가진다.
  • 클러스터 인덱스와 달리 데이터를 자동 정렬하지 않는다.
  • CREATE INDEX 문으로 직접 보조 인덱스를 생성할 수 있다.
  • 클러스터 인덱스에 비해 검색 속도가 느리다.
  • 클러스터 인덱스에 비해 INSERT, UPDATE, DELETE 연산이 빠르다.

인덱스 설계 방법

• 무조건 많이 설정하지 않는다.
  • 목적에 따라 다르겠지만 한 테이블당 3~5개가 적정하다.
• 자주 사용하는 속성에 설정한다.
• 고유한 값 위주로 설계한다,
• 카디널리티가 높을 수록 좋다.
  • 해당 속성이 가지고 있는 중복도가 낮다는 것을 의미한다.
• INDEX 키의 크기는 되도록 작게 설계해야 한다.
• JOIN의 연결고리가 되는 속성에 사용하면 좋다.
• 단일 인덱스 여러 개 대신에 다중 속성 INDEX 생성하는 고려해보는 것도 좋다.
• UPDATE가 빈번하지 않은 속성에 사용한다.
• JOIN 시 자주 사용하는 속성
• INDEX를 생성할 때 가장 효율적인 자료형은 정수형 자료(가변적 데이터는 비효율적)
• 검색할 데이터가 전체 데이터의 20% 이상이라면 인덱스를 사용하지 않는다.
  • 강제로 사용할 경우 성능 저하가 발생할 수 있음
  • 전체 페이지의 대부분을 읽어야 하기도 하고, 인덱스 관련 페이지도 읽어야 해서 작업량이 크다.
• 사용하지 않는 인덱스는 제거해야 한다.
  • 오히려 인덱스를 삭제했을 때 성능이 향상되는 경우도 있다.
• 손익분기점을 계산한다.
  • 테이블이 가지고 있는 전체 데이터양의 10% ~ 15%이내의 데이터가 출력 될 때만 INDEX를 타는게 효율적이고,
    그 이상이 될 때에는 오히려 풀스캔이 더 빠르다.

인덱스 관련 SQL

인덱스 생성

• 속성명 뒤에 ASC나 DESC를 붙여서 정렬 순서를 지정할 수 있다.
• 키워드 생략 시 중복이 허용되는 인덱스로 생성된다.
• UNIQUE 키워드 지정 시 동일한 데이터 값이 입력될 수 없는 인덱스가 된다.
• FULLTEXT 키워드 지정 시 전체 텍스트를 검색하는 인덱스가 된다.
• SPATIAL 키워드 지정 시 점, 선, 면 등 공간 데이터와 관련된 인덱스가 된다.
• 생성된 인덱스를 실제 적용하려면 ANALYZE TABLE 테이블명;을 실행해야 한다.

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 인덱스명 ON 테이블명(속셩명1, 속셩명2, ...);

인덱스 제거

DROP INDEX 인덱스명 ON 테이블명;

인덱스 조회

SHOW INDEX FROM 테이블명;

인덱스 적용

ANALYZE TABLE 테이블명;

인덱스 크기 확인

SHOW TABLE STATUS LIKE 테이블명;

인덱스 알고리즘

B-TREE 인덱스

• 가장 일반적으로 사용되는 알고리즘
• 노드 종류
  • 루트 노드 (Root Node)
    • 최상위에 있는 노드
  • 브랜치 노드 (Branch Node)
    • 루트 노드와 리프 노드 사이의 노드
  • 리프 노드 (Leaf Node)
    • 최하위에 있는 노드
• 노드에는 테이블 내에 존재하는 데이터와 자식 노드 주소가 저장된다.
• 부모 노드를 기준으로 왼쪽 자식 노드는 더 작은 값을, 오른쪽 자식 노드는 더 큰 값을 갖고 있다.
• 어떤 데이터를 조회하던지, 이에 사용하는 조회 과정의 길이 및 비용은 균등하다.
• 어떤 데이터를 조회하던지, 루트 노드부터 리프 노드까지 모두 거처야 하다보니 비교적 속도가 느리다.
• 시간 복잡도
  • O(n)
• Balanced Tree의 약자
• B-Tree가 주로 사용되는 이유
  • 노드 하나에 배열 형태로 여러 개의 데이터를 저장할 수 있다.
  • 트리 포인터를 참조해서 계속 깊이가 증가하는 것보다 효율적이다.

HASH 인덱스

• 인덱스가 적용된 속성의 값으로 해시 값을 계산해서 인덱싱한다.
• 매우 빠른 검색을 지원한다.
• 컬럼 값과 인덱스 키 값이 일치하지 않기 때문에 문자열 검색과 같이 일부 일치에 대한 검색 기능은 지원하지 않는다.
• 데이터가 정렬되어 있지 않기 때문에 부등호를 사용하지 못 한다.
• 시간 복잡도
  • O(1)
• 범위로 묶어서 보관하는 인덱스보다 더 큰 저장공간이 필요하다.

Fractal-Tree 인덱스

• B-Tree의 단점을 보완하기 위해 고안된 알고리즘이다.
• 값을 변경하지 않고 인덱싱한다.
• 범용적인 목적으로 사용할 수 있다.
• 데이터의 저장 및 삭제될 떄의 처리 비용을 많이 줄일 수 있게 설계되었다.
• 아직 많이 사용되지는 않는다.

R-TREE

• N차원 사각형 기반의 구조화된 자료구조
• N차원의 공간 데이터를 효과적으로 저장한다.
• 지리정보와 관련된 질의를 빠르게 수행 할 수 있다.
• RedBlack Tree의 약자