인덱스(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이다. → 카디널리티가 높을 수록 컬럼 값의 중복이 없다는 것을 의미한다.

1. 아메리카노

2. 카페라테

3. 콜드브루

4. 아메리카노

5. 아인슈페너

• 카디널리티가 낮을수록 인덱스가 중복된 값들을 가지게 되므로 인덱스를 사용하여 특정 값을 찾는 동작이 비효율적일 수 있다.

• 활용도는 말 그래도 해당 컬럼이 실제 작업에서 얼마나 활용 되는지에 대한 값이다. 특히, 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 작업 감소로 인해 성능 향상

• 커버링 인덱스는 클러스터 인덱스, 넌 클러스터 인덱스의 상위 개념으로 볼 수 있다.