[MySQL] 인덱스(index), 클러스터/보조인덱스

인덱스

데이터를 좀 더 빠르게 찾을 수 있도록 도와주는 도구이다.

목차를 생성하는 것과 같다고 볼 수 있다.

 

MySQL은 데이터 검색 시 첫번째 필드부터 전체를 검색한다.

필요없는 인덱스를 만들게 될 경우 차지하는 공간만 늘어나고, 인덱스를 이용한 조회의 속도가 전체 테이블 조회의 속도보다 느려진다.

 

 

인덱스의 장단점

 

장점

• 검색 속도의 향상쿼리의 부하가 줄어들며 시스템 전체 성능의 향상

 

단점

• 인덱스 저장할 공간이 필요하다.
• 처음 인덱스 생성시 많은 시간이 소요됨.
• 데이터 변경 작업 (삽입 수정 삭제)이 많이 일어나면 성능이 나빠질 수 있다.

 

 

MySQL 인덱스 종류

 

클러스터형 인덱스 (Clustered Index)

키 값에 대한 테이블의 데이터 행을 정렬하는 인덱스의 한 유형 테이블의 데이터를 정렬해서 저장되는 순서를 정의

1. 테이블당 하나만 생성 가능
2. 행 데이터를 인덱스로 지정한 열에 맞춰서 자동으로 정렬함
3. 영어 사전처럼 책의 내용 자체가 순서대로 정렬되어 있어 인덱스가 책의 내용과 같음

 

PRIMARY KEY에 의해 자동으로 생성된다.

UNIQUE NOT NULL에 의해 자동으로 생성된다.

PRIMARY KEY와 UNIQUE NOT NULL이 한 테이블에 함께 있을 경우 PRIMARY KEY를 클러스터형 인덱스로 사용한다.

 

데이터 검색 순서 : 루트페이지 > 리프페이지(데이터 페이지) 

리프 페이지가 모두 차있을 때 페이지 분할이 일어남.

 

 

 

보조 인덱스 (비클러스터형 인덱스 / Nonclustered Index)

데이터와 인덱스를 각각 다른 위치에 저장 인덱스는 해당 데이터의 위치에 대한 포인터를 포함하고 있다.

1. 테이블당 여러 개 생성 가능
2. 찾아보기에서 먼저 단어를 찾은 후 그 옆에 표시된 페이지로 이동하여 원하는 내용을 찾는 것과
   같은 개념

UNIQUE, UNIQUE NULL에 의해 자동 생성

 

별도의 인덱스가 생성되지만, 원본데이터의 변경은 없다.

 

데이터 검색 순서 : 루트페이지 > 리프페이지 > 데이터 페이지 (Heap page)

Heap : 정렬 기준 없는 테이블, 입력된 순서대로 저장된다. 

 

리프페이지가 데이터페이지를 가리키는 포인터를 포함한다.

리프페이지가 모두 차 있어도 페이지 분할은 일어나지 않는다.

 

정리

• PRIMARY KEY로 지정한 열에 클러스터형 인덱스가 생성
• UNIQUE NOT NULL로 지정한 열에 클러스터형 인덱스가 생성
• UNIQUE 또는 UNIQUE NULL로 지정한 열에 보조 인덱스가 생성
• PRIMARY KEY와 UNIQUE NOT NULL이 같이 있으면 PRIMARY KEY로 지정한 열에 우선 클러스터형 인덱스가 생성
• PRIMARY KEY로 지정한 열을 기준으로 데이터가 오름차순 정렬

 

 

인덱스 생성

 

보조 인덱스 : CREATE INDEX 문
>> CREATE INDEX 문으로는 클러스터형 인덱스를 만들 수 없다,

 

클러스터형 인덱스 : ALTER TABLE 문을 이용

ALTER TABLE clusterTBL ADD CONSTRAINT PK_clusterTBL_userID PRIMARY KEY (userID);

 

• CREATE INDEX 문의 UNIQUE 옵션은 고유한 인덱스를 만들 때 사용
• UNIQUE로 설정된 인덱스에는 동일한 데이터 값이 입력될 수 없음
• ASC, DESC는 정렬 방식을 지정하는데, ASC가 기본 값이고 오름차순으로 정렬된 인덱스가 생성
• index_type은 생략 가능하며, 생략할 경우 기본 값인 B - Tree 형식을 사용

 

-- 보조 인덱스

 

보조 인덱스 생성

CREATE UNIQUE INDEX index_name ON table_name (index_column);

 

UNIQUE  : 고유 설정을 했기 때문에 동일한 데이터 값 입력 불가능

index_name : index 자체 이름 설정

table_name : 어떤 테이블에 인덱스 설정할 건지

index_column : 어떤 열에 보조 인덱스 생성할 건지

 

 

테이블에 생성된 인덱스 정보 확인하기 

SHOW INDEX FROM userTBL;

 

 

두개의 열을 조합해 인덱스 생성하기

CREATE INDEX idx_userTBL_userName_birthYear ON userTBL (name, birthYear);

 

 

실행계획을 통한 인덱스 사용 확인

SELECT * FROM userTBL WHERE name= ''신동엽" and birthYear = '1971';

위에 쿼리에서 조합한 인덱스 두개 (name, birthYear) 를 조건문에 넣고 실행,

실행 계획을 확인해보면 아래와 같이 인덱스 (idx_userTBL_userName_birthYear) 에서 가져온 것을 확인할 수 있다.

 

 

------------------------------------------------------------------------------------------------------------------------

원래 인덱스를 사용하지 않은 상태의 쿼리 실행 계획을 보면

Full Table Scan 에서 가져온다.

-----------------------------------------------------------------------------------------------------------------------

 

 

인덱스 삭제

DROP INDEX index_name ON table_name;

 

 

 

인덱스 생성의 판단 기준

인덱스는 열 단위에 생성 인덱스는 WHERE 절에서 사용되는 열에 생성 WHERE 절에 사용되는 열이라도 자주 사용해야 가치가 있음 데이터 중복도가 높은 열에는 인덱스를 만들어도 효과가 없음 (선택도가 높은 경우는 효과 없음) 외래키를 설정한 열에는 자동으로 외래키 인덱스가 생성됨 조인에 자주 사용되는 열에는 인덱스를 생성하는 것이 좋음 데이터 변경(삽입, 수정, 삭제) 작업이 얼마나 자주 일어나는지 고려해야 함 단일 테이블에 인덱스가 많으면 속도가 느려질 수있다. (테이블당 4~5개 권장) 클러스터형 인덱스는 테이블당 하나만 생성할 수 있음 테이블에 클러스터형 인덱스가 아예 없는 것이 좋은 경우도 있음 사용하지 않는 인덱스는 제거