[MySQL] Index 공식 문서 읽어 보기

개요

공식 문서를 읽어보며 내용을 정리해 보자

• MySQL 최적화 및 인덱스 공식 문서
  https://dev.mysql.com/doc/refman/8.4/en/optimization-indexes.html

---

1. 최적화 및 인덱스

1. 인덱스 생성의 장점

   • 인덱스는 테이블 행으로 가는 포인터 역할을 하여,
     WHERE 절 조건에 맞는 행을 빠르게 찾고 다른 열 값을 조회할 수 있도록 돕는다.
   • MySQL의 모든 자료형에 인덱스를 생성할 수 있다.
   • 과도한 인덱스 생성은
     • 저장 공간 낭비
     • 옵티마이저가 사용할 인덱스를 결정하는 시간 증가
     • INSERT/UPDATE/DELETE 시 인덱스 유지 비용 증가
   • 적절한 균형으로 최적의 인덱스 집합을 찾는 것이 중요하다.

2. 인덱스가 없을 때

   • MySQL이 전체 테이블을 스캔하며 조건에 맞는 행을 찾아야 하므로, 테이블이 클수록 성능 저하가 심해진다.

3. 인덱스가 있을 때

   • MySQL은 B-Tree(대부분의 인덱스)나 R-Tree(공간 데이터) 등에서 중간 위치를 바로 찾아가므로
     전체 행을 순차적으로 읽는 것보다 훨씬 빠르다.

4. 인덱스 저장 구조

   • PRIMARY KEY, UNIQUE, INDEX, FULLTEXT 등은 B-Tree에 저장된다.
   • 예외
     • 공간 데이터 인덱스: R-Tree
     • MEMORY 테이블의 해시 인덱스: Hash Index
     • InnoDB의 FULLTEXT: 역순 목록

---

2. 인덱스 사용 시기 및 방법

1. WHERE 절 필터링

   • 조건에 맞는 행을 빠르게 찾기 위해 사용.

2. 선택적 인덱스 제거

   • 여러 인덱스 중 가장 선택도가 높은(가장 적은 행을 걸러내는) 인덱스를 MySQL이 선택한다.

3. 다중 열 인덱스

   • (col1, col2, col3) 인덱스는
     • (col1)
     • (col1, col2)
     • (col1, col2, col3)
       에 대해 사용할 수 있다.
       (왼쪽 접두사 원칙)

4. 조인 최적화

   • 조인할 때 양쪽 테이블의 조인 키가 같은 자료형·길이·문자셋이어야 인덱스를 효과적으로 사용한다.
   • 예: VARCHAR(10) vs CHAR(10)는 가능, VARCHAR(10) vs CHAR(15)는 불가능.

5. MIN()/MAX() 최적화

   • WHERE key_part1 = 10 과 같은 상수 조건이 있으면
     INDEX(key_part1, key_part2) 에서 MIN(key_part2), MAX(key_part2)를
     단일 인덱스 조회로 처리할 수 있다.

6. ORDER BY / GROUP BY

   • 인덱스가 (k1, k2) 일 때 ORDER BY k1, k2 또는 ORDER BY k1 DESC, k2 DESC 에만 적용된다.

7. 커버링 인덱스

   • 쿼리에 사용된 모든 열이 인덱스에 포함되면, 데이터 행을 읽지 않고 인덱스만으로 결과를 반환할 수 있다.
   • 예:

     SELECT key_part3
     FROM tbl_name
     WHERE key_part1 = 1;

     에서 (key_part1, key_part3) 인덱스는 커버링 인덱스가 된다.