SQL 인덱스와 성능 최적화 기본기: 데이터베이스를 빠르게 만드는 기술

SQL 인덱스와 성능 최적화 기본기

1. 인덱스란 무엇인가?

1-1. 인덱스의 개념

 인덱스(index)란 테이블의 데이터를 빠르게 조회하기 위해 미리 만들어 놓은 정렬된 참조 구조이다. 쉽게 말해, 책의 목차나 전화번호부처럼 검색 속도를 높여주는 도우미다. DBMS에서 SELECT 쿼리를 실행할 때, 인덱스가 없다면 전체 데이터를 처음부터 끝까지 훑어야 한다. 이를 풀 테이블 스캔(full table scan)이라고 하며, 데이터가 많을수록 속도가 급격히 느려진다. 반면 인덱스를 활용하면 원하는 값을 찾기 위해 소수의 탐색만으로도 결과를 얻을 수 있다.

---

2. 인덱스의 동작 원리

2-1. B-Tree 기반 구조

 대부분의 RDBMS는 B-Tree(균형 트리) 기반 인덱스를 사용한다. 이 구조는 정렬된 상태를 유지하며, 검색, 삽입, 삭제 시 효율적인 탐색을 가능하게 한다. 예를 들어 id 컬럼에 인덱스가 걸려 있다면, 다음 쿼리는 B-Tree를 따라 빠르게 탐색이 이뤄진다

SELECT * FROM users WHERE id = 1024;

2-2. 탐색 비용 차이

• 인덱스 없을 경우: 100만 건 데이터 중에서 원하는 행을 찾으려면 평균 500,000번 조회
• 인덱스 존재 시: B-Tree 깊이에 따라 10~20번 내외로 탐색 가능

→ 속도 차이가 수백~수천 배에 이를 수 있음

---

3. 인덱스의 종류

3-1. 단일 인덱스 (Single-column Index)

 하나의 컬럼에만 인덱스를 거는 가장 기본적인 방식이다.

 

예: CREATE INDEX idx_user_email ON users(email);

3-2. 복합 인덱스 (Composite Index)

 두 개 이상의 컬럼을 조합해 만든 인덱스다. 복합 인덱스는 왼쪽부터 순서대로 적용되며, 순서에 따라 효율이 달라진다.

 

예: CREATE INDEX idx_order_user_date ON orders(user_id, created_at);

→ 이 인덱스는 WHERE user_id = ? 혹은 WHERE user_id = ? AND created_at = ?에는 적용되지만, WHERE created_at = ? 단독 조건에는 비효율적이다.

3-3. 유니크 인덱스 (UNIQUE INDEX)

 중복을 허용하지 않는 인덱스. 기본 키(PK)는 항상 유니크 인덱스로 생성된다.

3-4. 풀텍스트 인덱스 (FULLTEXT INDEX)

 긴 문자열(게시글, 댓글 등)에 대한 자유로운 검색을 가능하게 해주는 인덱스. MySQL에서는 InnoDB 기준으로 FULLTEXT를 사용하면 MATCH AGAINST 검색을 할 수 있다.

---

4. 언제 인덱스를 걸어야 하는가?

4-1. WHERE 절에 자주 등장하는 컬럼

 조건 검색에 자주 사용되는 컬럼은 인덱스가 필요하다. 특히 사용자가 직접 입력하는 검색어, 카테고리, 날짜 필터 등은 인덱스의 대상이 되어야 한다.

4-2. JOIN 또는 정렬 기준 컬럼

 JOIN 조건으로 자주 사용되는 컬럼 (ex: user_id) 또는 ORDER BY created_at DESC처럼 정렬 기준이 자주 되는 컬럼은 반드시 인덱스를 고려해야 한다.

4-3. 데이터 건수가 많은 테이블일수록 인덱스는 필수

 수천 건 미만의 데이터에서는 인덱스 유무에 따른 체감 차이가 거의 없지만, 수만~수십만 건 이상이 넘어가는 테이블은 반드시 인덱스를 기반으로 설계해야 한다.

---

5. 인덱스의 단점과 주의사항

5-1. INSERT / UPDATE / DELETE 성능 저하

 인덱스는 데이터를 빠르게 찾게 해주지만, 반대로 데이터를 추가하거나 수정할 때마다 인덱스를 갱신해야 하므로 오버헤드가 발생한다. 즉, 읽기(Read)는 빠르게, 쓰기(Write)는 느려질 수 있다. 인덱스를 너무 많이 설정하면 이 문제가 더 심해진다.

5-2. 과도한 인덱스는 저장 공간 낭비

 인덱스는 실제 데이터 외에도 별도의 저장공간을 차지한다. 사용되지 않는 인덱스는 디스크 공간만 차지하며 성능에 아무 도움도 되지 않는다.

5-3. LIKE '%검색어%'는 인덱스를 무효화한다

 일반적인 B-Tree 인덱스는 문자열 앞부분부터 일치하는 경우에만 인덱스를 활용할 수 있다.

 

예:

• WHERE name LIKE '김%' → 인덱스 사용됨
• WHERE name LIKE '%길동' → 인덱스 사용 안 됨

→ 이런 경우는 FULLTEXT INDEX 또는 별도 검색엔진 사용을 고려해야 한다.

---

6. 실행계획(EXPLAIN)으로 쿼리 성능 진단

6-1. EXPLAIN이란?

 SQL 앞에 EXPLAIN 키워드를 붙이면, 해당 쿼리가 어떻게 실행될지 DBMS가 예상하는 계획을 보여준다. 이를 통해 인덱스 사용 여부, 테이블 접근 방식 등을 확인할 수 있다.

EXPLAIN SELECT * FROM users WHERE email = 'abc@example.com';

6-2. 확인해야 할 주요 항목

1. type: 접근 방식 (ALL, index, ref, const 등)
2. key: 실제 사용된 인덱스 이름
3. rows: 대략 몇 개의 행을 읽을지 추정
4. Extra: "Using index" 또는 "Using where" 등 추가 정보

 type이 ALL이면 풀 테이블 스캔, ref나 const면 인덱스가 잘 사용되고 있다는 뜻이다.

---

7. 실무에서 흔한 성능 저하 쿼리 패턴

1. WHERE 절에 함수 사용
   • WHERE DATE(created_at) = '2024-01-01' → 인덱스 사용 안 됨
   • 대안: WHERE created_at BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 23:59:59'
2. OR 조건 남용
   • WHERE user_id = 1 OR email = 'a@a.com' → 인덱스 적용 어려움
3. 정렬 후 LIMIT 사용
   • ORDER BY + LIMIT 조합은 인덱스 없이 실행 시 매우 느려질 수 있음

---

8. 쿼리 최적화 실무 전략

 단순히 인덱스를 추가한다고 해서 모든 성능 문제가 해결되는 것은 아니다. 인덱스를 어떻게 활용하는 쿼리 구조를 설계하느냐가 훨씬 중요하다.

8-1. SELECT 컬럼 최소화

 SELECT *는 보기에는 간편하지만, 실제로는 모든 컬럼을 불필요하게 가져오므로 성능에 악영향을 미칠 수 있다. 인덱스만으로도 처리 가능한 쿼리(covering index)가 있는 경우에도, *를 사용하면 인덱스를 사용하지 못하는 일이 발생한다. 따라서 반드시 필요한 컬럼만 명시해서 조회해야 한다.

SELECT id, name FROM users; -- O  
SELECT * FROM users;        -- X (불필요한 I/O 발생)

8-2. 불필요한 서브쿼리 제거

 WHERE IN (SELECT ...) 형태의 서브쿼리는 실행 계획 상 중첩 루프로 인해 성능 저하를 유발할 수 있다. 가능하다면 JOIN 또는 EXISTS로 리팩터링하는 것이 일반적인 전략이다.

8-3. LIMIT 최적화

 LIMIT 100000, 10처럼 OFFSET이 큰 쿼리는 결과는 10건이지만, 실제로는 앞의 10만 건을 모두 읽고 버린다. 가능하다면 커서 기반 페이징으로 전환하거나, 정렬 기준에 인덱스를 추가해 탐색 성능을 높여야 한다.

---

9. 인덱스 설계 시 우선순위 판단 기준

 모든 컬럼에 인덱스를 거는 것은 성능을 높이는 것이 아니라 오히려 성능과 자원을 낭비하는 잘못된 접근이다. 실무에서는 아래의 기준을 바탕으로 인덱스 적용 여부를 결정해야 한다.

9-1. WHERE 조건에 자주 사용되는가?

 조회 조건에 반복적으로 등장하는 컬럼은 인덱스 대상이다. 단, **조건의 분포도(=데이터의 다양성)**가 높아야 인덱스의 효과도 커진다.

 

예:

• status 값이 항상 0 또는 1 → 인덱스 효율 낮음
• user_id, product_id 등 고유 ID → 인덱스 효율 높음

9-2. 정렬, 그룹화 기준에 쓰이는가?

 ORDER BY, GROUP BY, DISTINCT 등의 대상이 되는 컬럼은 정렬 비용이 크기 때문에 인덱스가 반드시 고려되어야 한다.

9-3. JOIN 연결 고리가 되는가?

 테이블 간 JOIN 조건에 사용되는 컬럼은 양쪽 테이블 모두 인덱스가 있어야 최적화가 가능하다. 특히 외래 키(foreign key)로 지정한 컬럼은 인덱스를 함께 걸어야 참조 무결성과 성능을 모두 확보할 수 있다.

---

10. 인덱스 없는 구조에서의 대응 전략

 때때로 프로젝트 상황상 인덱스를 추가하지 못하거나, 외부 시스템의 DB 구조에 손을 댈 수 없는 경우도 있다. 이럴 때는 다음과 같은 우회 전략을 사용할 수 있다.

10-1. 캐싱 레이어 도입

 자주 조회되는 쿼리는 애플리케이션 또는 미들웨어에서 캐시로 응답을 저장하고 재사용하는 방식이 효과적이다. Redis, Memcached, DB 레벨의 Materialized View 등이 여기에 해당한다.

10-2. 테이블 분할 (Sharding / Partitioning)

 대규모 데이터가 한 테이블에 몰려 있을 경우, 기준값(user_id, date 등)을 기반으로 데이터를 물리적으로 나눠 저장하면 쿼리 단위의 검색 범위가 줄어들어 성능이 향상된다.

• 월별 주문 테이블 (orders_2024_01, orders_2024_02 등)
• 사용자 ID 별 DB 분산 (user_id % 4 = 0 → DB A, 1 → DB B)

10-3. 비정규화 기반 조회 테이블 설계

 복잡한 JOIN이 빈번한 경우, 이를 미리 조합한 조회 전용 테이블을 만들어둠으로써 실시간 계산 없이 빠르게 결과를 보여줄 수 있다.

 

예: 게시글 + 사용자명 + 댓글수 조합 테이블

이 방식은 성능은 뛰어나지만, 정합성 유지 로직이 필요하므로 운영 비용이 증가할 수 있다.