[DB] 인덱스

• 인덱스는 데이터베이스 테이블의 특정 칼럼에 대해 생성되는 구조로, 데이터 검색을 더 빠르게 수행할 수 있도록 도와주는 도구이다.
• 검색 속도 향상: 전체 테이블을 스캔하는 것보다 훨씬 빠르게 데이터를 찾을 수 있다.
• 정렬 및 필터링 성능 개선: 데이터 정렬 및 필터링 작업의 성능을 향상시킨다.
• Unique Constraint 지원: 유니크 인덱스는 테이블 내에서 중복된 데이터를 방지할 수 있다.
• 디스크 공간 소모: 인덱스를 생성하면 추가적인 디스크 공간이 필요하다.
• 데이터 수정 시 성능 저하: 삽입, 삭제, 업데이트 작업이 발생할 때 인덱스도 함께 업데이트되므로 성능이 저하된다.

 

Full Table Scan이 더 좋은 경우

• 테이블에 데이터가 몇십 또는 몇 백 건밖에 없는 경우, 전체 테이블을 스캔하는 것이 인덱스를 사용하는 것보다 빠를 수 있다.
• 조회하려는 조건의 데이터가 테이블의 상당 부분을 차지하는 경우, 전체 테이블을 스캔하는 것이 더 효율적일 수 있다.

 

Covering Index

SELECT 절에 칼럼들이 인덱스에 모두 포함될 경우, 실제 테이블 데이터에 접근할 필요가 없다. 이러한 경우에 인덱스를 커버링 인덱스라고 한다.

• 성능 향상: 인덱스만으로 쿼리 결과를 반환할 수 있어 조회 성능이 매우 빠르다.
• 쿼리 효율성: 테이블 접근을 줄이므로 I/O 작업이 감소한다.

 

복합 인덱스

여러 칼럼을 이용하여 생성된 인덱스이다.

• 다중 칼럼 검색 최적화: 두 개 이상의 칼럼을 포함하는 쿼리에서 성능을 향상한다.
• 부분적 인덱스 사용: 인덱스의 첫 번째 또는 첫 번째와 두 번째 칼럼만 사용하는 쿼리에서도 성능이 개선될 수 있다.
• 인덱스 크기: 복합 인덱스는 단일 인덱스에 비해 크기가 클 수 있다.
• 단일 칼럼 인덱스보다 성능 저하: 복합 인덱스가 포함된 모든 칼럼이 검색 조건에 포함되지 않으면 성능이 떨어질 수 있다.

 

Optimizer

옵티마이저(Optimizer)는 DBMS의 핵심 컴포넌트로, 쿼리 실행 계획을 생성하고 쿼리 성능을 최적화하는 역할을 한다. 옵티마이저는 다음과 같은 작업을 수행한다.

• 인덱스 선택: 쿼리를 실행할 때 사용할 인덱스를 결정한다. 이는 쿼리의 조건, 데이터 분포, 테이블의 구조 등에 따라 달라질 수 있다.
• 실행 계획 생성: 다양한 실행 계획을 고려하여 최적의 실행 계획을 선택한다. 이 과정에서 인덱스를 사용하는 것뿐만 아니라, 조인 방식, 데이터 접근 방법 등을 결정한다.
• 성능 개선: 옵티마이저는 쿼리의 실행 속도를 향상하기 위해 쿼리 리팩토링, 인덱스 활용, 조인 전략 등의 최적화 기법을 적용한다.

 

클러스터 인덱스 (Clustered Index)

클러스터 인덱스는 데이터가 인덱스의 순서에 따라 물리적으로 정렬되어 저장되는 인덱스이다. PK에 기본적으로 클러스터 인덱스가 적용되는 데이터베이스들이 있다.

• 정렬: 데이터 페이지는 클러스터 인덱스 순서에 따라 정렬된다.
• 유일성: 각 테이블에는 하나의 클러스터 인덱스만 존재할 수 있다.
• 범위 쿼리 최적화: 범위 검색에 효율적이기 때문에 날짜 범위 쿼리 등 정렬된 결과가 필요한 경우에 유리하다.
• 삽입 및 삭제 성능: 데이터 정렬로 인해 삽입 및 삭제 작업이 복잡할 수 있다.

 

논클러스터 인덱스 (Non-Clustered Index)

논클러스터 인덱스는 테이블의 데이터와 별도로 인덱스를 구성하는 방식이다. 데이터는 클러스터 인덱스의 순서와 무관하게 저장되며, 인덱스는 데이터의 위치를 참조하는 포인터를 저장한다.

• 포인터 저장: 논클러스터 인덱스는 인덱스 키와 해당 키에 대한 데이터 페이지의 위치를 저장한다.
• 다중 인덱스: 테이블당 여러 개의 논클러스터 인덱스를 생성할 수 있다.
• 삽입 및 삭제 성능: 인덱스 관리에 오버헤드가 발생한다.
• 조회 성능: 데이터 페이지에 추가적인 접근이 필요할 수 있다.
• 디스크 공간 사용: 인덱스가 데이터와 별도로 저장되므로 디스크 공간을 더 많이 사용할 수 있다.
• 특정 칼럼 검색: 특정 칼럼에 대한 검색이 빈번하거나, 정렬되지 않은 데이터에 대한 검색이 필요한 경우 유용하다.

 

유니크 인덱스

유니크 키 제약 조건을 설정하면, 데이터베이스는 자동으로 유니크 인덱스를 생성하여 중복 값을 방지한다. 직접 유니크 인덱스를 생성하는 것과 결과적으로 동일한 효과를 가지게 된다.

 

Hash Index

해시 인덱스는 해시 테이블을 사용하여 인덱스를 구현하는 방식이다. 데이터 검색이 매우 빠르지만, 범위 검색에는 적합하지 않다.

• 빠른 검색 성능: 평균적으로 O(1)의 시간 복잡도를 가지며, 매우 빠르다.
• 범위 검색 불가: 해시 인덱스는 해시값을 기준으로 검색하므로 범위 검색이 불가능하다.
• 리해싱 부담: 데이터 양이 변경될 때 해시 테이블의 크기를 조정해야 하는 부담이 있다.
• 정확한 일치 검색: 특정 값에 대한 정확한 검색이 필요한 경우 유용하다.
• 해시 인덱스는 일반적으로 MySQL의 MEMORY 스토리지 엔진에서 사용된다.

 

B-Tree

B트리는 이진트리에서 발전되어 모든 리프노드들이 같은 레벨을 가질 수 있도록 자동으로 벨런스를 맞추는 트리이다. 또한 정렬된 순서를 보장하고, 멀티레벨 인덱싱을 통한 빠른 검색을 할 수 있기 때문에 인덱스에서 주로 사용하는 자료 구조이다.

• B-tree는 노드가 자식 노드로 확장되며, 각 노드는 정렬된 키 값과 포인터를 포함한다.
• 노드가 가득 차면 분할되고, 노드가 비면 병합된다.
• 클러스터드 인덱스와 논클러스터드 인덱스 모두 B-tree 구조를 사용할 수 있다.
• 모든 리프 노드가 동일한 깊이에 있어 균형을 유지하므로 일정한 시간복잡도를 제공한다.

 

B-tree 기반의 인덱스가 동작하는 방식

단일 인덱스

CREATE TABLE members (
  a INT NOT NULL,
  b INT NOT NULL,
  c INT NOT NULL,
  INDEX idx_a (a)
);

 

인덱스를 설명하기 위한 예시

a	해당 값이 저장된 데이터의 위치를 가리키는 포인터
1	…
2	…
5	…
6	…
8	…

 

단일 조건 검색

WHERE a = 9;

B-tree 인덱스를 사용하여 a 값이 9인 튜플의 위치를 빠르게 찾는다.

 

복합 조건 검색

WHERE a = 8 AND b = 95;

a 값이 8인 튜플들을 먼저 B-tree 인덱스에서 찾는다. 이후, 해당 튜플들 중 b 값이 95인 튜플을 실제 데이터에서 추가로 찾는다. 인덱스는 a 값에 대해 효율적으로 검색하지만, b에 대해서는 인덱스가 없으므로 데이터 페이지를 스캔해야 한다.

 

부분 조건 검색

WHERE b = 95;

b 칼럼에 대한 인덱스가 없으므로, 전체 테이블을 스캔하여 b 값이 95인 튜플을 찾는다. 이 경우, B-tree 인덱스는 사용되지 않는다.

 

OR 조건 검색

WHERE a = 8 OR b = 92;

a = 8에 대해서는 B-tree 인덱스를 사용하여 빠르게 튜플을 찾을 수 있지만, b = 92에 대해서는 인덱스가 없으므로 풀스캔이 필요하다. OR 조건은 두 조건의 결과를 결합해야 하므로, a = 8의 결과와 b = 92의 결과를 합쳐야 한다.

복합 인덱스

CREATE INDEX idx_ab ON members (a, b);

 

인덱스를 설명하기 위한 예시

a	b	해당 값이 저장된 데이터의 위치를 가리키는 포인터
1	92	…
2	92	…
5	39	…
6	100	…
8	95	…

 

단일 조건 검색

WHERE a = 8;

a에 대한 조건이 인덱스의 첫 번째 칼럼이므로, 복합 인덱스를 사용할 수 있다. 인덱스는 a 값이 8인 레코드를 빠르게 찾고, 해당 레코드를 조회한다.

 

복합 조건 검색

WHERE a = 8 AND b = 95;

복합 인덱스를 사용하여 a와 b 두 칼럼 모두에 대한 조건을 검색할 수 있다. 인덱스는 a 값이 8인 레코드를 먼저 찾고, 그중 b 값이 95인 레코드를 빠르게 찾는다. 인덱스가 두 칼럼 모두를 포함하므로 효율적인 검색이 가능하다.

 

부분 조건 검색

WHERE b = 95;

복합 인덱스의 첫 번째 칼럼인 a를 사용하지 않기 때문에, 인덱스를 사용할 수 없다. 이 경우, 전체 테이블을 스캔하여 b 값이 95인 레코드를 찾아야 한다. 복합 인덱스는 첫 번째 칼럼의 조건을 필수로 사용해야 하므로, b만으로 검색이 불가능하다.

 

OR 조건 검색

WHERE a = 8 OR b = 92;

a = 8에 대해서는 복합 인덱스를 사용할 수 있지만, b = 92에 대해서는 인덱스를 사용할 수 없다. 이 경우, a = 8에 대한 검색 결과와 b = 92에 대한 검색 결과를 합쳐야 하므로, 인덱스를 효율적으로 활용할 수 없다. b에 대한 조건은 복합 인덱스에서 첫 번째 칼럼이 없으므로 풀 스캔이 필요하다.

 

인덱스 관련 명령어

명령어	설명	결과 컬럼	컬럼 설명
SHOW INDEX	테이블의 인덱스 정보를 조회한다.	Table	테이블 이름
		Non_unique	인덱스 유니크 여부 (0: 유니크, 1: 비유니크)
		Key_name	인덱스 이름
		Seq_in_index	컬럼 순서
		Column_name	컬럼 이름
		Collation	정렬 방식
		Cardinality	예상 고유 값 수
		Sub_part	부분 인덱스 길이 (부분 인덱스가 아닌 경우 NULL)
		Packed	인덱스 압축 정보 (압축된 경우)
		Null	NULL 허용 여부 (YES/NO)
		Index_type	인덱스 유형 (BTREE, FULLTEXT 등)
		Comment	추가 설명
EXPLAIN	SQL 쿼리가 어떻게 실행될지를 보여주는 명령어로 쿼리 실행 계획을 분석한다.	id	쿼리 단계
		select_type	쿼리 유형 (SIMPLE, PRIMARY, SUBQUERY 등)
		table	테이블 이름
		type	조인 방식 (ALL, INDEX, RANGE 등)
		possible_keys	사용 가능한 인덱스 목록
		key	실제 사용된 인덱스
		key_len	인덱스 키 길이
		rows	예상 결과 행 수
		Extra	추가 정보 (Using where, Using index 등)
USE INDEX	인덱스 사용을 권장하지만, 반드시 사용하라고 강제하지는 않는다. 만약 주어진 인덱스가 유효하지 않거나 더 나은 인덱스가 존재하면 다른 인덱스를 사용할 수 있다.	SELECT * FROM members USE INDEX (idx_ab) WHERE a = 8;
FORCE INDEX	인덱스를 강제로 사용하도록 한다.	SELECT * FROM members FORCE INDEX (idx_ab) WHERE a = 8;
IGNORE INDEX	특정 인덱스를 무시하고 쿼리를 실행한다.	SELECT * FROM members IGNORE INDEX (idx_ab) WHERE a = 8;