1. 문제 상황

Spring Boot 프로젝트에서 patients 테이블을 관리하다 보니, 특정 patient_number로 환자를 조회하는 쿼리가 자주 실행되었다.

// QueryDSL 예시
public Patient findByPatientNumber(Integer number) {
    return queryFactory
        .selectFrom(patient)
        .where(patient.patientNumber.eq(number))
        .fetchOne();
}

처음에는 단순히 “숫자 조건으로 조회하는데 당연히 빠르겠지” 정도로 생각했다. 그런데 문득 의문이 들었다.

“인덱스를 타고 있을까? 아니면 Full Table Scan일까?”

2. 인덱스 자동 생성과 선언

테이블을 만들 때 patient_number 컬럼에 UNIQUE 제약조건을 걸어두었기 때문에 사실 별도의 코드를 작성하지 않아도 MySQL은 자동으로 인덱스를 생성한다.

CREATE TABLE patients (
    id INT PRIMARY KEY AUTO_INCREMENT,
    patient_number INT UNIQUE,
    created_date_time TIMESTAMP,
    modified_date_time TIMESTAMP
);

위 정의에서 PRIMARY KEY (id)는 물론이고, UNIQUE (patient_number)도 자동으로 인덱스를 갖는다. 만약 수동으로 만들었다면 다음과 같이 쓸 수 있다.

@Table(name = "patients",
        uniqueConstraints = @UniqueConstraint(columnNames = {"patientNumber"}),
        indexes = {@Index(name = "patient_number", columnList = ("patientNumber"))})

즉, 내가 고민한 patient_number 조회는 이미 인덱스가 걸린 상태였던 것이다. 하지만 이게 실제 실행계획에서 사용되는지는 따져봐야 했다.

3. 인덱스 기본 동작

MySQL의 인덱스는 기본적으로 B+ Tree 자료구조 위에서 돌아간다.

• 동등 검색 (=) → 트리 탐색으로 O(log n)에 원하는 레코드를 찾는다.
• LIKE prefix 검색 ('123%') → prefix 길이만큼 범위를 잡고 순차 탐색. prefix가 길수록 후보군이 좁아져 효율적이다.
• %123 또는 '%23%' → 인덱스 무력화. 전체 스캔 발생.

즉, 내가 patient_number = 12345 같은 조건을 걸었다면 인덱스를 제대로 활용하는 케이스였다.

4. EXPLAIN으로 확인하기

단순히 “인덱스가 걸렸다더라”를 믿을 수는 없다. MySQL이 실제로 어떤 계획으로 쿼리를 수행하는지 직접 확인해야 한다.

EXPLAIN
SELECT *
FROM patients
WHERE patient_number = 12345;

실행 결과는 대략 다음과 같이 나온다.

여기서 주목할 부분은 type, key, 그리고 Extra다.

• type이 const → Unique 인덱스로 단일 행 검색. 가장 효율적인 방식.
• key에 idx_patient_number → 우리가 의도한 인덱스를 실제로 사용하고 있음.
• Extra의 Using index → 커버링 인덱스가 적용된 상황.

5. EXPLAIN Extra 옵션 해석

실제로 EXPLAIN 결과에서 가장 헷갈리는 부분은 Extra 칼럼이다. 여기에는 옵티마이저가 어떤 추가 최적화를 사용했는지 표시된다.

내가 공부하면서 정리한 중요한 분기점은 다음과 같다.

• Using where 인덱스로 후보를 좁혔지만, 최종 조건 검증은 row 단위에서 수행한다. 즉, 인덱스가 완벽히 조건을 다 처리하진 못했다.

• Using index 커버링 인덱스 상황. 필요한 데이터가 전부 인덱스에 들어 있어서 테이블에 접근하지 않는다. 가장 빠른 경우.

• Using index condition Index Condition Pushdown (ICP). 인덱스를 탐색하는 과정에서 WHERE 조건을 최대한 미리 적용한다. 불필요한 row 접근을 줄여준다.

• Using temporary GROUP BY, DISTINCT 같은 연산을 위해 임시 테이블이 필요하다. 보통 성능 저하의 원인.

• Using filesort ORDER BY가 인덱스 순서로 해결되지 않아 별도의 정렬 작업이 발생한다. 대용량 데이터에서 성능 문제가 되는 대표적인 원인.

5-1. 조건절별 인덱스 동작 요약