[MySQL] 8장 인덱스

디스크 읽기 방식 

-> 랜덤 I/O, 순차 I/O

컴퓨터에서 CPU나 메모리 같은 주요 장치는 대부분 전자식 장치지만 하드 디스크 드라이브는 기계식 장치다. 

하드 디스크를 대체하기 위해서 SSD(solid state driver)가 출시되었다. 

SSD는 기존 하드 디스크에서 데이터 저장용 플래터(원판)를 제거하고 그 대신 플래시 메모리를 장착했다. 

 

하드 디스크의 경우 물리적인 원판을 돌려야 하기 때문에 랜덤 I/O에는 느리지만, 순차 I/O의 경우에는 상대적으로 빠르다. 

 

인덱스란?

인덱스의 비유

색인 -> 정렬된 키 기준으로 빠르게 데이터를 찾아갈 수 있다. 

자료구조 -> sortedList(인덱스, 저장되는 값을 항상 정렬된 상태로 유지), arrayList(데이터 파일, 저장되는 순서 그대로 유지) 

 

sortedList의 장단점을 통해 인덱스의 장단점을 살펴보자

값이 저장될 때마다 정렬되어야 해서 저장하는 과정이 복잡하고 느리지만 찾는 데는 빠르다. 

 

인덱스의 분류

역할로 분류 -> 프라이머리키, 보조키(세컨더리 인덱스)

데이터 자장 방식별로 분류 -> B-Tree(balanced) 인덱스, Hash 인덱스

중복 허용 여부로 분류 -> 유니크 인덱스, non-unique 인덱스

기능별로 분류 -> 전문 검색용 인덱스, 공간 검색용 인덱스.. 

 

B-Tree 인덱스 

인덱싱 알고리즘 가운데 가장 일반적이고 범용적인 목적으로 사용된다. 

B-Tree는 칼럼의 원래 값을 변형시키지 않고 인덱스 구조체 내에서는 항상 정렬된 상태를 유지한다. 

B-Tree는 트리 구조, 최상위에 하나의 루트 노드가 존재

가장 하위에 있는 노드는 리프노드, 중간의 노드를 브랜치 노드 

 

데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리된다.

인덱스의 리프노드는 항상 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있다 -> 인덱스키, PK

 

인덱스를 통해 데이터를 조회하는 과정

• 인덱스를 통해 PK를 찾음
• PK를 통해 레코드를 찾음

이러한 과정을 통해 데이터를 찾으므로 레코드 1건을 읽는 것이 테이블을 통해 직접 읽는것보다 4~5배 정도 비용이 더 많이 든다고 예측된다.

하지만 DBMS는 우리가 원하는 레코드가 어디있는지 모르므로 모든 테이블을 뒤져서 레코드를 찾아야한다.

테이블에 레코드가 많다면 엄청난 양의 디스크 읽기 작업이 발생한다. 

 

이러한 장단점으로 만약 읽어야 할 레코드의 건수가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 이용하지 않는 것이 효율적이라 판단하여 옵디마이저는 인덱스를 이용하지 않고 테이블 전체를 읽어 처리한다. 

 

페이지(Page)

인덱스는 페이지 단위로 저장된다

페이지란 디스크와 메모리(버퍼풀)에 데이터를 읽고 쓰는 최소 작업 단위이다. 

일반적인 인덱스를 포함해 PK와 테이블 등은 모두 페이지 단위로 관리된다.

따라서 만약 쿼리를 통해 1개의 레코드를 읽고 싶더라도 결국은 하나의 페이지을 읽어야 하는 것이다

 

그래서 페이지에 저장되는 개별 데이터의 크기를 최대한 작게 하여, 

1개의 페이지에 많은 데이터들을 저장할 수 있도록 하는 것이 상당히 중요하다. 

페이지에 저장되는 데이터의 크기가 클수록 다음과 같은 문제가 생길 수 있다.

• 디스크 I/O가 많아질 수 있음
• 메모리에 캐싱할 수 있는 페이지의 수가 줄어들 수 있음

 

인덱스 사용에 영향을 주는 요소

• PK의 크기
  • PK가 클수록 한 페이지에 담을 수 있는 인덱스 정보도 줄어들고, 메모리도 비효율적으로 사용되기 때문
  • 또한 트리의 깊이도 지나치게 깊어지면서 읽어야 하는 페이지가 많아져서 성능에 좋지 않다.
• 인덱스의 컬럼 순서
  • (PK를 포함하여) 인덱스는 여러 개의 컬럼으로 구성될 수 있는데, 이를 다중 컬럼 인덱스(Multi-column Index)라고 부른다.
  • 다중 컬럼 인덱스에서 중요한 것은 항상 다음 컬럼이 이전 컬럼에 의존하여 정렬된다는 것이다.
• 카디날리티(Cardinality)(유니크한정도)
  • 카디날리티란 특정 컬럼에 존재하는 데이터의 고유성을 의미한다.
  • 따라서 카디날리티가 높을수록 중복도가 낮아지며, 유니크한 값이 많다는 것이다.
  • 항상 그런 것은 아니지만 일반적으로 값이 유니크할수록 검색 대상이 줄어들어서 처리가 빠르다.
• 인덱스의 정렬 및 스캔 방향