데이터베이스 인덱스의 원리와 최적화 전략 완벽 가이드

1. 인덱스란 무엇인가 - 기본 개념과 필요성

데이터베이스 인덱스는 테이블의 검색 속도를 향상시키기 위한 자료구조이다. 책의 목차나 색인과 동일한 역할을 한다고 생각하면 이해가 쉽다. 만약 1000페이지짜리 책에서 특정 키워드를 찾아야 한다면, 처음부터 끝까지 한 페이지씩 넘기는 것보다 색인을 통해 해당 키워드가 있는 페이지 번호를 바로 찾는 것이 훨씬 빠르다. 데이터베이스의 인덱스도 정확히 같은 원리로 동작한다.

인덱스가 없는 테이블에서 특정 조건의 데이터를 검색하면, 데이터베이스는 테이블의 모든 행을 처음부터 끝까지 순회하는 Full Table Scan을 수행해야 한다. 데이터가 수백만 건, 수천만 건이 되면 이 작업은 엄청난 시간이 소요된다. 인덱스를 생성하면 검색 대상 컬럼의 값과 해당 행의 물리적 위치(포인터)를 별도의 자료구조에 정렬된 상태로 저장하기 때문에, 원하는 데이터를 로그 시간 복잡도(O(log N))로 빠르게 찾을 수 있다.

1
2
3
4
5
6
7
8
-- 인덱스 없이 검색하는 경우 (Full Table Scan 발생)
SELECT * FROM users WHERE email = 'user@example.com';

-- email 컬럼에 인덱스 생성
CREATE INDEX idx_users_email ON users(email);

-- 인덱스 생성 후 동일 쿼리 실행 시 인덱스를 타게 됨
SELECT * FROM users WHERE email = 'user@example.com';

그러나 인덱스에는 비용이 따른다. 첫째, 인덱스 자체가 별도의 저장 공간을 차지한다. 테이블 크기의 10~20% 정도의 추가 공간이 필요할 수 있다. 둘째, INSERT, UPDATE, DELETE 연산 시 인덱스도 함께 갱신해야 하므로 쓰기 성능이 저하된다. 따라서 읽기가 빈번하고 쓰기가 상대적으로 적은 컬럼에 인덱스를 생성하는 것이 바람직하다. 무분별한 인덱스 생성은 오히려 전체적인 데이터베이스 성능을 떨어뜨릴 수 있으므로 신중하게 설계해야 한다.

인덱스가 필요한 대표적인 상황은 다음과 같다. WHERE 절에서 자주 사용되는 컬럼, JOIN 조건에 사용되는 컬럼, ORDER BY나 GROUP BY에 자주 등장하는 컬럼, 그리고 선택도(Selectivity)가 높은 컬럼(즉, 고유한 값의 비율이 높은 컬럼)이 인덱스의 좋은 후보이다.

2. B-Tree 인덱스의 구조와 동작 원리

B-Tree(Balanced Tree)는 데이터베이스 인덱스에서 가장 널리 사용되는 자료구조이다. B-Tree는 이진 탐색 트리(Binary Search Tree)를 일반화한 것으로, 하나의 노드가 2개 이상의 자식 노드를 가질 수 있다. 이를 통해 트리의 높이를 낮추고, 디스크 I/O 횟수를 최소화할 수 있다.

B-Tree의 구조는 크게 세 가지 종류의 노드로 구성된다.

• 루트 노드(Root Node): 트리의 최상위 노드. 항상 메모리에 상주하는 경우가 많다.
• 브랜치 노드(Branch Node, Internal Node): 루트와 리프 사이의 중간 노드. 키 값과 자식 노드에 대한 포인터를 가진다.
• 리프 노드(Leaf Node): 트리의 최하위 노드. 실제 데이터 레코드에 대한 포인터(또는 데이터 자체)를 가진다.

B-Tree에서 검색이 이루어지는 과정을 단계별로 살펴보자. 예를 들어, 인덱스에 [10, 20, 30, 40, 50, 60, 70, 80, 90] 값이 저장되어 있고 45를 검색한다고 가정하자.

1. 루트 노드에서 시작한다. 루트 노드에 [30, 60]이 저장되어 있다면, 45는 30보다 크고 60보다 작으므로 중간 자식 노드로 이동한다.
2. 중간 브랜치 노드에 [40, 50]이 저장되어 있다면, 45는 40보다 크고 50보다 작으므로 해당 범위의 자식(리프) 노드로 이동한다.
3. 리프 노드에서 45를 찾는다. 존재하지 않으면 검색 실패를 반환한다.

이처럼 B-Tree는 각 레벨에서 단 한 번의 비교로 검색 범위를 대폭 줄여나간다. 일반적으로 B-Tree의 높이는 3~4 수준이므로, 수백만 건의 데이터에서도 3~4번의 디스크 I/O만으로 원하는 데이터를 찾을 수 있다.

1
2
3
4
5
6
7
8
-- B-Tree 인덱스 생성 (MySQL에서 기본 인덱스 타입)
CREATE INDEX idx_employee_name ON employee(name);

-- B-Tree 인덱스를 활용한 범위 검색
SELECT * FROM employee WHERE name BETWEEN '김' AND '박';

-- B-Tree 인덱스를 활용한 정렬
SELECT * FROM employee ORDER BY name ASC LIMIT 100;

B-Tree 인덱스는 등호(=) 비교뿐만 아니라, 범위 검색(>, <, BETWEEN), 정렬(ORDER BY), 그리고 LIKE 검색에서 접두사 패턴(‘김%’)까지 효율적으로 지원하는 것이 최대 장점이다. 이러한 다용도성 때문에 대부분의 RDBMS에서 기본 인덱스 구조로 B-Tree 계열을 채택하고 있다.

B-Tree에서 삽입과 삭제 연산도 중요하다. 새로운 키를 삽입할 때 리프 노드에 공간이 부족하면 노드 분할(Split) 이 발생한다. 이는 부모 노드에 새로운 키를 추가하는 연쇄적인 작업으로 이어질 수 있으며, 극단적인 경우 루트 노드까지 분할이 전파되어 트리의 높이가 1 증가한다. 반대로 삭제 시에는 노드의 키 수가 최소 기준 이하로 떨어지면 노드 병합(Merge) 이 발생할 수 있다.

3. B+Tree vs B-Tree 차이점

실제 데이터베이스에서는 순수한 B-Tree보다 B+Tree를 더 많이 사용한다. MySQL InnoDB, PostgreSQL, Oracle 등 주요 RDBMS가 모두 B+Tree를 인덱스 구조로 사용한다. B+Tree는 B-Tree를 개선한 자료구조로, 몇 가지 핵심적인 차이점이 있다.

첫째, 데이터 저장 위치가 다르다. B-Tree에서는 모든 노드(루트, 브랜치, 리프)에 키와 데이터(또는 데이터 포인터)가 저장될 수 있다. 반면 B+Tree에서는 오직 리프 노드에만 실제 데이터(또는 데이터 포인터)가 저장되고, 브랜치 노드에는 키 값만 저장된다. 이로 인해 브랜치 노드가 더 많은 키를 담을 수 있어 트리의 높이가 낮아지고, 전체적인 검색 성능이 향상된다. 한 페이지에 더 많은 키를 넣을 수 있으므로 디스크 I/O 효율이 뛰어나다.

둘째, 리프 노드 간 연결이 있다. B+Tree의 리프 노드들은 연결 리스트(Linked List)로 서로 연결되어 있다. 이 구조 덕분에 범위 검색(Range Scan)이 매우 효율적이다. 시작 키를 찾은 후 리프 노드 간 포인터를 따라가면서 순차적으로 읽기만 하면 되기 때문이다. B-Tree에서는 범위 검색 시 중위 순회(In-order Traversal)를 해야 하므로 상대적으로 비효율적이다.

셋째, 검색 경로가 일정하다. B+Tree에서는 어떤 키를 검색하든 항상 루트에서 리프까지의 경로를 거쳐야 한다. 모든 검색이 동일한 깊이를 가지므로 성능 예측이 용이하다. B-Tree에서는 운이 좋으면 브랜치 노드에서 바로 데이터를 찾을 수 있지만, 이는 예측 불가능한 성능 편차를 만든다.

1
2
3
4
5
6
7
8
9
B-Tree 구조:
         [30 | ptr | 60 | ptr]
        /         |          \
  [10|d, 20|d]  [40|d, 50|d]  [70|d, 80|d]

B+Tree 구조:
           [30 | 60]             <- 브랜치 (키만 저장)
        /       |       \
  [10,20] -> [30,40,50] -> [60,70,80]   <- 리프 (데이터 + 연결 리스트)

이 차이 때문에 B+Tree는 순차 접근과 범위 쿼리에 압도적으로 유리하며, 데이터베이스 워크로드에 매우 적합하다. 결론적으로 대부분의 관계형 데이터베이스가 B+Tree를 인덱스의 기본 구조로 채택하는 이유는 범위 검색의 효율성, 일관된 검색 성능, 그리고 디스크 I/O 최소화 등 데이터베이스 환경에 최적화된 특성을 갖고 있기 때문이다.

4. Hash 인덱스

Hash 인덱스는 해시 함수를 사용하여 키 값을 해시 버킷에 매핑하는 인덱스 구조이다. 해시 함수는 입력 값을 고정된 크기의 해시 값으로 변환하고, 이 해시 값이 데이터가 저장된 위치를 직접 가리킨다. 이 때문에 정확한 일치 검색(Equal lookup) 에서 O(1)의 시간 복잡도로 매우 빠른 성능을 보여준다.

1
2
3
4
5
6
7
8
9
-- MySQL MEMORY 엔진에서 Hash 인덱스 생성
CREATE TABLE cache_data (
    cache_key VARCHAR(255),
    cache_value TEXT,
    INDEX USING HASH (cache_key)
) ENGINE = MEMORY;

-- Hash 인덱스가 효과적인 쿼리 (정확한 일치 검색)
SELECT * FROM cache_data WHERE cache_key = 'session_abc123';

그러나 Hash 인덱스에는 심각한 제약 사항들이 있다.

1. 범위 검색 불가: 해시 함수는 값의 순서를 보존하지 않으므로, >, <, BETWEEN, ORDER BY 등의 연산에 Hash 인덱스를 사용할 수 없다. 범위 검색을 하려면 Full Table Scan이 필요하다.
2. 부분 일치 검색 불가: LIKE 'abc%'와 같은 접두사 검색도 불가능하다.
3. 정렬 불가: 해시 값은 원래 키의 순서와 무관하므로 인덱스를 통한 정렬이 불가능하다.
4. 해시 충돌: 서로 다른 키 값이 동일한 해시 값으로 매핑될 수 있으며, 충돌이 많아지면 성능이 저하된다.

MySQL InnoDB 엔진은 Hash 인덱스를 직접 지원하지 않지만, 내부적으로 Adaptive Hash Index라는 기능을 제공한다. 이는 InnoDB가 자주 접근되는 인덱스 페이지에 대해 자동으로 해시 인덱스를 메모리에 구축하는 기능으로, DBA가 직접 생성하거나 제거할 수 없다. 자주 참조되는 B+Tree 인덱스 페이지에 대한 빠른 접근을 위해 InnoDB가 자동으로 관리한다.

1
2
3
4
5
6
-- InnoDB Adaptive Hash Index 상태 확인
SHOW ENGINE INNODB STATUS;

-- Adaptive Hash Index 활성화/비활성화 설정
SET GLOBAL innodb_adaptive_hash_index = ON;
SET GLOBAL innodb_adaptive_hash_index = OFF;

실무에서 Hash 인덱스는 캐시 테이블이나 세션 저장소처럼 정확한 키 기반의 조회가 대부분인 경우에 적합하다. 일반적인 비즈니스 데이터 테이블에서는 범위 검색, 정렬 등 다양한 쿼리 패턴이 필요하므로 B+Tree 인덱스가 더 적합하다.

5. 클러스터형 인덱스 vs 비클러스터형 인덱스

데이터베이스 인덱스는 데이터의 물리적 정렬과의 관계에 따라 클러스터형 인덱스(Clustered Index) 와 비클러스터형 인덱스(Non-clustered Index, Secondary Index) 로 나뉜다. 이 구분을 이해하는 것은 데이터베이스 성능 최적화에 매우 중요하다.

클러스터형 인덱스 (Clustered Index)

클러스터형 인덱스는 테이블의 데이터 자체가 인덱스의 리프 노드에 저장되는 구조이다. 즉, 인덱스의 키 순서에 따라 데이터가 물리적으로 정렬되어 저장된다. 테이블당 단 하나의 클러스터형 인덱스만 존재할 수 있다. 데이터의 물리적 순서는 하나만 가능하기 때문이다.

MySQL InnoDB에서는 Primary Key가 자동으로 클러스터형 인덱스가 된다. Primary Key가 없으면 NOT NULL인 UNIQUE 키를 사용하고, 그것도 없으면 InnoDB가 내부적으로 6바이트의 숨겨진 Row ID를 생성하여 클러스터형 인덱스로 사용한다.

1
2
3
4
5
6
7
8
9
-- Primary Key가 클러스터형 인덱스가 됨
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,        -- 클러스터형 인덱스
    customer_id BIGINT,
    order_date DATETIME,
    total_amount DECIMAL(10, 2),
    INDEX idx_customer (customer_id),   -- 비클러스터형 인덱스 (세컨더리)
    INDEX idx_date (order_date)         -- 비클러스터형 인덱스 (세컨더리)
);

클러스터형 인덱스의 장점은 범위 검색이 매우 효율적이라는 것이다. 데이터가 물리적으로 연속된 위치에 정렬되어 있으므로, 범위 내의 데이터를 순차적으로 읽을 수 있다. 이는 디스크 I/O를 크게 줄여준다.

비클러스터형 인덱스 (Non-clustered Index / Secondary Index)

비클러스터형 인덱스는 인덱스의 리프 노드에 실제 데이터가 아닌 데이터의 위치 정보(포인터) 가 저장되는 구조이다. InnoDB에서 세컨더리 인덱스의 리프 노드에는 해당 행의 Primary Key 값이 저장된다. 따라서 세컨더리 인덱스를 통해 데이터를 조회하면 먼저 세컨더리 인덱스에서 PK 값을 얻고, 그 PK 값으로 클러스터형 인덱스를 다시 검색하여 실제 데이터를 가져온다. 이 과정을 더블 룩업(Double Lookup) 또는 북마크 룩업(Bookmark Lookup) 이라고 한다.

1
2
3
4
5
6
7
세컨더리 인덱스를 통한 검색 과정 (InnoDB):

1단계: 세컨더리 인덱스(idx_customer) 검색
   customer_id = 1001 → PK = 5042 를 찾음

2단계: 클러스터형 인덱스(PK) 검색
   PK = 5042 → 실제 데이터 행을 찾음

이 더블 룩업 구조 때문에, InnoDB에서 세컨더리 인덱스를 통한 검색은 클러스터형 인덱스를 통한 검색보다 비용이 더 든다. 또한 Primary Key의 크기가 크면 모든 세컨더리 인덱스의 크기도 커지므로, InnoDB에서는 PK를 가능하면 작은 크기(예: AUTO_INCREMENT BIGINT)로 설계하는 것이 좋다. UUID를 PK로 사용하면 세컨더리 인덱스의 크기가 크게 증가하고, 랜덤한 값이므로 삽입 시 페이지 분할이 빈번하게 발생하여 성능에 좋지 않다.

6. 복합 인덱스 (Composite Index)와 컬럼 순서의 중요성

복합 인덱스(Composite Index)란 두 개 이상의 컬럼을 결합하여 생성하는 인덱스를 말한다. 복합 인덱스를 효과적으로 설계하면 여러 조건이 결합된 쿼리의 성능을 극적으로 향상시킬 수 있다. 그러나 복합 인덱스에서 컬럼의 순서는 성능에 결정적인 영향을 미치므로 매우 신중하게 결정해야 한다.

1
2
-- 복합 인덱스 생성
CREATE INDEX idx_orders_composite ON orders(customer_id, order_date, status);

복합 인덱스에서 가장 중요한 원리는 최좌선 접두사 규칙(Leftmost Prefix Rule)이다. 복합 인덱스 (A, B, C)가 있을 때, 이 인덱스는 다음 조건들에서 활용될 수 있다.

1
2
3
4
5
6
7
8
9
10
-- 인덱스 사용 가능 (최좌선 접두사 만족)
WHERE A = 1
WHERE A = 1 AND B = 2
WHERE A = 1 AND B = 2 AND C = 3
WHERE A = 1 AND B > 5

-- 인덱스 사용 불가 (최좌선 접두사 불만족)
WHERE B = 2
WHERE C = 3
WHERE B = 2 AND C = 3

복합 인덱스의 컬럼 순서를 결정할 때는 다음 원칙을 따르는 것이 좋다.

1. 등호(=) 조건으로 사용되는 컬럼을 앞에 배치: 등호 조건은 검색 범위를 정확하게 좁히므로, 범위 조건보다 앞에 배치하는 것이 효율적이다.
2. 선택도(Selectivity)가 높은 컬럼을 앞에 배치: 고유한 값이 많은 컬럼이 앞에 오면 더 빠르게 검색 범위를 좁힐 수 있다. 다만 이 원칙은 실제 쿼리 패턴에 따라 달라질 수 있다.
3. 범위 조건 컬럼 뒤의 컬럼은 인덱스 활용이 제한적: 복합 인덱스에서 범위 조건이 사용된 컬럼 이후의 컬럼은 인덱스 필터링에 활용되지 못할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 예시: orders 테이블에서 자주 실행되는 쿼리
SELECT * FROM orders
WHERE customer_id = 1001
  AND order_date BETWEEN '2026-01-01' AND '2026-01-31'
  AND status = 'COMPLETED';

-- 좋은 복합 인덱스 설계 (등호 조건 먼저)
CREATE INDEX idx_good ON orders(customer_id, status, order_date);
-- customer_id = 등호, status = 등호로 범위를 좁힌 후 order_date 범위 검색

-- 나쁜 복합 인덱스 설계 (범위 조건이 중간에)
CREATE INDEX idx_bad ON orders(customer_id, order_date, status);
-- order_date 범위 조건 이후 status 컬럼은 인덱스 필터링 활용 불가

이처럼 동일한 컬럼을 사용하더라도 순서에 따라 인덱스의 효율이 크게 달라진다. 실무에서는 해당 테이블에 실행되는 주요 쿼리 패턴을 분석한 후, 가장 많은 쿼리를 효율적으로 지원할 수 있는 복합 인덱스를 설계해야 한다.

7. 커버링 인덱스 (Covering Index)

커버링 인덱스란 쿼리가 필요로 하는 모든 컬럼이 인덱스에 포함되어 있어, 테이블(클러스터형 인덱스)에 접근하지 않고 인덱스만으로 쿼리를 처리할 수 있는 경우를 말한다. 이는 세컨더리 인덱스의 더블 룩업 비용을 완전히 제거하여 성능을 크게 향상시킨다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 복합 인덱스 생성
CREATE INDEX idx_covering ON orders(customer_id, order_date, total_amount);

-- 커버링 인덱스가 적용되는 쿼리
-- 필요한 모든 컬럼(customer_id, order_date, total_amount)이 인덱스에 포함됨
SELECT customer_id, order_date, total_amount
FROM orders
WHERE customer_id = 1001
  AND order_date >= '2026-01-01';

-- 커버링 인덱스가 적용되지 않는 쿼리
-- status 컬럼이 인덱스에 없으므로 테이블 접근 필요
SELECT customer_id, order_date, total_amount, status
FROM orders
WHERE customer_id = 1001;

EXPLAIN 실행 계획에서 커버링 인덱스가 사용되면 Extra 컬럼에 “Using index” 라고 표시된다. 이 표시가 나타나면 테이블 데이터에 접근하지 않고 인덱스만으로 결과를 반환한다는 뜻이다.

1
2
3
4
5
6
7
8
EXPLAIN SELECT customer_id, order_date, total_amount
FROM orders
WHERE customer_id = 1001;

-- 결과 예시:
-- type: ref
-- key: idx_covering
-- Extra: Using index    <-- 커버링 인덱스 적용!

커버링 인덱스의 활용은 특히 대량의 데이터를 조회하는 분석 쿼리나 페이지네이션 쿼리에서 매우 효과적이다. COUNT 쿼리에서도 커버링 인덱스가 적용되면 테이블 풀 스캔을 하지 않아도 된다.

1
2
3
4
5
6
7
8
9
10
11
-- 커버링 인덱스를 활용한 효율적인 페이지네이션
-- 먼저 인덱스만으로 PK를 구한 뒤, 해당 PK로 데이터를 가져옴
SELECT o.*
FROM orders o
INNER JOIN (
    SELECT order_id
    FROM orders
    WHERE customer_id = 1001
    ORDER BY order_date DESC
    LIMIT 20 OFFSET 1000
) sub ON o.order_id = sub.order_id;

다만 커버링 인덱스를 위해 인덱스에 너무 많은 컬럼을 포함시키면 인덱스 크기가 커지고, 쓰기 성능이 저하될 수 있으므로 실제 쿼리 패턴을 면밀히 분석하여 적절한 균형을 찾는 것이 중요하다.

8. 인덱스 스캔 방식

데이터베이스 옵티마이저는 쿼리의 조건과 인덱스의 구조에 따라 다양한 스캔 방식을 선택한다. 주요 인덱스 스캔 방식을 이해하면 실행 계획을 분석하고 쿼리를 최적화하는 데 큰 도움이 된다.

Index Full Scan

인덱스의 처음부터 끝까지 전체를 순차적으로 읽는 방식이다. 테이블 풀 스캔보다는 효율적이지만(인덱스가 테이블보다 작으므로), 인덱스를 부분적으로 사용하는 것에 비하면 비효율적이다. 주로 커버링 인덱스가 적용 가능하지만 WHERE 조건이 인덱스의 최좌선 컬럼과 맞지 않을 때 사용된다.

1
2
3
-- 인덱스 (A, B, C)가 있을 때
-- B 컬럼만으로 검색 -> Index Full Scan 가능성
SELECT A, B, C FROM table WHERE B = 100;

Index Range Scan

인덱스의 특정 범위만 읽는 방식으로, 가장 일반적이고 효율적인 인덱스 스캔 방식이다. WHERE 절에서 등호 조건이나 범위 조건이 인덱스 컬럼과 일치할 때 사용된다.

1
2
3
4
-- Range Scan 예시
SELECT * FROM employee WHERE age BETWEEN 25 AND 35;
SELECT * FROM employee WHERE name >= '가' AND name < '나';
SELECT * FROM employee WHERE department_id IN (10, 20, 30);

Index Unique Scan

유니크 인덱스에서 정확히 하나의 값을 찾는 스캔 방식이다. Primary Key나 UNIQUE 인덱스에서 등호 조건으로 검색할 때 사용된다. 최대 하나의 결과만 반환되므로 가장 효율적인 스캔 방식이다.

1
2
3
-- Unique Scan 예시
SELECT * FROM employee WHERE employee_id = 12345;
SELECT * FROM users WHERE email = 'admin@company.com'; -- email에 UNIQUE 인덱스

Index Skip Scan

MySQL 8.0에서 도입된 기능으로, 복합 인덱스에서 첫 번째 컬럼이 WHERE 절에 없더라도 인덱스를 활용할 수 있도록 하는 최적화 방식이다. 첫 번째 컬럼의 고유 값(Distinct Value)이 적을 때 효과적이다.

1
2
3
4
5
6
7
8
9
-- 인덱스: (gender, age)
-- 기존에는 gender 조건 없이 age만으로 검색하면 인덱스를 사용할 수 없었음
-- MySQL 8.0의 Index Skip Scan으로 가능해짐
SELECT * FROM employee WHERE age = 30;

-- 내부적으로 다음과 같이 동작:
-- gender = 'M' AND age = 30 → 인덱스 검색
-- gender = 'F' AND age = 30 → 인덱스 검색
-- 결과를 합침

Index Skip Scan은 첫 번째 컬럼의 카디널리티가 낮을 때(예: 성별처럼 고유 값이 2~3개 정도)만 효과적이다. 카디널리티가 높으면 오히려 비효율적이므로 옵티마이저가 Full Table Scan을 선택할 수 있다.

9. EXPLAIN 실행 계획 분석

쿼리의 성능을 분석하고 최적화하려면 EXPLAIN 명령어를 사용하여 실행 계획을 확인하는 것이 필수적이다. EXPLAIN은 MySQL 옵티마이저가 해당 쿼리를 어떻게 실행할 것인지를 보여준다.

1
2
3
4
5
EXPLAIN SELECT o.order_id, o.order_date, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01'
  AND o.status = 'COMPLETED';

EXPLAIN 결과의 핵심 컬럼들을 하나씩 살펴보자.

type 컬럼 - 접근 방식을 나타내며, 성능이 좋은 순서대로 나열하면 다음과 같다.

key 컬럼은 실제로 사용된 인덱스를 나타내고, rows 컬럼은 옵티마이저가 예상하는 검색 행 수를 나타낸다. Extra 컬럼은 추가 정보를 제공하며, 주요 값은 다음과 같다.

1
2
3
4
5
6
-- Extra 컬럼의 주요 값들:
-- "Using index"       : 커버링 인덱스 사용 (좋음)
-- "Using where"       : WHERE 절로 추가 필터링 (보통)
-- "Using temporary"   : 임시 테이블 사용 (주의)
-- "Using filesort"    : 추가 정렬 작업 필요 (주의)
-- "Using index condition" : 인덱스 컨디션 푸시다운 (ICP)

MySQL 8.0에서는 EXPLAIN ANALYZE를 사용하면 실제 실행 결과와 함께 실행 시간, 실제 행 수 등의 정보를 얻을 수 있어 더욱 정확한 분석이 가능하다.

1
2
3
4
5
6
7
8
9
10
11
12
EXPLAIN ANALYZE
SELECT customer_id, COUNT(*) as order_count
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY customer_id
HAVING order_count > 5;

-- 결과 예시:
-- -> Filter: (order_count > 5)  (actual time=15.3..15.3 rows=42 loops=1)
--     -> Group aggregate: count(*)  (actual time=12.1..15.2 rows=1250 loops=1)
--         -> Index range scan on orders using idx_date
--            (actual time=0.05..8.7 rows=35000 loops=1)

실행 계획을 분석할 때의 체크포인트는 다음과 같다. type이 ALL인 경우 인덱스 추가를 고려해야 하고, rows 값이 비정상적으로 큰 경우 조건 최적화가 필요하다. Using temporary나 Using filesort가 나타나면 인덱스나 쿼리 구조를 개선하여 제거할 수 있는지 검토해야 한다.

10. 인덱스가 사용되지 않는 경우 (인덱스 무효화 조건)

인덱스를 생성해두었더라도 특정 조건에서는 옵티마이저가 인덱스를 사용하지 않는다. 이러한 경우를 알고 있어야 비효율적인 쿼리를 작성하는 것을 방지할 수 있다.

인덱스 컬럼에 함수나 연산을 적용한 경우

1
2
3
4
5
6
7
8
9
-- 인덱스 사용 불가 (컬럼에 함수 적용)
SELECT * FROM orders WHERE YEAR(order_date) = 2026;
SELECT * FROM users WHERE UPPER(email) = 'USER@EXAMPLE.COM';
SELECT * FROM employee WHERE salary * 12 > 50000000;

-- 인덱스 사용 가능하도록 변환
SELECT * FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';
SELECT * FROM users WHERE email = 'user@example.com'; -- 대소문자 구분 없는 collation 사용
SELECT * FROM employee WHERE salary > 50000000 / 12;

컬럼에 함수를 적용하면 인덱스에 저장된 원래 값과 비교할 수 없기 때문에 인덱스를 사용할 수 없다. MySQL 8.0에서는 함수 기반 인덱스(Functional Index) 를 지원하여 이 문제를 해결할 수 있다.

1
2
3
-- MySQL 8.0 함수 기반 인덱스
CREATE INDEX idx_year ON orders((YEAR(order_date)));
SELECT * FROM orders WHERE YEAR(order_date) = 2026; -- 인덱스 사용 가능!

묵시적 타입 변환이 발생하는 경우

1
2
3
4
5
6
-- phone_number가 VARCHAR 타입인데 숫자로 비교
-- 묵시적 타입 변환이 발생하여 인덱스 사용 불가
SELECT * FROM users WHERE phone_number = 01012345678;

-- 올바른 방법 (문자열로 비교)
SELECT * FROM users WHERE phone_number = '01012345678';

LIKE 검색에서 와일드카드가 앞에 오는 경우

1
2
3
4
5
6
-- 인덱스 사용 불가 (와일드카드가 앞에)
SELECT * FROM products WHERE name LIKE '%노트북%';
SELECT * FROM products WHERE name LIKE '%Pro';

-- 인덱스 사용 가능 (와일드카드가 뒤에)
SELECT * FROM products WHERE name LIKE '삼성%';

NOT, <>, != 연산자 사용 시

부정 조건은 인덱스를 효율적으로 사용하기 어렵다. 옵티마이저가 Full Table Scan이 더 효율적이라고 판단하는 경우가 많다.

1
2
3
4
5
6
-- 인덱스를 타지 않을 가능성 높음
SELECT * FROM orders WHERE status != 'CANCELLED';
SELECT * FROM orders WHERE status NOT IN ('CANCELLED', 'REFUNDED');

-- 대안: 가능하다면 긍정 조건으로 변환
SELECT * FROM orders WHERE status IN ('PENDING', 'PROCESSING', 'COMPLETED', 'SHIPPED');

OR 조건 사용 시

OR 조건은 각 조건이 서로 다른 인덱스를 사용해야 할 수 있어 인덱스 활용이 제한적이다. MySQL은 Index Merge 최적화를 통해 일부 OR 조건에서도 인덱스를 사용할 수 있지만, 항상 보장되는 것은 아니다.

1
2
3
4
5
6
7
-- 인덱스를 효율적으로 사용하지 못할 수 있음
SELECT * FROM orders WHERE customer_id = 1001 OR order_date = '2026-01-28';

-- UNION으로 변환하여 각각 인덱스를 타도록 하는 것이 더 효율적일 수 있음
SELECT * FROM orders WHERE customer_id = 1001
UNION
SELECT * FROM orders WHERE order_date = '2026-01-28';

데이터 비율이 높아 옵티마이저가 Full Scan을 선택하는 경우

인덱스를 통해 검색할 행의 비율이 전체 테이블의 20~30% 이상이면, 옵티마이저는 인덱스를 사용하는 것보다 테이블 풀 스캔이 더 효율적이라고 판단할 수 있다. 인덱스를 통한 랜덤 I/O보다 풀 스캔의 순차 I/O가 더 빠를 수 있기 때문이다.

11. MySQL InnoDB의 인덱스 특성

MySQL의 기본 스토리지 엔진인 InnoDB는 인덱스와 관련하여 독특한 특성들을 가지고 있다. 이를 이해하는 것은 MySQL 환경에서의 효과적인 인덱스 설계에 필수적이다.

클러스터형 인덱스와 Primary Key

앞서 설명한 것처럼 InnoDB에서 모든 테이블은 반드시 클러스터형 인덱스를 가진다. PK 선택이 테이블의 전체적인 I/O 패턴과 성능에 지대한 영향을 미친다. AUTO_INCREMENT를 PK로 사용하면 삽입 시 항상 끝에 추가되므로 페이지 분할이 최소화된다. 반면 UUID를 PK로 사용하면 랜덤한 위치에 삽입이 일어나 빈번한 페이지 분할과 단편화가 발생한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 권장: AUTO_INCREMENT PK
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    username VARCHAR(100)
);

-- 비권장: UUID PK (성능 문제 발생 가능)
CREATE TABLE users_bad (
    id CHAR(36) PRIMARY KEY,  -- UUID
    email VARCHAR(255) UNIQUE,
    username VARCHAR(100)
);

-- MySQL 8.0에서 UUID를 사용해야 한다면 정렬 가능한 UUID 사용
-- UUID_TO_BIN(UUID(), true)를 사용하면 시간 기반 정렬이 가능
CREATE TABLE users_uuid (
    id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID(), true)),
    email VARCHAR(255) UNIQUE,
    username VARCHAR(100)
);

Change Buffer

InnoDB는 세컨더리 인덱스의 변경 사항을 즉시 디스크에 반영하지 않고, Change Buffer라는 메모리 영역에 임시 저장했다가 나중에 해당 인덱스 페이지가 읽힐 때 일괄 적용한다. 이는 세컨더리 인덱스의 쓰기 성능을 크게 향상시킨다. 단, UNIQUE 인덱스에는 Change Buffer가 적용되지 않는다. 유니크 제약 조건을 확인하기 위해 반드시 인덱스 페이지를 읽어야 하기 때문이다. 따라서 유니크 제약 조건이 반드시 필요한 경우가 아니라면 일반 인덱스를 사용하는 것이 쓰기 성능에 유리하다.

페이지 구조와 인덱스 크기

InnoDB는 데이터를 16KB 크기의 페이지(Page) 단위로 관리한다. 인덱스의 각 노드는 하나의 페이지에 해당하며, 페이지 하나에 더 많은 키를 담을 수 있을수록 트리의 높이가 낮아져 성능이 좋아진다. 따라서 인덱스 키의 크기를 가능한 한 작게 유지하는 것이 중요하다.

1
2
3
4
5
6
7
8
-- 인덱스 크기 확인
SELECT
    table_name,
    index_name,
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'mydb'
  AND stat_name = 'size';

12. JPA/Hibernate에서의 인덱스 활용

Java 백엔드 개발에서 JPA/Hibernate를 사용할 때 엔티티 클래스에 인덱스를 선언하는 방법과 인덱스를 효과적으로 활용하는 방법을 살펴보자.

@Table과 @Index 어노테이션

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
@Entity
@Table(
    name = "orders",
    indexes = {
        @Index(name = "idx_customer_id", columnList = "customer_id"),
        @Index(name = "idx_order_date", columnList = "order_date"),
        @Index(name = "idx_composite", columnList = "customer_id, status, order_date"),
        @Index(name = "idx_unique_order_number", columnList = "order_number", unique = true)
    }
)
public class Order {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "customer_id", nullable = false)
    private Long customerId;

    @Column(name = "order_number", unique = true, nullable = false)
    private String orderNumber;

    @Column(name = "order_date", nullable = false)
    private LocalDateTime orderDate;

    @Enumerated(EnumType.STRING)
    @Column(name = "status", nullable = false, length = 20)
    private OrderStatus status;

    @Column(name = "total_amount", precision = 10, scale = 2)
    private BigDecimal totalAmount;
}

JPQL과 인덱스 활용

JPQL이나 QueryDSL을 사용할 때도 인덱스가 효과적으로 사용되도록 쿼리를 작성해야 한다. 특히 N+1 문제를 해결하면서도 인덱스를 활용할 수 있도록 설계하는 것이 중요하다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {

    // customer_id 인덱스 활용
    List<Order> findByCustomerId(Long customerId);

    // 복합 인덱스 (customer_id, status, order_date) 활용
    List<Order> findByCustomerIdAndStatusAndOrderDateBetween(
        Long customerId,
        OrderStatus status,
        LocalDateTime startDate,
        LocalDateTime endDate
    );

    // 커버링 인덱스를 활용한 최적화 쿼리
    @Query("SELECT o.id FROM Order o WHERE o.customerId = :customerId ORDER BY o.orderDate DESC")
    List<Long> findIdsByCustomerIdOrderByOrderDateDesc(@Param("customerId") Long customerId,
                                                        Pageable pageable);
}

Spring Data JPA에서의 페이지네이션과 인덱스

대량의 데이터를 페이지네이션할 때 인덱스를 효과적으로 활용하는 것이 매우 중요하다. OFFSET 기반 페이지네이션은 페이지가 커질수록 성능이 떨어지므로, 커서 기반(Keyset) 페이지네이션을 사용하는 것이 좋다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {

    // OFFSET 기반 (페이지가 커지면 느려짐)
    @Query("SELECT o FROM Order o WHERE o.customerId = :customerId ORDER BY o.orderDate DESC")
    Page<Order> findByCustomerIdPaged(@Param("customerId") Long customerId, Pageable pageable);

    // 커서 기반 (일정한 성능)
    @Query("SELECT o FROM Order o WHERE o.customerId = :customerId AND o.orderDate < :cursor " +
           "ORDER BY o.orderDate DESC")
    List<Order> findByCustomerIdWithCursor(
        @Param("customerId") Long customerId,
        @Param("cursor") LocalDateTime cursor,
        Pageable pageable
    );
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
// 커서 기반 페이지네이션 서비스 코드
@Service
@RequiredArgsConstructor
public class OrderService {

    private final OrderRepository orderRepository;

    public List<OrderResponse> getOrders(Long customerId, LocalDateTime cursor, int size) {
        Pageable pageable = PageRequest.of(0, size);

        List<Order> orders;
        if (cursor == null) {
            orders = orderRepository.findByCustomerIdPaged(customerId,
                PageRequest.of(0, size, Sort.by(Sort.Direction.DESC, "orderDate"))).getContent();
        } else {
            orders = orderRepository.findByCustomerIdWithCursor(customerId, cursor, pageable);
        }

        return orders.stream()
            .map(OrderResponse::from)
            .collect(Collectors.toList());
    }
}

Hibernate 쿼리 힌트와 인덱스

Hibernate에서는 네이티브 쿼리를 통해 인덱스 힌트를 직접 지정할 수도 있다. 다만 이는 데이터베이스에 종속적이므로 가능하면 사용하지 않는 것이 좋고, 옵티마이저가 올바른 인덱스를 선택하도록 쿼리와 인덱스를 설계하는 것이 바람직하다.

1
2
3
4
5
@Query(value = "SELECT /*+ INDEX(orders idx_composite) */ * FROM orders " +
               "WHERE customer_id = :customerId AND status = :status",
       nativeQuery = true)
List<Order> findWithIndexHint(@Param("customerId") Long customerId,
                              @Param("status") String status);

13. 인덱스 설계 Best Practices

실무에서 인덱스를 설계할 때 따라야 할 핵심 원칙들을 정리하면 다음과 같다.

원칙 1: 쿼리 패턴을 먼저 분석하라

인덱스는 테이블 구조가 아닌 쿼리 패턴에 기반하여 설계해야 한다. 어떤 쿼리가 가장 빈번하게 실행되는지, 어떤 쿼리의 응답 시간이 가장 중요한지를 파악하는 것이 첫 번째 단계이다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- MySQL의 슬로우 쿼리 로그 활성화
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 1초 이상 소요되는 쿼리 기록
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';

-- 현재 실행 중인 쿼리 확인
SHOW PROCESSLIST;

-- 쿼리 통계 확인 (Performance Schema)
SELECT
    digest_text,
    count_star AS exec_count,
    ROUND(avg_timer_wait / 1000000000, 2) AS avg_time_ms,
    sum_rows_examined,
    sum_rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC
LIMIT 20;

원칙 2: 선택도가 높은 컬럼에 인덱스를 생성하라

선택도(Selectivity)는 컬럼의 고유 값 수를 전체 행 수로 나눈 비율이다. 선택도가 1에 가까울수록(즉, 고유한 값이 많을수록) 인덱스 효과가 높다.

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 각 컬럼의 선택도 확인
SELECT
    COUNT(DISTINCT customer_id) / COUNT(*) AS customer_selectivity,
    COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
    COUNT(DISTINCT order_date) / COUNT(*) AS date_selectivity,
    COUNT(DISTINCT email) / COUNT(*) AS email_selectivity
FROM orders;

-- 결과 예시:
-- customer_selectivity: 0.15   (낮음)
-- status_selectivity:   0.0001 (매우 낮음 - 인덱스 효과 적음)
-- date_selectivity:     0.08   (보통)
-- email_selectivity:    1.00   (매우 높음 - 인덱스 효과 높음)

원칙 3: 인덱스 개수를 적절히 유지하라

테이블당 인덱스 개수는 보통 3~5개 이내로 유지하는 것이 좋다. 인덱스가 많아질수록 INSERT, UPDATE, DELETE 성능이 저하되고, 옵티마이저가 최적의 실행 계획을 선택하기 어려워진다. 이미 존재하는 인덱스로 커버 가능한 쿼리에 대해 중복 인덱스를 만들지 않도록 주의해야 한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 중복 인덱스 확인
-- 인덱스 (A, B)가 있으면 인덱스 (A)는 중복이다
SELECT
    table_name,
    index_name,
    GROUP_CONCAT(column_name ORDER BY seq_in_index) AS columns
FROM information_schema.statistics
WHERE table_schema = 'mydb'
GROUP BY table_name, index_name
ORDER BY table_name;

-- 사용되지 않는 인덱스 확인 (MySQL 8.0 sys 스키마)
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'mydb';

원칙 4: PK는 짧고 순차적으로

InnoDB에서 PK의 크기는 모든 세컨더리 인덱스의 크기에 영향을 미친다. BIGINT(8바이트)를 AUTO_INCREMENT로 사용하는 것이 가장 이상적이다. VARCHAR나 UUID를 PK로 사용하면 저장 공간과 성능 모두에서 불리하다.

원칙 5: 복합 인덱스를 적극 활용하라

여러 개의 단일 컬럼 인덱스보다 하나의 잘 설계된 복합 인덱스가 훨씬 효율적일 수 있다. 복합 인덱스는 여러 쿼리 패턴을 동시에 지원할 수 있다.

1
2
3
4
5
6
7
8
9
-- 단일 인덱스 3개보다
CREATE INDEX idx_a ON orders(customer_id);
CREATE INDEX idx_b ON orders(status);
CREATE INDEX idx_c ON orders(order_date);

-- 잘 설계된 복합 인덱스 1개가 더 효율적
CREATE INDEX idx_composite ON orders(customer_id, status, order_date);
-- customer_id만 검색, customer_id + status 검색,
-- customer_id + status + order_date 검색 모두 지원

14. 실무에서의 쿼리 최적화 사례

사례 1: 대용량 테이블의 페이지네이션 최적화

주문 테이블에 1억 건의 데이터가 있고, 특정 고객의 최근 주문을 페이지 단위로 조회해야 하는 상황을 가정하자.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 문제가 되는 쿼리: OFFSET이 커지면 매우 느려짐
SELECT * FROM orders
WHERE customer_id = 1001
ORDER BY order_date DESC
LIMIT 20 OFFSET 100000;
-- 100,020건을 읽고 100,000건을 버림 → 매우 비효율적

-- 최적화 방법 1: 커서 기반 페이지네이션
SELECT * FROM orders
WHERE customer_id = 1001
  AND order_date < '2025-06-15 14:30:00'  -- 이전 페이지의 마지막 order_date
ORDER BY order_date DESC
LIMIT 20;

-- 최적화 방법 2: 지연 조인 (Deferred Join)
SELECT o.*
FROM orders o
INNER JOIN (
    SELECT order_id
    FROM orders
    WHERE customer_id = 1001
    ORDER BY order_date DESC
    LIMIT 20 OFFSET 100000
) sub ON o.order_id = sub.order_id;
-- 서브쿼리에서 커버링 인덱스를 타서 PK만 빠르게 추출

지연 조인이 효과적인 이유는, 서브쿼리에서 인덱스 (customer_id, order_date)와 PK만 사용하므로 커버링 인덱스가 적용되기 때문이다. 실제 데이터 행 접근은 최종 20건에 대해서만 발생한다.

사례 2: 복합 조건 검색의 인덱스 최적화

상품 검색 페이지에서 카테고리, 가격 범위, 브랜드, 재고 여부 등 다양한 조건이 조합되는 검색 쿼리를 최적화해야 하는 상황이다.

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 다양한 검색 조건 조합이 가능한 상황
SELECT * FROM products
WHERE category_id = 10
  AND price BETWEEN 10000 AND 50000
  AND brand_id IN (1, 5, 12)
  AND is_available = 1
ORDER BY created_at DESC
LIMIT 20;

-- 가장 효과적인 인덱스 설계
-- 등호 조건 → 범위 조건 → 정렬 순서로 컬럼 배치
CREATE INDEX idx_product_search ON products(category_id, is_available, brand_id, price);
CREATE INDEX idx_product_search_sort ON products(category_id, is_available, created_at);

실제로는 모든 검색 조건의 조합을 하나의 인덱스로 커버하기 어렵다. 가장 빈번한 쿼리 패턴 2~3개를 선별하여 각각에 최적화된 인덱스를 설계하는 것이 현실적인 접근 방법이다.

사례 3: COUNT 쿼리 최적화

대용량 테이블에서 조건부 COUNT는 매우 비용이 큰 연산이다. 이를 최적화하는 방법을 살펴보자.

1
2
3
4
5
6
7
8
9
10
11
-- 느린 COUNT 쿼리
SELECT COUNT(*) FROM orders WHERE status = 'COMPLETED' AND order_date >= '2026-01-01';

-- 커버링 인덱스로 최적화
CREATE INDEX idx_count ON orders(status, order_date);
-- 이 인덱스만으로 COUNT를 처리할 수 있으므로 테이블 접근 불필요

-- 정확한 수가 필요 없다면 근사치 사용
SELECT table_rows
FROM information_schema.tables
WHERE table_schema = 'mydb' AND table_name = 'orders';

사례 4: JOIN 쿼리의 인덱스 최적화

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 주문과 주문상세를 조인하는 쿼리
SELECT o.order_id, o.order_date, od.product_id, od.quantity, od.price
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
WHERE o.customer_id = 1001
  AND o.order_date >= '2026-01-01'
ORDER BY o.order_date DESC;

-- 필요한 인덱스:
-- orders 테이블: (customer_id, order_date) 인덱스 → WHERE + ORDER BY
-- order_details 테이블: (order_id) 인덱스 → JOIN 조건
CREATE INDEX idx_orders_search ON orders(customer_id, order_date);
CREATE INDEX idx_od_order ON order_details(order_id);

JOIN 쿼리에서는 드라이빙 테이블(Driving Table)과 드리븐 테이블(Driven Table)의 관계를 이해하는 것이 중요하다. MySQL의 Nested Loop Join에서는 드라이빙 테이블의 결과 건수만큼 드리븐 테이블을 반복 조회하므로, 드리븐 테이블의 조인 컬럼에 인덱스가 반드시 있어야 한다. 그렇지 않으면 드라이빙 테이블의 각 행마다 드리븐 테이블 풀 스캔이 발생하여 극심한 성능 저하가 나타난다.

사례 5: 실행 계획 비교를 통한 튜닝

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
// Spring Boot에서 슬로우 쿼리 모니터링 설정
@Configuration
public class DataSourceConfig {

    @Bean
    public DataSource dataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
        config.setUsername("root");
        config.setPassword("password");

        // 커넥션 풀 설정
        config.setMaximumPoolSize(20);
        config.setMinimumIdle(5);
        config.setConnectionTimeout(30000);

        return new HikariDataSource(config);
    }
}

// application.yml에서 JPA 쿼리 로깅 설정
// spring:
//   jpa:
//     properties:
//       hibernate:
//         format_sql: true
//         generate_statistics: true
//     show-sql: true
// logging:
//   level:
//     org.hibernate.SQL: DEBUG
//     org.hibernate.type.descriptor.sql.BasicBinder: TRACE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
// 슬로우 쿼리 감지를 위한 AOP 설정
@Aspect
@Component
@Slf4j
public class QueryPerformanceAspect {

    @Around("execution(* com.example..repository.*.*(..))")
    public Object monitorQueryPerformance(ProceedingJoinPoint joinPoint) throws Throwable {
        long startTime = System.currentTimeMillis();

        Object result = joinPoint.proceed();

        long executionTime = System.currentTimeMillis() - startTime;

        if (executionTime > 1000) {
            log.warn("Slow query detected! Method: {}, Execution time: {}ms",
                joinPoint.getSignature().toShortString(), executionTime);
        }

        return result;
    }
}

마무리

데이터베이스 인덱스는 애플리케이션 성능의 핵심 요소이다. 인덱스의 내부 구조와 동작 원리를 깊이 이해하면 단순히 “인덱스를 추가한다”는 수준을 넘어, 쿼리 패턴에 최적화된 인덱스를 설계하고 기존 인덱스의 문제점을 정확히 진단할 수 있게 된다.

핵심 내용을 다시 정리하면 다음과 같다. B+Tree는 범위 검색과 정렬에 최적화된 구조이며 대부분의 RDBMS에서 기본 인덱스로 사용된다. 클러스터형 인덱스와 세컨더리 인덱스의 차이를 이해해야 InnoDB에서의 인덱스 설계를 올바르게 할 수 있다. 복합 인덱스에서 컬럼 순서는 성능에 결정적인 영향을 미치며, 등호 조건 컬럼을 앞에 배치하는 것이 원칙이다. 커버링 인덱스는 테이블 접근을 제거하여 극적인 성능 향상을 가져올 수 있다. 그리고 항상 EXPLAIN으로 실행 계획을 확인하고, 인덱스가 의도대로 사용되고 있는지 검증해야 한다.

인덱스 최적화는 한 번 설계하고 끝나는 것이 아니라, 데이터의 증가와 쿼리 패턴의 변화에 따라 지속적으로 모니터링하고 조정해야 하는 영역이다. 슬로우 쿼리 로그, Performance Schema, EXPLAIN ANALYZE 등의 도구를 적극 활용하여 데이터베이스의 건강 상태를 꾸준히 관리하는 것이 실무에서 가장 중요한 습관이다.