'이것이 MySQL이다' 라는 책을 보며 내용을 정리한 글입니다.

이 전 포스팅에서 Index의 개념과 종류, 내부 구조에 대해서 알아보았다. 이제 실제 사용해보는 방법을 학습해보자.

인덱스 생성 / 제거

클러스터형 인덱스 생성

기본적으로 Primary Key를 지정하게 되면 클러스터형 인덱스로 생성이 된다.

단순 보조 인덱스 생성(중복 허용)

단순 보조 인덱스는 중복을 허용하는 인덱스를 의미한다. 쉽게 UNIQUE INDEX의 반대라고 생각하면 된다.

CREATE INDEX 생성할 인덱스 이름
ON 테이블명 테이블의 col_name

Unique 보조 인덱스 생성

CREATE UNIQUE INDEX 생성할 인덱스 이름
ON 테이블명 테이블col_name;

생성 할 field에 중복 되는 값이 있는 경우 Error Code: 1062. Duplicate entry ~~와 같은 에러가 발생하기 때문에 해당 에러가 발생 한 경우 중복되는 레코드가 있는 상태에서 Unique index를 생성하려고 하진 않았는지 확인하도록 하자.

실제 서비스에서는 이름에 unique index를 생성 할 경우 동명이인이 존재 할 경우에 문제가 발생하기 때문에 충분히 고려하고 꼭 중복이 발생하지 않아야 하는 field에만 unique index를 생성하도록 하자.

이름에선 Unique 한 속성을 줄 수 없으니 이름과 생년월일까지 묶어서 Unique하게 index를 생성해주는 해결 방법도 있다. 이와 같이 두개의 field를 묶어서 unique index를 생성하고자 하는 경우는 다음과 같이 하면 된다.

CREATE INDEX 생성할인덱스명
ON 테이블명 (name,birthYear);

INDEX 제거

DROP INDEX 삭제할idx명 ON 테이블명;

index 확인

인덱스를 생성하거나 제거했다면 제대로 작동 되었는지 확인하는 절차가 필요하다. 이 명령을 실행하기 위해선 다음의 명령어를 입력해주자.

SHOW INDEX FROM 테이블명;

자동 생성 된 클러스터형 인덱스 삭제

Primary Key로 인해 자동으로 생성 된 클러스터형 인덱스의 경우에는 기존과 같은 DROP INDEX문으로 삭제되지 않고, ALTER TABLE문으로만 삭제가 가능하다.

ALTER TABLE 테이블명 DROP PRIMARY KEY;

위의 명령어를 실행했을 때, Error Code: 1025. Error on rename of ~~~~~ Foreign key constraint is incorrectly formed)와 같은 에러가 발생했다면, 다른 테이블에서 참조하고 있기 때문에 삭제를 하지 못하는 경우이다. 그러므로 먼저 참조하고 있는 외래 키 관계를 제거 한 후에 다시 Primary Key를 제거해야한다.

ALTER TABLE 참조테이블명 DROP FOREIGN KEY 참조된테이블의열;
ALTER TABLE 인덱스가진테이블명 DROP PRIMARY KEY;

인덱스의 크기 확인

SHOW TABLE STATUS LIKE '검색할 테이블명';

정리

인덱스의 개념과 사용 방법은 사실 그렇게 어렵지 않다. 중요한건 설계 단계에서 어느 부분에 인덱스를 어떻게 사용할 것인지에 대한설계가 정말 중요하다.

성능을 높이기 위해 사용 한 인덱스가 자칫 성능의 악화로 이어질 수 있기 때문이다. 그렇기 때문에 인덱스를 사용하는 경우와 그렇지 않은 경우에 대해서 알고 있어야 한다.

아래의 주제는 어디까지나 고려해야 할 사항이지 절대적으로 이러한 경우에는 인덱스를 사용해야 한다는 것은 절대 아님을 유의하자.

1. 인덱스는 열 단위에 생성된다.
2. WHERE절에서 사용되는 열에 인덱스를 만들어야 한다
3. WHERE절에 사용되더라도 자주 사용해야 가치가 있다
4. 데이터의 중복도가 높은 열은 인덱스를 만들어도 별 효과가 없다
5. Foreign Key를 지정한 열에는 자동으로 Foreign Key인덱스가 생성된다