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

개요

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

https://dev.mysql.com/doc/refman/8.4/en/optimization-indexes.html

MySQL :: MySQL 8.4 Reference Manual :: 10.3 Optimization and Indexes

Document

1. 최적화 및 인덱스

• DB 성능 개선하는 가장 좋은 방법 중 하나는 하나 이상의 열에 Index를 생성하는 것
  • Index 항목은 테이블 행으로의 포인터 역할
  • Query가 WHERE 절의 조건에 맞는 행을 빠르게 확인하고 해당 행의 다른 열 값을 검색할 수 있도록 도움
  • MySQL의 모든 데이터 타입은 Index를 생성할 수 있습니다.
  • 주의 할점
    • Query에서 사용될 수 있는 모든 열에 인덱스를 생성하는 것은 공간 낭비
    • MySQL이 사용할 Index를 결정하는 데 드는 시간을 낭비
    • 삽입, 업데이트, 삭제 작업에서도 각각의 Index를 업데이트 해야 하므로 추가 비용이 발생
  • 따라서, 최적의 인덱스 집합을 사용하여 빠른 Query를 실행할 수 있도록 적절한 균형을 찾는 것이 중요합니다.
• Index가 없을 때
  • MySQL은 첫 번째 행부터 시작하여 전체 테이블을 읽어 관련 행을 찾아야 합니다.
  • 테이블이 클수록 비용이 더 많이 듭니다.(성능 저하)
• Index가 있을 대
  • MySQL은 모든 데이터를 살펴 보지 않고도 데이터 파일 중간에서 찾을 위치를 빠르게 결정할 수 있습니다.
    이는 모든 행을 순차적으로 읽는 것보다 훨신 빠릅니다.
•  DB Index 관리 구조
  • 대부분의 MySQL Index(PRIMARY KEY, UNIQUE, INDEX 및 FULLTEXT)는 B-TREE에 저장
  • 예외
    • 공간 데이터 유형의 Index는 R-TREE를 사용합니다.
    • MEMORY 테이블도 HASH INDEX를 지원합니다.
    • InnoDB는 FULLTEXT INDEX에 역순 목록을 사용합니다.

2. Index를 언제/어떻게 사용 해야 할까?

1. WHERE 절에 일치하는 행을 빠르게 찾기 위해.
2. 고려 대상에서 행을 제외하기 위해
   • 여러 인덱스 중에서 선택해야 하는 경우, MySQL은 일반적으로 가장 적은 수의 행(가장 선택적인 인덱스)을 찾는 인덱스를 사용합니다.
3. 테이블에 다중 열 인덱스가 있는 경우
   • 인덱스의 최좌측 접두사(leftmost prefix)를 사용하여 행을 조회할 수 있습니다. 예를 들어, (col1, col2, col3)에 대해 3개의 열로 구성된 인덱스가 있다면, (col1), (col1, col2), (col1, col2, col3)에 대해 인덱싱된 검색 기능을 사용할 수 있습니다. 자세한 내용은 10.3.6절 “다중 열 인덱스(Multiple-Column Indexes)”를 참조하세요.
4. 조인을 수행할 때 다른 테이블에서 행을 검색하기 위해.
   • MySQL은 열이 동일한 타입과 크기로 선언된 경우, 인덱스를 더 효율적으로 사용할 수 있습니다.
   • 이때, VARCHAR와 CHAR는 크기가 동일하게 선언되었다면 동일하다고 간주됩니다.
     • EX) VARCHAR(10)과 CHAR(10)는 동일하지만, VARCHAR(10)과 CHAR(15)는 다릅니다.
   • 비바이너리(nonbinary) 문자열 열 간의 비교의 경우 두 열 모두 동일한 문자 집합을 사용해야 합니다.
     • EX) utf8mb4 열과 latin1 열을 비교하면 인덱스를 사용할 수 없습니다.
   • 서로 다른 데이터 타입의 열을 비교할 때, 인덱스가 비효율적이거나 사용되지 않을 수 있다
     
     • EX) 숫자 열에 값이 1이 있다고 가정하고, 이를 문자열 열과 비교한다고 해봅시다.
       
       • 문자열 열에는 '1', ' 1'(앞에 공백 있음), '00001'(앞에 0이 추가됨), '01.e1'(지수 형태) 같은 다양한 형식의 값이 있을 수 있습니다.
       • 이런 경우, MySQL은 문자열을 숫자로 변환하거나 숫자를 문자열로 변환해야만 비교가 가능합니다.
5. 특정 인덱스된 열 key_col의 MIN() 또는 MAX() 값을 찾기 위해 사용할 경우
   • WHERE 조건 최적화
     • MySQL은 WHERE 절에서 인덱스의 각 부분(key part)에 대해 고정된 값(constant)을 사용하는지 확인합니다.
     • EX) key_part1, key_part2 같은 인덱스가 있을 때, WHERE key_part1 = 10 AND key_part2 = 20처럼 각 인덱스 부분에 대해 상수를 사용하는 경우를 말합니다.
   • 단일 키 조회(Single Key Lookup)
     • 모든 조건이 상수인 경우, MySQL은 해당 인덱스에서 한 번의 키 조회만으로 최솟값(MIN()) 또는 최댓값(MAX())을 가져올 수 있습니다.
     • 즉, 데이터를 일일이 스캔하지 않고도 결과를 빠르게 반환할 수 있다는 뜻입니다.
   • 상수 대체(Constant Replacement)
     • 만약 쿼리에서 MIN() 또는 MAX()가 사용된 모든 표현식이 고정된 값(상수)으로 대체된다면, MySQL은 추가적인 계산 없이 즉시 결과를 반환합니다.
   • SELECT MIN(key_part2), MAX(key_part2) 
     FROM tbl_name 
     WHERE key_part1 = 10;
     • 인덱스 구조
       • INDEX (key_part1, key_part2)라는 인덱스가 있다고 가정합니다.
       • 여기서 key_part1과 key_part2는 다단계(다중 열) 인덱스입니다.
     • 쿼리 동작 방식:
       • WHERE key_part1 = 10 조건은 key_part1에 대해 상수를 제공합니다.
       • MySQL은 이 조건을 만족하는 key_part1 값의 범위에서 key_part2의 최솟값과 최댓값을 인덱스를 통해 바로 조회합니다.
     • 결과 최적화:
       • key_part1 = 10이 상수로 고정되었으므로, MySQL은 범위 검색 없이 인덱스를 한 번만 탐색하여 MIN(key_part2)와 MAX(key_part2) 값을 가져옵니다.
       • 이로 인해 쿼리는 매우 빠르게 실행됩니다
6. MySQL이 인덱스를 사용하여 테이블을 정렬(ORDER BY)하거나 그룹화(GROUP BY)하는 경우
   
   • 왼쪽 접두사(leftmost prefix):
     • MySQL에서 다중 열 인덱스(multi-column index)는 순서대로 열을 포함합니다.
     • 예를 들어, INDEX(key_part1, key_part2, key_part3)라는 인덱스가 있다면, 이 인덱스를 다음과 같이 사용할 수 있습니다:
       • (key_part1)
       • (key_part1, key_part2)
       • (key_part1, key_part2, key_part3)
     • 이런 부분집합을 왼쪽 접두사라고 부릅니다.
     • 단, key_part2나 key_part3만 독립적으로 사용하는 것은 인덱스를 사용할 수 없습니다.
   • 정렬(ORDER BY):
     • ORDER BY에서 인덱스를 사용할 수 있는 조건은, 정렬 기준이 왼쪽 접두사 순서와 동일해야 한다는 점입니다.
       • EX) INDEX(key_part1, key_part2) 일경우
         • ORDER BY key_part1, key_part2  -- Index 적용
         • ORDER BY key_part2, key_part1 -- 인덱스 활용 불가
   • 역순(DESC)
     • 인덱스를 활용해 정렬할 때, 모든 정렬 기준이 동일한 방향(ASC or DESC)이어야 합니다.
       • EX)
         • ORDER BY key_part1 DESC, key_part2 DESC -- 인덱스 활용
         • ORDER BY key_part1 ASC, key_part2 DESC -- 인덱스 활용 불가
     • 역순 최적화
       • 만약 DESC로 정렬하면, MySQL은 인덱스를 거꾸로 읽어서 정렬을 수행합니다.
       • INDEX(key_part1, key_part2)는 기본적으로 오름차순(ASC) 인덱스지만, DESC 정렬도 인덱스를 활용할 수 있습니다.
7. 커버링 인덱스
   • 쿼리에 필요한 모든 결과를 제공하는 인덱스를 커버링 인덱스(covering index)라고 합니다
   • 경우에 따라, 쿼리가 데이터 행을 조회하지 않고 값을 검색하도록 최적화될 수 있습니다.
   • 쿼리가 테이블에서 특정 인덱스에 포함된 열만 사용하는 경우, 선택된 값은 데이터 행 대신 인덱스 트리에서 더 빠르게 검색될 수 있습니다.
   • SELECT key_part3 FROM tbl_name WHERE key_part1 = 1;