인덱스와 쿼리 성능 최적화 완벽 가이드

데이터베이스 성능의 핵심인 인덱스를 처음부터 끝까지 배워봅니다. B-Tree 구조부터 실행 계획 분석까지, 실무에서 바로 사용할 수 있는 인덱스 최적화 전략을 초급자도 이해할 수 있게 설명합니다.

---

목차

1. 인덱스란_무엇인가
2. B-Tree_인덱스_구조_이해
3. CREATE_INDEX로_인덱스_생성
4. 단일_컬럼_vs_복합_인덱스
5. EXPLAIN으로_실행_계획_분석
6. 인덱스_최적화_전략

---

1. 인덱스란_무엇인가

시작하며

여러분이 회원 테이블에서 특정 사용자를 찾을 때, 쿼리가 너무 느려서 답답했던 적 있나요? 10만 건의 데이터 중에서 단 1명의 정보를 찾는데 몇 초씩 걸리는 상황 말이죠.

이런 문제는 실제 개발 현장에서 정말 자주 발생합니다. 데이터베이스는 기본적으로 테이블의 첫 번째 행부터 마지막 행까지 하나하나 확인하면서 원하는 데이터를 찾습니다.

이를 '풀 테이블 스캔'이라고 하는데, 데이터가 많아질수록 성능이 급격히 나빠집니다. 바로 이럴 때 필요한 것이 인덱스입니다.

인덱스는 마치 책의 목차처럼 데이터의 위치를 빠르게 찾을 수 있게 해주어, 검색 속도를 수십 배, 수백 배까지 향상시킬 수 있습니다.

개요

간단히 말해서, 인덱스는 데이터베이스 테이블의 검색 속도를 높여주는 자료구조입니다. 책 뒤에 있는 색인처럼, 특정 값이 어디에 있는지 빠르게 찾을 수 있도록 도와줍니다.

왜 인덱스가 필요한지 실무 관점에서 설명하자면, 사용자 경험과 직결되기 때문입니다. 로그인할 때 이메일로 사용자를 찾거나, 상품 목록을 카테고리별로 보여주거나, 주문 내역을 날짜순으로 조회하는 경우에 매우 유용합니다.

이런 작업들이 1초 이상 걸린다면 사용자들은 불편함을 느낍니다. 기존에는 데이터베이스가 모든 행을 하나씩 확인해야 했다면, 인덱스를 사용하면 정렬된 목록에서 이진 탐색처럼 빠르게 찾을 수 있습니다.

1만 건의 데이터라면 최대 1만 번 확인하던 것을, 단 14번 정도의 비교로 찾을 수 있게 됩니다. 인덱스의 핵심 특징은 세 가지입니다.

첫째, 검색 속도를 획기적으로 개선합니다. 둘째, 정렬된 상태로 데이터를 관리합니다.

셋째, 추가 저장 공간이 필요합니다. 이러한 특징들이 중요한 이유는 인덱스를 언제, 어떻게 사용할지 결정하는 기준이 되기 때문입니다.

코드 예제

-- 기본적인 인덱스 조회 예시
-- 인덱스가 없을 때: 전체 테이블 스캔 (느림)
SELECT * FROM users WHERE email = 'user@example.com';
-- 실행 시간: 1000ms (100만 건 기준)

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

-- 인덱스가 있을 때: 인덱스를 통한 빠른 검색
SELECT * FROM users WHERE email = 'user@example.com';
-- 실행 시간: 5ms (100만 건 기준)

-- 인덱스 사용 확인
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

설명

이것이 하는 일: 인덱스는 테이블의 특정 컬럼 값을 정렬된 형태로 별도로 저장하여, 검색 시 전체 테이블을 스캔하지 않고 빠르게 원하는 데이터를 찾을 수 있게 합니다. 첫 번째로, 인덱스가 없는 상황에서 SELECT * FROM users WHERE email = 'user@example.com'을 실행하면 데이터베이스는 첫 번째 행부터 마지막 행까지 모든 행의 email 컬럼을 확인해야 합니다.

이것이 바로 풀 테이블 스캔(Full Table Scan)입니다. 100만 건의 데이터가 있다면 최악의 경우 100만 번의 비교가 필요합니다.

그 다음으로, CREATE INDEX idx_users_email ON users(email)로 인덱스를 생성하면, 데이터베이스는 email 컬럼의 값들을 정렬하여 별도의 자료구조(B-Tree)에 저장합니다. 이 자료구조는 'user@example.com'이 어느 행에 있는지 빠르게 찾을 수 있도록 설계되어 있습니다.

마지막으로, 같은 쿼리를 다시 실행하면 데이터베이스는 인덱스를 사용하여 검색합니다. 100만 건의 데이터에서도 약 20번 정도의 비교만으로 원하는 데이터를 찾을 수 있습니다.

이것이 O(n)에서 O(log n)으로 개선되는 것입니다. 여러분이 이 코드를 사용하면 사용자 로그인 시간이 1초에서 0.01초로 단축되고, 대규모 데이터 조회 시에도 빠른 응답 속도를 유지할 수 있습니다.

또한 데이터가 증가해도 성능 저하가 선형적이지 않고 로그 스케일로 증가하므로 확장성이 크게 향상됩니다.

실전 팁

💡 WHERE 절에 자주 사용되는 컬럼에 인덱스를 생성하세요. 특히 로그인 시 email, username처럼 검색 조건으로 많이 쓰이는 컬럼은 필수입니다.

💡 모든 컬럼에 인덱스를 만들지 마세요. 인덱스는 저장 공간을 차지하고 INSERT, UPDATE, DELETE 시 성능을 저하시킵니다. 실제로 검색에 사용되는 컬럼만 선택하세요.

💡 EXPLAIN 명령어로 쿼리가 실제로 인덱스를 사용하는지 확인하세요. 인덱스를 만들었어도 쿼리 작성 방식에 따라 사용되지 않을 수 있습니다.

💡 작은 테이블(수천 건 이하)에는 인덱스가 오히려 성능을 저하시킬 수 있습니다. 풀 테이블 스캔이 더 빠를 수 있으니, 실제 데이터 규모를 고려하세요.

💡 Primary Key에는 자동으로 인덱스가 생성됩니다. id 컬럼에 별도로 인덱스를 만들 필요가 없으니 중복 생성을 피하세요.

---

2. B-Tree_인덱스_구조_이해

시작하며

여러분이 인덱스를 생성했는데도 생각보다 성능이 개선되지 않아서 당황한 적 있나요? 어떤 쿼리는 빨라지는데 어떤 쿼리는 여전히 느린 상황 말이죠.

이런 문제는 인덱스의 내부 구조를 이해하지 못해서 발생합니다. 인덱스가 "어떻게" 데이터를 저장하고 검색하는지 알아야, 왜 어떤 쿼리에서는 인덱스가 효과적이고 어떤 쿼리에서는 그렇지 않은지 이해할 수 있습니다.

바로 이럴 때 필요한 것이 B-Tree 구조에 대한 이해입니다. B-Tree는 대부분의 데이터베이스가 기본적으로 사용하는 인덱스 구조로, 이를 이해하면 인덱스를 효과적으로 설계할 수 있습니다.

개요

간단히 말해서, B-Tree(Balanced Tree)는 데이터를 계층적으로 저장하는 균형 잡힌 트리 구조입니다. 나무가 가지를 뻗듯이 데이터가 여러 레벨로 나뉘어 저장되어 있습니다.

왜 B-Tree가 필요한지 실무 관점에서 설명하자면, 대용량 데이터를 빠르게 검색하면서도 삽입과 삭제 시에도 성능을 유지해야 하기 때문입니다. 예를 들어, 100만 건의 데이터에서 특정 값을 찾을 때 최대 3-4단계만 거치면 되고, 새 데이터를 추가해도 트리의 균형이 자동으로 유지됩니다.

기존의 이진 탐색 트리는 데이터가 한쪽으로 치우치면 성능이 나빠지는 문제가 있었다면, B-Tree는 항상 균형을 유지하여 일정한 성능을 보장합니다. 또한 각 노드가 여러 개의 값을 가질 수 있어 디스크 I/O 횟수를 줄입니다.

B-Tree의 핵심 특징은 세 가지입니다. 첫째, 모든 리프 노드가 같은 레벨에 있어 검색 시간이 균일합니다.

둘째, 데이터가 정렬된 상태로 유지됩니다. 셋째, 범위 검색(BETWEEN, >, < 등)에 매우 효율적입니다.

이러한 특징들이 중요한 이유는 실무에서 사용하는 대부분의 쿼리 패턴을 효율적으로 처리할 수 있기 때문입니다.

코드 예제

-- B-Tree 인덱스의 효율성 비교
-- 1. 단일 값 검색 (인덱스 최적)
SELECT * FROM orders WHERE order_id = 12345;
-- B-Tree 탐색: Root -> Branch -> Leaf (3단계)

-- 2. 범위 검색 (인덱스 효율적)
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';
-- B-Tree에서 시작점 찾고 순차적으로 스캔

-- 3. 정렬 조회 (인덱스가 이미 정렬됨)
SELECT * FROM products ORDER BY price ASC LIMIT 10;
-- 별도 정렬 불필요, 인덱스 순서대로 읽기

-- 4. LIKE 패턴 (앞부분 매칭만 인덱스 사용)
SELECT * FROM users WHERE name LIKE '김%';  -- 인덱스 사용 O
SELECT * FROM users WHERE name LIKE '%김';  -- 인덱스 사용 X

설명

이것이 하는 일: B-Tree 인덱스는 데이터를 루트(Root), 브랜치(Branch), 리프(Leaf) 노드의 3단계 계층으로 저장하여, 어떤 데이터든 일정한 단계만 거치면 찾을 수 있게 합니다. 첫 번째로, 단일 값 검색인 WHERE order_id = 12345의 경우, 데이터베이스는 루트 노드에서 시작하여 12345가 어느 범위에 속하는지 판단합니다.

예를 들어 루트 노드에 [10000, 50000, 90000]이 있다면, 12345는 10000과 50000 사이이므로 두 번째 브랜치로 이동합니다. 이렇게 몇 단계만 거치면 정확한 위치를 찾습니다.

그 다음으로, 범위 검색인 BETWEEN '2024-01-01' AND '2024-01-31'은 B-Tree의 정렬 특성을 활용합니다. 먼저 시작점('2024-01-01')을 B-Tree에서 찾고, 그 다음부터는 리프 노드를 순차적으로 읽으면서 종료점('2024-01-31')까지 스캔합니다.

리프 노드들은 연결 리스트처럼 연결되어 있어 순차 스캔이 빠릅니다. 정렬 조회인 ORDER BY price ASC는 B-Tree가 이미 price 순서로 정렬되어 있기 때문에 별도의 정렬 작업이 필요 없습니다.

인덱스를 처음부터 순서대로 읽기만 하면 되므로 매우 빠릅니다. 하지만 LIKE '%김'처럼 뒷부분 매칭은 B-Tree의 정렬 특성을 활용할 수 없어 인덱스를 사용하지 못합니다.

여러분이 B-Tree 구조를 이해하면 어떤 쿼리가 인덱스를 효과적으로 사용하는지 예측할 수 있고, 인덱스 설계 시 어떤 컬럼을 선택해야 할지 판단할 수 있습니다. 또한 EXPLAIN 결과를 해석할 때 왜 특정 인덱스가 선택되었는지, 혹은 왜 선택되지 않았는지 이해할 수 있어 쿼리 최적화가 훨씬 쉬워집니다.

실전 팁

💡 등호(=) 조건이 가장 효율적이고, 범위 조건(>, <, BETWEEN)도 효율적이지만, 부정 조건(!=, NOT IN)은 인덱스를 제대로 활용하지 못합니다.

💡 LIKE 패턴에서 앞부분 매칭('%'가 뒤에)만 인덱스를 사용합니다. 'Kim%'은 가능하지만 '%Kim'은 불가능합니다. 전체 텍스트 검색이 필요하면 Full-Text Index를 고려하세요.

💡 ORDER BY에 사용되는 컬럼에 인덱스가 있으면 별도의 정렬 작업(filesort)을 피할 수 있어 성능이 크게 향상됩니다. 특히 LIMIT과 함께 사용할 때 효과적입니다.

💡 함수를 사용하면 인덱스가 무효화됩니다. WHERE YEAR(created_at) = 2024 대신 WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'을 사용하세요.

💡 B-Tree의 높이는 데이터 양에 따라 결정됩니다. 100만 건은 약 3-4레벨, 1억 건도 약 4-5레벨이므로 데이터가 많아져도 성능 저하가 완만합니다.

---

3. CREATE_INDEX로_인덱스_생성

시작하며

여러분이 인덱스가 필요하다는 걸 알았는데, 막상 생성하려니 어떤 옵션을 사용해야 할지, 어떤 이름을 지어야 할지 막막한 적 있나요? 잘못 만들면 나중에 관리가 어려워지거나 성능 개선 효과가 없을까봐 걱정되기도 하죠.

이런 문제는 인덱스 생성 문법과 베스트 프랙티스를 모를 때 발생합니다. 인덱스는 한 번 만들면 오래 사용하는 경우가 많기 때문에, 처음부터 제대로 만드는 것이 중요합니다.

잘못 만든 인덱스는 저장 공간만 낭비하고 INSERT/UPDATE 성능만 떨어뜨립니다. 바로 이럴 때 필요한 것이 CREATE INDEX 문법에 대한 정확한 이해입니다.

기본 문법부터 UNIQUE 인덱스, 부분 인덱스까지 실무에서 자주 사용하는 패턴들을 배워봅시다.

개요

간단히 말해서, CREATE INDEX는 테이블의 특정 컬럼에 대한 인덱스를 생성하는 SQL 명령어입니다. 인덱스 이름, 대상 테이블, 대상 컬럼을 지정하여 검색 성능을 향상시킵니다.

왜 제대로 된 인덱스 생성이 필요한지 실무 관점에서 설명하자면, 인덱스 이름의 일관성은 유지보수성을 높이고, 적절한 옵션 선택은 성능과 데이터 무결성을 보장하기 때문입니다. 예를 들어, 이메일 컬럼은 UNIQUE 인덱스로 만들어 중복을 방지하고, 자주 검색되는 status 컬럼은 일반 인덱스로 만드는 것처럼 목적에 맞게 선택해야 합니다.

기존에는 테이블 생성 시에만 인덱스를 정의했다면, CREATE INDEX를 사용하면 나중에라도 필요할 때 추가할 수 있습니다. 또한 프로덕션 환경에서도 ONLINE 옵션을 사용하면 서비스 중단 없이 인덱스를 생성할 수 있습니다.

CREATE INDEX의 핵심 특징은 세 가지입니다. 첫째, 명확한 네이밍 규칙으로 인덱스를 관리하기 쉽게 만듭니다.

둘째, UNIQUE 옵션으로 데이터 무결성을 보장할 수 있습니다. 셋째, WHERE 절을 사용한 부분 인덱스로 저장 공간을 절약할 수 있습니다.

이러한 특징들이 중요한 이유는 인덱스가 단순히 성능만이 아니라 데이터 품질과 관리 효율성에도 영향을 주기 때문입니다.

코드 예제

-- 1. 기본 인덱스 생성 (네이밍 규칙: idx_테이블명_컬럼명)
CREATE INDEX idx_users_email ON users(email);

-- 2. UNIQUE 인덱스 (중복 방지)
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- 3. 복합 인덱스 (여러 컬럼)
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- 4. 부분 인덱스 (조건부 인덱스, PostgreSQL)
CREATE INDEX idx_orders_active ON orders(user_id)
WHERE status = 'active';

-- 5. ONLINE 인덱스 생성 (서비스 중단 없이)
CREATE INDEX CONCURRENTLY idx_products_category ON products(category);

-- 인덱스 목록 확인
SHOW INDEX FROM users;

설명

이것이 하는 일: CREATE INDEX는 지정된 테이블의 컬럼에 대한 검색용 자료구조를 생성하여, 이후 해당 컬럼을 사용하는 쿼리의 성능을 향상시킵니다. 첫 번째로, 기본 인덱스인 CREATE INDEX idx_users_email ON users(email)은 users 테이블의 email 컬럼에 대한 B-Tree 인덱스를 생성합니다.

네이밍 규칙으로 'idx_테이블명_컬럼명'을 사용하면 어떤 인덱스가 어느 테이블의 어떤 컬럼에 있는지 한눈에 알 수 있어 관리가 쉽습니다. 그 다음으로, UNIQUE 인덱스인 CREATE UNIQUE INDEX idx_users_username ON users(username)은 검색 성능을 높이면서 동시에 username의 중복을 데이터베이스 레벨에서 방지합니다.

이는 애플리케이션 레벨에서 검증하는 것보다 안전하며, 동시성 문제도 해결합니다. 두 사용자가 동시에 같은 username으로 가입하려 해도 데이터베이스가 막아줍니다.

복합 인덱스인 CREATE INDEX idx_orders_user_date ON orders(user_id, order_date)는 user_id와 order_date를 함께 사용하는 쿼리를 최적화합니다. 예를 들어 "특정 사용자의 최근 주문"을 조회할 때 매우 효율적입니다.

부분 인덱스인 WHERE status = 'active'는 활성 주문만 인덱싱하여 저장 공간을 절약하고 인덱스 크기를 줄입니다. 여러분이 이 코드를 사용하면 상황에 맞는 최적의 인덱스를 생성할 수 있습니다.

이메일과 사용자명은 UNIQUE 인덱스로 중복을 방지하고, 주문 조회는 복합 인덱스로 속도를 높이고, 대용량 테이블은 부분 인덱스로 효율을 극대화할 수 있습니다. 또한 CONCURRENTLY 옵션으로 운영 중인 서비스에 영향 없이 인덱스를 추가할 수 있습니다.

실전 팁

💡 인덱스 이름은 'idx_테이블명_컬럼명' 규칙을 따르세요. 나중에 'SHOW INDEX'로 조회할 때 어떤 인덱스인지 바로 알 수 있습니다.

💡 UNIQUE 인덱스는 성능과 무결성을 동시에 제공합니다. email, username처럼 중복되면 안 되는 컬럼은 UNIQUE 인덱스로 만드세요.

💡 대용량 테이블에 인덱스를 추가할 때는 CONCURRENTLY(PostgreSQL) 또는 ONLINE(MySQL) 옵션을 사용하세요. 테이블 잠금 없이 인덱스를 생성할 수 있습니다.

💡 인덱스 생성 시간은 데이터 양에 비례합니다. 수백만 건 이상의 테이블에서는 수십 분 이상 걸릴 수 있으니, 서비스 부하가 적은 시간대에 실행하세요.

💡 불필요한 인덱스는 과감히 삭제하세요. 'DROP INDEX idx_name'으로 사용하지 않는 인덱스를 제거하면 INSERT/UPDATE 성능이 개선되고 저장 공간이 절약됩니다.

---

4. 단일_컬럼_vs_복합_인덱스

시작하며

여러분이 WHERE 절에 여러 컬럼을 사용하는 쿼리를 작성했는데, 각 컬럼마다 인덱스를 따로 만들어야 할지, 아니면 하나로 합쳐야 할지 고민한 적 있나요? "user_id와 status를 함께 검색하는데, 인덱스를 2개 만들면 2배로 빨라지나요?"라는 질문 말이죠.

이런 혼란은 단일 컬럼 인덱스와 복합 인덱스의 차이를 정확히 이해하지 못해서 발생합니다. 잘못 선택하면 인덱스를 여러 개 만들었는데도 실제로는 하나만 사용되거나, 심지어 전혀 사용되지 않을 수도 있습니다.

바로 이럴 때 필요한 것이 인덱스 설계 전략입니다. 언제 단일 컬럼 인덱스를 사용하고, 언제 복합 인덱스를 사용해야 하는지 명확히 알아야 효율적인 인덱스를 만들 수 있습니다.

개요

간단히 말해서, 단일 컬럼 인덱스는 하나의 컬럼만 인덱싱하고, 복합 인덱스(Composite Index)는 여러 컬럼을 순서대로 인덱싱합니다. 전화번호부에서 단일 인덱스는 이름만 찾는 것이고, 복합 인덱스는 '성 → 이름' 순서로 찾는 것과 같습니다.

왜 이 차이가 중요한지 실무 관점에서 설명하자면, 쿼리 패턴에 따라 성능이 완전히 달라지기 때문입니다. 예를 들어, "특정 사용자의 활성 주문"을 자주 조회한다면 (user_id, status) 복합 인덱스가 훨씬 효율적입니다.

반면 user_id만 검색하거나 status만 검색하는 경우가 많다면 각각 단일 인덱스를 만드는 게 나을 수 있습니다. 기존에는 "일단 각 컬럼마다 인덱스를 만들면 되겠지"라고 생각했다면, 실제로는 데이터베이스가 한 번에 하나의 인덱스만 주로 사용한다는 것을 알아야 합니다.

WHERE user_id = 1 AND status = 'active'라는 쿼리에 두 개의 단일 인덱스가 있어도, 보통 하나만 사용되고 나머지는 필터링됩니다. 복합 인덱스의 핵심 특징은 세 가지입니다.

첫째, 컬럼 순서가 매우 중요합니다(user_id, status와 status, user_id는 완전히 다름). 둘째, 왼쪽 컬럼만으로도 사용 가능합니다(Leftmost Prefix).

셋째, 여러 컬럼 조건을 한 번에 처리하여 더 빠릅니다. 이러한 특징들이 중요한 이유는 인덱스 개수를 줄이면서도 더 나은 성능을 얻을 수 있기 때문입니다.

코드 예제

-- 시나리오: 사용자별 활성 주문 조회

-- 방법 1: 단일 컬럼 인덱스 2개 (비효율)
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

SELECT * FROM orders
WHERE user_id = 1001 AND status = 'active';
-- 실행 계획: idx_orders_user 사용 후 status 필터링

-- 방법 2: 복합 인덱스 (효율적)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

SELECT * FROM orders
WHERE user_id = 1001 AND status = 'active';
-- 실행 계획: 복합 인덱스로 한 번에 조회

-- Leftmost Prefix 원칙
SELECT * FROM orders WHERE user_id = 1001;  -- 인덱스 사용 O
SELECT * FROM orders WHERE status = 'active';  -- 인덱스 사용 X

설명

이것이 하는 일: 복합 인덱스는 여러 컬럼의 값을 조합하여 하나의 인덱스로 만들어, 여러 조건을 동시에 사용하는 쿼리를 최적화합니다. 첫 번째로, 단일 인덱스 2개를 사용하는 방법은 WHERE user_id = 1001 AND status = 'active' 쿼리에서 데이터베이스가 idx_orders_user로 user_id = 1001인 행들을 찾은 후, 그 결과를 다시 하나씩 확인하며 status = 'active'인지 필터링합니다.

이것을 Index + Filter 방식이라고 합니다. user_id = 1001인 주문이 1000건이고 그 중 active가 100건이라면, 900건은 불필요하게 읽게 됩니다.

그 다음으로, 복합 인덱스 idx_orders_user_status를 사용하면 B-Tree에 (user_id, status) 조합이 정렬되어 저장되어 있습니다. 예를 들어 (1000, 'active'), (1000, 'cancelled'), (1001, 'active'), (1001, 'cancelled') 순서로 되어 있어서 (1001, 'active')를 바로 찾을 수 있습니다.

필요한 100건만 정확히 읽으므로 훨씬 효율적입니다. Leftmost Prefix 원칙은 복합 인덱스의 중요한 특성입니다.

(user_id, status) 인덱스는 user_id만으로 검색할 때도 사용할 수 있지만, status만으로는 사용할 수 없습니다. 이는 전화번호부가 '성'으로 정렬되어 있어서 성만 알아도 찾을 수 있지만, 이름만 알면 모든 페이지를 뒤져야 하는 것과 같은 원리입니다.

여러분이 복합 인덱스를 사용하면 자주 함께 검색되는 컬럼들을 효율적으로 처리할 수 있고, 인덱스 개수를 줄여 관리 부담과 저장 공간을 절약할 수 있습니다. 예를 들어 (user_id, created_at, status) 3개 컬럼의 복합 인덱스 하나로 user_id만, user_id+created_at, user_id+created_at+status 총 3가지 검색 패턴을 커버할 수 있습니다.

실전 팁

💡 WHERE 절에 항상 함께 나오는 컬럼들은 복합 인덱스로 만드세요. "특정 사용자의 최근 주문"처럼 user_id와 created_at를 항상 함께 쓴다면 (user_id, created_at) 복합 인덱스가 최적입니다.

💡 복합 인덱스의 컬럼 순서는 "등호 조건 → 범위 조건 → 정렬" 순서로 배치하세요. (user_id, created_at)에서 user_id는 등호, created_at는 범위/정렬이므로 이 순서가 맞습니다.

💡 카디널리티(고유값 개수)가 높은 컬럼을 앞에 배치하세요. user_id(수만 가지)를 status(3-4가지)보다 앞에 두는 것이 효율적입니다. 단, 쿼리 패턴이 더 우선입니다.

💡 복합 인덱스 하나가 여러 단일 인덱스를 대체할 수 있습니다. (a, b, c) 인덱스는 a, (a,b), (a,b,c) 검색에 모두 사용 가능하므로 중복 인덱스를 만들지 마세요.

💡 EXPLAIN으로 실제 사용되는 인덱스를 확인하세요. 복합 인덱스를 만들었어도 쿼리 작성 방식에 따라 사용되지 않을 수 있으니 반드시 검증하세요.

---

5. EXPLAIN으로_실행_계획_분석

시작하며

여러분이 인덱스를 열심히 만들었는데, 쿼리가 여전히 느려서 답답한 적 있나요? "분명 인덱스가 있는데 왜 안 빨라지지?"라고 의문을 가져본 적 말이죠.

이런 문제는 인덱스가 실제로 사용되고 있는지 확인하지 않아서 발생합니다. 인덱스를 만들었다고 해서 자동으로 사용되는 것이 아닙니다.

데이터베이스 옵티마이저가 상황에 따라 인덱스를 사용할지 말지 결정하며, 때로는 잘못된 선택을 하기도 합니다. 바로 이럴 때 필요한 것이 EXPLAIN 명령어입니다.

EXPLAIN은 쿼리가 어떻게 실행되는지 계획을 보여주는 도구로, 인덱스 사용 여부, 검색 방식, 처리할 행 수 등을 알려줍니다. 이것이 없으면 성능 문제를 해결하는 것은 눈 가리고 아웅하는 것과 같습니다.

개요

간단히 말해서, EXPLAIN은 SQL 쿼리가 어떻게 실행될지 보여주는 명령어입니다. 실제로 쿼리를 실행하지 않고도 어떤 인덱스를 사용하고, 몇 개의 행을 검사하며, 어떤 순서로 테이블을 조인하는지 알 수 있습니다.

왜 EXPLAIN이 필요한지 실무 관점에서 설명하자면, 쿼리 성능 문제를 진단하고 해결하는 가장 기본적이면서도 강력한 도구이기 때문입니다. 예를 들어, 어떤 쿼리가 느릴 때 EXPLAIN을 실행하면 "전체 테이블 스캔을 하고 있네요", "인덱스는 사용하지만 10만 행을 검사하네요", "임시 테이블을 만들어서 정렬하네요" 같은 정보를 즉시 얻을 수 있습니다.

기존에는 쿼리를 실행해보고 느리면 "뭔가 문제가 있나보다"라고 추측만 했다면, EXPLAIN을 사용하면 정확히 어디가 문제인지 파악할 수 있습니다. "인덱스를 안 쓰고 있구나", "잘못된 인덱스를 쓰고 있구나", "조인 순서가 비효율적이구나" 같은 구체적인 원인을 알 수 있습니다.

EXPLAIN의 핵심 특징은 세 가지입니다. 첫째, type 필드로 검색 방식(const, ref, range, index, ALL 등)을 알 수 있습니다.

둘째, possible_keys와 key로 사용 가능한 인덱스와 실제 사용한 인덱스를 확인합니다. 셋째, rows로 검사할 행 수를 예측하여 쿼리 비용을 추정합니다.

이러한 정보들이 중요한 이유는 성능 문제의 근본 원인을 정확히 파악해야 올바른 해결책을 찾을 수 있기 때문입니다.

코드 예제

-- 1. 기본 EXPLAIN
EXPLAIN SELECT * FROM orders WHERE user_id = 1001;

-- 결과 예시:
-- type: ref (인덱스 사용)
-- possible_keys: idx_orders_user_id
-- key: idx_orders_user_id
-- rows: 150 (예상 행 수)

-- 2. EXPLAIN ANALYZE (실제 실행 + 분석, PostgreSQL)
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 1001 AND status = 'active';

-- 3. 인덱스 미사용 케이스 확인
EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- type: ALL (전체 테이블 스캔)
-- key: NULL (인덱스 미사용)

-- 4. 복잡한 쿼리 분석
EXPLAIN SELECT o.*, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'active'
ORDER BY o.created_at DESC
LIMIT 10;

설명

이것이 하는 일: EXPLAIN은 쿼리 옵티마이저가 선택한 실행 계획을 출력하여, 개발자가 쿼리의 성능 특성을 이해하고 최적화할 수 있게 합니다. 첫 번째로, 기본 EXPLAIN의 결과에서 가장 중요한 필드는 'type'입니다.

type이 'const'면 Primary Key로 단 1개 행을 찾는 가장 빠른 방식이고, 'ref'면 인덱스로 여러 행을 찾는 방식, 'range'면 범위 검색, 'index'면 인덱스 전체 스캔, 'ALL'이면 테이블 전체 스캔으로 가장 느립니다. WHERE user_id = 1001처럼 인덱스가 있으면 type이 'ref'가 나와야 정상입니다.

그 다음으로, 'key' 필드는 실제로 사용된 인덱스를 보여줍니다. 'possible_keys'에는 여러 인덱스가 나열되어도 'key'에는 하나만 선택됩니다.

만약 key가 NULL이면 인덱스를 전혀 사용하지 않는다는 뜻이므로, WHERE 절을 수정하거나 새 인덱스를 만들어야 합니다. WHERE YEAR(created_at) = 2024처럼 함수를 사용하면 key가 NULL이 되므로 주의해야 합니다.

'rows' 필드는 데이터베이스가 예상하는 검사 행 수입니다. 이 숫자가 크면 클수록 쿼리가 느려집니다.

예를 들어 100만 건 테이블에서 rows가 1000이면 괜찮지만, rows가 50만이면 문제가 있는 것입니다. EXPLAIN ANALYZE를 사용하면 예상치(rows)와 실제치(actual rows)를 비교할 수 있어 더 정확합니다.

복잡한 JOIN 쿼리에서는 여러 테이블의 실행 계획이 순서대로 나옵니다. 먼저 실행되는 테이블의 rows가 작을수록 좋으며, 각 단계에서 인덱스를 사용하는지 확인해야 합니다.

Extra 필드에 'Using temporary', 'Using filesort'가 나오면 임시 테이블이나 정렬 작업이 발생하므로 인덱스로 개선할 여지가 있습니다. 여러분이 EXPLAIN을 사용하면 "왜 이 쿼리가 느린가?"에 대한 명확한 답을 얻을 수 있습니다.

type이 ALL이면 인덱스를 추가하고, rows가 크면 WHERE 조건을 개선하고, Using filesort가 나오면 ORDER BY 컬럼에 인덱스를 추가하는 식으로 구체적인 조치를 취할 수 있습니다. 또한 프로덕션 배포 전에 EXPLAIN으로 미리 검증하여 성능 문제를 예방할 수 있습니다.

실전 팁

💡 type이 'ALL'이면 빨간 신호입니다. 전체 테이블 스캔이므로 WHERE 절에 사용되는 컬럼에 인덱스를 추가하세요. 단, 작은 테이블(<1000행)은 괜찮습니다.

💡 rows 값이 실제 결과보다 훨씬 크면 비효율적입니다. 예를 들어 10개 결과를 위해 10만 개를 검사한다면 인덱스를 개선하거나 쿼리를 수정하세요.

💡 Extra 필드의 'Using index'는 좋은 신호입니다. 커버링 인덱스를 사용하여 테이블에 접근하지 않고 인덱스만으로 결과를 반환한다는 뜻입니다.

💡 'Using filesort'나 'Using temporary'가 보이면 성능 저하 가능성이 있습니다. ORDER BY나 GROUP BY에 사용되는 컬럼에 인덱스를 추가하면 해결될 수 있습니다.

💡 프로덕션 배포 전 모든 중요 쿼리에 EXPLAIN을 실행하세요. 개발 환경의 소량 데이터에서는 빠르지만, 실제 데이터에서는 느릴 수 있으므로 미리 검증하는 습관을 들이세요.

---

6. 인덱스_최적화_전략

시작하며

여러분이 인덱스를 만들고 EXPLAIN도 확인했는데, 시간이 지나면서 다시 성능이 떨어지거나 새로운 기능을 추가할 때마다 어떤 인덱스를 만들어야 할지 고민되는 적 있나요? 인덱스가 점점 많아지면서 "이 인덱스들이 정말 다 필요한 건가?"라는 의문이 들기도 하죠.

이런 문제는 인덱스를 지속적으로 관리하고 최적화하는 전략이 없어서 발생합니다. 인덱스는 한 번 만들면 끝이 아닙니다.

데이터 패턴이 바뀌고, 쿼리가 추가되고, 테이블이 커지면서 계속 조정해야 합니다. 사용하지 않는 인덱스는 삭제하고, 새로운 패턴에 맞는 인덱스를 추가해야 합니다.

바로 이럴 때 필요한 것이 체계적인 인덱스 최적화 전략입니다. 어떤 인덱스를 만들고, 언제 삭제하고, 어떻게 모니터링할지에 대한 명확한 기준과 방법을 알아봅시다.

개요

간단히 말해서, 인덱스 최적화는 성능과 비용의 균형을 맞추는 작업입니다. 너무 적으면 조회가 느리고, 너무 많으면 저장 공간과 쓰기 성능이 낭비되므로 적절한 수준을 찾아야 합니다.

왜 지속적인 최적화가 필요한지 실무 관점에서 설명하자면, 서비스는 계속 진화하기 때문입니다. 예를 들어, 초기에는 사용자 검색이 중요해서 name에 인덱스를 만들었지만, 서비스가 성장하면서 지역별 검색이 중요해져서 (region, name) 복합 인덱스가 필요해질 수 있습니다.

또한 데이터가 쌓이면서 인덱스 통계가 오래되어 옵티마이저가 잘못된 판단을 할 수도 있습니다. 기존에는 "일단 인덱스를 만들어놓고 잊어버리기"였다면, 이제는 정기적으로 사용하지 않는 인덱스를 찾아 삭제하고, 느린 쿼리 로그를 분석하여 새로운 인덱스를 추가하고, 통계를 갱신하여 옵티마이저가 올바른 선택을 하도록 관리해야 합니다.

인덱스 최적화 전략의 핵심은 다섯 가지입니다. 첫째, 쿼리 패턴 분석으로 필요한 인덱스를 파악합니다.

둘째, 커버링 인덱스로 최대 성능을 얻습니다. 셋째, 사용하지 않는 인덱스를 제거합니다.

넷째, 인덱스 통계를 주기적으로 갱신합니다. 다섯째, 쓰기 성능과 읽기 성능의 균형을 맞춥니다.

이러한 전략들이 중요한 이유는 장기적으로 안정적이고 효율적인 데이터베이스 운영을 위해 필수적이기 때문입니다.

코드 예제

-- 1. 커버링 인덱스 (테이블 접근 없이 인덱스만으로 조회)
CREATE INDEX idx_orders_covering
ON orders(user_id, created_at, status, total_amount);

SELECT user_id, created_at, status, total_amount
FROM orders
WHERE user_id = 1001;
-- EXPLAIN Extra: Using index (빠름!)

-- 2. 사용하지 않는 인덱스 찾기 (MySQL)
SELECT
    s.table_name,
    s.index_name,
    s.rows_read
FROM sys.schema_unused_indexes s;

-- 3. 인덱스 통계 갱신 (MySQL)
ANALYZE TABLE orders;

-- 4. 인덱스 조각화 확인 및 재구성 (PostgreSQL)
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan = 0;  -- 사용되지 않는 인덱스

REINDEX INDEX idx_orders_user_id;  -- 인덱스 재구성

-- 5. 느린 쿼리 로그에서 인덱스 후보 찾기
-- slow_query_log를 활성화하고 분석
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 1초 이상 쿼리 기록

설명

이것이 하는 일: 인덱스 최적화 전략은 현재 쿼리 패턴을 분석하여 최소한의 인덱스로 최대의 성능을 얻고, 불필요한 오버헤드를 제거하는 체계적인 접근 방식입니다. 첫 번째로, 커버링 인덱스는 쿼리에 필요한 모든 컬럼을 인덱스에 포함시켜 테이블에 전혀 접근하지 않고 결과를 반환하는 최고의 최적화 기법입니다.

idx_orders_covering은 user_id, created_at, status, total_amount를 모두 포함하므로, 이 컬럼들만 SELECT하는 쿼리는 인덱스만 읽으면 됩니다. EXPLAIN에서 'Using index'가 나오면 커버링 인덱스를 사용한다는 의미입니다.

그 다음으로, 사용하지 않는 인덱스를 찾아 삭제하는 것이 매우 중요합니다. sys.schema_unused_indexes(MySQL) 또는 pg_stat_user_indexes(PostgreSQL)를 조회하면 한 번도 사용되지 않은 인덱스를 발견할 수 있습니다.

예를 들어 초기 개발 단계에서 만든 인덱스가 실제로는 쓰이지 않는 경우가 많습니다. 이런 인덱스는 과감히 삭제하여 INSERT/UPDATE 성능을 회복해야 합니다.

인덱스 통계 갱신은 옵티마이저가 올바른 실행 계획을 선택하도록 돕습니다. 데이터가 크게 변경된 후에는 ANALYZE TABLE(MySQL) 또는 ANALYZE(PostgreSQL)를 실행하여 인덱스 통계를 갱신해야 합니다.

예를 들어 백만 건의 데이터를 삽입한 후 통계를 갱신하지 않으면, 옵티마이저는 여전히 작은 테이블이라고 착각하여 잘못된 인덱스를 선택할 수 있습니다. 느린 쿼리 로그(slow query log)는 실제 프로덕션에서 느린 쿼리를 자동으로 기록합니다.

long_query_time = 1로 설정하면 1초 이상 걸리는 쿼리가 로그에 남으므로, 이를 분석하여 어떤 인덱스가 필요한지 파악할 수 있습니다. 예를 들어 특정 WHERE 절이 자주 나타나면 해당 컬럼에 인덱스를 추가하는 것을 고려해야 합니다.

여러분이 이러한 최적화 전략을 적용하면 장기적으로 안정적인 성능을 유지할 수 있습니다. 커버링 인덱스로 조회 성능을 극대화하고, 불필요한 인덱스를 제거하여 쓰기 성능을 개선하고, 통계 갱신으로 옵티마이저가 최선의 선택을 하도록 돕고, 느린 쿼리 모니터링으로 문제를 조기에 발견할 수 있습니다.

이것이 바로 프로덕션 환경에서 필요한 실전 인덱스 관리입니다.

실전 팁

💡 SELECT하는 컬럼이 적다면 커버링 인덱스를 만드세요. 특히 SELECT user_id, status FROM orders WHERE user_id = ? 같은 쿼리는 (user_id, status) 인덱스로 크게 개선됩니다.

💡 쓰기가 많은 테이블은 인덱스를 최소화하세요. 로그 테이블처럼 INSERT만 빈번하다면 필수 인덱스 1-2개만 유지하고 나머지는 삭제하는 것이 좋습니다.

💡 대량 데이터 작업 전에는 인덱스를 제거하고 작업 후 재생성하세요. 100만 건 이상 INSERT할 때 인덱스가 있으면 10배 이상 느려질 수 있습니다.

💡 인덱스 통계는 매주 또는 데이터 변경이 큰 작업 후 갱신하세요. 정기적인 ANALYZE 작업을 크론잡이나 스케줄러에 등록하여 자동화하는 것을 추천합니다.

💡 모니터링 도구를 활용하세요. MySQL의 Performance Schema, PostgreSQL의 pg_stat_statements를 활성화하면 실시간으로 쿼리 성능과 인덱스 사용률을 추적할 수 있습니다.

---