[Real Mysql] 인덱스

디스크 읽기 방식

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

모든 저장매채들은 내부적으로 1개 이상의 디스크 드라이브를 장착하고 있는데,

대부분 디스크 드라이브의 플래터(저장용 원판)을 회전시켜 데이터를 읽고 쓰는 방식을 사용한다.

디스크에 데이터를 쓰고 읽을때 걸리는 시간은 디스크 헤더를 움직여서 읽고 쓸 위치로 옮기는 단계에서 결정된다.

즉, 디스크 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 기록하냐에 결정된다.

 

 

인덱스란?

DBMS 에서 인덱스는 데이터의 저장 성능을 희생하고 대신 데이터의 읽기 성능을 높이는 기능이다.

인덱스를 역할별로 구분해보면 PK 와 Secondary Key로 구분 할 수 있다.

 

- PK : 레코드를 대표하는 컬럼으로 만들어진 인덱스

- Secondary Key: PK 를 제외한 나머지 모든 인덱스

 

DBMS 의 인덱스에 널리 사용되는 알고리즘은 다음과 같다.

 

- B-Tree 알고리즘: 컬럼 값을 변경하지 않고 원래 값을 이용해 인덱싱하는 알고리즘

- Hash 인덱스: 컬럼의 값으로 해시 값을 계산해서 인덱싱하는 알고리즘. 매우 빠른 검색 지원

- Fractal-Tree 알고리즘: B-Tree 보완 알고리즘

 

데이터 중복 허용 여부로 분류하면 유니크 인덱스와 유니크 하지 않은 인덱스로 구분 할 수 있다.

유니크 인덱스로 검색하는 건 항상 1건의 레코드만 찾으면 된다고 옵티마이저에게 알려주는 효과를 준다.

 

B-Tree 인덱스란?

Balanced-Tree 를 뜻한다.

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

 

 

B-Tree 구조 및 특성

 

트리구조로 되어있다.

루트 노드 -> 브랜치 노드 -> 리프 노드 -> 데이터 파일 순으로 되어있다.

DB에서 인덱스와 실데 데이터는 따로 관리 되고 있는데, 인덱스의 리프노드는 데이터 파일 주소값을 가지고 있다.

InnoDB 에서는 PK 값으로 클러스터링 되기 때문에 PK 값이 레코드 주소 역할을 한다.

 

B-Tree 인덱스 키 추가 및 삭제

 

인덱스 키 추가

 

적절한 위치를 찾아 리프 노드에 저장을 하게 되는데,

리프노드가 꽉차게 되면 분리를 시켜야 하는데 이건 상위 브랜치 노드까지 영향을 끼쳐서

상대적으로 쓰기 작업에 비용이 많이 들게 된다.

 

 

인덱스 추가로 INSERT 나 UPDATE 할때 어떤 영향을 받을까?

레코드를 추가하는 작업 비용이 1 이라면,

해당 테이블의 인덱스에 키를 추가하는 작업을 보통 1~1.5 정도로 예측한다.

테이블에 인덱스가 3개라면 5.5 정도의 비용을 예측한다.

이 비용의 대부분이 디스크로부터 인덱스를 읽고 쓰는 작업이라 시간이 오래 걸리게 된다.

 

InnoDB에서는 적절하게 인덱스 키 추가 작업을 지연시켜 나중에 처리할지 바로 처리할지 결정하게 된다.

 

1. 사용자 쿼리 실행

2. InnoDB 버퍼 풀에 해당 위치에 맞는 리프노드가 있다면 키 추가 작업 처리

3. 버퍼 풀에 리프노드가 없다면 인서트 버퍼에 키 값과 레코드 주소를 임시로 기록해 두고 사용자 쿼리 실행 완료

4. 백그라운드 작업은 인덱스 페이지를 읽을 때마다 인서트 버퍼에 머지할 인덱스 키값이 있는지 확인하고,

있다면 병합

5. db 자원 여유가 생기면 인서트 버퍼 머지 스레드가 조금씩 인서트 버퍼에서 값을 가져와 인덱스에 머지 시킨다 

 

 

인덱스 키 삭제

 

B-Tree 리프 노드를 찾아서 삭제하기만 하면 된다.

삭제된 키 공간은 방치하거나 재활용 할 수 있다.

 

인덱스 키 변경

인덱스 키 값만 변경은 불가능하다. 먼저 키값을 삭제하고 다시 새로운 키값을 추가하는 형태로 처리

 

인덱스 키 검색

검색 작업은 트리 검색 과정을 통해 아주 빠른 검색이 가능해진다.

 

여기서 주의해야할건 검색 값에 변경을 가하면 인덱스를 이용할 수 없다는것.

함수나 연산을 한 후 값을 검색하면 인덱스를 타지 않는다.

 

 

B-Tree 인덱스를 통한 읽어내기

 

MySQL 이 어떻게 인덱스를 이용해서 실제 레코드를 읽어 내는지 알고 있어야 한다.

인덱스를 이용하는 대표적인 방법 3가지를 봐보자

 

인덱스 레인지 스캔

 

인덱스 레인지 스캔은 가장 대표적인 접근 방식이다.

 

검색 할 인덱스의 범위가 있을때 사용하는 방식이다.

루트 노드에서부터 시작해서 리프노드 까지 찾아 들어가 원하는 시작 지점을 찾을 수 있다.

시작할 리프노드 위치를 찾으면 그 후 부터는 레코드 순서대로 읽으면 된다.

 

리프 노드 끝에 도착하면 링크를 타서 다음 리프 노드를 찾아서 다시 스캔하는 방식

 

- 리프 노드에서 데이터 파일을 읽어올때는 랜덤 I/O 가 발생해서 비용이 발생 (데이터의 20~25%가 넘는 인덱스를 읽을땐 오히려 인덱스 말고 테이블에서 읽는게 빠르다)

 

 

인덱스 풀 스캔

 

인덱스의 처음부터 끝까지 모두 읽는 방식을 인덱스 풀 스캔이라고 한다.

테이블 풀 스캔보다는 효율적이다.

 

루스 인덱스 스캔

 

중간마다 필요치 않은 인덱스 키 값은 무시하고 다음으로 넘어가는 형태로 처리한다.