[MySQL]MySQL 벼락치기(3)

[MySQL]MySQL 벼락치기(3) - 인덱스(2)

강씨아저씨 2018. 8. 20. 09:36

이번 포스팅은 사내에서 MySQL 관련 내용 발표를 위해 Real MySQL(http://wikibook.co.kr/real-mysql/) 서적을 기반으로 학습하고 이해한 내용을 정리하는 포스팅이다. 포스팅에서는 주로 InnoDB 스토리지 엔진을 기준으로 설명할 예정이다.

MySQL 역시 내용이 많기 때문에 시리즈로 나눠서 정리할 예정이다.

해시 인덱스

해시 인덱스는 동등 비교 검색에는 최적화돼 있지만 범위를 검색한다거나 정렬된 결과를 가져오는 목적으로는 사용 할 수 없다.
해시 인덱스는 주로 메모리 기반의 테이블에서 사용되고 InnoDB 스토리지 엔진에서는 해시 인덱스를 지원하지 않지만 기본적인 특성은 알아보자.

구조 및 특성

해시 인덱스의 장점으로는 실제 키값과는 관계없이 인덱스 크기가 작고 검색이 빠르고 원래의 키값을 저장하는 것이 아니라 해시 함수의 결과만을 저장하므로 키 컬럼의 값이 아무리 길어도 실제 해시 인덱스에 저장되는 값은 4~8바이트 수준으로 상당히 줄어든다.

해시 인덱스에서 가장 중요한것은 해시 함수로 입력된 키 값이 어디에 저장될 지를 결정하는 함수이다.
* 하지만 이 해시 인덱스에서 사용할 해시 함수에는 어떤 종류가 있고 어떻게 설정하는지 찾지못했다... ㅜ.ㅜ
해시 함수의 결과 값의 범위가 넓으면 그만큼 버켓이 많이 필요해져서 공간낭비가 커지고 값의 범위가 너무 작으면 충돌되는 경우가 너무 많이 발생해서 인덱스의 장점이 사라진다.

가용성 및 효율성

작업범위제한조건으로 해시 인덱스를 사용하는 쿼리

SELECT .. FROM tb_hash WHERE column = '검색어';
SELECT .. FROM tb_hash WHERE column <=> '검색어';
SELECT .. FROM tb_hash WHERE column IN ('검색어', '검색어1');
SELECT .. FROM tb_hash WHERE column IS NULL;
SELECT .. FROM tb_hash WHERE column IS NOT NULL;

해시 인덱스를 전혀 사용하지 못하는 쿼리

SELECT .. FROM tb_hash WHERE column >= '검색어';
SELECT .. FROM tb_hash WHERE column BETWEEN 100 AND 120;
SELECT .. FROM tb_hash WHERE column LIKE '검색어%';
SELECT .. FROM tb_hash WHERE column <> '검색어';

크다 또는 작다 기반의 검색은 어떠한 방법으로도 해시 인덱스를 사용할 수 없다.

클러스터링 인덱스

클러스터링 인덱스는 테이블의 프라이머리 키에 대해서만 적용되는 내용이다. 즉 프라이머리 키값이 비슷한 레코드끼리 묶어서 저장하는것을 클러스터링 인덱스라고 표현한다. 여기서 중요한것은 프라이머리 키값에 의해 레코드의 저장위치가 결정된다는 것이다. 또한 프라이머리 키 값이 변경된다면 그 레코드의 물리적인 저장위치가 바뀌어야 한다는것을 의미한다.

클러스터링 인덱스는 프라이머리 키값에 의해 레코드의 저장 위치가 결정되므로 사실 인덱스 알고리즘이라기보다 테이블 레코드의 저장 방식이라고 볼 수 있다. 그래서 클러스터링 인덱스와 클러스터 테이블은 동의어로 사용되기도 한다.

클러스터링 테이블 구조 자체는 B-Tree 와 많이 비슷하게 닮아있지만 클러스터링 인덱스의 리프노드에는 레코드의 모든 컬럼이 같이 저장돼 있다. 즉 클러스터링 테이블은 그 자체가 하나의 거대한 인덱스 구조로관리되는 것이다.

만약 PK를 변경하는 문장이 실행되면 클러스터 테이블의 데이터 레코드에는 어떤 변화가 일어날까?

UPDATE tb_test SET emp_no = 10002 WHERE emp_no = 10007

위와 같은 쿼리가 실행될경우 3번 페이지에 있던 10007인 레코드가 10002로 변경되면서 2번 페이지로 이동한것을 알 수 있다.

프라이머리 키가 없는 InnoDB 테이블은 어떻게 클러스터 테이블로 구성될까? 프라이머리 키가 없는 경우에는 InnoDB 스토리지 엔진이 다음의 우선순위대로 프라이머리키를 대체할 컬럼을 선택한다.

프라이머리 키가 있으면 기본적으로 프라이머리 키를 클러스터 키로 선택
NOT NULL 옵션의 유니크 인덱스 중에서 첫번째 인덱스를 클러스터 키로 선택
자동으로 유니크한 값을 가지도록 증가되는 컬럼을 내부적으로 추가한 후, 클러스터 키로 선택

InnoDB 스토리지 엔진이 적절한 클러스터 키 후보를 찾지 못해서 내부적으로 자동 증가 컬럼을 추가한 경우, 자동 추가된 컬럼은 사용자에게 노출되지 않으며 쿼리 문장에 명시적으로 사용할 수 없다.
클러스터링 인덱스는 테이블별로 단 한번만 가질수 있는 엄청난 혜택이므로 가능하다면 프라이머리키를 명시하자.

클러스터링 인덱스의 장단점

장점

프라이머리 키로 검색할때 처리 성능이 매우 빠름
테이블의 모든 보조 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음

단점

테이블의 모든 보조 인덱스가 프라이머리 키를 가지고 있기 때문에 클러스터 키값의 크기가 클 경우 전체적으로 인덱스의 크기가 커짐
보조 인덱스를 통해 검색할 때 프라이머리 키로 다시한번 검색해야하므로 처리 성능이 조금 느림
INSERT 할때 프라이머리 키에 의해 레코드의 저장 위치가 결정되기 때문에 처리 성능이 느림.
프라이머리 키를 변경할 때 레코드를 DELETE 하고 INSERT 하는 작업이 필요하기 때문에 처리 성능이 느림.

클러스터 테이블 사용시 주의사항

클러스터 인덱스 키의크기

5개의 보조 인덱스를 가지는 테이블의 프라이머리 키가 10바이트인 경우와 50바이트 인경우를 한번 비교해 보자.

레코드 한건 한건을 생각하면 50바이트 쯤이야 대수롭지 않지만 레코드가 100만건만 돼도 인덱스의 크기가 거의 190Mb 나 증가했다. 10000만건이 되면 1.9GB가 증가한다!

오늘은 여기까지~

누군가에게 도움이 되었길 바라면서 오늘의 포스팅 끝~