진짜 문제 나갑니다!! Index... 과연 무엇일까요?

들어가며

Database Indexing은 최신 데이터베이스에서 효율적인 데이터 검색을 위해 필수적이다. 기본키를 통해 레코드를 찾을 때 Index가 적용되어 결과를 빠르게 찾을 수 있다. 10만개가 넘는 데이터 중 내가 원하는 값을 가져오는데 시간이 매우 적게 든다면 더욱더 빠른 서비스를 제공할 수 있을 것이다. 이제 Index를 사용할 수 있도록 인덱스가 무엇이고, 어떻게 사용하는지 알아보자.

 

Index는 얼마나 빠르지?

설명을 위해 ID, Email, 이름, 나이, 핸드폰 번호라는 속성이 있는 간단한 전화번호부 테이블을 생각해보자. 테이블에서 데이터는 행으로 구성되며 각 행은 Record이다. 열은 레코드의 속성이나 특성을 정의한다. 100개의 데이터가 있는 테이블에서 내가 원하는 조건을 가지는 데이터를 검색할 때 어떻게 해야할까?

 

1. 고유하지 않은 열 검색

나이가 25살인 사람을 찾는다고 하자. 나이라는 값은 이 테이블에서 고유하지 않다. 즉 데이터(레코드)마다 중복되는 값이 있을 수 있다는 의미이다. 따라서 25살의 사람을 찾고 싶다면 반드시 모든 데이터를 확인해야한다. 모든 데이터를 확인할 때까지 25살인 사람이 몇 명 존재하는지 모르기 때문이다. 행이 N개 있다면 고유하지 않은 열을 찾을 땐 평균적으로 O(N)시간이 걸린다.

Baeldug : How Does Database Indexing Work? 참고

 

2. 고유한 열 검색

'bob@gmail.com' 메일을 가지는 사용자를 찾고 싶다고 가정해보자. 이메일은 전화번호부에서 고유하므로 원하는 값을 가지는 데이터를 찾으면 검색을 중단할 수 있다. 경우에 따라 한번 검색으로 데이터를 찾을 수도 있고, 모든 행을 검색해야만 데이터를 찾을 수도 있으므로 평적 O((N+1)/2)의 시간이 걸린다.

 

Baeldug : How Does Database Indexing Work? 참고

 

3. 기본 키 검색

이번엔 ID가 39인 사용자를 찾고 싶다고 가정해보자. ID라는 기본 키는 Index 덕분에 검색시간이 상당히 줄어들게 된다. N개의 행에 대해 평균적으로 O(logN)의 시간을 가진다. 데이터가 10,000,000개(N)라면 log₂(N) ≈ 24 단계만에 찾을 수 있다. 실제 DB 환경에서는 각 단계가 매우 빠르게 수행되므로, 전체 검색 시간은 밀리초 수준에 불과하다.

Baeldug : How Does Database Indexing Work? 참고

 

B-Tree

대부분의 RDBMS에선 자동으로 기본키로 인덱스를 생성한다.  이 인덱스는 보통 B-Tree or B+Tree 라는 자료구조로 저장되고 인덱스는 이러한 트리구조를 사용하기 때문에 매우 빠르다. B-Tree는 이진 트리를 확장해 하나의 노드가 가질 수 있는 자식 노드의 최대 숫자가 2보다 큰 트리 구조를 말한다. 이는 루트 노드에서 시작해 조건에 맞는 하위 노드로 이동하고, 그 과정을 원하는 데이터를 찾을 때까지 반복한다. 

 

B-Tree 이미지 출처: https://rebro.kr/169

 

여기서 8이라는 값을 찾을 때 몇 번의 비교가 필요한가? 만약 이 데이터들이 일렬로 나열되어 있었다면 찾기 꽤 어려웠을 것이다.!

 

실제로는 각 노드에 실제 데이터를 저장하지 않고 키와 데이터를 가리키는 포인터가 저장된다. 인덱스를 타는 쿼리를 실행하면 컬럼 값을 key로 하여 노드를 찾고 노드 내의 포인터를 통해 실제 데이터에 접근한다.

인덱스를 사용하는 방법

테이블에 기본키를 설정하면 인덱스를 생성할 수 있다. 테이블을 만들 때 기본키(Primary Key), 고유한(Unique) 컬럼, 외래키에는 자동으로 인덱스가 생성되기 때문이다.

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    name VARCHAR(50)
);

 

직접 인덱스를 추가하고 싶다면 CREATE INDEX라는 명령어를 통해 인덱스를 생성할 수 있다. 하나의 컬럼 뿐만 아니라 두 개 이상의 컬럼을 사용한 복합 인덱스로 생성할 수 있다.

-- 단일 컬럼 인덱스
CREATE INDEX idx_users_name ON users(name);

-- 복합 인덱스 (두 개 이상의 컬럼)
CREATE INDEX idx_users_name_age ON users(name, age);

 

쿼리를 작성하여 인덱스를 사용해보자. 

SELECT * FROM users WHERE name = 'John';

 

인덱스를 적용한 컬럼에 조건절을 포함하여 쿼리를 날리면 자동으로 인덱스가 사용된다. 하지만 쿼리를 사용한다고 해서 무조건 인덱스가 사용되는건 아니다. 쿼리가 인덱스를 타는 조건이어야 한다! 그리고 인덱스 컬럼에 함수나 연산을 걸면 인덱스 사용이 불가능할 수도 있다.

-- 인덱스 사용 안 하는 예시 (함수 사용)
SELECT * FROM users WHERE LEFT(name, 2) = 'Jo';

 

매번 이런걸 기억하기 싫다면 EXPLAIN을 사용하여 인덱스가 실제로 사용되는지 확인해보자.

type = ALL인 걸보니 Full Table Scan이므로 인덱스를 사용하지 않고 테이블 전체를 읽어야 한다는 것을 알 수 있고, possible_keys와 key 가 NULL이니 이 쿼리가 사용가능한 인덱스가 없고 실제 사용된 키도 없음을 알 수 있다.

 

주의할 점

복합 인덱스를 사용할 때는 순서가 매우 중요하다. 

복합 키를 사용하면 구조는 위와 같다.

- n: 노드 내의 키의 개수

- P: 자식 노드에 대한 포인터

- K: 키 값

- A: 키 값에 해당하는 데이터 레코드에 대한 포인터

 

즉 가장 왼쪽의 키(K1)를 먼저 확인하므로 자주 사용되는 인덱스가 앞서야 한다.

 

예를 들어 사용자가 작성한 게시물을 확인한다고 할 때, (user_id, created_at)으로 설정하면 사용자의 id에 맞는 게시물을 찾고, created_at 기준으로 값이 정렬되어 있다. 하지만 (created_at, user_id) 순으로 Index를 설정하면 사용자의 id에 맞는 게시물을 찾을 때엔 인덱스가 적용될 수 없다!

(user_id, created_at) 순서로 구성된 복합인덱스

 

이 경우엔 2번 사용자의 게시글을 모두 가져올 수 있다. 

 

(created_at, user_id) 순서로 구성된 복합 인덱스

이번엔 created_at이 먼저 오는 복합인덱스를 보자. 이래도 2번 사용자의 글을 쉽게 가져올 수 있을까? 

정렬 기준이 생성일자가 우선이라 2번 사용자의 게시글을 쉽게 가져올 수 없다!!

 

JPA에서 Index를 사용해보자.

사실 전 DB Index라는 개념을 최근에 알아서 한번도 사용해본 적이 없습니다...허허

 

물론 이런 사람들을 위해서 DB에선 기본적으로 기본키와 외래키, UNIQUE 컬럼에 Index를 적용해주기 때문에 JPA를 사용하면서 `findById`로 엔티티를 찾았다면 인덱스를 사용하고 있었던 것이다.

 

코드를 통해 Index를 사용하는 방법에 대해 알아보자.

@Entity
public class Point {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private Integer amount;
    
    ...
}

위와 같은 형식을 가장 많이 사용할 것이다. Table에 대한 제약없이 사용된 경우엔 id에 인덱스가 적용되게 되며 findById나 findByIdAnd...과 같은 메서드에 자동적으로 적용된다. (물론 findByIdAnd...같은 메서드를 사용할일은 없겠지만 말이다.)

 

@Entity
@Table(name = "point",
        indexes = {
                @Index(name = "idx_point_user", columnList = "user_id"), // 단일 인덱스
                @Index(name = "idx_point_user_created_at", columnList = "user_id, created_at") // 복합 인덱스
        })
public class Point {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private Integer amount;

    @CreatedDate
    @Column(name = "created_at", nullable = false, updatable = false)
    private LocalDateTime createdAt;

    @ManyToOne
    @JoinColumn(name = "user_id")
    private User user;
	...
}

Entity에 직접 Index를 생성하여 DDL 생성 시 인덱스를 함께 만든다. 단일 인덱스를 만들 수도 있고, 복합 인덱스를 만들 수도 있다. columnList를 작성할 때 컬럼명 오타와 컬럼 순서에 주의하자.

 

복합 인덱스의 장점은 인덱스의 일부를 사용할 수 있다는 것이다. 위의 코드에서 복합 인덱스만 정의되어 있다고 가정할 때, `findByUserAndAmount`로 함수를 사용해도 인덱스가 적용된다. user_id에 해당하는 범위를 찾은 후 amount를 필터링하여 원하는 컬럼만 뽑아내는 방식이다.

 

정말 인덱스가 사용되는지 확인하기 위해 EXPLAIN을 사용해보자.

Hibernate: select p1_0.id,p1_0.amount,ad1_0.id,ad1_0.att_date,ad1_0.checked_at,ad1_0.user_id,p1_0.content,p1_0.created_at,p2_0.id,p2_0.content,p2_0.created_at,p2_0.mission_content,p2_0.post_type,p2_0.title,p2_0.updated_at,p2_0.user_id,v1_0.id,v1_0.failure_reason,v1_0.is_image_verified,v1_0.is_text_verified,v1_0.success_reason,p2_0.view_count,p1_0.source,u2_0.id,u2_0.completed_mission_count,u2_0.email,u2_0.nickname,u2_0.password,u2_0.point_balance,u2_0.profile_image_url,u2_0.provider,u2_0.provider_id,u2_0.role,u2_0.username,u2_0.verified_post_count 
	from point p1_0 
    left join attendance_day ad1_0 on ad1_0.id=p1_0.attendance_day_id 
    left join posts p2_0 on p2_0.id=p1_0.post_id 
    left join verification v1_0 on p2_0.id=v1_0.post_id 
    left join users u2_0 on u2_0.id=p1_0.user_id 
    where p1_0.id=?