Ch07. 인덱스 - 인덱스 설계 가이드라인

인덱스를 만드는 법(CREATE INDEX)을 아는 것보다 더 중요한 것은, 어디에 인덱스를 만들어야 하는지 아는 것이다. 잘못된 인덱스는 오히려 시스템 성능을 떨어뜨리는 애물단지가 될 수 있다

 

인덱스는 결코 공짜가 아니다. 데이터를 추가(INSERT), 수정(UPDATE), 삭제(DELETE)할 때마다 인덱스도 함께 변경되어야 하므로 쓰기 성능이 저하되고, 별도의 저장 공간도 차지한다. 따라서 우리는 이 비용을 상쇄하고도 남을 만큼의 "검색 성능 향상"이라는 이득을 얻을 수 있는 곳에만 전략적으로 인덱스를 생성해야 한다.

 

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

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

• 카디널리티가 높다 (High Cardinality): 해당 컬럼에 중복되는 값이 거의 없다는 의미다.
  • 예: items 테이블의 "item_id", "item_name"
• 카디널리티가 낮다 (Low Cardinality): 해당 컬럼의 값이 몇 종류 안되어 중복되는 값이 많다는 의미다.
  • 예: items 테이블의 category(5종류), is_active(2종류)

인덱스는 "찾아보기"다. 찾아보기가 효과적이려면, 특정 키워드를 찾았을 때 검색 범위가 확!!! 줄어들어야 한다. items 테이블에서 "WHERE is_active = TRUE"라는 조건으로 검색한다고 생각해 보자. `is_active` 컬럼에 인

덱스가 있더라도, `TRUE` 인 데이터가 전체의 80%라면, 데이터베이스는 인덱스를 통해 전체 데이터의 80%를 스캔해야

한다. 이런 경우 데이터베이스 옵티마이저는 "이럴 바엔 그냥 풀 테이블 스캔하는 게 낫겠다"고 판단할 수 있다.

반면 `WHERE item_name = '게이밍 노트북'

` 은 어떤가? 인덱스는 수십만 건의 상품 데이터 중 단 1건으로 검색 범위를 완벽하게 좁혀준다.

 

 

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

 

인덱스 생성 가이드라인

 

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

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

 

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"인 상품을 하나씩 찾아낸다.
  • 풀 테이블 스캔이 발생한다.
• "items.seller_id"에 인덱스가 있을 때
  • 다행히 "items.seller_id"에는 외래 키 제약 조건 덕분에 인덱스가 자동으로 생성되어 있다. 인덱스가 있을 때의 동작은 완전히 다르다.
    1. sellers 테이블에서 "seller_name"이 '행복쇼핑'인 판매자를 찾는다.(seller_id = 1)
    2. "items.seller_id" 인덱스를 사용하여 "seller_id"가 "1"인 상품 데이터의 위치를 곧바로 찾아낸다.
  •  풀 테이블 스캔이 사라지고 몇 번의 탐색만으로 "JOIN"이 완료된다.

쿼리 실행 결과 확인

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 = '행복쇼핑';

쿼리 결과

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

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

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

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

 

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

 

"ORDER BY"를 사용한 정렬은 데이터의 양이 많을 경우 매우 비용이 큰 작업이다. 데이터베이스는 결과를 반환하기 전에 모든 데이터를 메모리에 올리고 정렬해야 하기 때문이다.

 

만약 "ORDER BY"에 사용된 컬럼에 인덱스가 있다면 어떨까? B-Tree 인덱스는 이미 데이터가 정렬된 상태로 저장되어 있다. 데이터베이스는 굳이 데이터를 따로 정렬할 필요 없이, 인덱스에 있는 순서 그대로 데이터를 읽기만 하면 된다. 비용이 큰 정렬 작업(filesort)을 완전히 건너뛸 수 있는 것이다. "최신 등록 상품 목록 10개"를 보여주는 "ORDER BY registered_date DESC LIMIT 10"과 같은 쿼리는"registered_date" 컬럼에 인덱스가 있을 때 엄청난 성능 향상을 기대할 수 있다.