Ch07. 인덱스 - 옵티마이저와 인덱스 선택

컬럼에 인덱스를 생성하면, 해당 컬럼을 조건으로 사용하는 모든 "WHERE"절의 성능이 향상될 것이라고 기대하기 쉽다. 하지만 항상 그렇지는 않다. 데이터베이스의 "옵티마이저(Optimizer)"는 쿼리를 실행하기 전에 여러 실행 가능한 방법을 평가하고, 그중 가장 비용이 적게 드는, 즉 가장 효율적이라고 판단되는 방법을 선택한다.

 

이 과정에서 옵티마이저는 "인덱스를 사용하는 것이 오히려 비효율적이라고 판단"하면, 인덱스가 존재하더라도 과감히 포기하고 "테이블 전체를 스캔(Full Table Scan)"하는 방법을 선택할 수 있다.

 

인덱스 동작 방식

• 인덱스를 사용하면 검색 대상의 양은 줄어들지만, items 테이블의 여러 위치에 흩어진 데이터에 랜덤 하게 접근해야 한다.
  • 예를 들어, "19, 6, 3, 14, 8"번 행 순서로 데이터를 읽어오는 식이다.
• 풀 테이블 스캔을 사용하면 items 테이블을 순서대로 쭉 읽으면 된다

인덱스 손익분기점

옵티마이저가 인덱스 사용 여부를 결정하는 핵심 기준은 바로 "손익분기점"이다. 여기서 손익분기점이란, 인덱스를 통해 데이터를 읽는 비용이 테이블 전체를 직접 읽는 비용보다 높아지는 지점을 의미한다

• 인덱스를 사용하는 비용: 인덱스 탐색 비용 + 인덱스에서 찾은 주소로 테이블에 접근하는 비용 (랜덤 I/O)
• 풀 테이블 스캔 비용: 테이블 전체를 순차적으로 읽는 비용 (순차 I/O)

일반적으로 "전체 데이터의 약 20~25% 이상을 조회해야 하는 쿼리"는 인덱스를 통해 테이블의 각 행에 개별적으로 접근하는 것보다, 차라리 테이블 전체를 순차적으로 스캔하는 것이 더 효율적이라고 알려져 있다. 즉, 조회하려는 데이터의 양이 손익분기점을 넘어가면 옵티마이저는 인덱스 사용을 포기한다.

 

왜 랜덤 I/O가 더 느릴까?

랜덤 I/O가 순차 I/O보다 느린 이유는 데이터를 찾는 과정에서 발생하는 시간 때문이다. 이를 HDD, SSD 디스크를 예로 들어 책 읽기에 비유해보자.

 

• 순차 I/O (Sequential I/O): 책을 1페이지부터 순서대로 읽기
  • 데이터가 저장된 위치를 한 번 찾으면, 그 이후로는 순서대로 쭉 읽기만 하면 된다.
  • HDD의 경우 디스크의 헤드가 거의 움직이지 않고 연속된 데이터를 읽어오므로 작업이 매우 빠르고 효율적이다.
  • SSD의 경우 "여기서부터 100개 읽어와"라는 하나의 큰 명령으로 처리할 수 있다.
• 랜덤 I/O (Random I/O): 책의 여러 페이지를 순서 없이 찾아 읽기
  • "5페이지"를 읽은 후 "200페이지"를 읽고, 다시 "45 페이지"를 읽는 것과 같다.
  • HDD의 경우 읽어야 할 데이터가 디스크의 여러 곳에 흩어져 있어, 데이터를 읽을 때마다 디스크 헤드가 물리적으로 해당 위치까지 이동해야 한다.
    • 이렇게 데이터의 위치를 찾는 데 걸리는 시간(탐색 시간, Seek Time)이 추가되기 때문에 순차 I/O에 비해 느리다.
    • SSD의 경우 찾아야 하는 데이터가 "100개"라면 "이거 읽어와", "저거 읽어와"라는 "100개"의 작은 명령을 각각 처리해야 한다.
      • 작은 명령을 여러 번 처리하는 것은 SSD 컨트롤러에 더 많은 오버헤드(부하)를 준다.

 

데이터베이스에서 인덱스를 통해 테이블의 여러 행에 접근하는 것은, 인덱스에 저장된 주소에 따라 디스크의 여러 위치를 오가야 하는 "랜덤 I/O"를 유발할 수 있다. 반면, 테이블 전체를 스캔하는 것은 처음부터 끝까지 데이터를 읽는 "순차 I/O"에 해당한다. 이 때문에 조회할 데이터가 아주 많을 경우, 여러 번의 "랜덤 I/O"를 수행하는 것보다 한 번의 "순차 I/O"가 더 빠를 수 있다.

 

 

예시 1: 인덱스를 사용하는 효율적인 범위 검색

explain select * from items where price between 50000 and 100000;

쿼리 결과

• type(range): 옵티마이저는 "idx_items_price" 인덱스를 사용해 특정 범위만 스캔했다.
• rows(5): 조회할 데이터가 "5건"으로 예상된다. 이는 전체 25건 중 20%에 해당하므로, 손익분기점을 넘지 않는다.
• key(idx_items_price): 따라서 옵티마이저는 "idx_items_price" 인덱스를 사용하는 효율적인 실행 계획을 세웠다.

 

예시 2: 인덱스를 포기하는 비효율적인 범위 검색

50,000 ~ 100,000 => 1,000 ~ 200,000으로 검색 범위를 넓혀보자

explain select * from items where price between 1000 and 200000;

쿼리 결과

• possible_keys(idx_items_price): 옵티마이저는 "idx_items_price" 인덱스를 사용할 수 있다는 것을 알고 있었다.
  • "possible_keys"는 이 쿼리에서 사용할 수 있는 인덱스 후보이다.
• key(NULL): 하지만 최종적으로 인덱스를 사용하지 않기로 결정했다.
• type(ALL): 결국 선택된 방법은 "풀 테이블 스캔"이다.
• filtered(76.00): 옵티마이저는 이 쿼리가 전체 데이터(25건)의 약 "76%", 즉 "19건" 정도를 반환할 것이라고 예측했다.
  • 이 정도면 손익분기점을 훌쩍 넘는 수치다.

이처럼 인덱스는 만능이 아니다. "WHERE" 절에 인덱스가 걸린 컬럼을 사용하더라도, 조회하려는 데이터의 범위가 너무 넓어 손익분기점을 넘어가면 옵티마이저는 인덱스를 사용하지 않을 수 있다. 따라서 쿼리 튜닝을 할 때는 "EXPLAIN"을 통해 옵티마이저가 실제로 인덱스를 사용하고 있는지 반드시 확인하는 습관을 들여야 한다.

 

 

데이터가 많이 부족하다면?

데이터 자체가 많이 부족하다면 옵티마이저는 "풀 테이블 스캔을 선택할 가능성이 있다." 테이블에 데이터가 몇 건 없다면, 테이블 전체를 순차적으로 읽는 비용이 인덱스를 탐색하고 테이블에 접근하는 비용보다 훨씬 저렴하기 때문이다.

 

"1,000 페이지"짜리 두꺼운 책에서는 색인(인덱스)을 보고 원하는 페이지를 찾아가는 것이 빠르다. 하지만 단 "3 페이지"짜리 얇은 소책자에서 특정 내용을 찾을 때는, 굳이 색인을 볼 필요 없이 그냥 1페이지부터 빠르게 훑어보는 것이 더 효율적이다.

 

마찬가지로 옵티마이저도 테이블이 몇 페이지 되지 않을 정도로 작다면, 굳이 인덱스를 사용하는 복잡한 과정을 거치지 않고 테이블 전체를 직접 스캔하는 것이 더 효율적이라고 판단한다.

 

이는 개발 환경에서 자주 발생하는 오해 중 하나다. 개발 중인 테이블에 소량의 테스트 데이터만 넣고 쿼리를 실행했을때실행했을 때, "EXPLAIN" 결과에 "type(ALL)" 이 표시되어 '인덱스가 왜 작동하지 않지?'라고 생각할 수 있다. 하지만 이는 옵티마이저의 지극히 합리적인 판단일 가능성이 높다. 프로덕션 환경에서 데이터가 수만, 수백만 건으로 늘어나면, 옵티마이저는 다시 인덱스를 사용하는 효율적인 실행 계획을 선택하게 될 것이다.

 

 

참고로 테스트 목적으로 인덱스를 강제로 적용하려면 다음과 같이 "FORCE INDEX"를 사용하면 된다.

SELECT * FROM my_table FORCE INDEX (idx_my_index) WHERE column = 'value';

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