mysql - 인덱스에 대하여

인덱스란? 데이터를 빠르게 찾을 수 있도록 도와주는 도구이다.

 

• 클러스터형 인덱스 : 기본 키로 지정하면 클러스터형 인덱스가 생성되고 해당 열로 자동 정렬된다.        

• 보조 인덱스 : 고유 키로 지정하면 보조 인덱스가 생성되고 자동 정렬되지 않는다.

 

---

 

인덱스 생성

create [UNIAUE] index 인덱스_이름 on 테이블_이름(열_이름)

 

인덱스 제거

drop index  인덱스_이름 on 테이블_이름

---

 

인덱스의 내부 구조 : 인덱스는 균형 트리의 자료 구조로 구성되어있다.

균형 트리 구조에서 데이터가 저장되는 공간을 노드라고 한다.

MySQL에서 노드는 페이지라고 부릅니다.

 

 

 

use market_db;
create table cluster
(mem_id char(8),
mem_name varchar(10));


insert into cluster values('TWC','트와이스');
insert into cluser values('BLK','블랙핑크');
INSERT INTO CLUSTER VALUES('OMY','오마이걸');
INSERT INTO CLUSTER VALUES('GRL','소녀시대');
INSERT INTO CLUSTER VALUES('ITZ','잇지');
INSERT INTO CLUSTER VALUES('RED','레드벨벳');
INSERT INTO CLUSTER VALUES('APN','에이핑크');
INSERT INTO CLUSTER VALUES('SPC','우주소녀');
INSERT INTO CLUSTER VALUES('MMU','마마무');
INSERT INTO CLUSTER VALUES('WMN','여자친구');

 

 

현재 index를 따로 지정해주지 않았기 때문에 균형 트리 구조를 나타내지 않고 페이지들만 있는 상태입니다.

select * from cluster;

이 상태에서 select 구문을 사용하면 아래 그림과 같이 Full Table Scan을 한다.

select * from cluster where mem_id = 'twc';

 

 

 

•  클러스터형 인덱스

alter table cluster add constraint primary key(mem_id);

mem_id열을 기본 키(primary key)로 설정한다면(index 지정) 아래의 그림과 같은 구조를 나타낸다.

균형 트리

 

select * from cluster;

 

select * from cluster where mem_id = 'twc';

 

=> 기본 키를 부여한 열을 기준으로 select 문을 사용하였을 때가 기본 키를 부여하지 않았을 때보다 빠르다는 것을 확인할 수 있다.

 

 

그러나 인덱스를 구성하면 페이지 분할 작업때문에 데이터 변경 작업(insert, update, delete)시 성능이 나빠진다. 

 

페이지 분할 작업이란? 

새로운 페이지를 준비해서 데이터를 나누는 작업이다.

 

성능이 나빠지는 원인은 균형트리의 구조 때문이다. 균형트리는 루트 페이지, 중간 페이지, 리프 페이지로 나뉜다.

이는 데이터 하나를 삽입으로 인해 새로운 페이지 1개가 아니라 2개이상이 생겨날 수 있기 때문이다.

 

따라서, 인덱스를 사용할 때 어떻게 사용해야 더 효율적으로 사용할 수 있을 지 생각하며 인덱스를 생성하여야한다.

 

 

 

• 보조 인덱스

alter table cluster1 add constraint unique(mem_id);

 

 

 

보조 인덱스를 구성하면 인덱스가 별도의 공간에 만들어진다. 일반 책의 찾아보기가 책 뒤에 별도로 만들어지는 것과 같은 개념이다.

 

select * from cluster1 where mem_id = 'twc';

 

마찬가지로, 보조 인덱스를 사용하였을 때가 인덱스를 사용하지 않았을 때보다 빠릅니다.

 

=> 두 인덱스 검색이 모두 빠르지만, 클러스터형 인덱스가 조금 더 빠릅니다.

 

 

보조 인덱스는 고유 보조 인덱스와 단순 보조 인덱스로 나뉜다.

1. 고유 보조 인덱스 : 중복된 데이터를 허용하지 않는 열
2. 단순 보조 인덱스 : 중복된 데이터를 허용하는 열

 

create index idx_membeer_addr on member (addr);

단순 보조 인덱스 생성

Non_unique가 1로 설정 => 고유 보조 인덱스 x

 

 

고유 보조 인덱스 생성

 

create unique index idx_member_mem_number on member (mem_number);

 

하지만, 인원수열에는 중복되는 값이 있기 때문에 고유 보조 인덱스를 생성하지 못한다.

 

따라서, 중복되는 데이터의 여부에 따라서 어떤 인덱스를 생성할 지 판단해야한다.

 

 

 

• 인덱스를 효과적으로 만드는 법

where 절에서 사용되는 열에 인덱스를 만들어라

데이터 중복이 높은 열은 인덱스를 만들어도 별로 효과가 없다.

사용하지 않는 index는 제거한다.

 

 

 

이상입니다!