6. 인덱스-1

0. 학습 목표

• 인덱스의 개념과 종류를 이해
• 인덱스의 작동 원리와 구조를 이해
• 실제로 인덱스를 만들고 사용하는 방법을 학습

0. 요약

• 클러스터형 인덱스는 영어사전처럼 내용이 이미 정렬되어 있는 인덱스, 기본 키로 지정하면 클러스터형 인덱스가 생성되고 자동으로 해당 열을 기준을 정렬됨
• 보조 인덱스는 일반 책의 찾아보기와 같이 별도의 공간에 인덱스가 생성, 고유 키로 지정하면 보조 인덱스가 생성되고 자동 정렬되지 않음
• 고유 인덱스는 앖이 중복되지 않는 인덱스, 기본 키나 고유 키로 지정하면 값이 중복되지 않아 자동으로 고유 인덱스가 생성

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

---

1. 인덱스의 개념을 파악하자

※핵심 키워드:  클러스터형 인덱스, 보조 인덱스, 고유 인덱스

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

• 클러스터형 인덱스: 기본키로 지정하면 자동 생성되며 테이블 당 1개만 만들 수 있음, 기본 키로 지정한 열을 기준으로 자동 정렬 됨
• 보조 인덱스: 고유 키로 지정하면 자동 생성되며 여러개를 만들 수 있지만 자동 정렬되지 않음

---

1-1. 인덱스의 개념

인덱스는 책 뒤의 색인과 비슷한 개념이다. 데이터의 양이 많이 질 수록 특정 데이터를 찾기 어려운 경우 색인, 인덱스를 활용하면 빠르게 찾을 수 있다.

---

인덱스의 문제점

인덱스가 있다고 해서 무조건 좋은 것은 아니다. 예를 들어 SELECT라는 단어를 찾는다고 생각하면 많은 인덱스가 포함되어 찾는데 더 오랜 시간이 걸릴 수 있다. 즉 인덱스는 필요한 곳에만 설정하는 것이 좋다.

하지만 MySQL의 경우 인덱스를 설정해 놓아도 인덱스를 사용해서 검색하는 것이 빠를지 아니면 전체 테이블을 검색하는 것이 빠를 지 MySQL이 판단한다.

---

인덱스의 장점과 단점

인덱스는 SELECT에서 즉각적인 효과를 내는 빠른 방법 중 한 가지 이다.

인덱스의 장점은 아래와 같다.

• SELECT 문으로 검색하는 속도가 매우 빨라진다.
• 컴퓨터의 부담이 줄어들어 전체 시스템의 성능이 향상된다.

반대로 인덱스의 단점은 아래와 같다.

• 인덱스도 공간을 차지해서 데이터베이스 안에 추가적인 공간이 필요하다.
• 처음에 인덱스를 만드는데 시간이 오래 걸릴 수 있다.

---

1-2. 인덱스의 종류

MySQL에서 사용되는 인덱스의 종류는 두 가지로 나뉜다.

• 클러스터 인덱스: 영어사전 처럼 책의 내용이 이미 알파벳 순서대로 정렬되어 있는 것
• 보조 인덱스: 일반 책 뒤에 찾아보기가 별도로 있음

---

자동으로 생성되는 인덱스

인덱스는 테이블의 열 단위에 생성되며 하나의 열에는 하나의 인덱스를 생성할 수 있다. member 테이블의 경우 열이 8개가 있기 때문에 8개의 서로 다른 인덱스를 생성할 수 있다.

member 테이블 정의 시 mem_id를 기본키로 정의했다. 이렇게 기본 키로 지정하면 자동으로 mem_id 열에 클러스터형 인덱스가 생성된다. 기본 키는 테이블에 하나만 지정 가능하므로클러스터형 인덱스는 테이블에 한 개만 만들 수 있다.

간단한 테이블을 만들고 첫 번째 열을 기본 키로 지정해 보자.

USE market_db;

DROP TABLE IF EXISTS table1;

CREATE TABLE table1 (
	col1 INT PRIMARY KEY,
    col2 INT,
    col3 INT
);

테이블의 인덱스를 확인해 보자. SHOW INDEX문을 사용하면 확인 가능하다.

SHOW INDEX FROM table1;

# Table, Non_unique, Key_name, Seq_in_index, Column_name, Collation, Cardinality, Sub_part, Packed, Null, Index_type, Comment, Index_comment, Visible, Expression
'table1', '0', 'PRIMARY', '1', 'col1', 'A', '0', NULL, NULL, '', 'BTREE', '', '', 'YES', NULL

중요 부분은 아래와 같다.

• Key_name: PRIMARY - 기본 키로 설정해서 자동으로 생성된 인덱스의 의미로 클러스터형 인덱스
• Column_name: col1 - col1 열에 인덱스가 만들어 졌다는 의미
• Non_Unique: 0 - 고유하지 않다는 의미로 0은 False, 1은 True로 고유하지 않다의 거짓이기 때문에 중복이 허용되지 않는 인덱스
  참고로 기본키, 고유키는 중복값을 허용하지 않으므로 고유 인덱스가 설정되고 그 외의 인덱스는 단순 인덱스가 설정된다.

기본 키와 더블어 고유 키도 인덱스가 자동으로 생성된다. 

CREATE TABLE table2 (
	col1 INT PRIMARY KEY,
    col2 INT UNIQUE,
    col3 INT UNIQUE
);

SHOW INDEX FROM table2;

# Table, Non_unique, Key_name, Seq_in_index, Column_name, Collation, Cardinality, Sub_part, Packed, Null, Index_type, Comment, Index_comment, Visible, Expression
'table2', '0', 'PRIMARY', '1', 'col1', 'A', '0', NULL, NULL, '', 'BTREE', '', '', 'YES', NULL
'table2', '0', 'col2', '1', 'col2', 'A', '0', NULL, NULL, 'YES', 'BTREE', '', '', 'YES', NULL
'table2', '0', 'col3', '1', 'col3', 'A', '0', NULL, NULL, 'YES', 'BTREE', '', '', 'YES', NULL

---

자동으로 정렬되는 클러스터형 인덱스

앞서 클러스터형 인덱스를 영어사전과 비교하였다. 즉 기본 키로 설정하면 자동으로 인덱스가 설정되어 정렬이 된다.

간단한 실습으로 확인해 보자. 우선 member 테이블을 만들어 보자.

USE market_db;

DROP TABLE IF EXISTS member, buy;

CREATE TABLE member (
	mem_id		CHAR(8),
    mem_name	VARCHAR(10),
    mem_number	INT,
    addr		CHAR(2)
);

INSERT INTO member VALUES('TWC', '트와이스', 9, '서울');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기');
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울');

SELECT * FROM member;

# mem_id, mem_name, mem_number, addr
'TWC', '트와이스', '9', '서울'
'BLK', '블랙핑크', '4', '경남'
'WMN', '여자친구', '6', '경기'
'OMY', '오마이걸', '7', '서울'

보는 봐와 같이 정렬되지 않은 데이로 확인된다.

ALTER TABLE을 통해 mem_id를 기본키로 설정해 보자.

ALTER TABLE member
	ADD CONSTRAINT
    PRIMARY KEY(mem_id);

SELECT * FROM member;

# mem_id, mem_name, mem_number, addr
'BLK', '블랙핑크', '4', '경남'
'OMY', '오마이걸', '7', '서울'
'TWC', '트와이스', '9', '서울'
'WMN', '여자친구', '6', '경기'

mem_id를 기준으로 자동 정렬 된 것을 확인할 수 있다.

이번에는 mem_name을 기본 키로 변경해 보자. 우선 ALTER 문을 통해 PRIMARY KEY를 삭제해야 한다.

ALTER TABLE member DROP PRIMARY KEY;

ALTER TABLE member
	ADD CONSTRAINT
    PRIMARY KEY(mem_name);

SELECT * FROM member;

# mem_id, mem_name, mem_number, addr
'BLK', '블랙핑크', '4', '경남'
'WMN', '여자친구', '6', '경기'
'OMY', '오마이걸', '7', '서울'
'TWC', '트와이스', '9', '서울'

mem_name을 기준으로 자동 정렬 되었다. 데이터를 추가해도 정렬된다. 

    
INSERT INTO member VALUES('GRL', '소녀시대', 8, '서울');

SELECT * FROM member;

# mem_id, mem_name, mem_number, addr
'BLK', '블랙핑크', '4', '경남'
'GRL', '소녀시대', '8', '서울'
'WMN', '여자친구', '6', '경기'
'OMY', '오마이걸', '7', '서울'
'TWC', '트와이스', '9', '서울'

주의 할점은 대용량의 데이터가 있는 상태에서 기본키를 지정하면 시간이 오래 걸릴 수 있다.

---

정렬되지 않는 보조 인덱스

고유 키로 지정하면 보조 인덱스가 생성된다. 

보조 인덱스는 일반 책의 찾아보기라고 비유하였다. 찾아보기를 만들더라도 실제 책의 순서는 변경되지 않는다. 즉 보조 인덱스가 설정되더라도 클러스터 인덱스처럼 정렬되지 않는다.

마찬가지로 예제를 통해 확인해 보자. 우선 회원 테이블의 열을 고유 키 없이 몇 개 만들어 보자.

USE market_db;

DROP TABLE IF EXISTS member;

CREATE TABLE member (
	mem_id		CHAR(8),
    mem_name	VARCHAR(10),
    mem_number	INT,
    addr		CHAR(2)
);

INSERT INTO member VALUES('TWC', '트와이스', 9, '서울');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기');
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울');

SELECT * FROM member;

# mem_id, mem_name, mem_number, addr
'TWC', '트와이스', '9', '서울'
'BLK', '블랙핑크', '4', '경남'
'WMN', '여자친구', '6', '경기'
'OMY', '오마이걸', '7', '서울'

입력한 순서대로 테이블에 데이터가 보인다. mem_id를 고유 키로 설정하고 확인하자.

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

# mem_id, mem_name, mem_number, addr
'TWC', '트와이스', '9', '서울'
'BLK', '블랙핑크', '4', '경남'
'WMN', '여자친구', '6', '경기'
'OMY', '오마이걸', '7', '서울'

정렬되지 않음이 확인된다. 고유 키는 여러개 설정해도 되므로 mem_name에 추가적으로 설정을 해보자. 마찬가지로 순서에는 영향을 미치지 않는다.

ALTER TABLE member
	ADD CONSTRAINT
    UNIQUE (mem_name);
    
SELECT * FROM member;

# mem_id, mem_name, mem_number, addr
'TWC', '트와이스', '9', '서울'
'BLK', '블랙핑크', '4', '경남'
'WMN', '여자친구', '6', '경기'
'OMY', '오마이걸', '7', '서울'

데이터를 추가해도 마찬 가지이다. 마지막에 추가된다.

INSERT INTO member VALUES('GRL', '소녀시대', 8, '서울');

SELECT * FROM member;

# mem_id, mem_name, mem_number, addr
'TWC', '트와이스', '9', '서울'
'BLK', '블랙핑크', '4', '경남'
'WMN', '여자친구', '6', '경기'
'OMY', '오마이걸', '7', '서울'
'GRL', '소녀시대', '8', '서울'

보조 인덱스는 여러개를 만들 수 있다. 보조 인덱스를 생성할 때 마다 데이터베이스의 공간을 차지하게 되고 전반적으로 시스템에 나쁜 영향을 미친다. 꼭 필요한 열에만 적절히 보조 인덱스를 설정하는 것이 중요하다.