인덱스 개념 간단 정리

인덱스란

인덱스란 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조이다. 만약 우리가 책에서 원하는 내용을 찾는다고 하면, 책의 모든 페이지를 찾아 보는것은 오랜 시간이 걸린다. 그렇기 때문에 책의 저자들은 책의 맨 앞 또는 맨 뒤에 색인을 추가하는데, 데이터베이스의 index는 책의 색인과 같다.

 

단순히 생각하면 어떤 데이터를 찾을 때 전체를 확인하는 방식(Full Scan)을 해서 오래 걸리지 않도록 인덱스를 이용한다고 생각하면 된다.

 

하지만 DB 인덱싱은 새로운 테이블을 생성하기 때문에 별도의 저장공간이 발생하고, 인덱스 테이블은 기본적으로 sorting이 된 상태기 때문에 새로운 데이터를 삽입하거나 수정할때 오히려 더 성능이 안좋아진다.

 

인덱스는 데이터의 저장(INSERT, UPDATE, DELETE) 성능을 희생하고, 그 대신 데이터의 읽기속도(SELECT)를 높이는 기능

 

인덱스를 생성하려는 컬럼의 분포도가 10~15% 이하이면 인덱스를 타는게 효율적이라고 한다.

("분포도가 좋다."라 표현하는 것은 해당 컬럼의 유니크한 데이터 종류가 많다라고 이해하시면 될 것 같습니다.)

(1/해당 컬럼의 distinct 데이터 개수 * 100 또는 데이터별 평균 Rows / 테이블 전체 Rows * 100)

 

인덱스 선정

모든 액세스 형태와 분석을 토대로 이상적인 컬럼 구성과 순서 결정을 통해 최소의 인덱스로 모든 액세스 형태를 만족할 수 있도록 해야한다.

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

2. 카디널리티가 높은 컬럼

3. order by 빈도가 높은 컬럼

 

결합 인덱스

여러 컬럼을 모아 하나의 인덱스를 만드는 방식

sql문에서 where 절의 조건 컬럼이 2개이상의 and로 연결되어 함계 사용되는 경우에 사용(or에는 만들면 안됨)

• 인덱스의 첫 번째 컬럼이 조건절에 없다면 일반적으로 인덱스가 사용되지 않는다
• Equal 연산이 아닌 검색 조건이 들어오는 경우(범위 연산), 처리 범위가 크게 증가하여 효율이 크게 저하될 수 있다.

 

인덱스에 사용되는 알고리즘

DBMS에서 인덱스는 일반적으로 B+Tree 알고리즘을 사용한다.

< 인덱싱 알고리즘의 종류 >

 

- B-tree 알고리즘 : 가장 일반적으로 사용, 오래전부터 도입되어 많이 안정화 된 알고리즘. 컬럼 값을 변환하지 않고 원래 값을 이용해 인덱싱.

 

- Hash 인덱스 알고리즘 : 컬럼 값으로 Hash 값을 계산하여 인덱싱하는 알고리즘으로 매우 빠른 검색을 지원. 변환된 값을 이용하여 인덱싱 하므로 LIKE 검색과 같은 부분일치 값을 검색하고자 할때는 사용 불가. 주로 메모리 기반의 DB에서 많이 사용.

 

- Fractal-Tree 알고리즘 : B-tree 알고리즘의 단점을 보완을 위해 등장, 원래 값을 이용해 인덱싱. 데이터의 저장과 삭제 시 처리비용을 감축, 조만간 B-tree 알고리즘을 대체할 수 있을거라 예상.

 

 

< 동작방식에 따른 인덱스의 종류 >

 

너무 세부적인 내용이 아닌 개괄적이고, 굵직굵직한 것만을 적어보도록 하겠다.

 

- B-tree 인덱스: DB 인덱싱 방법중 가장 일반적이고 먼저 도입되었음. B-tree 알고리즘 사용

 

- Hash 인덱스 : Hash 인덱스 알고리즘 사용. 동등비교 검색에 최적화 되어있다. 메모리 기반 테이블에 주로 사용되며 대용량 디스크 기반 테이블에는 사용되지 않음.

 

- R-tree 인덱스 : 2차원의 데이터를 인덱싱하고 검색하는 목적의 인덱스, Spatial Index라고도 불린다.

 

- Fractal-Tree 인덱스 : 가장 최근에 개발된 대용량 데이터 처리에 적합한 알고리즘. 독점 특허 등록으로 DBMS에 구현되지 못하고 있다. 랜덤 I/O를 순차I/O로 변환해 처리할 수 있는것이 큰 장점. 

 

- 전문검색(Full-text search) 인덱스 : 문서 내용 전체를 인덱스화 하여 특정 키워드가 포함된 문서 검색을 지원. 인덱싱 기법으로 크게 구분자(Stopwords)와 N-그램(N-gram) 기법이 있다. B-tree로 대체 불가능하다.

 

- 비트맵 인덱스 및 함수기반 인덱스 : MySQL에서 지원하지 않음. 함수기반 인덱스는 우회하여 쉽게 만들 수 있지만, 비트맵인덱스는 대안조차 없다.

 

- 클러스터링 인덱스 : InnoDB 및 TokuDB 엔진만 제공, 테이블의 Primary Key에 대해서만 적용되는 내용, 모든 동작이 Primary key에 많이 의존적임.

 

클러스터드 & 논 클러스터드 인덱스

	클러스터드 인덱스	넌 클러스터드 인덱스
차이	물리적으로 행을 재배열	물리적으로 재배열 하지 않는다.
크기	인덱스 페이지 용량이 넌 클러스터드 인덱스 페이지 용량보다 작다.	클러스터드 인덱스 페이지 용량보다 크다.
선택도	30% 이내에서 사용해야 좋은 선택도	3% 이내에서 사용해야 좋은 선택도
최대 갯수	테이블당 1개	테이블당 249개