1. 인덱스 설계 가이드라인

• 인덱스를 만드는 법(CREATE INDEX)을 아는 것보다 더 중요한 것은, 어디에 인덱스를 만들어야 하는지 아는 것입니다.
• 잘못된 인덱스는 오히려 시스템 성능을 떨어뜨리는 애물단지가 될 수 있습니다.
• 인덱스는 결코 공짜가 아닙니다. 데이터를 추가(INSERT), 수정(UPDATE), 삭제(DELETE)할 때마다 인덱스도 함께 변경되어야 하므로 쓰기 성능이 저하되고, 별도의 저장 공간도 차지합니다.
• 따라서 우리는 이 비용을 상쇄하고도 남을 만큼의 '검색 성능 향상'이라는 이득을 얻을 수 있는 곳에만 전략적으로 인덱스를 생성해야 합니다.

기본 개념 참고

인덱스에 대한 기본적인 내용은 Index 문서, 옵티마이저와 커버링 인덱스는 Optimizer 문서, 복합 인덱스는 Composite Index 문서를 참고해주세요.

2. 핵심 원칙: 카디널리티 (Cardinality)

• 인덱스를 어디에 걸지 판단하는 가장 중요한 기준은 바로 **카디널리티(Cardinality)**입니다.
• 카디널리티란, 해당 컬럼에 저장된 값들의 고유성(uniqueness) 정도를 나타내는 지표입니다.

2.1 카디널리티가 높다 (High Cardinality)

• 해당 컬럼에 중복되는 값이 거의 없다는 의미입니다.
• 예: items 테이블의 item_id, item_name

2.2 카디널리티가 낮다 (Low Cardinality)

• 해당 컬럼의 값이 몇 종류 안되어 중복되는 값이 많다는 의미입니다.
• 예: items 테이블의 category (5종류), is_active (2종류)

2.3 왜 카디널리티가 중요한가?

• 인덱스는 '찾아보기'입니다. 찾아보기가 효과적이려면, 특정 키워드를 찾았을 때 검색 범위가 확 줄어들어야 합니다.
• items 테이블에서 WHERE is_active = TRUE라는 조건으로 검색한다고 생각해 보겠습니다.
• is_active 컬럼에 인덱스가 있더라도, TRUE인 데이터가 전체의 80%라면, 데이터베이스는 인덱스를 통해 전체 데이터의 80%를 스캔해야 합니다.
• 이런 경우 데이터베이스 옵티마이저는 "이럴 바엔 그냥 풀 테이블 스캔하는 게 낫겠다"고 판단할 수 있습니다.
• 반면 WHERE item_name = '게이밍 노트북'은 어떤가요?
• 인덱스는 수십만 건의 상품 데이터 중 단 1건으로 검색 범위를 완벽하게 좁혀줍니다.
• 더 자세한 내용은 아래 문서를 참고해주세요.
  • Selectivity and Cardinality

인덱스 설계의 핵심 규칙

인덱스는 카디널리티가 높은, 즉 식별력이 좋은 컬럼에 생성할 때 가장 효율적입니다.

3. 인덱스 생성 가이드라인

• 위의 핵심 원칙을 바탕으로, 어떤 컬럼이 인덱스 후보가 되는지 구체적인 가이드라인을 살펴보겠습니다.

3.1 WHERE 절에서 자주 사용되는 컬럼

• 가장 기본적이고 명백한 가이드라인입니다.
• 인덱스의 존재 이유 자체가 WHERE 절의 검색 속도를 높이는 것이기 때문입니다.
• 사용자들이 상품을 검색할 때 items.item_name으로 검색하거나, 특정 카테고리(items.category)를 필터링한다면 이 컬럼들은 인덱스 생성의 우선 후보가 됩니다.

3.2 JOIN의 연결고리가 되는 컬럼 (외래 키)

• JOIN의 성능은 연결고리가 되는 컬럼에 인덱스가 있는지 여부에 따라 극적으로 달라집니다.
• '행복쇼핑' 판매자가 등록한 모든 상품을 조회하는 쿼리를 예로 들어보겠습니다.

SELECT
    s.seller_name,
    i.item_name,
    i.price
FROM items i
JOIN sellers s ON i.seller_id = s.seller_id
WHERE s.seller_name = '행복쇼핑';

items.seller_id에 인덱스가 없을 때

• 만약 items 테이블의 seller_id 컬럼(외래 키)에 인덱스가 없다면, 데이터베이스는 다음과 같이 비효율적으로 동작합니다.

1. sellers 테이블에서 seller_name이 '행복쇼핑'인 판매자를 찾습니다. (seller_id = 1)
2. items 테이블의 모든 행을 처음부터 끝까지 스캔하면서, seller_id가 1인 상품을 하나씩 찾아냅니다.

조인의 논리적인 순서와 실제 순서의 차이

SQL의 논리적인 순서는 조인을 모두 다 한 다음에 WHERE를 실행합니다. 하지만 데이터베이스는 최적화를 위해 먼저 데이터를 줄인 다음에 조인합니다. 이때 최종 결과는 논리적인 순서와 같음을 보장합니다.

• items 테이블에 상품이 100만 개 있다면, JOIN을 위해 100만 번의 비교가 일어나는 끔찍한 일이 벌어집니다. 풀 테이블 스캔이 발생하는 것입니다.

items.seller_id에 인덱스가 있을 때

• 다행히 items.seller_id에는 외래 키 제약 조건 덕분에 인덱스가 자동으로 생성되어 있습니다.
• 인덱스가 있을 때의 동작은 완전히 다릅니다.

1. sellers 테이블에서 seller_name이 '행복쇼핑'인 판매자를 찾습니다. (seller_id = 1)
2. items.seller_id 인덱스를 사용하여 seller_id가 1인 상품 데이터의 위치를 곧바로 찾아냅니다. 풀 테이블 스캔이 사라지고 몇 번의 탐색만으로 JOIN이 완료됩니다.

• EXPLAIN으로 확인해보겠습니다.

EXPLAIN SELECT
    s.seller_name,
    i.item_name,
    i.price
FROM items i
JOIN sellers s ON i.seller_id = s.seller_id
WHERE s.seller_name = '행복쇼핑';

실행 결과

id	table	type	key	rows	Extra
1	s	const	seller_name	1	Using index
1	i	ref	fk_items_sellers	5

• items 테이블(i)의 type이 ref이고, key가 fk_items_sellers인 것을 볼 수 있습니다.
• 이는 JOIN 과정에서 items 테이블을 조회할 때 seller_id 인덱스를 매우 효율적으로 사용했다는 증거입니다.

JOIN과 인덱스

따라서 JOIN에 사용되는 외래 키(Foreign Key) 컬럼에는 반드시 인덱스를 생성해야 합니다.

MySQL은 외래 키 제약조건을 설정하면 인덱스를 자동으로 생성합니다.

외래 키 제약조건이 없는 경우

종종 외래 키 제약조건을 걸지 않고 데이터베이스를 사용하는 경우도 있습니다. 이때는 조인 성능을 위해 외래 키로 사용되는 컬럼에 반드시 인덱스를 직접 생성해야 합니다.

3.3 ORDER BY 절에서 자주 사용되는 컬럼

• ORDER BY를 사용한 정렬은 데이터의 양이 많을 경우 매우 비용이 큰 작업입니다.
• 데이터베이스는 결과를 반환하기 전에 모든 데이터를 메모리에 올리고 정렬해야 하기 때문입니다.
• 만약 ORDER BY에 사용된 컬럼에 인덱스가 있다면 어떨까요?
• B-Tree 인덱스는 이미 데이터가 정렬된 상태로 저장되어 있습니다.
• 데이터베이스는 굳이 데이터를 따로 정렬할 필요 없이, 인덱스에 있는 순서 그대로 데이터를 읽기만 하면 됩니다.
• 비용이 큰 정렬 작업(filesort)을 완전히 건너뛸 수 있는 것입니다.
• '최신 등록 상품 목록 10개'를 보여주는 ORDER BY registered_date DESC LIMIT 10과 같은 쿼리는 registered_date 컬럼에 인덱스가 있을 때 엄청난 성능 향상을 기대할 수 있습니다.

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

• 지금까지 인덱스의 장점, 즉 검색(SELECT) 속도를 비약적으로 향상시키는 원리에 대해 배웠습니다.
• 이쯤 되면 '그럼 모든 컬럼에 인덱스를 걸면 최고 아닌가?' 라는 순수한 생각을 할 수도 있습니다.
• 결론부터 말하자면, 그것은 데이터베이스 성능을 망치는 최악의 선택입니다.
• 이번에는 "인덱스는 공짜가 아니다"라는 중요한 명제를 이해하고, 인덱스를 생성하고 유지하는 데 따르는 '비용', 즉 인덱스의 단점과 관리 시의 주의사항에 대해 알아보겠습니다.
• 모든 기술에는 명암이 있듯, 인덱스 역시 잘못 사용하면 약이 아니라 독이 될 수 있습니다.

4.1 인덱스는 공짜가 아니다: 인덱스의 단점

• 인덱스의 단점은 크게 두 가지 비용으로 설명할 수 있습니다.

1. 저장 공간 (Storage)

• 인덱스는 원본 테이블과는 별개로, B-Tree 구조를 가진 물리적인 파일로 디스크에 저장됩니다.
• 즉, 인덱스를 생성하면 그만큼의 추가 저장 공간이 필요합니다.
• 인덱스는 어떻게 구성하는지에 따라 다르지만, 일반적으로 원본 테이블 크기의 약 10% 내외의 공간을 추가로 차지한다고 알려져 있습니다.
• 만약 100GB에 달하는 거대한 items 테이블이 있고, 여기에 5개의 인덱스를 추가로 생성한다면?
• 인덱스만으로 약 50GB라는 무시할 수 없는 추가 디스크 공간이 필요하게 됩니다.
• 인덱스를 무분별하게 생성하면 디스크 사용량이 계속해서 늘어나는 것을 보게 될 것입니다.

2. 쓰기 성능 (INSERT, UPDATE, DELETE)

• 이것이 인덱스의 가장 치명적인 단점이자, 반드시 이해해야 할 핵심 트레이드오프입니다.
• 인덱스는 SELECT의 속도를 높이는 대가로, INSERT, UPDATE, DELETE의 속도를 희생시킵니다.

INSERT

• 새로운 상품이 등록되면(INSERT), items 테이블에 행이 추가됩니다.
• 동시에, 이 테이블에 생성된 모든 인덱스(예: PRIMARY, seller_id, idx_items_category_price)의 B-Tree에도 새로운 데이터에 대한 키 값과 주소가 추가되어야 합니다.
• 이 과정에서 B-Tree의 정렬 순서를 유지하고 균형을 맞추기 위한 추가적인 연산이 발생합니다.
• 인덱스가 5개라면, 테이블 삽입 1번에 인덱스 삽입 5번의 작업이 추가되는 셈입니다.

DELETE

• 상품이 삭제되면(DELETE), 테이블에서 행이 사라집니다.
• 동시에 모든 인덱스에서도 해당 상품에 대한 키 값이 삭제되어야 합니다.

UPDATE

• 상품 정보가 수정될 때가 가장 복잡합니다.
• 만약 인덱스가 없는 stock_quantity 컬럼의 값이 변경된다면, 인덱스는 수정할 필요가 없으므로 비교적 빠릅니다.
• 하지만 인덱스가 있는 price 컬럼의 값이 변경된다면, 데이터베이스는 기존 price 값으로 된 인덱스 항목을 '삭제'하고, 새로운 price 값으로 인덱스 항목을 '추가'하는 것과 유사한 작업을 수행합니다.
• 왜냐하면 인덱스는 변경된 값에 맞추어 새로운 정렬 상태를 유지해야 하기 때문입니다.
• 이는 INSERT와 DELETE가 동시에 발생하는 것과 같아 부하가 큽니다.

5. 실무 가이드: 균형의 미학

• 이러한 장단점을 고려할 때, 우리는 어떤 전략을 취해야 할까요?

5.1 워크로드를 분석하라: 읽기 vs 쓰기

읽기 중심(Read-heavy) 서비스

• 데이터 분석 시스템, 블로그, 뉴스 사이트처럼 데이터 변경보다는 조회가 훨씬 더 빈번한 서비스라면, 다양한 조회 성능을 높이기 위해 인덱스를 비교적 자유롭게 생성해도 좋습니다.
• 우리 쇼핑몰의 상품 조회 기능이 대표적입니다.

쓰기 중심(Write-heavy) 서비스

• 실시간으로 데이터를 기록하는 로깅 시스템, 주식 거래 시스템, 채팅 서비스처럼 INSERT나 UPDATE가 매우 빈번한 서비스라면, 인덱스 생성에 매우 신중하고 보수적이어야 합니다.
• 모든 인덱스는 쓰기 작업에 오버헤드를 추가하기 때문입니다.
• 꼭 필요한 최소한의 인덱스만 유지해야 합니다.

5.2 "혹시나 해서" 인덱스를 만들지 마라

• 사용하지 않는 인덱스는 저장 공간만 차지하고 쓰기 성능만 저하시키는 암적인 존재입니다.
• 명확한 목적 없이, "나중에 쓸 것 같아서" 라는 이유로 인덱스를 미리 만드는 것은 좋지 않습니다.
• 느린 쿼리가 발견되었을 때, 그 쿼리를 개선하기 위한 목적으로 생성해야 합니다.

5.3 사용하지 않는 인덱스는 주기적으로 정리하라

• 대부분의 데이터베이스는 특정 인덱스가 얼마나 사용되었는지 모니터링하는 기능을 제공합니다.
• 몇 달, 혹은 1년 이상 아무도 사용하지 않는 인덱스가 있다면, 과감하게 삭제하여 시스템 자원을 확보하고 쓰기 성능을 높여야 합니다.

인덱스 관리의 핵심

인덱스는 SELECT 성능을 위한 최고의 무기이지만, 저장 공간과 쓰기 성능이라는 비용을 요구하는 양날의 검과 같습니다.

지금까지 우리는 데이터를 빠르고 효과적으로 '읽는' 방법에 많은 시간을 투자했습니다. 하지만 데이터베이스의 더 근본적인 역할은 데이터를 '안전하게 지키는' 것입니다.

만약 상품 가격에 음수(-)가 들어가거나, 있지도 않은 판매자 ID로 상품이 등록되는 등 말도 안 되는 '쓰레기 데이터'가 시스템에 저장된다면 어떻게 될까요? 이는 분석 결과를 왜곡하고, 심각한 시스템 오류를 야기할 수 있습니다.

6. 인덱스 컬럼은 가공하면 안된다

• WHERE 절에서 인덱스가 적용된 컬럼을 함수로 감싸거나 계산을 하는 등 가공하게 되면 인덱스가 적용되지 않습니다.
• 이는 실무에서 정말 자주 하는 실수이므로 반드시 기억해야 합니다.

인덱스 컬럼 가공 금지

WHERE 절에서 인덱스가 적용된 컬럼에 SUBSTRING() 같은 함수를 사용하거나 연산을 하면 인덱스가 작동하지 않아 테이블 전체를 스캔하게 되므로 성능이 크게 저하됩니다.

문제 상황

• 문제: WHERE SUBSTRING(item_name, 1, 5) = '게이밍'처럼 인덱스 컬럼(item_name)을 가공하면, 데이터베이스는 정렬된 인덱스를 활용하지 못하고 모든 데이터를 일일이 확인합니다.
• WHERE indexed_column * 10 = 100 이런 경우도 마찬가지로 인덱스를 사용하지 못합니다.
• 원인: 인덱스는 가공되지 않은 원본 값을 기준으로 만들어지기 때문입니다.
• 해결책: 컬럼 자체를 가공하는 대신, LIKE 연산자를 사용하여 WHERE item_name LIKE '게이밍%'와 같이 조건을 변경해야 인덱스를 효율적으로 사용할 수 있습니다.

인덱스 활용의 핵심

SQL 성능을 높이려면 인덱스 컬럼은 절대 가공하지 말고 원본 상태 그대로 사용해야 합니다.

7. 문제와 풀이

7.1 문제와 풀이를 위한 인덱스 초기화

주의사항

문제와 풀이 진행 전에 기존에 존재하는 idx로 시작하는 모든 인덱스를 제거해야 합니다.

강의를 복습하는 과정에서 지금 과정보다 이후에 만들어진 인덱스가 존재할 수도 있습니다. 찾아서 모두 제거해주세요.

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;

7.2 문제: 인덱스들을 만들어서 다음 쿼리 성능을 개선해라

• 최근 쇼핑몰의 items 테이블에 데이터가 많아지면서, 사용자들이 특정 조건으로 상품을 조회할 때 시스템이 점점 느려진다는 불만이 접수되었습니다.
• 원인 파악 결과, 자주 사용되는 조회 쿼리에 인덱스가 걸려있지 않아 전체 데이터를 스캔(Full Table Scan)하고 있었습니다.
• 다음은 느리다고 보고된 주요 쿼리입니다.

SELECT * FROM items
WHERE category = '전자기기' AND is_active = TRUE;

SELECT * FROM items
WHERE category = '전자기기' AND is_active = TRUE
ORDER BY stock_quantity DESC;

SELECT * FROM items
WHERE stock_quantity < 90 AND category = '전자기기' AND is_active = TRUE;

SELECT * FROM items
WHERE stock_quantity < 90 AND category = '전자기기' AND is_active = TRUE
ORDER BY stock_quantity DESC;

• 인덱스를 만들어서 이 쿼리들이 풀 테이블 스캔이 걸리지 않도록 해주세요.
• 필요하다면 여러 인덱스를 만들어도 됩니다.

7.3 정답

CREATE INDEX idx_items_category_active_stock ON items (category, is_active, stock_quantity DESC);

정답 해설

1. 컬럼 순서: category, is_active, stock_quantity 순서로 배치했습니다.

   • category와 is_active는 등호(=) 조건으로 사용됩니다.
   • stock_quantity는 범위 조건(<)으로 사용됩니다.
   • 복합 인덱스 대원칙에 따라 등호 조건을 앞에, 범위 조건을 뒤에 배치했습니다.

2. 내림차순 인덱스: stock_quantity DESC로 내림차순 인덱스를 생성했습니다.

   • 쿼리에서 ORDER BY stock_quantity DESC를 사용하므로, 내림차순 인덱스를 만들면 정방향 스캔으로 처리할 수 있어 가장 효율적입니다.
   • 역방향 스캔(Backward index scan)을 피할 수 있습니다.

3. 모든 쿼리 커버: 이 하나의 인덱스로 제시된 4개의 쿼리 모두를 효율적으로 처리할 수 있습니다.

   • 첫 번째 쿼리: category와 is_active로 필터링
   • 두 번째 쿼리: category와 is_active로 필터링 + stock_quantity DESC 정렬
   • 세 번째 쿼리: category, is_active, stock_quantity 모두 사용
   • 네 번째 쿼리: category, is_active, stock_quantity 모두 사용 + stock_quantity DESC 정렬

8. 정리

8.1 옵티마이저와 인덱스 선택

• 데이터베이스 옵티마이저는 쿼리 실행 시, 인덱스 사용과 테이블 전체 스캔(Full Table Scan) 중 더 효율적인 방법을 선택합니다.
• 인덱스를 사용하는 것이 오히려 비용이 크다고 판단되면(손익분기점 초과), 인덱스가 있어도 사용하지 않습니다.
• 일반적으로 전체 데이터의 20~25% 이상을 조회하는 경우, 인덱스를 통한 랜덤 I/O보다 테이블 전체를 순차적으로 읽는 순차 I/O가 더 빠르다고 판단합니다.
• 데이터 양이 매우 적을 때도 옵티마이저는 풀 테이블 스캔을 선택할 수 있습니다.

8.2 커버링 인덱스

• 쿼리에 필요한 모든 컬럼을 포함하는 인덱스를 커버링 인덱스라 합니다.
• 커버링 인덱스를 사용하면 원본 테이블에 접근하지 않고 인덱스만으로 쿼리를 처리할 수 있어, 랜덤 I/O가 발생하지 않아 성능이 크게 향상됩니다.
• 실행 계획(EXPLAIN)의 Extra 컬럼에 Using index가 표시되면 커버링 인덱스가 사용된 것입니다.
• SELECT 성능을 크게 높이지만, 인덱스 크기가 커지고 쓰기(INSERT, UPDATE, DELETE) 성능이 저하되는 단점이 있습니다.

8.3 복합 인덱스 대원칙

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

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

8.4 복합 인덱스 상세

• 두 개 이상의 컬럼을 묶어 하나의 인덱스로 만든 것을 복합 인덱스라 합니다.
• 컬럼의 순서가 매우 중요하며, 인덱스는 첫 번째 컬럼부터 순서대로 조건에 사용되어야 합니다 (인덱스 왼쪽 접두어 규칙).
• 인덱스가 (A, B) 순서라면 WHERE A=... 또는 WHERE A=... AND B=...는 효율적이지만, WHERE B=...는 인덱스를 제대로 활용할 수 없습니다.
• WHERE 절과 ORDER BY 절이 인덱스 순서와 일치하면, 불필요한 정렬 작업(filesort)을 생략할 수 있어 성능에 매우 유리합니다.
• 복합 인덱스의 첫 번째 컬럼을 건너뛰고 두 번째 이후의 컬럼만 조건으로 사용하면 인덱스를 활용할 수 없어 풀 테이블 스캔이 발생할 수 있습니다.
• 복합 인덱스의 선행 컬럼에 범위 조건(>, <, BETWEEN 등)이 사용되면, 그 뒤에 오는 컬럼은 인덱스의 정렬 효과를 누릴 수 없어 효율이 떨어집니다.
• 범위 조건으로 넓게 가져온 데이터를 필터링하는 방식으로 동작하여, 인덱스의 성능을 절반만 활용하게 됩니다.
• 복합 인덱스 설계 시 등호(=) 조건을 사용하는 컬럼을 앞에, 범위 조건을 사용하는 컬럼을 뒤에 두는 것이 일반적인 최적화 전략입니다.
• 범위 검색(>=) 대신 IN 절을 사용하면, 옵티마이저는 이를 여러 개의 동등 비교(=)로 인식하여 복합 인덱스를 더 효율적으로 활용할 수 있습니다.

8.5 인덱스 설계 가이드라인

• 인덱스 생성의 가장 중요한 기준은 카디널리티(Cardinality), 즉 값의 고유성 정도입니다. 중복도가 낮은(카디널리티가 높은) 컬럼에 생성해야 효과적입니다.

인덱스 생성 가이드라인

• WHERE 절에서 자주 사용되는 컬럼
• JOIN의 연결고리가 되는 컬럼 (외래 키)
• ORDER BY 절에서 자주 사용되는 컬럼 (정렬 작업 회피)

8.6 인덱스의 단점과 주의사항

• 인덱스는 공짜가 아니며, 단점도 명확히 존재합니다.
• 저장 공간 차지: 인덱스는 별도의 파일로 저장되어 추가 디스크 공간을 사용합니다.
• 쓰기 성능 저하: INSERT, UPDATE, DELETE 작업 시 테이블뿐만 아니라 인덱스도 함께 수정해야 하므로 오버헤드가 발생합니다. 특히 인덱스 컬럼의 UPDATE는 부하가 큽니다.
• 읽기 중심 서비스와 쓰기 중심 서비스를 구분하여 필요한 최소한의 인덱스만 생성하고, 사용하지 않는 인덱스는 주기적으로 정리해야 합니다.
• WHERE 절에서 인덱스 컬럼을 함수나 연산으로 가공하면 인덱스가 작동하지 않으므로 원본 상태 그대로 사용해야 합니다.