본문으로 건너뛰기

1. 복합 인덱스​

  • 지금까지 우리는 하나의 컬럼으로 구성된 **단일 인덱스(Single-column Index)**에 대해 주로 알아보았습니다.
  • 하지만 실제 쇼핑몰 운영 환경에서는 여러 조건을 조합해서 데이터를 검색하는 경우가 훨씬 더 많습니다.
  • 예를 들어, "카테고리가 '전자기기'인 상품들 중에 가격이 100,000원 이상인 상품을 보여줘"와 같은 요구사항은 매우 흔합니다.
SELECT * FROM items
WHERE category = '전자기기' AND price >= 100000;
  • 이런 다중 조건 쿼리의 성능을 최적화하기 위해 사용하는 것이 바로 복합 인덱스(Composite Index) 또는 **다중 컬럼 인덱스(Multi-column Index)**입니다.
  • 복합 인덱스는 이름 그대로 두 개 이상의 컬럼을 묶어서 하나의 인덱스로 만드는 것입니다.
  • Index Optimizer 문서에서 (price, item_name) 인덱스를 만들었던 것을 기억하시나요? 그것이 바로 복합 인덱스입니다.

1.1 왜 컬럼 순서가 중요할까?​

  • 하지만 복합 인덱스를 제대로 사용하려면 한 가지 매우 중요한 규칙을 이해해야 합니다.
  • 바로 **'컬럼의 순서'**입니다.
  • 인덱스를 어떤 컬럼 순서로 만드느냐에 따라 쿼리 성능이 하늘과 땅 차이로 달라질 수 있습니다.

복합 인덱스의 동작 원리​

  • 복합 인덱스의 동작 원리는 우리가 실생활에서 사용하는 '전화번호부'나 '국어사전'과 똑같습니다.
  • 전화번호부: '성(Last Name)'으로 먼저 정렬된 후, 같은 성 안에서 '이름(First Name)'으로 다시 정렬됩니다.
  • 국어사전: '첫 번째 글자'로 먼저 정렬된 후, 같은 첫 글자로 시작하는 단어들끼리 '두 번째 글자'로 다시 정렬됩니다.
  • items 테이블에 (category, price) 순서로 복합 인덱스를 만들었다고 상상해 보겠습니다.
  • 이 인덱스는 내부적으로 다음과 같이 정렬됩니다.
  1. category를 기준으로 먼저 정렬합니다. ('도서', '생활용품', '전자기기', '패션', '헬스/뷰티' 순서)
  2. 같은 category 내에서는 price를 기준으로 다시 정렬합니다.

idx_items_category_price 인덱스 예시

categorypriceitem_id
도서1800021
도서2200025
도서2800015
도서300004
도서3500011
생활용품500016
생활용품150005
생활용품400009
생활용품6000019
전자기기800003
전자기기9000014
전자기기1200001
전자기기20000024
전자기기2500007
전자기기30000013
전자기기35000023
전자기기4500002
전자기기80000020
전자기기150000010
패션4500018
패션700006
패션950008
패션18000017
헬스/뷰티2000012
헬스/뷰티2500022
  • 이 구조를 보면 왜 컬럼 순서가 중요한지 감이 올 것입니다.

검색 효율성 비교​

  • category로 검색: 매우 효율적입니다. 인덱스의 앞부분만 보고 빠르게 찾을 수 있습니다. (예: '전자기기' 섹션으로 바로 점프)
  • category와 price로 검색: 역시 매우 효율적입니다. '전자기기' 섹션을 찾은 뒤, 그 안에서 price 순으로 정렬된 데이터를 탐색하면 됩니다.
  • price만으로 검색: 매우 비효율적입니다. 전화번호부에서 성은 모르고 이름만으로 찾는 것과 같습니다. price 값은 각 category 섹션마다 흩어져 있기 때문에, 인덱스 전체를 다 훑어봐야 합니다. 이런 경우 옵티마이저는 차라리 풀 테이블 스캔을 선택할 수도 있습니다.
price만으로 검색할 수 없는 이유

각각의 카테고리 안에서는 price가 항상 정렬된 상태를 유지합니다. 1차 정렬의 기준 안에서 2차 정렬은 항상 정렬된 상태를 유지합니다.

하지만 price만으로 검색하면 왜 인덱스를 사용하기 어려운지 자세히 알아보겠습니다.

idx_items_category_price 인덱스 예시에서 price 컬럼만 딱 분류해서 보면, 가격순으로 정렬이 되어 있는 것 같아 보이지만 중간에 정렬이 다시 틀어집니다. 결과적으로 price 컬럼만 보면 정렬이 되어 있지 않습니다. 결과적으로 1차 정렬의 기준 없이 2차 정렬의 값만 가지고는 정렬된 상태를 유지할 수 없습니다.

1.2 인덱스 왼쪽 접두어 규칙​

  • 이처럼 복합 인덱스는 첫 번째 컬럼을 기준으로 정렬된 상태에서만 제 역할을 할 수 있습니다.
  • 이를 **인덱스 왼쪽 접두어 규칙(Index Left-Prefix Rule)**이라고 합니다.
  • 인덱스를 (A, B, C) 순서로 생성했다면, WHERE 조건에 다음과 같이 사용될 때 효율적입니다.
    • (A)
    • (A, B)
    • (A, B, C)
    • 하지만 (B), (C), (B, C)와 같이 첫 번째 기준인 A가 빠진 조건으로는 인덱스를 제대로 활용할 수 없습니다.

2. 복합 인덱스 대원칙​

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

3. 복합 인덱스 준비 과정​

SHOW INDEX FROM items;
  • 실행 결과 idx로 시작하는 모든 인덱스를 제거하겠습니다.
  • 인덱스는 아쉽게도 IF EXISTS 구문이 없습니다. 하나하나를 직접 제거해야 합니다.
DROP INDEX idx_items_item_name ON items;
DROP INDEX idx_items_price_name ON items;
DROP INDEX idx_items_price ON items;
DROP INDEX idx_items_price_category_temp ON items;
DROP INDEX idx_items_category_price ON items;
  • 이전에 만들어진 idx로 시작하는 인덱스는 모두 제거해야 합니다.
  • 실습을 위해 (category, price) 순서로 복합 인덱스를 생성하겠습니다.
CREATE INDEX idx_items_category_price ON items (category, price);
  • 만들어진 인덱스를 최종 확인하겠습니다.
SHOW INDEX FROM items;
  • 결과는 반드시 다음과 같이 idx로 시작하는 인덱스가 idx_items_category_price 하나만 추가되어야 합니다.
TableNon_uniqueKey_nameSeq_in_indexColumn_nameCardinality
items0PRIMARY1item_id25
items1fk_items_sellers1seller_id10
items1idx_items_category_price1category5
items1idx_items_category_price2price25
  • idx_items_category_price는 하나의 복합 인덱스입니다.
  • Seq_in_index는 복합 인덱스의 컬럼 순서를 나타냅니다. idx_items_category_price의 경우 1번은 category, 2번은 price 순서로 만들어진 것을 확인할 수 있습니다.

4. 복합 인덱스 성공 예제1: category 사용​

  • 복합 인덱스의 첫 번째 컬럼만 WHERE 절에 사용하는 경우입니다.
  • 이는 인덱스 왼쪽 접두어 규칙을 가장 잘 활용하는 기본적인 예시입니다.
  • "카테고리가 '전자기기'인 모든 상품을 찾아보자."
EXPLAIN SELECT * FROM items WHERE category = '전자기기';

실행 결과

idtypekeyrowsfilteredExtra
1refidx_items_category_price10100.00NULL
  • 실행 계획을 분석해 보겠습니다.

type: ref​

  • category가 '전자기기'인 조건을 만족하는 데이터를 찾기 위해 동등 비교(=)나 JOIN을 사용하고, 인덱스를 효율적으로 사용했음을 의미합니다.
  • 쉽게 이야기해서 Key 값 하나를 딱 집어서 찾은 것입니다.

key: idx_items_category_price​

  • 우리가 만든 복합 인덱스가 사용되었습니다.

rows: 10​

  • 옵티마이저는 '전자기기' 카테고리에 해당하는 상품이 약 10건 있을 것으로 정확히 예측하고, 그 부분만 탐색합니다.
  • 이는 (category, price)로 정렬된 인덱스에서 category가 '전자기기'인 첫 번째 위치를 빠르게 찾아낸 뒤, '전자기기'가 끝날 때까지 인덱스를 순차적으로 읽기만 하면 되므로 매우 효율적입니다.
  • 전화번호부에서 '김'씨를 찾는 것과 같이, 'ㄱ' 섹션으로 바로 이동해서 '김'씨가 끝날 때까지 읽는 것과 같습니다.

5. 복합 인덱스 성공 예제2: category, price 함께 사용​

  • 이번에는 복합 인덱스를 구성하는 모든 컬럼을 WHERE 절에 사용하는 경우입니다.
  • 인덱스의 필터링 능력을 최대로 활용하는 상황입니다.
  • "카테고리가 '전자기기'이면서, 가격이 정확히 120,000원인 상품을 찾아보자."
EXPLAIN SELECT * FROM items WHERE category = '전자기기' AND price = 120000;

실행 결과

idtypekeyrowsfilteredExtra
1refidx_items_category_price1100.00NULL
  • 실행 계획은 훨씬 더 효율적으로 보입니다.

type: ref​

  • 두 개의 컬럼 조건(category, price)을 모두 만족하는 데이터를 찾기 위해 인덱스를 사용했습니다.

rows: 1​

  • 탐색할 행의 수가 단 1개로 예측됩니다.

  • 데이터베이스는 먼저 category가 '전자기기'인 섹션을 찾고, 그 안에서 price가 120000인 지점을 탐색합니다.

  • '전자기기' 섹션 내부는 이미 price 순으로 정렬되어 있으므로, 원하는 데이터를 매우 빠르게 특정할 수 있습니다.

  • 이는 전화번호부에서 '김수로'라는 사람을 찾는 것과 같습니다. '김'씨 섹션을 찾고, 그 안에서 '수로'라는 이름을 찾아내는 과정은 매우 빠릅니다.

  • 이처럼 인덱스를 구성하는 모든 컬럼을 조건으로 사용하면 가장 효과적으로 데이터를 필터링할 수 있습니다.

6. 복합 인덱스 성공 예제3: 복합 인덱스와 정렬​

  • 복합 인덱스의 진정한 힘은 정렬(ORDER BY) 작업을 피할 때 드러납니다.
  • WHERE 절의 필터링과 ORDER BY의 정렬 방향이 인덱스 순서와 일치하면, 데이터베이스는 불필요한 filesort 작업을 생략해서 성능을 크게 향상시킬 수 있습니다.
  • "카테고리가 '전자기기'이면서 100,000원 초과인 상품을 가격 오름차순으로 정렬해서 보여줘."
EXPLAIN SELECT * FROM items 
WHERE category = '전자기기' AND price > 100000
ORDER BY price;

실행 결과

idtypekeyrowsfilteredExtra
1rangeidx_items_category_price8100.00Using index condition
  • 가장 주목해야 할 부분은 Extra 컬럼에 Using filesort가 없다는 점입니다.
  • 이것이 어떻게 가능할까요?

데이터베이스의 동작 과정​

  1. idx_items_category_price 인덱스를 사용해 category가 '전자기기'인 섹션으로 빠르게 이동합니다.
  2. 해당 섹션 내에서, price가 100000을 초과하는 첫 번째 데이터를 찾습니다.
  3. 그 지점부터 '전자기기' 섹션이 끝날 때까지 인덱스를 순서대로 읽기만 하면 됩니다.
  • 왜냐하면 인덱스의 '전자기기' 섹션은 이미 price 순서로 완벽하게 정렬되어 있기 때문입니다.
  • 따라서 데이터베이스는 별도로 데이터를 모아 다시 정렬할 필요 없이, 인덱스를 읽는 즉시 ORDER BY price 조건을 만족하는 결과를 얻게 됩니다.
ORDER BY와 인덱스 순서

이처럼 WHERE 절의 조건과 ORDER BY 절의 조건이 복합 인덱스의 순서(category → price)와 일치하면, 데이터베이스는 가장 효율적인 방식으로 데이터를 찾고 정렬까지 한 번에 처리합니다. filesort를 피하는 것이야말로 복합 인덱스를 사용하는 핵심적인 이유 중 하나입니다.

  • 쉽게 이야기하면 ORDER BY를 사용할 때 복합 인덱스의 순서대로 정렬하면 추가적인 정렬(filesort)을 피할 수 있다는 것입니다.
EXPLAIN SELECT * FROM items 
WHERE category = '전자기기' AND price > 100000
ORDER BY category, price; -- category, price 순서로 정렬
  • 여기서 복합 인덱스가 category, price 순서이므로 ORDER BY도 이 순서에 맞추어 사용하면 정렬을 최적화할 수 있습니다.
  • 그런데 여기서 선택된 category는 '전자기기' 단 하나이므로 이런 경우에는 category를 정렬 조건에 넣을 필요가 없습니다.
  • 정렬은 최소 2개 이상 있을 때 의미가 있습니다.
  • 이런 경우에는 ORDER BY에 category를 두는 것이 의미가 없으므로 다음과 같이 생략합니다.
EXPLAIN SELECT * FROM items 
WHERE category = '전자기기' AND price > 100000
ORDER BY price;
  • 당연한 이야기지만 만약 ORDER BY item_name처럼 인덱스 순서와 다른 컬럼으로 정렬을 요청했다면, 조회한 결과를 다시 정렬해야 하기 때문에 Extra 컬럼에 Using filesort가 발생할 것입니다.
EXPLAIN SELECT * FROM items 
WHERE category = '전자기기' AND price > 100000
ORDER BY item_name;

실행 결과

idtypekeyrowsfilteredExtra
1rangeidx_items_category_price8100.00Using index condition; Using filesort

7. 복합 인덱스 실패 예제1: 인덱스 순서 무시​

  • 이제부터 중요한 문제 상황입니다.
  • 복합 인덱스의 첫 번째 컬럼(category)을 건너뛰고, 두 번째 컬럼(price)만으로 데이터를 검색하면 어떻게 될까요?
  • "카테고리와 상관없이 가격이 80,000원인 상품을 찾아보자."
EXPLAIN SELECT * FROM items WHERE price = 80000;

실행 결과

idtypekeyrowsfilteredExtra
1ALLNULL2510.00Using where
  • 실행 계획은 처참합니다.

type: ALL​

  • 풀 테이블 스캔이 발생했습니다.

key: NULL​

  • idx_items_category_price 인덱스가 있음에도 불구하고, 옵티마이저는 이 인덱스를 사용하지 않았습니다.
왜 이런 결과가 나왔을까?

인덱스 왼쪽 접두어 규칙 때문입니다.

idx_items_category_price 인덱스는 category로 먼저 정렬되어 있습니다. price가 80000인 상품은 '전자기기' 카테고리에도 있을 수 있고, 만약 있다면 '패션' 카테고리에도 있을 수 있습니다. 즉, price 값은 인덱스 전체에 흩어져 있습니다.

데이터베이스 입장에서 price만으로 데이터를 찾으려면, '도서' 카테고리 섹션을 처음부터 끝까지 다 보고, '생활용품' 섹션도 다 보고, '전자기기', '패션' 등 모든 카테고리 섹션을 다 뒤져봐야 합니다. 이는 인덱스 전체를 스캔하는 작업으로, 차라리 원본 테이블 전체를 스캔하는 것보다 나을 것이 없습니다.

전화번호부에서 성은 모르고 이름이 '수로'인 사람을 찾으려면, 가나다순으로 모든 페이지를 넘겨봐야 하는 것과 같은 이치입니다.

  • 이처럼 복합 인덱스는 선행 컬럼 조건 없이는 제 역할을 하지 못합니다.
  • 이 규칙을 반드시 기억해야 합니다.

8. 복합 인덱스 실패 예제2: 범위 조건을 먼저 사용​

  • 복합 인덱스 활용에는 한 가지 더 중요한 제약 조건이 있습니다.
  • 바로 선행 컬럼에 범위 조건(>, <, BETWEEN, LIKE %)이 사용되면, 그 뒤에 오는 컬럼은 인덱스를 제대로 활용할 수 없다는 점입니다.
  • "카테고리명이 '패션' 이상인 상품들 중에서, 가격이 정확히 20,000원인 상품을 찾아줘."
  • 문자열 정렬 순서로 보면 카테고리명이 패션 이상인 것은 '패션', '헬스/뷰티'입니다.
EXPLAIN SELECT * FROM items WHERE category >= '패션' AND price = 20000;

실행 결과

idtypekeyrowsfilteredExtra
1rangeidx_items_category_price610.00Using index condition
  • 실행 계획을 자세히 분석해 보겠습니다.
  • type이 range이고, key에 idx_items_category_price가 사용되었으니 언뜻 보기에는 인덱스를 잘 사용한 것처럼 보입니다.
  • 하지만 여기서 주목해야 할 것은 filtered 컬럼의 값인 10.00%와 Extra 컬럼의 Using index condition입니다.

데이터베이스의 동작 과정​

  1. idx_items_category_price 인덱스를 사용해 category가 '패션'인 위치를 찾습니다. (>= 조건의 시작점)
  2. 거기서부터 인덱스의 끝까지 모든 데이터를 스캔합니다. ('패션', '헬스/뷰티' 카테고리에 해당하는 모든 데이터)
  3. 스캔하는 각 레코드마다 price = 20000 조건을 만족하는지 하나하나 검사합니다. (인덱스 사용이 아니라 직접 필터링 합니다.)
  • 이것이 바로 filtered: 10.00%의 의미입니다.
  • 옵티마이저는 category >= '패션' 조건으로 약 6개의 행을 찾을 것으로 예상하고(rows: 6), 그 중에서 price = 20000 조건을 만족하는 데이터는 10% 정도일 것이라고 예측합니다.
  • 즉, price 컬럼은 데이터를 효율적으로 '찾는(seek)' 데 사용된 것이 아니라, 일단 category 조건으로 넓게 가져온 데이터를 '걸러내는(filter)' 데만 사용된 것입니다.

왜 이런 문제가 발생할까?​

  • 데이터베이스는 category >= '패션' 조건에 따라 인덱스에서 '패션'과 '헬스/뷰티' 섹션을 찾았습니다.
  • 하지만 그 다음 조건인 price = 20000을 처리할 때 문제가 생깁니다.

패션 섹션의 price

priceitem_id
4500018
700006
950008
18000017

헬스/뷰티 섹션의 price

priceitem_id
2000012
2500022

패션 + 헬스/뷰티의 price (합쳐진 상태)

priceitem_id
4500018
700006
950008
18000017
2000012
2500022
  • '패션' 섹션은 price 순으로 정렬되어 있습니다.
  • '헬스/뷰티' 섹션도 price 순으로 정렬되어 있습니다.
  • 하지만 '패션' 섹션의 가격들과 '헬스/뷰티' 섹션의 가격들이 전체적으로 정렬된 것은 아닙니다.
  • '패션'의 마지막 상품 가격이 '헬스/뷰티'의 첫 상품 가격보다 높을 수 있습니다.
  • 합쳐진 가격은 이제 정렬된 상태가 아닙니다! 인덱스를 활용하려면 정렬된 상태여야 합니다!
  • 따라서 데이터베이스는 category >= '패션' 범위를 스캔하면서 가져온 각각의 데이터에 대해 price = 20000 인지 일일이 확인하는 추가 작업을 해야만 합니다.
범위 조건 이후 컬럼의 문제

이처럼 복합 인덱스에서 앞선 컬럼(category)에 범위 조건(>=)이 걸리는 순간, 데이터베이스는 더 이상 뒤따라오는 컬럼(price)의 정렬 순서를 활용할 수 없게 됩니다. category가 '패션'일 때의 price 정렬과 category가 '헬스/뷰티'일 때의 price 정렬은 둘을 합치는 순간 정렬이 깨지기 때문입니다.

따라서 데이터베이스는 category >= '패션'이라는 범위 조건만 사용해서 인덱스를 스캔하고, 나머지 price = 20000 조건은 빠르게 찾지 못하고, 필터링만 수행하는 것입니다. 이는 인덱스의 성능을 절반만 활용하는 셈입니다.

  • 이처럼 복합 인덱스에서 어떤 컬럼에 범위 검색을 사용하는 순간, 그 뒤에 오는 컬럼들은 인덱스의 정렬 효과를 제대로 누릴 수 없게 됩니다.
  • 따라서 인덱스를 설계할 때는 = 조건으로 사용될 컬럼을 범위 조건으로 사용될 컬럼보다 앞에 배치하는 것이 일반적인 최적화 전략입니다.

9. 범위 검색은 마지막에 한 번만 사용!​

  • 이러한 제약 때문에 복합 인덱스를 설계할 때는 다음과 같은 순서를 따르는 것이 매우 중요합니다.
  • 등호(=) 조건을 사용하는 컬럼을 앞에, 범위 조건을 사용하는 컬럼을 뒤에 둡니다.
  • 예를 들어, 앞서 사용한 쿼리가 자주 사용된다면,
SELECT * FROM items
WHERE category >= '패션' AND price = 20000;
  • 최적의 인덱스는 순서를 변경해서 (price, category) 순서를 사용해야 합니다.
  • 왜냐하면 price = 20000이라는 등호(=) 조건을 먼저 처리해서 검색 대상을 크게 줄일 수 있기 때문입니다.
  • 이번에는 price, category 순서의 인덱스를 추가해보겠습니다.
CREATE INDEX idx_items_price_category_temp ON items (price, category);
  • 이번 예제에서만 임시로 사용할 예정입니다. 따라서 끝에 temp라는 이름을 붙여두었습니다.
EXPLAIN SELECT * FROM items
WHERE category >= '패션' AND price = 20000;

실행 결과

idtypepossible_keyskeyrowsfilteredExtra
1rangeidx_items_category_price, idx_items_price_category_tempidx_items_price_category_temp1100.00Using index condition
  • 옵티마이저는 idx_items_category_price, idx_items_price_category_temp 두 인덱스 중에 idx_items_price_category_temp가 더 효율적이라고 판단합니다.
  • Extra: Using index condition, rows: 1, filtered: 100.00을 통해 인덱스를 통해 하나의 데이터를 찾았고 해당 데이터가 별도의 필터링 없이 바로 선택된 것을 확인할 수 있습니다.

데이터베이스의 동작 과정​

  1. 인덱스에서 price가 20000인 데이터 블록을 매우 빠르게 찾습니다(lookup). 이는 인덱스가 price로 정렬되어 있기 때문에 가능한, 가장 효율적인 ref 방식(=)의 접근입니다.
  2. 그렇게 찾아낸 좁은 데이터 집합 안에서 category >= '패션'이라는 범위 조건을 만족하는 데이터를 찾습니다. 여기서는 '패션', '헬스/뷰티'를 만족하면 됩니다. price가 20000인 데이터들은 이미 category 순으로 정렬되어 있으므로, 이 과정 역시 효율적인 range 스캔으로 처리됩니다.
같은 가격의 제품

이번 예제에서는 같은 가격의 제품이 없으므로 카테고리 순 정렬이 크게 의미는 없습니다. 같은 가격의 제품이 많이 있다면 의미가 있을 것입니다.

  • 이처럼 가장 변별력 있는 등호(=) 조건을 먼저 처리해서 작업 범위를 최대한 좁히고, 그 다음에 범위 조건을 처리하는 것이 인덱스 설계의 핵심입니다.
  • 만약 순서를 반대로 (category, price)로 했다면, category >= '패션'이라는 범위 검색이 먼저 수행되면서 인덱스의 효율이 떨어졌을 것입니다.
  • 이는 '복합 인덱스 실패 예제2'에서 이미 확인했습니다.
인덱스 설계의 핵심

결론적으로, 복합 인덱스를 설계할 때는 어떤 쿼리가 주로 사용될지 예측하고, 해당 쿼리의 WHERE 절에 맞게 '등호 조건 컬럼 → 범위 조건 컬럼' 순서로 구성하는 것이 성능 최적화의 지름길입니다.

10. 기존 인덱스를 잘 활용하자​

  • 그런데 이처럼 인덱스를 계속 만드는 것만이 능사는 아닙니다.
  • 뒤에서 배우겠지만, 인덱스를 추가하면 그만큼 관리 비용이 들어갑니다.
  • 기본적으로 기존에 있는 인덱스를 최대한 잘 활용하고, 그래도 안되면 인덱스 추가를 고려해야 합니다.
  • 사실 이번에 사용한 다음 예제는
SELECT * FROM items
WHERE category >= '패션' AND price = 20000;
  • 이번에 생성한 idx_items_price_category_temp 인덱스 없이, 기존에 만든 idx_items_category_price 인덱스로도 최적의 성능을 활용할 수 있는 방법이 있습니다.
  • 이 방법을 알아보기 위해 이번에 만든 price, category 순서의 인덱스를 먼저 제거하겠습니다.
DROP INDEX idx_items_price_category_temp ON items;

11. 실무 팁: IN 절 활용하기​

  • 범위 조건 때문에 두 번째 인덱스 컬럼을 활용하지 못하는 이 문제는, > 나 < 같은 범위 대신 IN 절을 사용함으로써 해결할 수 있는 경우가 많습니다.
  • MySQL 옵티마이저는 IN (...)을 하나의 큰 범위로 취급하지 않고, 여러 개의 동등 비교(=) 조건의 묶음으로 인식하기 때문입니다.

범위 검색(>=)을 사용한 기존 검색 조건​

  • category >= '패션' 조건은 category IN ('패션','헬스/뷰티')와 논리적으로 동일합니다.
  • 이 쿼리를 IN을 사용하도록 변경하고 실행 계획을 다시 확인해보겠습니다.
EXPLAIN SELECT * FROM items WHERE category >= '패션' AND price = 20000;

실행 결과

idtypekeyrowsfilteredExtra
1rangeidx_items_category_price610.00Using index condition
  • idx_items_category_price 인덱스를 사용
  • rows 6: 패션, 헬스/뷰티를 포함해서 인덱스로 6건의 데이터를 찾을 것으로 예상
  • filtered 10.00%: 인덱스를 통해 찾은 데이터에서 약 10%를 필터링할 것으로 예상

IN 조건을 사용한 검색 조건​

EXPLAIN SELECT * FROM items
WHERE category IN ('패션', '헬스/뷰티') AND price = 20000;

실행 결과

idtypekeyrowsfilteredExtra
1rangeidx_items_category_price2100.00Using index condition
  • IN 연산자를 사용한 실행 결과를 자세히 분석해보겠습니다.
  • 범위 연산자(>=)를 사용했을 때와 IN 연산자를 사용했을 때 EXPLAIN 결과가 미묘하게 달라졌습니다.

예상 rows가 6 → 2로 줄어듦​

  • 인덱스로 찾는 범위가 더 줄어든다는 뜻입니다.

filtered: 100.00%​

  • 가장 극적인 변화는 filtered 컬럼이 10.00%에서 **100.00%**로 바뀐 것입니다.
  • 이것은 인덱스만을 잘 활용해서 원하는 데이터를 100% 다 찾았다는 의미입니다.
  • 그래서 인덱스를 통해 찾은 데이터를 100% 다 통과시킨다는 의미입니다.

IN 절의 동작 방식​

  • IN 절을 사용했을 때, MySQL 옵티마이저의 동작 방식은 다음과 같이 바뀝니다.
  1. 옵티마이저는 WHERE category IN ('패션', '헬스/뷰티')를 WHERE category = '패션' OR category = '헬스/뷰티'와 동일하게 인식합니다.
  2. 따라서 전체 쿼리는 내부적으로 (category = '패션' AND price = 20000) 또는 (category = '헬스/뷰티' AND price = 20000)를 만족하는 데이터를 찾는 것으로 해석됩니다.
  3. idx_items_category_price 인덱스를 사용해 ('패션', 20000) 조합을 만족하는 데이터를 찾습니다. (첫 번째 동등 비교)
  4. 이어서 ('헬스/뷰티', 20000) 조합을 만족하는 데이터를 찾습니다. (두 번째 동등 비교)
  • 이렇게 작성한 IN 쿼리는 쉽게 비유하자면 다음과 같이 나누어 실행됩니다.
SELECT * FROM items WHERE category = '패션' AND price = 20000;
UNION ALL
SELECT * FROM items WHERE category = '헬스/뷰티' AND price = 20000;
  • category '패션'만 보면 price가 완전히 정렬되어 있습니다. 따라서 price 컬럼도 인덱스를 사용해서 원하는 데이터를 빠르게 찾을 수 있습니다.
  • category '헬스/뷰티'만 보면 price가 완전히 정렬되어 있습니다. 따라서 price 컬럼도 인덱스를 사용해서 원하는 데이터를 빠르게 찾을 수 있습니다.
IN 절의 핵심

핵심은 범위 검색이 동등 비교(=)의 여러 묶음으로 바뀌었다는 점입니다.

>= 같은 범위 조건에서는 인덱스의 두 번째 컬럼(price)을 제대로 활용할 수 없었지만, category와 price가 모두 특정 값으로 고정된 동등 비교에서는 복합 인덱스의 모든 컬럼을 효율적으로 사용할 수 있습니다.

즉, 옵티마이저는 idx_items_category_price 인덱스를 사용해 ('패션', 20000) 지점으로 한 번, ('헬스/뷰티', 20000) 지점으로 또 한 번, 총 두 번의 **정확한 위치 탐색(seek)**을 수행합니다. 이 탐색 과정에서 price 조건까지 완벽하게 반영되므로, 불필요하게 데이터를 읽고 버리는 과정이 사라집니다. 이것이 바로 filtered 컬럼이 100.00%로 표시되는 이유입니다.

  • 결론적으로 >는 **'연속된 범위'**로 처리되어 복합 인덱스의 추가적인 활용을 막는 반면, IN은 '여러 개의 개별 지점'에 대한 동등(=) 비교의 묶음으로 처리됩니다.
  • 옵티마이저는 IN 절의 각 값에 대해 인덱스를 사용한 효율적인 탐색(Seek)을 여러 번 수행할 수 있으므로, 복합 인덱스의 모든 컬럼을 효과적으로 활용할 수 있습니다.
  • 논리적으로 같은 결과를 반환하더라도, >는 인덱스 스캔(Scan) 방식으로 동작하여 후속 컬럼 활용에 제한이 있는 반면, IN은 여러 번의 탐색(Seek) 방식으로 동작하여 복합 인덱스의 모든 컬럼을 효율적으로 활용할 수 있기 때문에 성능 차이가 발생합니다.
IN 절의 주의사항

물론 IN 절에 들어가는 값이 수백, 수천 개로 너무 많아지면 성능이 저하될 수 있지만, 이처럼 범위 조건을 몇 개의 동등 조건으로 바꿀 수 있는 상황이라면 IN 절은 매우 강력한 최적화 도구가 될 수 있습니다.

실무에서의 IN 절 활용

실무에서는 범위가 한정적인 컬럼에 이 IN 트릭을 자주 사용합니다.

예를 들어, 상품 상태를 나타내는 status 컬럼이 '판매중', '품절', '판매중지' 3가지 값만 가진다고 하겠습니다.

'판매중' 또는 '품절' 상태인 상품을 찾을 때 WHERE status >= '판매중'과 같이 조회하는 것보다 WHERE status IN ('판매중', '품절')로 조회하는 것이 복합 인덱스를 활용하는 데 훨씬 유리할 수 있습니다.

물론, IN 절에 들어가는 값의 개수가 너무 많아지면 오히려 성능이 저하될 수도 있으므로, 항상 EXPLAIN을 통해 실제 실행 계획을 확인하고 결정하는 것이 현명합니다.

12. 복합 인덱스 정리​

  • 지금까지 복합 인덱스가 성공하는 예제와 실패하는 예제를 다양하게 살펴보았습니다.
  • EXPLAIN의 결과가 미묘하게 달라지고, 옵티마이저의 동작 방식이 복잡해서 어렵게 느껴질 수도 있습니다.
  • 하지만 지금까지 배운 모든 내용은 사실 몇 가지 핵심 대원칙으로 귀결됩니다.
  • 이 원칙들만 제대로 이해하고 있다면, 어떤 복잡한 쿼리를 만나더라도 최적의 인덱스를 설계하고 사용할 수 있게 될 것입니다.
  • 실무에서는 이 원칙을 이해하는 것만으로도 많은 성능 문제를 해결할 수 있습니다.

복합 인덱스 대원칙​

복합 인덱스를 설계하고 사용할 때는 다음 세 가지 대원칙을 반드시 기억해야 합니다!

  1. 인덱스는 순서대로 사용하라! (왼쪽 접두어 규칙)
  2. 등호(=) 조건은 앞으로, 범위 조건(<, >)은 뒤로!
  3. 정렬(ORDER BY)도 인덱스 순서를 따르라!

1. 인덱스는 순서대로 사용하라!​

  • 이는 **'인덱스 왼쪽 접두어 규칙'**을 의미합니다.
  • 복합 인덱스는 (A, B, C) 순서로 생성되었을 때, WHERE 절에서 A 조건 없이는 B나 C를 사용할 수 없습니다.
  • 전화번호부에서 '성'을 모르고 '이름'만으로 사람을 찾을 수 없는 것과 같은 이치입니다.
  • 가능 (O): WHERE A, WHERE A AND B, WHERE A AND B AND C
  • 불가능 (X): WHERE B, WHERE C, WHERE B AND C
  • 이것이 복합 인덱스의 가장 기본적이고 절대적인 규칙입니다.

2. 등호(=) 조건은 앞으로, 범위 조건(<, >)은 뒤로!​

  • 복합 인덱스의 컬럼 중 하나에 범위 조건(>, <, BETWEEN, LIKE %...)이 사용되는 순간, 그 뒤에 오는 컬럼은 인덱스의 정렬 효과를 누릴 수 없습니다.
  • 예를 들어, (category, price) 인덱스가 있을 때 WHERE category > '도서' AND price = 30000 쿼리를 생각해보겠습니다.
    • 데이터베이스는 category가 '도서'보다 큰 ('생활용품', '전자기기', ...) 섹션을 인덱스에서 찾습니다.
    • 하지만 '생활용품' 섹션과 '전자기기' 섹션의 price는 서로 연결되어 정렬된 것이 아닙니다.
    • 결국 데이터베이스는 '도서' 이후의 모든 인덱스를 스캔하면서, price가 30000인지 일일이 확인해야 합니다.
    • price 컬럼은 필터링에만 사용될 뿐, 탐색(seek)에는 사용되지 못합니다.
  • 따라서 가장 효율적인 인덱스 설계는 다음과 같은 순서를 따릅니다.
    • 변별력이 높은 등호(=) 조건으로 사용할 컬럼을 인덱스 앞쪽에 배치합니다. (IN 절도 여러 개의 등호 조건으로 취급되어 유리합니다.)
    • 범위 조건으로 사용할 컬럼은 인덱스 뒤쪽에 배치합니다.
    • 이렇게 하면 등호 조건으로 검색 대상을 최대한 좁힌 뒤, 그 좁은 범위 내에서만 범위 검색을 수행하므로 성능이 극대화됩니다.

3. 정렬(ORDER BY)도 인덱스 순서를 따르라!​

  • 복합 인덱스의 가장 강력한 기능 중 하나는 불필요한 정렬 작업을 생략하게 해주는 것입니다.
  • ORDER BY 절이 인덱스 컬럼 순서와 일치하면, 데이터베이스는 이미 정렬된 인덱스를 순서대로 읽기만 하면 되므로 매우 빠릅니다.
  • 이 경우 실행 계획에서 Using filesort가 사라지는 것을 볼 수 있습니다.
  • 인덱스: (category, price)
  • 빠른 쿼리: WHERE category = '전자기기' ORDER BY price
    • '전자기기' 섹션은 이미 price 순으로 정렬되어 있으므로 추가 정렬이 필요 없습니다.
  • 느린 쿼리 (filesort 발생): WHERE category = '전자기기' ORDER BY stock_quantity
    • 인덱스는 stock_quantity 순서와는 무관하므로, 결과를 가져온 뒤 별도로 정렬해야 합니다.
  • WHERE 절에서 인덱스를 잘 활용하는 것만큼, ORDER BY에서 filesort를 피하는 것도 전체 쿼리 성능에 결정적인 영향을 미칩니다.
복합 인덱스의 핵심

결론적으로, 복합 인덱스는 단순히 여러 컬럼을 묶는 것이 아니라, '순서'와 '조건의 종류(등호/범위)'를 고려한 전략적인 설계가 핵심입니다. 이 세 가지 대원칙만 명심한다면, 여러분도 실무에서 고성능 쿼리를 충분히 잘 작성할 수 있을 것입니다.