인덱스(Index)

1. 인덱스란?

인덱스란 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조이다. → 예를 들어서 우리는 책에서 목차를 통해 원하는 곳으로 빠르게 이동할 수 있다.
실제 DB 관련 작업 시 대부분의 속도 저하의 원인은 조회의 WHERE 문에서 발생하는데, 가장 먼저 생각해 볼 수 있는 대안으로 Index 를 생각할 수 있다.

1-1. 인덱스 사용

-- MySQL
CREATE INDEX USER_COMPANY_INDEX ON USER(COMPANY_ID);

CREATE INDEX: 인덱스 생성 명령어
USER_COMPANY_INDEX: 인덱스 이름
ON USER(): 테이블 지정 명령어
COMPANY_ID: 인덱스를 걸어줄 컬럼명
- 여러 컬럼을 걸어주고 싶다면, (COMPANY_ID, EMAIL_ID,…, ADDRESS) 이런 식으로 설정해주면 된다.

1-2. 인덱스 사용 시 주의점

DBMS 는 index 를 항상 최신의 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다. 그렇기 때문에, 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE 가 수행된다면 각각 다음과 같은 연산을 추가적으로 해주어야 하면 그에 따른 오버헤드가 발생한다.
- INSERT : 새로운 데이터에 대한 인덱스를 추가
- DELETE : 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행
- UPDATE : 기존의 인덱스를 사용하지 않음 처리하고, 갱신 된 데이터에 대해 인덱스를 추가

1-3. 모든 컬럼에 인덱스를 걸어야 할까?

무분별한 인덱스를 설정하는 것은 위에서 설명한대로 INSERT, DELETE, UPDATE 시 오버헤드가 있기 때문에, 권장하지 않는다.
또한 인덱스는 DB 메모리를 사용하여 테이블 형태로 저장하기 때문에, 인덱스의 개수와 사용 중인 저장 공간이 비례한다.
즉, 인덱스를 많이 설정하면, 메모리 사용량과 사용 중인 저장 공간이 많아져 성능 저하의 가능성이 있다.

1-4. 그렇다면 언제 인덱스를 사용하는가?

카디널리티(Cardinality) 가 높고 활용도가 높을 수록 인덱스를 설정하기 좋은 컬럼이다.
또한, 규모가 큰 테이블이나 INSERT, DELETE, UPDATE 가 자주 발생하지 않는 컬럼에서 사용하기 적합하다.

DB 에서 카디널리티란 컬럼의 고유한 값의 개수를 나타내는 수치다. → 예를 들어, “커피” 라는 테이블이 내 “메뉴명” 이라는 컬럼이 있을 때, 아래의 데이터의 카디널리티는 4이다. → 카디널리티가 높을 수록 컬럼 값의 중복이 없다는 것을 의미한다.

아메리카노
카페라테
콜드브루
아메리카노
아인슈페너

카디널리티가 낮을수록 인덱스가 중복된 값들을 가지게 되므로 인덱스를 사용하여 특정 값을 찾는 동작이 비효율적일 수 있다.
활용도는 말 그래도 해당 컬럼이 실제 작업에서 얼마나 활용 되는지에 대한 값이다. 특히, WHERE 절에 자주 사용되는지 확인해보면 활용도를 판단할 수 있다.

2. 클러스터 인덱스

→ 책 페이지를 알기에 바로 펼침

클러스터 인덱스를 구성하기 위해서 데이터 페이지를 정렬한 후, 루트페이지를 만들게 된다.
- 클러스터 인덱스가 생성될 때, 데이터 페이지는 클러스터 인덱스 키 값에 따라 정렬되며, 이 정렬된 데이터에 기반하여 인덱스 트리가 구성됩니다. 루트 페이지는 인덱스 트리의 최상위 노드입니다.
클러스터 인덱스는 트리로 저장되어, Root 페이지와 Leaf 페이지로 구성된다.
- Root 페이지 : Leaf 페이지의 주소를 구성한다. 인덱스 트리의 최상위 노드로, 데이터 접근의 시작점이다.
- Leaf 페이지 : 실제 데이터 페이지로, 테이블의 데이터가 클러스터 인덱스 순서에 따라 저장됩니다.
클러스터 인덱스는 테이블 당 하나만 존재한다! → 물리적으로 정렬된 상태를 유지해야하기 때문이다.
클러스터 인덱스는 테이블의 데이터 레코드들이 인덱스 순서에 따라 물리적으로 정렬되어 저장됩니다.
클러스터 인덱스를 따로 지정하지 않으면 기본 키(PK) 가 클러스터 인덱스가 된다. → 즉 테이블 생성 시 PK 를 지정하면 자동적으로 클러스터 인덱스가 생성된다.
데이터 입력, 수정, 삭제 시 항상 정렬 상태를 유지한다.
클러스터 인덱스는 물리적으로 정렬되어 있어서, 검색 속도가 넌 클러스터 인덱스에 비해서 빠르지만 입력, 수정, 삭제는 느리다.
데이터 검색 순서 : 루트 페이지 → 리프 페이지(데이터 페이지)

2-1. 클러스터 인덱스를 언제 사용하는가?

테이블 데이터가 자주 업데이트 되지 않는 경우
- 클러스터 인덱스는 데이터 변경 시 오버헤드가 크므로, 자주 없데이트되지 않는 테이블에 적합하다.
범위 조회 또는 Group By 등의 쿼리
- MAX, MIN, COUNT 등의 쿼리나 특정 범위를 조회하는 경우 클러스터 인덱스가 효율적입니다.
항상 정렬된 방식으로 데이터를 반환해야 하는 경우
- 테이블은 항상 정렬되어 있기 때문에, ORDER BY 절을 활용해 모든 테이블 데이터를 스캔하지 않고 원하는 데이터를 조회할 수 있다.
읽기 작업이 월등히 많은 경우
- 클러스터 인덱스는 읽기 작업에서 매우 빠른 성능을 제공합니다.

2-2. 클러스터 인덱스 단점

데이터페이지 분할
- 데이터 페이지가 모두 찬 상태에서 새로운 데이터가 추가되면 페이지 분할이 발생한다. 이는 클러스터 인덱스가 B-Tree 구조를 가지므로, 새로운 데이터 추가 시 기존 데이터의 절반이 새로운 페이지로 이동한 후 새로운 데이터가 추가된다.
항상 순서를 유지해야 한다.
- 클러스터 인덱스는 데이터의 물리적 정렬을 유지해야 하므로, 추가적인 오버헤드가 발생할 수 있습니다. 이는 데이터 변경 작업에서 성능 저하로 이어질 수 있다.

3. 넌 클러스터 인덱스

-> 넌 클러스터 인덱스는 목차를 통해 페이지를 찾아감

넌 클러스터 인덱스는 데이터 페이지를 변경하지 않고, 별도의 장소에 인덱스 페이지를 생성한다.
인덱스 페이지 구성
- 인덱스 페이지의 리프 레벨은 인덱스로 구성된 열을 정렬한 후 위치 포인터(RID) 를 생성한다.
- 넌 클러스터링 인덱스의 인덱스 페이지는 키 값과 데이터의 위치를 표시하는 위치 포인터(RID) 로 구성된다.
추가 저장 공간
- 각 넌 클러스터링 인덱스마다 인덱스 페이지를 할당한다. (각 인덱스의 저장 영역은 고유하다) → 클러스터링 인덱스는 개수가 1개이기 때문에, 공간을 나눈다는 생각 자체가 무의미하다.
- 인덱스를 생성할 때 데이터 페이지는 그대로 두고 별도의 인덱스 페이지를 따로 만들기 때문에, 추가 저장 공간을 차지한다.
인덱스 수
- 테이블 당 약 240개의 넌 클러스터링 인덱스를 만들 수 있다.
성능 특성
- 검색 속도는 클러스터 인덱스보다는 느리지만 입력, 수정, 삭제는 더 빠르다.
- 리프 페이지가 모두 차 있어서 페이지 분할이 이루어지지 않는다.
데이터 검색 순서 : 루트 페이지 → 리프 페이지 → 데이터 페이지

3-1. 넌 클러스터 인덱스를 언제 사용하는가?

WHERE 절이나 JOIN 절과 같이 조건문을 활용하여 테이블을 필터링 하고자 할 경우
- 넌 클러스터 인덱스는 특정 조건에 맞는 데이터를 빠르게 찾을 수 있게 해준다.
데이터가 자주 업데이트될 경우
- 클러스터 인덱스보다 업데이트 시 성능이 더 좋음 → 물리적 정렬이 이루어지지 않기 때문에!
특정 컬럼이 쿼리에서 자주 사용되는 경우
- 자주 조회되는 컬럼에 인덱스를 추가해 성능을 향상

3-2. 넌 클러스터 인덱스의 단점

추가 저장 공간 필요
- 클러스터 인덱스는 테이블 정렬만 하면 되지만, 넌 클러스터 인덱스는 인덱스 페이지만을 위한 추가 공간이 필요하다.
추가 작업 필요
- 데이터 삽입 시 넌 클러스터 인덱스는 별도의 공간에 인덱스를 생성해야 하기 때문에, 추가 작업이 필요하다. → 하지만 클러스터 인덱스의 정렬 작업보다는 추가 작업이 적다. (리밸런싱 보다는 낫다)
느린 데이터 접근 속도
- 넌 클러스터 인덱스는 클러스터 인덱스에 비해 상대적으로 느리다. → 물론 풀스캔이랑은 비교가 안된다.
- 인덱스 조회 시 비용이 많이 발생한다.(거쳐야 하는 단계가 많다)
- 검색하려는 데이터의 키 값을 루트 페이지에서 비교하여, 리프 페이지 번호를 찾고, 리프페이지에서 RID 정보로 실제 데이터 위치로 이동해야 한다.

4. 커버링 인덱스

커버링 인덱스란, 쿼리를 충족시키는데 필요한 모든 데이터를 갖고 있는 인덱스를 말한다. → 즉 데이터베이스 엔진이 테이블의 데이터 페이지에 접근할 필요 없이 인덱스만으로 쿼리를 처리할 수 있다. (이는 성능을 크게 향상시킨다)

4-1. 예시

‘employees’ 테이블 생성

CREATE TABLE employees (
	employee_id INT PRIMARY KEY, 
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	department VARCHAR(50), 
	salary DECIMAL(10, 2)
);

커버링 인덱스 생성

CREATE INDEX inx_employees_last_first_dept 
ON employees(last_name, first_name, department);

커버링 인덱스 동작 O

커버링 인덱스 사용 쿼리

SELECT last_name, first_name, department
FROM employees
WHERE last_name = 'Smith';

커버링 인덱스 동작 방식
1. 인덱스 탐색
  - 데이터베이스 엔진은 ‘idx_employees_last_first_dept’ 인덱스를 사용해 ‘last_name’ 이 ‘Smith’ 인 모든 인덱스 항목을 찾는다.
2. 데이터 검색
  - 이 인덱스에는 ‘last_name’, ‘first_name’, ‘department’ 가 모두 포함되어 있으므로, 데이터 베이스 엔진은 테이블의 데이터 페이지를 탐색할 필요 없이 인덱스 페이지에서 필요한 모든 데이터를 얻을 수 있다.
3. 쿼리 결과 반환
  - 인덱스만으로 ‘last_name’, ‘first_name’, ‘department’ 값을 모두 제공할 수 있으므로, 쿼리를 매우 빠르게 처리할 수 있다.

커버링 인덱스 동작 X

커버링 인덱스 미사용 쿼리

SELECT last_name, first_name, department, salary
FROM employees
WHERE last_name = 'Smith';

커버링 인덱스 미사용 시 동작 방식
1. 인덱스 탐색
  - 데이터베이스 엔진은 여전히 ‘idx_employees_last_first_dept’ 인덱스를 사용해여 ‘last_name’ 이 ‘Smith’ 인 모든 인덱스 항목을 찾는다.
2. 데이터 페이지 접근
  - 그러나, ‘salary’ 값은 인덱스에 포함되어 있지 않으므로, 데이터베이스 엔진은 테이블의 데이터 페이지에 접근하여, ‘salary’ 값을 조회해야 한다.
3. 추가적인 I/O
  - 이 추가적인 I/O 작업 때문에, 쿼리 성능이 떨어질 수 있다.

4-2. 커버링 인덱스의 장점

성능 향상 : 커버링 인덱스를 사용하면 테이블의 데이터 페이지에 접근할 필요가 없으므로 I/O 작업이 줄어들어 쿼리 성능이 향상된다. → 필요한 데이터가 인덱스 페이지에 모두 있기 때문에, 조회 결과가 빠를 수 밖에 없다.

4-3. 커버링 인덱스 단점

인덱스 크기 증가
- 커버링 인덱스는 쿼리에 필요한 모든 컬럼을 포함하므로, 인덱스의 크기가 커진다.
인덱스 유지 비용 증가
- 인덱스에 포함된 컬럼이 많을수록 INSERT, UPDATE, DELETE 작업 시 인덱스를 유지하기 위한 비용이 많이 든다.
특정 쿼리에 국한된 최적화

4-4. 커버링 인덱스 요약

커버링 인덱스는 하나의 인덱스가 쿼리에서 필요한 모든 컬럼을 포함할 때 발생하는 개념이다.
쿼리에서 사용하는 모든 컬럼을 가진 인덱스가 있는 경우, 데이터 페이지를 가지 않고, 처리가 가능하다. → I/O 작업 감소로 인해 성능 향상
커버링 인덱스는 클러스터 인덱스, 넌 클러스터 인덱스의 상위 개념으로 볼 수 있다.

Previous그 외 Next트랜잭션(TRANSACTION)

Last updated 1 year ago