2025-09-08

오늘 배운 것

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

• 칼럼의 크기, 레코드 건수, 유니크한 인덱스 키 값의 개수

8.3.3.1 인덱스 키 값의 크기

• 페이지 OR 블록 : 디스크에 데이터를 저장하는 가장 기본 단위
  • 디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위
  • 버퍼 풀에서 데이터를 버퍼링하는 기본 단위
• B-Tree 자식 노드의 개수는 가변적
  • 인덱스의 페이지 크기와 키 값의 크기에 따라 결정
  • 키 값이 커지면 한 페이지에 저장되는 키의 개수가 줄어듬
  • 즉, 인덱스를 구성하는 키 값의 크기가 커지면 디스크로부터 읽어야 하는 횟수가 늘어나고, 그만큼 느려짐
  • 키값의 길이가 길어진다 → 전체 적인 인덱스의 크기가 커짐
    • 하나의 레코드를 위한 인덱스 크기가 커지면 커질수록 메모리에 캐시해 둘 수 있는 레코드 수 감소 → 메모리 효율이 떨어짐

8.3.3.2 B-Tree 깊이

• B-Tree의 깊이는 MySQL에서 값을 검색할 때 몇 번이나 랜덤하게 디스크를 읽어야 하는지와 직결
• 인덱스 키 값의 크기가 커지면 커질수록 하나의 인덱스 페이지가 담을 수 있는 인덱스 키 값의 개수 감소
• 이에 따라 B-Tree의 깊이가 깊어져서 디스크 읽기가 더 많이 필요
• 따라서 인덱스 키 값의 크기는 가능하면 작게 만드는 것이 좋음

8.3.3.3. 선택도(기수성)

• 선택도 OR 기수성 : 모든 인덱스 키 값 가운데 유니크한 값의 수
• 인덱스는 선택도가 높을수록 검색 대상이 줄어들기 때문에 빠르게 처리가능

8.3.3.4 읽어야 하는 레코드의 건수

• 인덱스를 통해 테이블의 레코드 읽기 > 바로 테이블의 레코드 읽기
• 인덱스를 통해 읽어야 할 레코드의 건수가 전체 테이블의 20% ~ 25%를 넘어서면 인덱스를 이용하지 않고 테이블을 모두 직접 읽어서 필요한 레코드만 가려내는(필터링) 방식으로 처리하는 것이 효율적

8.3.4 B-Tree 인덱스를 통한 데이터 읽기

8.3.4.1 인덱스 레인지 스캔

• 가장 빠른 방법
• 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식
• 방법
  • 인덱스 탐색 : 루트 노드에서부터 비교를 시작해 브랜치 노드를 거치고 최종적으로 리프노드까지 찾아 들어가 레코드의 시작 지점 찾기
  • 인덱스 스캔 : 시작해야할 위치부터 리프노드의 레코드를 순서대로 읽음
    • 리프 노드의 끝까지 읽으면 리프 노드 간의 링크를 이용해 다음 리프노드를 찾아서 다시 스캔
  • 스캔을 멈춰야할 위치에 도달 시, 지금까지 읽은 레코드를 반환하고 쿼리 마침
• 인덱스로 읽은 것수가 전체 테이블의 20~25% 이상일 때 왜 안쓰는게 더 효율적인가?
  • 리프노드를 스캔하면서 실제 데이터 파일의 레코드를 읽어와야 하는 경우
    • 리프 노드들은 인덱스 자체의 정렬 특성 때문에 정렬된 상태로 레코드를 가져옴
    • 하지만 실제 데이터 파일에 레코드들을 이렇게 정렬되어 있지 않음
    • 만약 인덱스의 키값 외의 추가 컬럼 값이 필요하면 해당 레코드 한건한건 랜덤 I/O 로 읽어와야함
    • 따라서 20 ~25%이상이면 그냥 필터링 하는것이 더 효율적
• 커버링 인덱스 : 인덱스로 처리가 완료되는 쿼리라 디스크의 레코드를 읽지 않아도 되는 것

8.3.4.2 인덱스 풀 스캔

• 인덱스의 처음부터 끝까지 모두 읽는 방식
• 쿼리의 조건절에 사용된 칼럼이 인덱스의 첫 번째 칼럼이 아닌 경우에 사용
• 인덱스의 크기는 테이블의 크기보다 작으므로 인덱스만 읽는것이 테이블을 처음부터 끝까지 읽는 것보다 효율적
• 따라서 쿼리가 인덱스에 명시된 칼럼만으로 조건 처리가 가능할 때 인덱스 풀 스캔 사용
• 방법
  • 인덱스 리프노드의 제일 앞 또는 제일 뒤로 이동
  • 인덱스의 리프노드를 연결하는 연결리스트를 따라서 처음부터 끝까지 스캔

8.3.4.3 루스 인덱스 스캔

• 타이트 인덱스 스캔 : 인덱스 레인지 스캔, 인덱스 풀 스캔
• 인덱스 레인지 스캔과 비슷하게 작동하지만 중간에 필요하지 않은 인덱스 키값은 무시하고 다음으로 넘어감
• GROUP BY 또는 MAX(), MIN()함수에 대해 최적화 하는 경우 사용

8.3.4.4 인덱스 스킵 스캔

• 8.0 이전 버전 까지는 복합인덱스의 한 컬럼이라도 비교 조건을 안하면 해당 인덱스를 사용할 수 없었음
• 이러한 문제를 해결한 것이 인덱스 스킵 스캔
• 루스 인덱스 스캔과의 차이점
  • 루스 인덱스 스캔은 GROUP BY 작업을 처리하기 위한 사용
• 방법
  • 복합 인덱스에서 비교하지 않은 컬럼의 유니크한 값을 모두 조회해 주어진 쿼리에다가 해당 유니크 한 값들의 조합들을 전부 쿼리하는 방식
• 단점
  • 조건이 없는 인덱스의 선행 칼럼이 유니크한 값의 개수가 적어야함
    • 개수가 많으면 오히려 인덱스를 사용하는 것이 느려질 수 있음
  • 쿼리가 인덱스에 존재하는 칼럼만으로 처리가 가능해야 함 (커버링 인덱스)

8.3.5 다중 칼럼(Multi-column) 인덱스

• 두 개 이상의 칼럼으로 구선된 인덱스
• 두 번째 칼럼은 첫 번째 칼럼에 의존해서 정렬되어 있음
• 따라서 인덱스 내에서 각 칼럼의 위치가 상당히 중요

8.3.6 B-Tree 인덱스의 정렬 및 스캔 방향

• 인덱스 키 값은 항상 오름차순이거나 내림차순으로 정렬되어 저장
• 오름차순으로 정렬되어 있다 하더라도 그 인덱스를 거꾸로 읽으면 내림차순과 같음

8.3.6.1 인덱스의 정렬

8.3.6.1.1 인덱스 스캔 방향

• 인덱스 생성 시점에 오름차순 또는 내림차순으로 정렬 결정
• 쿼리가 그 인덱스를 사용하는 시점에 인덱스를 읽는 방향에 따라 오름차순 또는 내림 차순 정렬 효과 적용

8.3.6.1.2 내림차순 인덱스

• 인덱스 역순 스캔이 인덱스 정순 스캔에 비해 느린 이유
  • 페이지 잠금이 인덱스 정순 스캔에 적합한 구조
  • 페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조
• 내림차순 조회가 많다면 오름 차순 인덱스를 역순으로 읽는 것보단 내림차순 인덱스를 정의하는 것이 더 좋음
• 인덱스의 앞쪽만 또는 뒤쪽만 집중적으로 읽어서 인덱스의 특정 페이지 잠금이 병목이 될 것으로 예상된다면, 쿼리에서 자주 사용되는 정렬 순서대로 인덱스를 생성하는 것이 잠금 병목 현상을 완화하는 데 도움이 될 것임

8.3.7 B-Tree 인덱스의 가용성과 효율성

8.3.7.1 비교조건의 종류와 효율성

• 필터링 : 인덱스를 통해 읽은 레코드가 나머지 조건에 맞는지 비교하면서 취사 선택하는 작업
• 작업 범위 결정 조건 : 작업의 범위를 결정하는 조건
• 필터링 조건 OR 체크 조건 : 비교 작업의 범위를 줄이지 못하고 단순히 거름 종이 역할만 하는 조건
• 작업 범위 결정 조건이 많을수록 쿼리 성능 증가
• 체크 조건은 오히려 느리게 만드는 경우가 많음

8.3.7.2 인덱스의 가용성

• 왼쪽 값을 기준으로 정렬되어 있기 때문에 인덱스의 설정 순서를 잘 못두면 인덱스가 작업 범위 결정 조건이 되지 못해 인덱스의 가용성을 떨어뜨린다.

8.3.7.3 가용성과 효율성 판단

• B-Tree 인덱스의 작업 범위 결정 조건으로 사용 할 수 없는 조건
  • NOT-EQUAL로 비교된 경우(<>, NOT IN, NOT BETWEEN, IS NOT NULL)
  • LIKE %??(앞부분이 아닌 뒷부분 일치) 형태로 문자열 패턴이 비교된 경우
  • 스토어드 함수나 다른 연산자로 인덱스 칼럼이 변형된 후 비교된 경우
  • NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우
  • 데이터 타입이 서로 다른 비교(인덱스 칼럼의 타입을 변환해야 비교가 가능한 경우)
  • 문자열 데이터 타입의 콜레이션이 다른 경우
• 다중 칼럼 인덱스
  • 작업 범위 결정 조건으로 인덱스를 사용하지 못하는 경우
    • 첫번째 컬럼에 대한 조건이 없을 때
    • 처번째 컬럼의 비교조건이 B-Tree 인덱스의 작업 범위 결정 조건으로 사용 할 수 없는 조건인 경우
  • 작업 범위 결정 조건으로 인덱스를 사용하는 경우
    • 선행 컬럼들까지 동등 비교형태로 비교할 때
    • 특정 컬럼에 대해 아래 조건으로 비교할 때
      • 동등비교
      • 크다 작다
      • LIKE로 좌측 일치 패턴

8.4 R-Tree 인덱스

• R-Tree(공간 인덱스) : 2차원의 데이터를 인덱싱하고 검색하는 목적의 인덱스
• MySQL의 공간 확장
  • 공간 데이터를 저장할 수 있는 데이터 타입
  • 공간 데이터의 검색을 위한 공간 인덱스(R-Tree 알고리즘)
  • 공간 데이터의 연산 함수(거리 또는 포함관계의 처리)

8.4.1 구조 및 특성

• 데이터 타입
  • Point
  • LINE
  • POLYGON
  • GEOMETRY : 나머지 3개의 슈퍼 타입으로 위 3개의 객체를 모두 저장 가능
• MBR(Minimum Bounding Rectangle) : 해당 도형을 감싸는 최소 크기의 사각형
• R-Tree : MBR들의 포함관계를 B-Tree 형태로 구현한 인덱스
  • 최하위 MBR → 리프 노드
  • 최상위 MBR → 루트 노드
  • 그외 MBR → 브랜치 노드

8.4.2 R-Tree 인덱스의 용도

• WGS84(GPS)기준의 위도, 경도 좌표 저장
• 좌표 시스템에 기반을 둔 정보 저장
• 함수
  • ST_Contains(사각 상자, px)
  • ST_Within(px, 사각상자)
• 위 함수를 활용해서 특정 거리 이내의 사각형들을 조회하고 ST_Distance_Sphere()함수로 필터링 하면 특정 거리 내의 위치들을 조회할 수 있음