B-Tree 인덱스 사용에 영향을 미치는 요소

인덱스 키 값의 크기

• 인덱스 키가 커지면 발생하는 일로는 인덱스는 디스크에 저장되고,
  검색 시 디스크 또는 메모리(Buffer Pool)에서 한 페이지 단위로 읽힌다.
• 그런데 인덱스 키 값이 길어질수록 한 페이지에 저장할 수 있는 레코드 수가 줄어든다.

👉 같은 데이터를 찾기 위해 더 많은 페이지를 읽어야 할 수 있음으로, 디스크 I/O 증가하고 검색 속도 저하

인덱스 키 값이 작아야하는 이유

인덱스에 저장되는 값의 바이트 수가 적어야한다는 의미

• 왜 작아야 좋을까?
  • InnoDB는 인덱스를 16KB 페이지 단위로 저장한다.
  • 키가 작으면
    • 한 페이지에 더 많은 인덱스 레코드 저장 가능
    • B-Tree 높이가 낮아짐
    • 디스크 I/O 감소
    • 메모리 캐시 효율 증가

인덱스의 선택도

• 기본 상황

SELECT *
FROM bt_test
WHERE country = 'KOREA'
  AND city = 'SEOUL';

 

• 가정
  • 전체 레코드 수 = 10,000건
  • country 컬럼에 인덱스 존재

1. country 유니크 값이 10개일 때
   • 한 국가당 평균 레코드 수 : 10,000 ÷ 10 = 1,000건
   • 즉, 약 1,000건 조회하지만 그 중에서 AND city = 'SEOUL’ 를 만족하는 레코드 1건
   • 결과적으로 1,000건 읽을 때, 999건은 불필요하게 읽음
   • 유니크 값 적음 → 많은 레코드 묶여 있음 → 많이 읽어야 함 👉 인덱스 효율 낮음
2. country 유니크 값이 1,000개일 때
   • 한 국가당 평균 레코드 수 : 10,000 ÷ 1,000 = 10건
   • 즉, 약 10건 조회하고, 그 중에서 AND city = 'SEOUL’ 를 만족하는 레코드 1건
   • 결과적으로 10건 읽고, 9건만 불필요하게 읽음
   • 유니크 값 많음 → 좁은 범위 → 적게 읽음 👉 인덱스 효율 매우 좋음

인덱스 손익분기점

일반적으로 인덱스를 통해 1건 읽는 비용 ≈ 테이블에서 직접 1건 읽는 비용의 4~5배

DB 옵티마이저는 보통 이렇게 판단한다.

• 읽어야 할 레코드가 전체의 20~25% 이하
• 👉 인덱스를 사용해 랜덤 I/O로 찾는게 유리
• 20~25% 초과
• 👉 풀 테이블 스캔을 통해 순차 I/O로 읽는게 유리

이걸 인덱스 손익분기점이라고 한다.