[ SQL ] 인덱스와 동작 원리

1. 인덱스 개념과 사용 이유

---

1-1. 인덱스 기본 개념

---

우리 옆에 있는 한 권의 책에 대해서 생각해보자.

만약, 목차나 찾아보기가 없다면, 해당 책에서 특정 내용을 찾기 위해서 모든 페이지를 찾아봐야 한다.

 

인덱스는 책에서 찾아보기의 역할을 한다.

인덱스에 대해 간단히 정의하자면, 데이터를 빠르게 찾기 위해 검색 경로를 미리 정리해둔 자료구조이다.

인덱스는 필수는 아니지만, 인덱스가 없다면 데이터가 많을 수록 SELECT 쿼리의 수행 시간이 길어질 것이다.  

1-2. 인덱스 장단점

---

• 장점
  • SELECT 쿼리의 속도가 매우 빨라짐
  • 결과적으로, 조회 쿼리가 많은 상황에서 컴퓨터 부담이 줄어들기에 시스템 성능이 개선됨
• 단점
  • 인덱스는 자료구조이기에 데이터베이스 내부 추가 공간을 사용함 ( 약 10 ~ 20% )
  • 인덱스 생성 시 상당히 오랜 시간이 소요되므로, 미리 만들어두는 것이 좋음
  • 데이터 변경 쿼리(INSERT, UPDATE, DELETE)가 많으면, 오히려 성능이 나빠질 수 있음 ( 페이지 분할 )

 

2. 인덱스 종류

---

2-1. 클러스터형 인덱스 ( Primary Index )

---

• 특징
  • 인덱스 대상 : 테이블의 어떤 칼럼(속성) 자체가 인덱스로서 동작함
  • 테이블 당 수 : 테이블 당 한 개만 존재함
  • 데이터 정렬 여부 : 클러스터형 인덱스를 기준으로 데이터가 자동으로 정렬됨
• 생성 방법
  • PK( Primary Key ) 제약 조건의 칼럼이 자동으로 클러스터형 인덱스로 등록됨
  • 만약, PK가 없다면 "UNIQUE + NOT NULL" 제약조건이, 그것도 없으면 ROW ID가 클러스터형 인덱스가 됨

2-2. 보조 인덱스 ( Secondary Index )

---

• 특징
  • 인덱스 대상 : 테이블의 어떤 칼럼을 참조하는 별도의 인덱스 공간이 생성됨
  • 테이블 당 수 : 테이블 당 여러개의 보조 인덱스가 존재할 수 있음
  • 데이터 정렬 여부 : 테이블 내 데이터는 정렬되지 않음
• 생성 방법
  • UNIQUE 제약조건을 갖는 칼럼이 자동으로 UNIQUE 보조 인덱스가 됨

 

3. 인덱스 동작 원리

---

3-1. 인덱스의 균형 트리

---

3-1-1. 트리의 기본 개념

인덱스는 기본적으로 균형 트리나 B+ 트리와 같은 트리 기반으로 동작한다

트리는 루트 노드, 중간 노드, 리프 노드로 구성되며, 루트 노드는 단 한개만 존재한다.

 

데이터베이스는 트리를 탐색할 때, 루트 노드 → 중간 노드 → 리프 노드 순으로 탐색한다.

MySQL은 노드를 페이지라 부르고, B+ 트리를 기본값으로 사용한다.

3-1-2. 인덱스가 없는 경우, SELECT 쿼리 동작 원리

인덱스가 없는 경우, 트리 내 모든 페이지를 탐색해야 할 것이다.

이러한 상황을 전체 테이블 스캔( Full Table Scan )이라 부르고, 이는 시간이 매우 오래 걸리는 작업이다

3-2. 클러스터형 인덱스 동작 원리

---

클러스터형 인덱스를 사용한다면, 데이터는 해당 인덱스를 기준으로 정렬되어 있는 상태이고, 리프 페이지는 실제 데이터를 갖는다.

 

아래는 Hat이라는 데이터를 찾을 때, 클러스터형 인덱스의 SELECT가 동작하는 방식이다.

1. 루트 페이지에서 Hat이 있는 중간 페이지의 메모리 주소를 읽음
2. 해당 중간 페이지에서 Hat이 있는 리프 페이지의 메모리 주소를 읽음
3. 해당 리프 페이지를 탐색하여 Hat row를 찾음

이처럼 클러스터형 인덱스는 실제 데이터를 리프 노드에 저장하고, 해당 데이터를 읽기에 보조 인덱스에 비해서 상대적으로 빠르다.

그렇다면 숫자 PK랑 문자열 PK 중 인덱스를 사용했을 때 더 효율이 좋은 것은 무엇일까?

정답은 숫자 PK를 사용하는 경우이다.
숫자 PK는 문자열에 비해 비교 연산의 단순성, 정렬 및 조인의 오버헤드 크기, 데이터 크기 등이 좋기에 데이터를 정렬 및 저장해야하는 인덱스 사용 시 더 효율적이다.

3-3. 보조 인덱스 동작 원리

---

보조 인덱스는 데이터를 정렬하지 않는다.

대신, 실제 데이터가 있는 데이터 페이지와 별개로 인덱스 페이지를 생성하여 인덱스 키를 정렬 및 관리한다.

이 때, 인덱스 페이지의 리프 페이지는 실제 데이터의 인덱스 키와 데이터의 메모리 주소를 갖는다.

 

아래는 Hat이라는 데이터를 찾을 때, 보조 인덱스의 SELECT가 동작하는 방식이다.

1. 인덱스 페이지의 루트 페이지에서 Hat이 있는 중간 페이지의 메모리 주소를 읽음
2. 인덱스 페이지의 해당 중간 페이지에서 Hat이 있는 리프 페이지의 메모리 주소를 읽음
3. 인덱스 페이지의 해당 리프 페이지를 탐색하여 Hat에 대한 메모리 주소를 읽음
4. 해당 메모리 주소를 통해 데이터 페이지에서 실제 Hat row를 읽음

이처럼 보조 인덱스는 클러스터형 인덱스와 달리 리프 노드의 메모리 주소를 통해 실제 row를 읽는 단계가 추가된다.

따라서 클러스터형 인덱스에 비해 상대적으로 느리지만, 그럼에도 매우 빠르고 하나의 테이블에 여러개의 보조 인덱스를 생성할 수 있다는 장점이 있다.

3-4. 페이지 분할 : 인덱스의 문제점

---

인덱스를 사용할 때, 데이터 변경 쿼리(INSERT, UPDATE, DELETE) 실행 시 페이지 분할로 인해 속도가 느려진다.

여기서 페이지 분할은 데이터 추가 및 변경 등의 상황에서 페이지 공간 부족으로 인해 페이지를 여러개로 분리하는 것이다.

 

아래는 그 대표적인 예시로, 우선 아래와 같이 각 페이지에 담을 수 있는 데이터 한계가 3개일 때를 가정해보자.

'

여기서 Page라는 데이터를 추가할 때, 페이지 분할로 인해 3개의 테이블이 추가로 생성된다.

1. addr=1006인 리프 페이지에 Page를 추가하지만, 공간이 없으므로 리프 페이지 분할
2. 루트 페이지에 새로 추가된 페이지 주소를 추가하려고 하지만, 공간이 없으므로 루트 페이지 분할
3. 루트 페이지는 하나만 있어야 하지만, 분할해야 하므로 기존 루트 페이지를 중간 페이지로 갖는 새로운 루트 페이지 추가

따라서 아래와 같은 페이지 구조로 변할 것이다.

따라서, 현재 서비스에서 자주 발생하는 요청에 대해서 명확히 파악하고, 인덱스를 적용했을 때의 효과에 대해 고려하여 인덱스를 사용하는 것이 가장 중요할 것이다.

현재는 매우 단순한 트리 구조이기에 그 여파가 크지 않았다.
하지만, 수백 수천만개의 데이터를 관리하는 데이터베이스라면, 데이터 추가 및 변경 쿼리가 지금보다는 많은 페이지 분할로 이어지는 문제가 발생할 것이고, 이는 데이터베이스 및 시스템 성능 저하로 이어질 것이다.

3-5. InnoDB 스토리지 엔진의 인덱스 구조 관리

간단하게 설명하자면 InnoDB 스토리지 엔진을 사용하는 MySQL와 다른 데이터베이스는 인덱스 관리 방법이 다르다.

InnoDB 스토리지 엔진은 리프 노드에 Primary Key를 저장하지만, 다른 스토리지 엔진은 메모리 주소를 저장함

해당 내용에 대해서는 추후 InnoDB 스토리지 엔진과 다른 스토리지 엔진의 데이터 및 인덱스 관리법을 통해 보다 자세히 다뤄볼 예정이다.

 

4. 인덱스 사용법

---

4-1. 인덱스 수동 생성

---

클러스터형 인덱스 및 보조 인덱스의 자동 생성 방법 외, SQL 문법을 활용하여 인덱스를 생성할 수 있다.

CREATE [ UNIQUE ] INDEX "인덱스명" ON "테이블명" ( "컬럼명" [ASC | DESC] );

여기서 UNIQUE 옵션은 해당 칼럼에 대한 유니크 인덱스를 추가하는 역할을 한다.

유니크 인덱스는 중복 값을 허용하지 않기 때문에, 사용 시 주의가 필요하다.

 

인덱스를 생성한 후에는 아래와 같은 명령어를 실행하여 통계 정보 갱신 및 인덱스를 적용해야 한다.

ANALYZE TABLE "테이블명";

4-2. 인덱스 수동 제거

---

인덱스 제거 시에는 아래 명령어를 실행하면 된다.

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

이 때 주의할 점은, 클러스터형 인덱스를 FK로 참조하고 있는 테이블이 있다면 직접 DROP을 할 수 없다.

따라서 아래 명령어를 통해 연관 FK를 확인 및 제거해야 한다.

SELECT table_name, constraint_name
FROM information_schema.referential_constraints
WHERE constraint_schema="DB이름";

이처럼 명령어를 실행하여 FK를 탐색 및 제거 후, PK 인덱스를 제거하면 된다.

4-3. 인덱스가 실제로 사용되지 않는 경우

---

모든 쿼리에서 인덱스가 사용되지는 않는다.

기본적으로 인덱스는 인덱스로 지정된 칼럼이 Where 절에 속해있는 경우에만 실행된다.

다만, 아래와 같은 경우에는 예외적으로 인덱스가 실제로 사용되지 않는다.

1. Where 절에 인덱스 칼럼이 있지만, 대부분의 데이터가 조건을 만족하여 Full Table Scan이 더 효율적인 경우
2. Where 절에 인덱스 칼럼이 있지만, 칼럼에 함수 및 계산을 직접 적용하는 경우
3. Where 절에 인덱스 칼럼이 없는 경우
4. Where 절에서 LIKE "%문자열"과 같은 와일드 카드를 사용하여 인덱스 범위 탐색이 불가한 경우

따라서 해당 내용들을 적절히 숙지하여 인덱스가 사용되어야 하지만, 사용되지 않는 문제를 최대한 예방하는 것이 중요할 것이다.

 

5. 마무리

---

이번에는 인덱스에 대해서 알아보았다.

 

인덱스는 매우 강력한 도구이지만, 모든 상황에 효율적인 것은 아니다.

 

만약, 서비스에서 발생하는 쿼리의 대부분이 조회 쿼리이고 발생 빈도가 매우 잦다면 인덱스를 사용하여 조회 성능을 개선하는 것이 서비스 성능 개선에 도움이 될 것 이다.

 

하지만, 조회 쿼리 외 INSERT 등의 쿼리가 자주 발생하는 서비스라면, 인덱스를 사용하는 것이 오히려 문제가 될 수 있다.

 

또한, 무분별한 인덱스 생성은 새로운 트리를 생성하기에 데이터베이스의 저장공간에 부정적인 영향을 줄 수 있다.

 

따라서 이러한 부분을 적절히 숙지하고 인덱스를 사용하는 것이 좋을 것이다.