[Lecture Note] MySQL 09. Index

이것이 MySQL이다 강의를 참고하여 정리한 글임을 밝힙니다.

인덱스 구조

B-Tree(Balanced Tree, 균형 트리)

구조

노드들은 기본적으로 가질 수 있는 데이터 크기가 16Kbyte로 정해져 있습니다.

크게 루트 노드와 리프 노드(페이지)로 구성되어 있으며

순서대로 리프 노드에 데이터들이 나눠서 들어가게 됩니다.

그리고 각 리프 노드의 첫번째 데이터들이 루트노드에 기록됩니다.

검색 방식

루트노드에서 시작해 하나씩 찾아가 효율적으로 찾게 되는 방식을 가지게 됩니다.

단점

효율적으로 찾을 수 있는 반면 INSERT UPDATE DELETE처럼 데이터에 변동이 있을 때 성능이 나빠질 수 있습니다.

정해진 노도의 용량을 넘지 않으면 속도에 문제가 없지만

정해진 노드의 용량을 넘을 경우 분할을 하는 방식으로 진행되기 때문입니다.

작동 방식

다음과 같이 Index를 설정할 수 있습니다.

1. 이렇게 우선 테이블을 설정합니다.

CREATE TABLE indextbl (first_name varchar(14), last_name varchar(16), hire_date date);
INSERT INTO indextbl	SELECT first_name, last_name, hire_date	FROM employees.employees	LIMIT 500;

1. 단순히 SELECT를 하는 것과 Index를 이용하는 것은 속도상 차이가 생기게 됩니다.

SELECT * FROM indextbl WHERE first_name = 'Mary';

이렇게 코딩할 경우 Mary를 전체 데이터 집합을 다 돌아다니며 찾게 됩니다.

CREATE INDEX idx_indextbl_firstname ON indextbl(first_name);

이렇게 할 경우 “idx_indextbl_firstname“이란 이름으로 first_name을 index로 만들게 되며 SELECT시 향상된 속도를 확인할 수 있습니다.

인덱스 종류

인덱스의 종류는 크게 다음과 같습니다.

• 클러스터형 인덱스
• 보조형 인덱스

Clusterred Index

• 영어사전처럼 정리되는 방식으로 생각하면 됩니다.

• 특히 테이블 당 한개만 생성가능합니다.

• 또한 행 데이터를 인덱스로 지정한 열에 맞춰서 자동 정렬됩니다.

• PK가 선언된 경우 자동으로 클러스터형 인덱스가 생성됩니다.
• 즉, PK이거나 UNIQUE NOT NULL인 열

• PK와 UNIQUE NOT NULL이 같이 있으면 PK를 우선

• 오름차순 정렬

이렇게 클러스터형 인덱스만 사용하게 된다면 인덱스의 페이지 내에서 분할이 이뤄지게 되고

이 때 인덱스 페이지 내 리프 페이지와 데이터 페이지는 동일하게 됩니다. 그래서 영어사전처럼 정리된 것과 데이터가 동일하게 된 것입니다.

Secondary Index

• 흔히 책 맨뒤에 확인할 수 있는 인덱스로 생각하면 됩니다.

• 테이블당 여러 개를 만들 수 있습니다.

• UNIQUE로 지정하면 자동으로 보조 인덱스가 설정이 되어서 여러개가 생성되는 것입니다.

• UNIQUE 혹은 UNIQUE NULL인 열

• 보조 인덱스를 잡아도 정렬은 클러스터형 인덱스에 따르니 순서는 바뀌지 않습니다.

보조 인덱스가 추가가 된다면 클러스터형 인덱스와 달리 리프 페이지와 데이터 페이지는 달라지게 됩니다.

즉, 정렬되지 않은 상태로 데이터 페이지는 두고, 인덱스 페이지의 리프 페이지에서는 별도의 페이지를 구성하게 되는 것입니다.

데이터 페이지는 바뀌지 않기 때문에 SELECT를 실행해도 순서가 바뀌지 않게 되는 것입니다.

검색 방법 비교

• 클러스터 인덱스

(단일 검색) 인덱스 페이지 내 루트 페이지에서 시작해서 리프 페이지까지 찾으면 데이터 페이지와 리프 페이지는 동일하기 때문에 검색이 끝이 나게 됩니다. (범위 검색) 되어 있기 때문에 범위로 검색해도 금방 찾게 됩니다.

• 보조 인덱스

(단일 검색) 인덱스 페이지 내 루트 페이지에서 시작해서 리프 페이지까지 찾으면 해당하는 데이터 페이지를 또 찾아가서 검색이 끝이 나게 됩니다. (범위 검색) 리프 페이지에서 위치를 찾고 다시 데이터 페이지를 찾아가게 됩니다.

그 결과 클러스터 인덱스가 비교적 보조 인덱스보다 조금 더 빠르게 됩니다.

데이터 입력 비교

• 클러스터 인덱스

페이지 분할이 일어날 수 있습니다.

• 보조 인덱스

데이터 페이지에서 정렬이 필요없으니 그냥 넣어주게 되고 인덱스 페이지 내 리프 페이지에 추가만 하면 됩니다. 물론 페이지가 다 차면 분할하긴 해야 합니다.

그 결과 보조 인덱스가 페이지 분할이 적게 일어납니다.

종합하자면 클러스터형 인덱스는

1. 계속 정렬하게 되어서 큰 데이터에선 시스템에 부하를 줄 수 있습니다.
2. 검색 GOOD vs 입력/수정/삭제 BAD
3. 테이블당 하나로 있기 때문에 PK가 없는 경우 어떤 열을 사용할지가 중요해진다.
4. 리프 페이지와 데이터 페이지가 동일하게 생각

종합하자면 보조 인덱스는

1. 리프 페이지와 데이터 페이지가 구분되어 있습니다.
2. 리프 페이지는 단순히 데이터 주소값(RID)만 가지고 있는 방식
3. 여러개 생성 가능

두 인덱스가 혼합된 경우

클러스터형 인덱스를 만들고 보조 인덱스를 추가하게 된다면

1. 우선 클러스터형 인덱스로 인해 데이터 페이지에서 정렬이 일어나게 됩니다.
2. 보조 인덱스가 추가 되면서 인덱스 페이지가 구분이 됩니다.
   • 클러스터 인덱스 페이지와 보조 인덱스 페이지
3. 보조 인덱스 페이지지를 거치고 클러스터 인덱스 페이지로 들어가는 구조가 됩니다.
   • 이 때 보조 인덱스 페이지의 루트 페이지가 데이터 페이지의 주소를 가리키고 있는 것이 아니라 PK(클러스터 인덱스 페이지 값)을 가지고 있습니다.

이렇게 된 경우 검색 방법은 다음과 같습니다.

1. 보조 인덱스 페이지의 루트 페이지-> 리프 페이지를 치게 되고 PK값을 가지게 됩니다.
2. PK값을 가지고 다시 클러스터 인덱스 페이지의 루트 페이지로 가서 해당하는 리프 페이지이자 곧 데이터 페이지를 찾습니다.

이렇게 구성하는 이유는 페이지 데이터를 정렬하는 것이 클러스터 인덱스에 따라서 진행되기 때문에 페이지 분할이 일어나게 된다면 보조 인덱스에서 바로 데이터 페이지로 포인팅하게 된다면 전체 주소가 바뀌는 문제가 될 수 있기 때문에 보조 인덱스를 거치고 클러스터 인덱스를 거쳐 가는 방식을 취하게 됩니다.

특히 이렇게 찾는 건 WHERE 문에서 해당 열이 사용될 때 효과를 발휘합니다.

인덱스 사용

• 인덱스 생성(단일열)

CREATE (UNIQUE | FULLTEXT | SPATIAL) INDEX index_name ON table_name(col_reference) ...

여기서 UNIQUE 인덱스는 동일한 데이터 값의 입력을 금지하게 됩니다. 기본 값은 중복이 허락되는 인덱스입니다.

생성한 이후에는 적용해야 하며 구문은 다음과 같습니다

ANALYZE TABLE usertbl;

만약 UNIQUE 인덱스를 생성했다면 추후에 값을 입력할 때에 중복되지 않아야 에러가 발생하지 않게 됩니다.

• 인덱스 생성(복수열)

여러개의 열을 인덱스로 사용가능하게 되고 이 때 인덱스 정보를 확인하게 되면 seq_in_index 값에 1,2로 구분됩니다.

• 인데스 제거

DROP INDEX index_name ON table_name ...

클러스터 인덱스의 이름은 PRIMARY로 되어 있어 이걸 사용해도 되고 ALTER TABLE 문으로 PK를 삭제하여 지울 수 있습니다.

특히 다 삭제할 때엔 보조 인덱스 먼저 삭제하여 데이터 정렬이 흐트려져 보조 인덱스 재구성을 굳이 하지 않게 합니다.

인덱스의 의미, 성능

1. 인덱스는 열 단위에 생성됩니다.
2. WHERE 절에 자주 사용되어야 의미있습니다.
3. 데이터의 중복도가 높으면 인덱스의 효과가 적습니다.
4. 외래키를 지정한 열에는 자동으로 외래 키 인덱스가 생성됩니다.
5. JOIN에 사용되는 열에 인덱스를 생성해주는 것이 좋습니다.
6. INSERT / UPDATE / DELETE가 자주일어나면 지양합니다.
7. PK는 필수가 아닙니다.

기타

• 테이블의 인덱스 확인 방법

SHOW INDEX FROM table_name;

• 페이지의 크기 확인 방법

SHOW VARIABLES LIKE "innodb_page_size";

• 인덱스 크기 확인 방법

  SHOW TABLE STATUS LIKE table_name;