MySQL 인덱스 Part 1

디스크 읽기 방식

• 랜덤 I/O: 디스크 헤더를 N번 움직인다.
• 순차 I/O: 디스크 헤더를 한번 만 움직인다.
• 디스크 I/O가 많을수록 처리 속도가 떨어진다.
  • 쿼리 튜닝은 꼭 필요한 데이터만 읽도록 디스크 I/O를 줄이는 것이 주요 목적
• 인덱스 스캔 후 레코드를 읽는 것 → 랜덤 I/O
• 테이블 스캔(풀 스캔) → 순차 I/O
• 대량의 레코드를 읽는 작업은 풀 스캔이 효율적

인덱스

• 테이블의 검색 속도를 향상시키기 위해 레코드의 정보를 정렬된 형태로 저장해둔 데이터 구조

B-Tree 인덱스

• 계층 구조
  • 루트 노드
  • 중간 노드(브랜치 노드)
  • 리프 노드: 데이터 레코드의 주소값을 가진다.
• 모든 리프 노드가 같은 깊이에 위치 → 일관된 검색 시간 보장
• 페이지 단위로 디스크에 저장된다.
  • 데이터 저장, I/O, 버퍼링 등의 기본(최소) 단위
  • 리프 노드를 구분하는 기준
• InnoDB 엔진에서 세컨더리 인덱스는 프라이머리 키 값을 가지고 있다.
  • 세컨더리 인덱스 → 프라이머리 키 인덱스 → 레코드

B-Tree 인덱스 CRUD

• 추가
  • 저장될 위치를 검색
  • 레코드의 주소 정보를 리프 노드에 저장
  • 리프 노드가 다 찬 경우 리프 노드를 분리 해야 함 → 상위 브랜치 노드까지 처리 범위가 늘어난다.
  • 인덱스 추가 비용은 대부분 디스크 I/O 처리 시간
• 삭제
  • 값이 저장된 리프 노드를 찾아 삭제 마킹
  • 삭제 마킹된 인덱스 키 공간은 방치하거나 재활용할 수 있다.
  • 디스크 I/O 필요
• 변경
  • 삭제 후 추가
• 검색
  • 완전 일치 또는 값의 앞부분만 일치하는 경우에 사용 가능
  • 부등호 비교 조건도 가능
  • 뒷부분만 검색하는 용도는 사용 불가
• 페이지 분할 때문에 대용량 테이블인 경우에

선택도(기수성)

• 유니크 값의 인덱스 수
• 고유 값 수 / 전체 행의 수
• 1에 가까울수록 선택도가 높음
• 예시
  • 1000명의 사용자가 있는 테이블
  • gender 칼럼: 2개 값(M/F) → 선택도 0.002 (낮음)
  • email 칼럼: 1000개 고유값 → 선택도 1.0 (높음)
  • country 칼럼: 50개 국가 → 선택도 0.05 (중간)

B-TREE 인덱스 데이터 읽기

인덱스 레인지 스캔

• 인덱스를 레코드를 한 건만 읽는 경우와 한 건 이상 읽는 경우
• 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방법
• 루트 노드부터 시작해 리프 노드의 시작점을 찾는다. (인덱스 탐색, index seek)
• 리프 노드의 레코드 순서대로 데이터를 읽는다. (인덱스 스캔, index scan)
• 인덱스를 이용해 데이터 파일에서 레코드를 읽어온다.
• 커버링 인덱스로 처리되는 쿼리는 디스크의 레코드를 읽지 않아도 된다.
  • 쿼리가 요청하는 컬럼이 인덱스에 모두 포함되어 있어 테이블 접근이 필요 없는 것

CREATE INDEX idx_user_email ON users(user_id, email);

SELECT user_id, email FROM users WHERE user_id BETWEEN 100 AND 200;

인덱스 풀 스캔

• 인덱스의 처음부터 끝까지 모두 읽는다
• 복합 인덱스의 첫 번째 컬럼을 사용하지 않고 후속 컬럼을 조건절에 사용할 때
• 인덱스의 정렬 순서를 활용하거나 커버링 인덱스 이점을 얻기 위함

  -- (first_name, last_name) 인덱스에서
  SELECT * FROM employees WHERE last_name = 'Kim

루스 인덱스 스캔

• 레인지 스캔과 비슷하나 중간에 필요하지 않은 인덱스는 무시한다.
• GROUP BY, MIN, MAX 함수 등의 최적화에 사용

  select dept_no, MIN(emp_no)
  from dept_emp
  where dep_no between 'd002' and 'd004'
  group by dept_no;

인덱스 스킵 스캔

• 8.0부터 도입

• 풀 스캔과 비슷

• 인덱스의 첫 번째 컬럼의 고유 값을 식별 후 검색

  -- (gender, birth_date) 복합 인덱스가 있을 때:
  
  -- 인덱스 사용 가능 (첫 번째 칼럼 포함)
  SELECT * FROM employees WHERE gender = 'M' AND birth_date >= '1965-02-01';
  SELECT * FROM employees WHERE gender = 'M';
  
  -- 인덱스 사용 불가 (첫 번째 칼럼 누락)
  SELECT * FROM employees WHERE birth_date >= '1965-02-01';

  SELECT * FROM employees WHERE gender = 'M' AND birth_date >= '1965-02-01';
  SELECT * FROM employees WHERE gender = 'F' AND birth_date >= '1965-02-01';

• 인덱스의 첫 번째 컬럼의 선택도가 낮아야 함(유니크한 값이 적어야 함)

• 커버링 인덱스여야 함