[SolveGO #20] 이게 정말 필요한 걸까? - DB Index를 제거한 이유

 
 

 
 
 
 
 

 
 
농구를 하다 보면 쉽게 쉽게 하는 사람들이 있다.
 
화려한 드리블이나 큰 움직임 없이도 수비의 허를 찌르는 데 필요한 근육만 정확하게 사용한다. 동작은 간결하지만 날카롭고, 타이밍에는 군더더기가 없다. 특히 선수 출신과 함께 경기를 뛰어 보면 일반인과 프로 사이에는 한두 수가 아니라 열 몇 수 이상의 차이가 있다는 것을 실감하게 된다. 움직임의 정교함과 날카로움이 극에 달해 있다.
 
예전에는 그런 사람들과 경기를 마치고 나면 ‘나는 왜 저렇게 하지 못할까?’라는 생각에 스트레스를 받곤 했다. 하지만 요즘에는 생각이 조금 달라졌다. 농구에서 그들과 같아져야 하는 것이 아니라, 나 역시 내 분야에서 그들처럼 프로다운 모습을 보여주면 된다고 생각한다. 그들이 간결한 움직임을 보여주는 것은 가진 기술이 부족해서가 아니다. 오히려 수많은 기술을 익힌 뒤 불필요한 움직임을 하나씩 깎아낸 결과다. 필요한 동작만 남았기 때문에 단순해 보이는 것이다.
 
이것은 농구에만 적용되는 이야기가 아니다. 자신의 분야에서 숙련된 사람의 결과물은 대개 복잡해 보이지 않는다. 무엇이 필요한지 알고, 필요하지 않은 것은 제거했기 때문이다.
 

---

 
최근 나는 문제 데이터를 최신순으로 조회하기 위해 created_at 칼럼에 인덱스를 적용하고 성능을 측정했다. 실험 결과는 예상대로였다. 인덱스를 사용한 쿼리는 사용하지 않은 쿼리보다 10배 이상 빨랐다. 반복 측정에서도 결과가 일정했고 표준편차도 작았기 때문에, 성능 개선이 안정적으로 나타났다고 판단했다. 예상한 이론이 실제 측정값으로 확인되자 꽤 만족스러웠다.
 
그런데 다음 날 문득 한 가지 의문이 생겼다. MySQL의 기본 키도 B+Tree 인덱스로 관리된다. 그리고 problems 테이블의 PK인 id는 AUTO_INCREMENT로 생성되므로 새로운 데이터가 추가될수록 값이 커진다.
 
그렇다면 최신 데이터를 가져오기 위해 굳이 created_at 인덱스를 하나 더 만들 필요가 있었을까?
이것 또한 불필요한 것이 아닌가?
 

ORDER BY created_at DESC

 
대신 다음과 같이 PK를 기준으로 조회해도 되지 않을까?

ORDER BY id DESC

 
 
id가 데이터의 생성 순서를 나타낸다면, 이미 존재하는 PK 인덱스를 이용해 최신 데이터를 빠르게 가져올 수 있다. 별도의 인덱스를 추가하지 않아도 된다면 인덱스가 차지하는 저장 공간뿐만 아니라, 데이터가 변경될 때마다 인덱스를 함께 관리해야 하는 비용도 줄일 수 있다.
 
created_at 인덱스를 통해 10배 이상의 성능 개선을 확인했다는 사실만으로 실험을 끝낼 수도 있었다. 그러나 성능이 향상됐다는 이유만으로 해당 설계가 최선이라고 단정할 수는 없다. 같은 목적을 이미 존재하는 구조로 달성할 수 있다면, 새로 추가한 인덱스는 불필요한 비용이 될 수 있기 때문이다.
 
앞에서 이야기한 농구 선수처럼, 프로는 단순히 많은 기술을 사용하는 것이 아니라 필요한 기술만 정확하게 사용한다. 데이터베이스 최적화에서도 새로운 구조를 추가하는 데서 끝나지 않고, 같은 목적을 더 적은 비용으로 달성할 수 있는지까지 확인해야 한다. 그래서 이번에는 created_at 보조 인덱스와 기존 PK 인덱스를 각각 이용해 최신 문제 데이터를 조회하고, 두 방식의 실행 계획과 실제 성능을 다시 비교했다.
 
 

---

 

PK로 최신 데이터를 조회할 수 있을까?

우선 다음 두 쿼리를 비교했다.

SELECT *
FROM problems
ORDER BY created_at DESC
LIMIT 20;

SELECT *
FROM problems
ORDER BY id DESC
LIMIT 20;

 
 
현재 실험 데이터는 순서대로(id와 created가 함께 증가) 삽입되었으며, id는 AUTO_INCREMENT로 증가하고 created_at 역시 삽입 시점에 생성된다. 즉 데이터에서는 id가 클수록 created_at도 최근인 셈이다.
 
먼저 두 쿼리가 실제로 같은 데이터를 반환하는지 확인했다.

두 조회 결과를 비교한 결과 모습 (mysql)

 
두 조회 결과를 비교한 결과, 현재 실험 데이터에서는 동일한 20개의 문제가 같은 순서로 반환됐다.
 
 
 
 

 
다만 id DESC와 created_at DESC가 본질적으로 같은 의미인 것은 아니다. id DESC는 ID가 큰 순서이고, created_at DESC는 실제 생성 시각이 최근인 순서다. 즉, 과거 데이터를 새로 이관하거나 created_at을 임의로 변경하는 상황이 발생하게 되면 두 값의 순서가 달라질 수 있다. 동시 트랜잭션으로 인해 ID 할당 순서와 커밋 순서가 어긋나는 상황도 생각할 수 있다. 따라서 일반적인 시스템에서 두 정렬 기준을 항상 동일한 것으로 취급할 수는 없다.
 
그렇다면 SolveGO에서 말하는 ‘최신 문제’는 무엇일까?

생성 시각이 가장 최근인 문제인가, 아니면 가장 나중에 ID를 부여받은 문제인가?

 
의미상 최신 문제는 생성 시각이 가장 최근인 문제다. 따라서 원칙적인 정렬 기준은 created_at이 맞다.
 
다만 현재 SolveGO에서는 문제 등록 API를 통해서만 새로운 문제가 생성되며, 등록 과정에서 AUTO_INCREMENT 방식으로 ID가 부여되고 created_at도 함께 생성된다. 또한 과거 데이터를 이관하거나 created_at을 임의로 변경하는 기능은 제공하지 않는다. 동시 등록 상황에서는 ID 부여 순서와 생성 시각 기준의 순서가 완전히 일치하지 않을 가능성도 있다. 하지만 현재 SolveGO에서 문제 등록은 빈번한 동시 쓰기가 발생하는 기능이 아니라고 판단했고, 이번 실험의 목적은 현재 서비스 흐름에서 created_at 인덱스를 반드시 유지해야 하는지 확인하는 데 있었다. 따라서 현재 프로젝트의 데이터 생성 규칙과 실험 조건에서는 ID가 클수록 나중에 등록된 문제라고 간주할 수 있다고 판단했다. 이러한 전제 아래에서는 다음 두 정렬 방식이 동일한 결과를 반환한다.
 

ORDER BY created_at DESC

ORDER BY id DESC

 

 

결론적으로 현재 SolveGO의 요구사항과 이번 실험의 데이터 생성 조건에서는 별도의 created_at 인덱스를 유지하지 않고, 이미 존재하는 PK 인덱스로 최신 문제를 조회할 수 있다고 판단했다. 다만 향후 데이터 이관, created_at 수정, 동시 등록 증가 등으로 이러한 전제가 깨진다면 id를 생성 시각의 대체 기준으로 사용할 수 없다. 이 경우에는 다시 created_at을 정렬 기준으로 사용해야 한다.
 
 

---

 
 

실행 계획 비교

반환 결과를 확인한 뒤 EXPLAIN ANALYZE를 이용해 실행 계획을 비교했다.

created_at 인덱스 사용

EXPLAIN ANALYZE
SELECT *
FROM problems
ORDER BY created_at DESC
LIMIT 20;

created_at 보조 인덱스가 존재하면 MySQL은 인덱스를 역순으로 스캔하면서 최신 데이터 20개를 찾을 수 있다. 따라서 전체 테이블을 읽고 정렬하는 과정은 발생하지 않는다. 하지만 created_at 인덱스는 보조 인덱스다. 보조 인덱스의 리프 노드에는 대략 다음과 같은 값이 저장된다.

created_at + PK

쿼리가 SELECT *를 요청하므로, MySQL은 보조 인덱스에서 PK를 찾은 뒤 클러스터드 인덱스로 이동해 실제 행 전체를 다시 가져와야 한다.

created_at 보조 인덱스 탐색
        ↓
PK 확인
        ↓
PRIMARY KEY 인덱스에서 실제 행 조회

이를 흔히 PK Lookup 또는 Bookmark Lookup이라고 부른다.

 

PK 인덱스 사용

EXPLAIN ANALYZE
SELECT *
FROM problems
ORDER BY id DESC
LIMIT 20;

InnoDB에서 PK는 단순히 ID만 저장하는 별도의 목록이 아니다. PK를 기준으로 구성된 클러스터드 인덱스의 리프 노드에 실제 행 전체가 저장된다.
따라서 PK를 역순으로 스캔하면 별도의 보조 인덱스를 거치지 않고 곧바로 최신 ID 20개의 행을 가져올 수 있다.

PRIMARY KEY 인덱스 역순 스캔
        ↓
실제 행 20개 반환

두 쿼리 모두 정렬 작업을 피할 수 있지만, 접근 과정에는 다음과 같이 차이가 있다.
 

created_at DESC	보조 인덱스 탐색 → PK Lookup → 실제 행 조회
id DESC	PK 인덱스 탐색 → 실제 행 조회

 

 
 
따라서 이번 실험 조건에서는 PK를 사용한 쿼리가 더 단순한 실행 경로를 가진다.

---

 

성능 재측정

 
실행 계획만으로 결론을 내리지 않고, 이전 실험과 동일한 조건에서 두 쿼리를 반복 측정했다.
비교 대상은 다음 세 가지다.

-- 1. created_at 인덱스를 사용하지 않는 방식
SELECT *
FROM problems IGNORE INDEX (idx_problems_created_at)
ORDER BY created_at DESC
LIMIT 20;

-- 2. created_at 보조 인덱스를 사용하는 방식
SELECT *
FROM problems
ORDER BY created_at DESC
LIMIT 20;

-- 3. 기존 PK 인덱스를 사용하는 방식
SELECT *
FROM problems
ORDER BY id DESC
LIMIT 20;

공정한 비교를 위해 동일한 데이터와 DB 환경을 사용하고, 각 쿼리를 충분히 워밍업한 뒤 100회씩 반복 측정했다. 이후 평균, 중앙값, 최솟값, 최댓값, 표준편차를 비교했다. 결과는 다음과 같다.
 

 

 
측정 결과 created_at 인덱스를 사용한 조회는 인덱스를 사용하지 않은 조회보다 평균 기준 약 11.18배 빨랐다. 이는 인덱스를 통해 전체 테이블 정렬 비용을 제거한 효과가 실제 실행 시간에서도 확인된 결과다.

또한 id 기준 PK 역순 조회는 created_at 인덱스를 사용한 조회보다 평균 기준 약 1.01배 빨랐다. 두 방식의 평균 실행 시간은 각각 1.0005ms와 1.0096ms로 차이는 0.0091ms에 불과했지만, 실행 경로를 생각하면 자연스러운 결과였다.

created_at 인덱스 방식은 보조 인덱스를 역순으로 스캔한 뒤, 각 행의 PK를 이용해 PRIMARY KEY 인덱스에서 실제 행을 다시 조회해야 한다. 반면 id 기준 PK 역순 조회는 PRIMARY KEY 클러스터드 인덱스를 바로 역순으로 스캔하므로 별도의 PK Lookup 과정이 필요하지 않다. 따라서 PK 역순 조회가 근소하게 더 빠른 결과는 이 Lookup 비용의 차이로 해석할 수 있다.

결국 이번 실험의 핵심은 PK 역순 조회가 압도적으로 빠르다는 것이 아니다. 현재 데이터 생성 조건에서는 별도의 created_at 인덱스를 유지하지 않아도, 이미 존재하는 PK 인덱스만으로 created_at 인덱스 사용보다 약간 더 단순한 실행 경로와 거의 동일한 수준의 조회 성능을 얻을 수 있다는 점이다.
 
따라서 기존 ProblemRepository의 JPQL을 아래와 같이 수정했다.
 

package com.kdh.solvego.domain.problem.repository;

public interface ProblemRepository extends JpaRepository<Problem, Long> {

    // 수정 전
    @Query("""
           select p
           from Problem p
           join fetch p.creator
           order by p.createdAt desc
           """)
    List<Problem> findAllWithCreatorOrderByCreatedAtDesc();

    // 수정 후
    @Query("""
           select p
           from Problem p
           join fetch p.creator
           order by p.id desc
           """)
    List<Problem> findAllWithCreatorOrderByIdDesc();
}

 
쿼리의 정렬 기준을 id로 변경하면서 created_at 인덱스는 더 이상 문제 목록 조회에 사용되지 않게 되었다. 따라서 불필요해진 created_at 보조 인덱스를 제거하기로 했다.

인덱스는 조회 성능을 높여주는 대신 저장 공간을 차지하고, INSERT, UPDATE, DELETE 시 함께 갱신되어야 한다. 현재 SolveGO의 문제 목록 조회에서는 PK 인덱스만으로 동일한 목적을 달성할 수 있으므로, created_at 인덱스를 유지하는 것은 오히려 불필요한 비용이라고 판단했다.

이를 반영하기 위해 Flyway 마이그레이션을 추가했다.

DROP INDEX idx_problems_created_at ON problems;

 

---

 

무엇을 얻었는가

 
프로는 화려한 것을 덧붙이는 사람이 아니라, 필요한 것과 불필요한 것을 구분하고 끝까지 불필요한 것을 걷어내는 사람에 가깝다고 생각한다. 나도 그런 마음가짐으로 다시 쿼리 설계를 고쳤다. 처음에는 created_at 인덱스를 추가해 문제 목록 조회 성능을 개선했고, 실제로 인덱스 미사용 대비 큰 성능 향상을 확인했다. 그런데 다시 살펴보니, 현재 SolveGO의 데이터 생성 조건에서는 이미 존재하는 PK 인덱스만으로도 거의 동일한 조회 성능을 낼 수 있었다.

created_at 보조 인덱스를 유지하면 추가 저장 공간이 필요하고, 데이터가 삽입되거나 변경될 때마다 인덱스도 함께 관리되어야 한다. 또한 SELECT *로 행 전체를 조회하는 현재 쿼리에서는 보조 인덱스를 통해 PK를 찾은 뒤 다시 실제 행을 조회하는 PK Lookup 과정도 발생한다. 반면 id 기준 PK 역순 조회는 PRIMARY KEY clustered 인덱스를 바로 스캔하므로 더 단순한 실행 경로를 가진다.

물론 이 판단은 현재 프로젝트의 전제 위에서만 유효하다. 향후 데이터 이관이 발생하거나, created_at을 임의로 지정해야 하거나, 동시 등록으로 인해 ID 순서와 생성 시각 순서의 불일치가 중요한 문제가 된다면 created_at 기준 정렬과 그에 맞는 인덱스를 다시 도입해야 한다.

겉으로 보면 단순히 인덱스를 제거한 것처럼 보일 수 있다. 그러나 인덱스를 모르고 사용하지 않은 것과, 실행 계획과 성능 측정을 통해 필요한 인덱스와 불필요한 인덱스를 구분한 뒤 걷어낸 것은 다르다고 생각한다. 앞으로도 이런 태도로, 필요한 것은 끝까지 파고들고 불필요한 것은 과감히 걷어내며 프로젝트를 완성해 나가자.
 
 

끝