[MySQL] Index 인덱스란?

필요성

cpu나 메모리 등 장치의 성능은 매우 빠른 속도로 발전했지만 디스크 같은 기계식 장치의 성능은 제한적으로 발전했다. 발전해도 여전히 컴퓨터에서 가장 느린 부분이라는 사실에는 변함이 없다.

 

데이터베이스의 성능 튜닝은 어떻게 디스크 I/O를 줄이느냐가 관건이다.

 

랜덤 I/O 와 순차 I/O

랜덤 I/O는 디스크 드라이브의 플래터(원판)를 돌려 알맞은 위치로 헤더를 이동시킨 후 데이터를 읽는 것을 의미한다.

순차 I/O는 디스크 헤드 이동이 적어 여러 번 쓰기 또는 읽기 요청 시 작업 부하가 훨씬 적다.

 

인덱스 레인지 스캔은 주로 랜덤 I/O를, 풀 테이블 스캔은 순차 I/O를 사용한다. 

 

그래서, 큰 테이블의 대부분의 레코드를 읽는 작업에서는 오히려 인덱스를 사용하지 않고 풀 테이블 스캔을 유도할 때도 있다.

 

인덱스는 조회 성능은 높이지만, (INSERT, UPDATE, DELETE) 성능은 떨어진다. 

 

중복 허용 여부

Unique한 인덱스이냐, 유니크 하지 않냐는 큰 차이를 만든다. 옵티마이저는 유니크 인덱스에서 동등 조건(=)으로 검색하는 것은 항상 1건의 레코드만 찾으면 되므로 더 이상 탐색을 진행하지 않지만, 유니크 하지 않은 인덱스는 더 찾아야 하므로 처리 방식에 차이가 발생한다. 이를 선택도(기수성)이 떨어진다고 한다.

 

선택도가 좋지 않더라도 정렬이나 그루핑같은 작업을 위해 인덱스를 만드는게 훨씬 나은 경우도 많다. 

B-Tree

데이터베이스의 인덱싱 알고리즘 가운데 가장 일반적으로 사용된다.

b-tree 구조

 

리프 노드가 꽉 차면 상위 브랜치 노드까지 처리 범위가 넓어져 b-tree는 상대적으로 쓰기 작업에 비용이 많이 드는 것으로 알려져있다.

 

InnoDB 스토리지 엔진은 인덱스 키 추가 작업을 지연시켜 나중에 처리할 수 있다. 하지만 PK나 UNIQUE 인덱스의 경우 중복 체크가 필요해 즉시 B-TREE에 추가하거나 삭제한다.

 

B-TREE 깊이

인덱스의 B-TREE 깊이가 3인 경우를 가정한다. 키 값이 16바이트인 경우 최대 2억개를, 32바이트로 늘어나면 5천 만개만 담을 수 있다. 인덱스 키 값의 크기는 가능하면 작게 만드는 것이 좋다.

 

 

인덱스 스캔 방식

2024.11.12 - [데이터베이스] - DB 성능 최적화 방법

DB 성능 최적화 방법

 

전 글에 정리가 되어있다.

 

가용성과 효율성 판단

 

이 조건의 경우 B-TREE 인덱스 특성상 작업 범위 결정 조건으로 사용할 수 없다.