[MySQL] 인덱스란 무엇일까?

인덱스의 개념

개념

인덱스는 데이터를 빠르게 찾을 수 있도록 도와주는 도구로, 실무에서는 많은 양의 데이터를 다루기 때문에 인덱스 없이 데이터베이스 운영이 불가능합니다.

인덱스를 사용하면 SELECT문으로 검색하는 속도가 빨라지고, 컴퓨터의 부담이 줄어들어서 전체 시스템의 성능이 향상됩니다.

하지만 인덱스도 공간을 차지하여 데이터베이스안에 추가적인 공간이 필요합니다. 인덱스는 대략 테이블 크기의 10% 정도의 공간이 추가로 필요합니다.

또 필요 없는 인덱스를 많이 만들게 되면 데이터베이스가 차지하는 공간만 더 늘어날 뿐더러 인덱스를 이용해 데이터를 찾는 것이 전체 테이블을 찾아보는 것보다 느려집니다.

인덱스는 테이블의 열 단위로 생성됩니다.

CREATE TABLE table1 (
    col1 INT PRIMARY KEY,
       col2 INT,
      col3 CHAR(10)
);
SHOW INDEX FROM table;

종류

인덱스의 종류는 크게 두가지로 나뉩니다.

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

클러스터형 인덱스는 영어사전과 국어사전처럼 내용이 이미 정렬되어 있는 것입니다

Secondary index(보조 인덱스)

보조 인덱스는 책의 부록의 "찾아보기" 와 같은 것입니다.

	클러스터형 인덱스	보조 인덱스
영문	Clustered Index	Secondary Index
관련 제약조건	기본 키(primary key)	고유 키(Unique)
테이블 당 개수	1개	여러 개
정렬	지정한 열로 정렬됨	정렬되지 않음
비유	영어사전	일반 책의 찾아보기

인덱스의 내부 작동

원리

클러스터형 인덱스와 보조 인덱스는 모두 내부적으로 균형 트리로 만들어집니다.

균형트리는 자료 구조에 나오는 데이터의 구조입니다.

균형 트리의 개념

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

• 루트 노드: 노드의 가장 상위 노드
• 리프 노드: 제일 마지막에 있는 노드

MySQL에서는 노드를 페이지 라고 부릅니다. 페이지는 최소한의 저장 단위로 16kbyte의 크기를 가집니다

위의 그림에서 귤이라는 데이터를 검색한다고 할 때, 리프 페이지만 존재한다면 귤을 처음부터 검색해야 합니다

딸기부터 귤까지 8건의 데이터, 페이지 3개를 검색해야 결과를 알 수 있습니다.

하지만 균형 트리는 루트 페이지부터 검색합니다.

루트 페이지의 모든 데이터는 정렬되어 있고, 딸기와 포도 그리고 오렌지 데이터 3개를 읽은 후 세번째 리프 페이지로 직접 이동하여 데이터를 찾으면 됩니다. 데이터는 루트 페이지에서 3개, 리프 페이지에서 2개 총 5개의 데이터, 페이지는 2개를 검색하여 결과를 얻을 수 있습니다. 여기서 주의할 점은, 데이터의 건수는 크게 의미가 없고, 몇 개의 페이지를 읽었느냐로 효율성을 따진다는 것을 알아두어야 합니다.

하지만 인덱스를 구성하면 데이터를 변경할 시 성능이 나빠집니다. 왜랴하면 페이지 분할이라는 작업이 발생하기 때문입니다. 페이지 분할이란 새로운 페이지를 준비해서 데이터를 나누는 작업을 말합니다. 페이지 분할이 일어나면 MySQL이 느려지고, 너무 자주 일어나면 성능에 큰 영향을 줍니다.

설명을 위해 루트 페이지와 리프 페이지의 데이터를 가나다순으로 정렬하였습니다.

두번째 리프 페이지에 샤인머스캣 데이터가 입력되었습니다. 샤인머스캣 데이터가 삽입된 후 포도 데이터가 가나다순 정렬로 인하여 두번째 리프 페이지의 빈 공간으로 밀려났습니다.

두번째 리프 페이지에 사파이어 포도 데이터를 입력해보겠습니다.

사파이어 포도를 입력하고 보니 두번째 리프페이지에 데이터가 들어갈 공간이 없어, 샤인머스캣과 포도 데이터가 새로운 페이지로 나뉘어 졌고(가나다순 데이터 정렬), 루트 노드에 새로운 페이지가 등록되었습니다.

위와 같은 사진에서는 루트 노드에 데이터가 들어갈 수 있는 자리가 없어 두개의 루트 노드로 페이지 분할이 되었습니다. 분할된 루트 노드는 중간 노드가 됩니다.

결국 새로운 로트 노드가 생성됩니다. 세번째 리프 노드에 데이터를 입력해야하는 상황에서는 3개의 페이지를 읽어야 하기 때문에 SQL의 성능이 나빠지게 됩니다.

인덱스의 구조

클러스터형 인덱스

USE market_db;
CREATE TABLE cluster -- 클러스터형 인덱스를 테스트하기 위한 테이블
(    mem_id        CHAR(8) ,
    mem_name    VARCHAR(10)

);

INSERT INTO cluster VALUES('TWC', '트와이스');
INSERT INTO cluster VALUES('BLK', '블랙핑크');
INSERT INTO cluster VALUES('WMN', '여자친구');
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', '마마무');

실습을 위해 다음 코드를 입력하여 아래와 같은 테이블을 생성하겠습니다.

ALTER TABLE cluster
        ADD CONSTRAINT
               PRIMARY KEY (mem_id);

SELECT * FROM cluster;

mem_id열을 Primary key로 지정하여 mem_id를 기준으로 테이블이 오름차순 정렬되었습니다.

보조 인덱스

USE market_db;
CREATE TABLE second -- 보조 인덱스를 테스트하기 위한 테이블
(    mem_id        CHAR(8) ,
    mem_name    VARCHAR(10)

);

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

클러스터형 인덱스와 동일한 데이터가 들어간 테이블을 만들었습니다.

ALTER TABLE second
        ADD CONSTRAINT
               UNIQUE (mem_id);
SELECT * FROM second;

보조 인덱스가 생성되었는데도 입력한 것과 순서가 동일합니다.왜냐하면 보조 인덱스가 데이터 페이지를 건드리지 않고 별도의 장소에 인덱스 페이지를 생성했기 때문입니다.

인덱스의 실제 사용

생성과 제거

인덱스를 생성하기 위해서는 CREATE INDEX 문을 사용하고, 제거학 위해서는 DROP INDEX문을 사용합니다. 기본 형식은 다음과 같습니다.

- 인덱스 생성

CREATE [UNIQUE] INDEX 인덱스_이름
    ON 테이블_이름 (열_이름) [ASC | DESC]

- 인덱스 제거

DROP INDEX 인덱스_이름 ON 테이블_이름

- 예시

CREATE INDEX idx_member_addr
    ON member (addr);

주소에(addr)에 중복을 허용하는 단순 보조 인덱스를 생성했습니다.

DROP INDEX idx_member_addr ON member;

위에서 생성한 인덱스를 제거하였습니다.

미션

기본미션 : 310 인덱스 생성하고 key_name이 PRIMARY로 출력된 결과 화면 캡처하기

선택미션 : 인덱스 생성, 제거하는 기본 형식 작성하기

- 인덱스 생성

CREATE [UNIQUE] INDEX 인덱스_이름
    ON 테이블_이름 (열_이름) [ASC | DESC]

- 인덱스 제거

DROP INDEX 인덱스_이름 ON 테이블_이름

마무리

이번 챕터에서는 인덱스에 대해서 알아보았습니다.

이제 곧 혼공학습단 9기가 끝나가는데 아쉽군요 ㅠㅠ

마지막까지 열심히 해보겠습니다