Ch11. 물리적 모델링 실습 - 인덱스 설계

인덱스는 데이터베이스 테이블의 특정 컬럼(들)의 데이터를 빠르게 찾을 수 있도록 도와주는 일종의 "찾아보기"나 "목차"와 같다. 인덱스가 없으면, 데이터베이스는 원하는 데이터를 찾기 위해 테이블의 모든 행을 처음부터 끝까지 스캔해야 한다. 이를 풀 테이블 스캔(Full Table Scan)이라고 한다. 데이터가 수백만, 수천만 건이 되면 이는 재앙과도 같은 성능 저하를 일으킨다.

 

"우리 쇼핑몰의 어떤 기능이 인덱스를 필요로 할까?" 이 질문에 답하기 위해, 우리 쇼핑몰에서 매우 자주 발생할 것으로 예상되는 핵심적인 조회 시나리오들을 분석하고, 그에 맞는 인덱스를 전략적으로 추가해 보자.

 

MySQL 인덱스 자동 생성
MySQL에서는 Primary Key(PK)와 Unique Key(UK), Foreign Key(FK)를 설정하면 자동으로 해당 컬럼에 인덱스가 생성된다.

 

 

시나리오 1: 로그인 및 중복 ID, 이메일 체크

-- 로그인 시
SELECT member_id, password FROM member WHERE login_id = 'user123';

-- 이메일로 회원 정보 찾기
SELECT member_id FROM member WHERE email = 'user123@example.com';

• 사용자가 로그인을 시도하거나, 회원가입 시 ID나 이메일이 중복되는지 확인해야 한다.
  • 이 작업은 매우 빈번하게 일어난다.
• 문제점
  • member 테이블에 회원이 100만 명 있다면, 이 쿼리는 100만 개의 데이터를 모두 스캔(Full Table Scan) 해야 한다.
• 해결책
  • "login_id"와 "email" 컬럼은 UNIQUE 제약조건을 걸어두었는데, MySQL에서 UNIQUE 제약조건은 자동으로 해당 컬럼에 고유 인덱스(Unique Index)를 생성한다.
  • 따라서 이 경우는 이미 최적화가 되어 있지만, 만약 UNIQUE가 아닌 일반 컬럼이었다면 반드시 인덱스를 생성해야 한다.

시나리오 2: 회원의 주문 목록 조회

-- member_id가 10번인 회원의 모든 주문을 최신순으로 조회한다.
SELECT *
FROM orders
WHERE member_id = 10
ORDER BY ordered_at DESC;

• 사용자가 "마이페이지"에서 자신의 과거 주문 내역을 조회한다.
  • 이는 쇼핑몰에서 가장 빈번하게 일어나는 조회 중 하나다.
• 인덱스 필요성
  • "member_id" 컬럼에 인덱스가 없다면, MySQL은 이 쿼리를 처리하기 위해 orders 테이블의 모든 데이터를 다 뒤져서 "member_id"가 "10"인 것을 찾아내야 한다.
  • orders 테이블에 데이터가 쌓일수록 이 작업은 점점 더 느려질 것이다.
  • 따라서 WHERE 절에서 자주 사용되는 "member_id" 컬럼에는 반드시 인덱스가 필요하다

인덱스 추가

-- orders 테이블의 member_id 컬럼에 인덱스를 추가한다.
CREATE INDEX idx_member_id ON orders (member_id);

• 인덱스를 추가해야 하지만, MySQL에서는 외래키에 자동으로 인덱스를 추가해 준다.

 

실무 팁

MySQL에서는 외래 키(Foreign Key)를 생성하면 해당 컬럼에 자동으로 인덱스가 생성된다. 따라서 "orders.member_id"는 사실 FK 제약조건을 거는 순간 인덱스가 이미 만들어진다.

하지만 FK 제약조건을 사용하지 않는 경우 반드시 인덱스를 직접 추가해야 한다

 

시나리오 3: 상품명으로 상품 검색

-- 이름에 '노트북'이 포함된 상품을 찾는다.
SELECT *
FROM product
WHERE product_name LIKE '노트북%';

 

• 사용자가 쇼핑몰 상단의 검색창에 "노트북"이라고 입력하여 상품을 검색한다.
  • 이 기능은 product 테이블의 "product_name" 컬럼에서 특정 키워드를 포함하는 상품을 찾는 "LIKE" 쿼리를 사용하게 된다.
• 인덱스 필요성
  • 상품 검색 역시 매우 중요한 기능이다.
  • "name" 컬럼에 인덱스가 없다면, 검색할 때마다 전체 상품을 스캔해야 하므로 매우 느린 검색 경험을 제공하게 된다.

인덱스 추가

-- product 테이블의 name 컬럼에 인덱스를 추가한다.
CREATE INDEX idx_product_name ON product (product_name);

• LIKE "%검색어%"처럼 검색어 앞에 "%"가 붙는 경우에는 일반적인 B-Tree 인덱스가 제대로 동작하지 않아 성능 향상에 한계가 있다.
• 이런 전문적인 텍스트 검색 기능을 위해서는 MySQL의 "Full-Text Index"나 "Elasticsearch" 같은 별도의 검색 엔진을 사용하는 것이 실무적인 해결책이다.
• 하지만 "LIKE '검색어%'"와 같이 검색어 앞에 "%"가 없는 경우에는 인덱스가 효과적으로 사용된다.

 

시나리오 4: 관리자의 주문 상태 및 기간별 조회 (복합 인덱스)

-- 2025년 7월 한 달간 취소된 주문을 조회한다.
SELECT *
FROM orders
WHERE order_status = 'CANCELED'
AND ordered_at BETWEEN '2025-07-01 00:00:00' AND '2025-07-31 23:59:59'

• 쇼핑몰 관리자가 어드민 페이지에서 "지난 한 달간 취소된 주문(CANCELED)" 목록을 조회하여 CS 처리를 하려고 한다.
  • 이 기능은 orders 테이블에서 "order_status"와 "ordered_at" 두 가지 조건을 동시에 만족하는 데이터를 찾는다
• 인덱스 필요성
  • "order_status"와 "ordered_at" 각각에 단일 인덱스가 있다면, 데이터베이스 옵티마이저는 둘 중 더 효율적이라고 판단되는 인덱스 하나만 사용하게 된다.
  • 이는 여전히 비효율적일 수 있다. 두 개 이상의 컬럼이 WHERE절 조건으로 함께 자주 사용될 때는, 이 컬럼들을 묶어서 복합 인덱스(Composite Index)를 만들어주는 것이 훨씬 더 효율적이다

복합 인덱스 추가

-- orders 테이블에 order_status와 ordered_at를 묶는 복합 인덱스를 추가한다.
CREATE INDEX idx_status_ordered_at ON orders (order_status, ordered_at);

• 복합 인덱스의 컬럼 순서
  • 복합 인덱스는 컬럼의 순서가 매우 중요하다.
  •  인덱스는 첫 번째 컬럼 기준으로 정렬되고, 그 안에서 두 번째 컬럼 기준으로 정렬되는 방식으로 동작한다.
  •  따라서 조회 조건에서 선택도(Selectivity)가 높고, "="(등호) 조건으로 사용되는 컬럼을 앞에 두는 것이 일반적인 원칙이다.
    • "order_status" : "ORDERED", "CANCELED", "SHIPPING" 등 몇 가지 값으로 정해져 있어 선택도가 낮다.
      • 하지만 "="(등호) 조건으로 사용된다.
    • ordered_at: 값의 종류가 매우 다양하여 선택도가 높다.
      • 하지만 BETWEEN 같은 범위 조건으로 사용된다.
      • 이 경우, "="(등호) 조건으로 범위를 좁힐 수 있는 "order_status"를 앞에 두고, 그 안에서 "ordered_at"로 원하는 범위를 빠르게 찾도록 "(order_status, ordered_at)" 순서로 인덱스를 구성하는 것이 효과적이다

BETWEEN 쿼리와 밀리초 문제

여기서는 예제를 단순하고 쉽게 설명하기 위해 일시에 BETWEEN 쿼리를 사용했지만, 실무에서는 이렇게 사용할 때 주의할 점이 있다.

만약 "ordered_at" 컬럼에 "2025-07-31 23:59:59.500"과 같이 초 단위보다 더 상세한 시간이 저장되어 있다면, 이 데이터는 "2025-07-31 23:59:59"보다 크다고 판단되어 쿼리 결과에서 누락된다.

밀리초가 사용되는 경우 가장 좋은 해결책은 다음과 같이 시작일은 포함(>=)하고, 종료일의 다음 날은 미포함(<)하는 방식을 사용하면 된다.

복합 인덱스 대원칙

복합 인덱스를 설계하고 사용할 때는 다음 세 가지 대원칙을 반드시 기억하자!
1. 인덱스는 순서대로 사용하라!(왼쪽 접두어 규칙)
2. 등호(=) 조건은 앞으로, 범위 조건(<, >)은 뒤로!
3. 정렬(ORDER BY)도 인덱스 순서를 따르라!

 

시나리오 5: 사용자의 주문 상태 및 기간별 조회

-- member_id가 10번인 회원의 최근 3개월간 '배송 완료(COMPLETED)'된 주문을 조회한다.
SELECT *
FROM orders
WHERE member_id = 10
AND order_status = 'COMPLETED'
AND ordered_at >= DATE_SUB(NOW(), INTERVAL 3 MONTH);

• 이 기능은 orders 테이블에서 특정 "member_id"를 가지면서, "order_status"와 "ordered_at" 조건까지 만족하는 데이터를 찾는다.
  • 따라서 "(member_id, order_status, ordered_at)" 순서로 복합 인덱스를 고려할 수 있다.

 

그런데 이 경우에는 인덱스를 만들지 말지 고민이 필요하다. 앞선 관리자 시나리오와 매우 유사해 보이지만, 결정적으로 다른 점이 하나 있다. 바로 WHERE 절에 "member_id = 10" 조건이 포함된다는 것이다. 이 작은 차이가 인덱스 설계 전략을 완전히 바꾼다.

 

 

인덱스를 추가하지 않는 이유: 데이터의 선택도(Selectivity)와 비용 고려

 

데이터베이스는 쿼리를 실행할 때, 가장 효율적으로 데이터를 걸러낼 수 있는 인덱스를 먼저 사용하려는 경향이 있다. 이것을 "선택도가 높다"라고 표현한다. 선택도는 특정 값으로 얼마나 많은 데이터를 걸러낼 수 있는지를 나타내는 지표다.

 

"member_id"의 압도적으로 높은 선택도

• "order_status = 'COMPLETED'" 조건: 전체 주문 데이터가 1억 건이라면, "배송 완료" 상태인 주문은 수천만 건에 달할 수 있다. (데이터를 크게 줄이지 못한다.)
• "member_id = 10" 조건: 전체 1억 건의 주문 중에서 특정 회원 ID가 10번인 주문은 많아야 수십, 수백 건에 불과할 것이다.
  • 이 조건 하나만으로 조회 대상이 1억 건에서 수백 건으로 극적으로 줄어든다.

따라서 MySQL 옵티마이저는 "member_id" 컬럼에 이미 생성되어 있는 인덱스("idx_member_id" 또는 FK로 인해 자동 생성된 인덱스)를 사용하는 것이 압도적으로 효율적이라고 판단한다.

 

인덱스 사용 후의 동작 과정

MySQL은 다음과 같은 순서로 이 쿼리를 처리한다.

1. "idx_member_id" 인덱스를 사용하여 "member_id"가 "10"인 주문 데이터가 디스크의 어디에 저장되어 있는지 빠르게 찾아낸다.
2. 찾아낸 수백 건의 데이터만 메모리로 가져온다.
3. 메모리에 올라온 이 소량의 데이터를 대상으로 "order_status = 'COMPLETED'"인지, "ordered_at"가 최근 3개월 이내인지 조건을 확인(필터링)한다.

수억 개의 전체 데이터를 스캔하는 것과 비교할 때, 이미 수백 개로 줄어든 데이터를 메모리에서 필터링하는 비용은 거의 무시할 수 있을 정도로 저렴하고 빠르다.

 

인덱스의 비용: 모든 쿼리에 인덱스를 만들지 않는 이유

만약 "(member_id, order_status, ordered_at)" 순서로 복합 인덱스를 만든다면 이 쿼리가 조금 더 빨라질 수는 있다. 하지만 약간의 성능 향상을 위해 우리가 감수해야 할 비용도 고려해야 한다.

• 저장 공간 비용: 인덱스는 데이터가 아니다. 데이터를 빠르게 찾기 위한 별도의 자료구조이며, 디스크 공간을 차지한다.
  • 인덱스를 많이 만들수록 데이터베이스의 전체 크기는 커진다.
• 쓰기(Write) 성능 저하: 인덱스는 테이블의 데이터가 변경될 때마다 함께 업데이트되어야 한다.
  • INSERT: 새로운 주문이 들어오면 orders 테이블뿐만 아니라, 이 테이블에 속한 모든 인덱스에도 새로운 정보를 추가해야 한다.
  • UPDATE: 주문 상태가 "ORDERED"에서 "SHIPPING"으로 바뀌면, "(member_id, order_status, ordered_at)" 인덱스의 "order_status" 부분을 포함하여 정렬 순서를 다시 맞춰야 하는 복잡한 작업이 발생한다.
  • DELETE: 주문이 삭제되면 모든 인덱스에서도 해당 데이터를 삭제해야 한다.

이처럼 인덱스는 조회(SELECT) 속도를 향상시키는 강력한 도구이지만, 데이터 변경(INSERT, UPDATE, DELETE) 작업에는 부하를 주는 양날의 검이다.

 

결론적으로, "member_id"라는 강력한 필터링 조건 덕분에 이미 충분히 빠른 쿼리에 추가 인덱스를 생성하는 것은, 얻는 이점(미미한 조회 성능 향상)보다 잃는 것(저장 공간 낭비와 심각한 쓰기 성능 저하)이 큰, 전형적인 "과잉 최적화(Over-optimization)"가 될 수 있다. 실무에서는 모든 조회 케이스에 인덱스를 거는 것이 아니라, 전체 시스템에 병목 현상을 일으키는 느린 쿼리(SlowQuery)를 찾아내고, 그 쿼리들을 중심으로 가장 효율적인 인덱스를 전략적으로 설계하는 것이 핵심이다.

 

 

인덱스를 추가하는 것이 좋은 상황은?

 

사실 이 부분은 정답이 있다기 보다는, 본인의 비즈니스 환경에 맞는 답을 찾아야 한다. 실무 관점에서 한 명의 회원이 갖는 주문 건수가 평균적으로 수천 건을 넘어서고, 해당 조건으로 조회하는 기능이 서비스의 핵심적인 성능 병목점이 될 때 "(member_id, order_status, ordered_at)" 복합 인덱스 추가를 고려해볼수 있다.

 

하지만 단순히 데이터 건수만으로 결정하기보다는, 여러 요소를 종합적으로 판단하는 것이 중요하다. 예를 들어서 주문 내역에 데이터가 많지 않아도 메인 화면에 주문 내역을 항상 노출해야 한다면, 그래서 주문 내역을 조회하는 기능이 매우 빈번하게 발생한다면 복합 인덱스 추가를 고려할 수 있다

 

인덱스 추가를 결정하는 실무적 기준

• 데이터 분포와 조회 효율성
  • 가장 중요한 기준이다. "member_id"로 데이터를 필터링했을 때 남는 데이터의 양이 얼마나 되는지가 핵심이다.
  • 수백 건 이하: 한 회원의 주문이 수십 ~ 수백 건 수준이라면 "member_id" 인덱스만으로 충분히 빠르다.
    • 이 정도 데이터는 메모리에서 필터링하는 속도가 매우 빨라서 사용자가 성능 저하를 거의 체감할 수 없다. 추가 인덱스로 인한 쓰기 성능 저하와 관리 비용이 더 클 수 있다.
  • 수천 건 이상: B2B 서비스나 특수 유통몰처럼 한 명의 사용자(예: 사업자 회원)가 수천, 수만 건의 주문을 생성하는 경우가 있다.
    • 이 경우 "member_id"로만 필터링해도 여전히 많은 데이터가 남게 되므로, "order_status"와 "ordered_at"로 추가 필터링하는 과정에서 성능 저하가 발생할 수 있다.
    • 이럴 때 복합 인덱스가 효과를 발휘하기 시작한다.
  • 서비스 규모가 아주 큰 경우: 전 국민이 사용하는 규모의 큰 쇼핑몰 서비스라면 데이터도 많고 트래픽도 많다.
    • 이런 경우 약간의 지연도 시스템에 민감하게 반응하기 때문에 인덱스를 고려해야 한다.
• 쓰기(Write) 작업의 빈도와 중요도
  • 쇼핑몰의 orders 테이블은 "SELECT" 만큼이나 "INSERT"와 "UPDATE"가 매우 빈번하게 일어나는 곳이다.
  • 주문 생성(INSERT)은 계속 발생한다.
  • 주문 상태(order_status) 변경(UPDATE)은 "주문 완료" -> "결제 확인" ->  "배송 준비" -> "배송 중" -> "배송 완료" 등으로 계속해서 일어난다.
  • 복합 인덱스를 추가하면 이 모든 "INSERT"와 "UPDATE" 작업에 부하가 걸린다.
    • 전체적인 주문 처리 속도가 느려지는 것은 사용자 경험에 치명적일 수 있다. 따라서 읽기(조회) 성능의 개선 효과가 쓰기 성능 저하로 인한 손실보다 확실히 클 때만 인덱스를 추가해야 한다.
• 실제 쿼리 패턴과 성능 측정 (가장 중요)
  • 결국 가장 확실한 방법은 실제 데이터를 기반으로 테스트하는 것이다.
  • 느린 쿼리(Slow Query) 로그 분석: 데이터베이스의 느린 쿼리 로그를 분석했을 때, 해당 사용자 조회 쿼리가 지속적으로 상위권에 등장하며 시스템에 부하를 주고 있는지 확인한다.
  • EXPLAIN 실행 계획 분석: "EXPLAIN"을 사용해 실제 쿼리가 "member_id" 인덱스를 사용한 후 얼마나 많은 행(rows)을 스캔하는지 확인한다. 여기서 스캔하는 행의 수가 너무 많다고 판단되면 인덱스 추가를 검토한다.
  • 성능 테스트: 개발 환경에 인덱스와 데이터를 추가하고, 추가하기 전과 후의 조회 성능 및 주문 생성/수정 성능을 직접 비교 측정한다.

핵심은 "예상 가능한 확실한 경우가 아니라면 미리 짐작해서 최적화하지 말고, 문제가 발생하기 전에 데이터를 기반으로 판단하고 개선한다"는 것이다.

 

전투에 패한 장수는 용서할 수 있지만 경계에 실패한 장수는 용서할 수 없다.

시스템에 장애는 언제든지 발생할 수 있다. 하지만 그 장애가 발생하기 전까지 아무런 징후를 파악하지 못하
고 속수무책으로 당하는 것은 개발자의 명백한 책임이다. 인덱스 최적화도 마찬가지다. 무작정 인덱스를 만
드는 것이 아니라, 느린 쿼리 로그를 통해 시스템의 병목을 '경계'하고, 데이터를 기반으로 '전투(최적화)'에
임해야 한다. 항상 시스템을 주의 깊게 관찰하고 문제가 발생했을 때 빠르게 대응할 수 있도록 준비하는 자
세가 무엇보다 중요하다.