Ch07. 인덱스 - 인덱스와 동등 비교

데이터베이스에서 인덱스는 다음 세 가지 상황에 사용된다.

• 동등 비교(=), 범위 검색(BETWEEN, >, <, >=, LIKE 등), "ORDER BY"를 통한 정렬 작업

인덱스와 동등 비교

"type: ref"는 동등 비교(=) 조건이나 "JOIN"에서 인덱스를 사용했다는 의미다. "EXPLAIN"을 사용해서 동등 비교를 확인해보고, 인덱스가 있을 때와 없을 때의 차이를 살펴보자

 

인덱스가 있을 때

 

1) 인덱스 생성

CREATE INDEX idx_items_item_name ON items (item_name);

 

2) 인덱스 생성 확인

show index from items;

 

3) "Explain"으로 쿼리 실행 계획 확인

EXPLAIN SELECT * FROM items WHERE item_name = '게이밍 노트북';

인덱스와 테이블

쿼리 결과

• type(REF): 이전의 "ALL"과 비교했을 때 가장 극적인 변화다.
  • type이 "ref"라는 것은, 인덱스를 사용해 동등 비교(=) 조건으로 데이터를 찾았다는 의미다.
  • ref는 "reference(참조)"의 약자로, 인덱스를 통해 조건에 맞는 데이터를 매우 효율적으로 참조해서 가져왔다는 뜻이다.
  • 풀 테이블 스캔(ALL)과는 비교할 수 없을 정도로 빠른 접근 방식이다.
• possible_keys: 현재 쿼리에서 사용 가능한 인덱스의 후보이다.
  • 지금은 "idx_items_item_name" 하나만 있지만 현재 쿼리에서 사용가능한 인덱스를 모두 보여준다.
  • 이 후보들 중에 선택되어 사용될 인덱스가 다음 "key" 항목에 나타난다.
• key(idx_items_item_name): 이전에는 "NULL"이었던 이 값에 우리가 방금 생성한 인덱스의 이름(idx_items_item_name)이 명확하게 표시된다.
  • 이것은 옵티마이저가 이 쿼리를 실행하는 데 "idx_items_item_name" 인덱스를 사용했음을 보여주는 직접적인 증거다.
• filtered(100.00) : 인덱스를 통해서 찾은 1개의 행을 100% 선택한다는 뜻이다.
• rows(1): 인덱스가 없을 때는 테이블 전체 행의 수인 "25"를 스캔할 것으로 예측했지만, 이제는 단 1개의 행만 읽으면 된다고 예측한다.
  • 마치 책의 맨 뒤에 있는 찾아보기를 통해 '게이밍 노트북'이라는 단어가 있는 페이지를 바로 찾아가는 것과 같다.
  • 테이블 전체를 뒤지는 것이 아니라, 인덱스를 통해 필요한 데이터의 위치를 정확히 찾아가기 때문에 탐색하는 행의 수가 극적으로 줄어든다.
  • EXPLAIN은 실제 SQL 쿼리를 실행하는 것이 아니다. 여기서 "rows"는 예측값이다. 따라서 환경에 따라 "1"이 아닌 다른 값이 나올 수 도 있다.
• Extra(NULL): 이전에 표시되었던 "Using where"가 사라졌다.
  • 인덱스 단계에서 이미 모든 검색 조건이 충족되었기 때문에, 데이터를 가져온 후 별도의 필터링 작업이 필요 없었다는 것을 의미한다.
  • 그만큼 작업이 더 단순하고 효율적으로 처리된 것이다.

결론적으로, "item_name" 컬럼에 인덱스를 생성하자 데이터베이스 옵티마이저는 풀 테이블 스캔이라는 비효율적인 방법을 버리고, 인덱스를 사용해 단 하나의 행만 읽어오는 매우 효율적인 실행 계획을 세웠다. 이것이 바로 우리가 인덱스를 사용하는 핵심적인 이유다

테이블에 데이터가 몇 만 건 이상이면 인덱스를 사용하는 게 이득이지만, 지금처럼 샘플 데이터가 너무 적은 경우 데이터베이스는 환경에 따라 인덱스를 사용하지 않고, 그냥 풀 테이블 스캔을 선택할 수도 있다. 예를 들어 2페이지 정도의 작은 책이라면 색인을 찾기보다 그냥 책을 바로 보는 게 원하는 결과를 더 빨리 얻을 수도 있기 때문이다.

이럴 때 인덱스를 강제로 적용하려면 다음과 같이 "FORCE INDEX"를 사용하면 된다. 그러면 인덱스를 사용한 실행 계획을 확인할 수 있다.

EXPLAIN SELECT * FROM items FORCE INDEX (idx_items_item_name) WHERE item_name = '게이밍 노트북';

이 방법을 사용하면 쿼리 옵티마이저가 최적의 인덱스를 선택할 수 없기 때문에 실무에서는 권장하지 않는다. 꼭 필요하다면 주의해서 사용해야 한다