인덱스

인덱스의 개념

**인덱스(Index)**는 SELECT로 테이블을 조회할 때 결과를 빠르게 추출하도록 도와주는 기능이다.

앞선 예시들에서 인덱스 없이도 문제가 없었던 건 데이터의 양이 적었기 때문이다. 데이터가 커지면 원하는 데이터를 빠르게 찾기 위해 인덱스가 반드시 필요하며, 현실적으로 인덱스 없이 데이터베이스를 운영하는 것은 불가능하다.

인덱스에는 클러스터형 인덱스와 보조 인덱스가 있다.

• 클러스터형 인덱스: 기본 키로 지정 시 자동 생성되며, 테이블에 1개만 만들 수 있다. 기본 키로 지정한 열을 기준으로 자동 정렬된다.
• 보조 인덱스(Secondary Index): 고유 키로 지정 시 자동 생성되며, 여러 개를 만들 수 있지만 자동 정렬되지는 않는다.

인덱스를 만들면 SELECT의 출력 속도가 빨라진다.

인덱스의 장단점

인덱스가 좋긴 하지만 남용도 문제가 된다. 필요 없는 인덱스를 만들면 데이터베이스가 차지하는 공간이 늘어나고, 인덱스로 데이터를 찾는 것이 전체 테이블을 찾는 것보다 오히려 느려질 수 있다.

인덱스는 SELECT에서 즉각적인 효과를 내는 빠른 방법 중 하나다. 적절한 인덱스를 생성한 뒤 인덱스를 활용하는 SQL을 작성하면 기존보다 아주 빠른 응답 속도를 얻을 수 있다.

인덱스의 장점

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

인덱스의 단점

• 인덱스도 공간을 차지하므로 데이터베이스 안에 추가 공간이 필요하다. (대략 테이블 크기의 10% 정도가 추가로 필요하다.)
• 처음 인덱스를 만드는 데 시간이 오래 걸릴 수 있다.
• SELECT가 아닌 데이터 변경 작업(CRUD)이 자주 일어나면 성능이 더 나빠질 수 있다.

인덱스를 잘 쓰면 SELECT 검색 속도가 빨라지지만, 잘못 사용하면 오히려 성능이 저하된다.

---

인덱스의 종류

MySQL의 인덱스는 앞서 말했듯 크게 클러스터형 인덱스와 **보조 인덱스(Secondary Index)**로 나뉜다.

클러스터형 인덱스는 영어 사전과 같고, 보조 인덱스는 책 뒤에 찾아보기가 있는 일반 사전과 같다.

---

자동으로 생성되는 인덱스

앞서 이야기한 인덱스의 개념과 장단점을 이해했다면 이제 테이블에 적용해보자.

인덱스는 테이블의 열(컬럼) 단위에 생성되며, 하나의 열에는 하나의 인덱스를 생성할 수 있다. 여러 열을 묶어 하나의 인덱스를 생성할 수도 있지만, 여기서는 하나의 열에 하나의 인덱스를 생성한다고 가정하자.

열 하나당 인덱스 하나를 생성한다면 이 테이블에는 우선 8개의 서로 다른 인덱스를 생성할 수 있다.

위에서 member 정의 시 회원 아이디(mem_id)를 기본 키로 정의했다. 이렇게 기본 키로 지정하면 자동으로 mem_id 열에 클러스터형 인덱스가 생성된다.

그런데 기본 키는 테이블에 하나만 생성할 수 있으므로, 결국 클러스터형 인덱스도 테이블에 한 개만 만들 수 있다.

CREATE TABLE table1  (
    col1  INT  PRIMARY KEY, -- 기본 키로 지정
    col2  INT,
    col3  INT
);

이제 SHOW INDEX 문으로 테이블의 인덱스를 확인해보자.

SHOW INDEX FROM table1;

결과를 살펴보면 다음과 같다.

• Key_name에 PRIMARY라고 써 있다. 이는 기본 키로 설정해서 자동 생성된 인덱스라는 의미이며, 이것이 바로 클러스터형 인덱스다. (Key_name에 PRIMARY라고 써 있으면 클러스터형 인덱스라고 생각하면 된다.)
• Column_name이 col1로 설정된 것은 col1 열에 인덱스가 만들어져 있다는 뜻이다.
• Non_Unique는 고유하지 않다, 즉 중복이 허용되는지를 나타낸다. 0이면 False, 1이면 True다.

결론적으로 이 인덱스는 Non_Unique가 0이므로 중복이 허용되지 않는 인덱스다.

고유 인덱스

고유 인덱스(Unique Index)는 인덱스의 값이 중복되지 않는다는 의미이고, 단순 인덱스는 인덱스의 값이 중복되어도 된다는 의미다. 기본 키(Primary Key)나 고유 키(Unique)로 지정하면 값이 중복되지 않으므로 고유 인덱스가 생성된다. 그 외의 인덱스는 단순 인덱스로 생성된다.

기본 키와 더불어 고유 키도 인덱스가 자동 생성되는데, 고유 키로 생성되는 인덱스는 보조 인덱스다.

CREATE TABLE table2  (
    col1  INT  PRIMARY KEY,
    col2  INT  UNIQUE,	-- 고유키로 지정
    col3  INT  UNIQUE	-- 고유키로 지정
);

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

USE market_db;
DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
( mem_id      CHAR(8) ,
  mem_name    VARCHAR(10),
  mem_number  INT ,  
  addr        CHAR(2)  
 );

클러스터형 인덱스를 적용해보자.

ALTER TABLE member DROP PRIMARY KEY ; -- 기본 키 제거
ALTER TABLE member
    ADD CONSTRAINT
    PRIMARY KEY(mem_name); -- 클러스터형 인덱스 생성
SELECT * FROM member;

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

기본 키 변경 시 주의할 점

• 이미 대용량의 데이터가 있는 상태에서 기본 키를 지정하면 정렬에 시간이 엄청 오래 걸릴 수 있다. 노트에 단어 4개를 정렬하는 건 금방이지만, 4만 개라면 매우 오래 걸리는 것과 같다.
• 위에서 회원 이름 열을 기본 키로 지정했는데, 이름은 중복될 수 있으므로 기본 키로 삼는 것은 위험하다.

---

정렬되지 않는 보조 인덱스

고유 키로 지정하면 보조 인덱스가 생성된다. 또한 고유 키를 테이블에 여러 개 설정할 수 있는 것처럼, 보조 인덱스도 테이블에 여러 개 설정할 수 있다.

보조 인덱스는 일반 책으로 비유하면 책 맨 뒤에 찾아보기가 추가되는 것이지, 본문이 변경되는 것은 아니다.

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

데이터를 추가로 입력하면 일반 책 본문의 제일 뒤에 추가되는 것과 유사하게 동작한다.

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

보조 인덱스는 여러 개를 만들 수 있다. 하지만 보조 인덱스를 만들 때마다 데이터베이스 공간을 차지하게 되고 전반적으로 시스템에 나쁜 영향을 미친다. 그러므로 필요한 열에만 적절히 보조 인덱스를 생성하는 것이 좋다. (보조 인덱스를 여러 개 생성할 수 있는 것은 고유 키를 테이블에 여러 개 지정할 수 있는 것과 마찬가지다.)

---

정리

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

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

---

인덱스 내부 작동

인덱스를 만들고 사용하는 법은 어렵지 않다. 하지만 작동 과정을 제대로 이해하지 못한 상태에서 사용하면 오히려 문제가 생길 수 있다.

클러스터형 인덱스와 보조 인덱스는 모두 내부적으로 균형 트리(Balanced Tree, B-Tree) 구조로 만들어진다. 균형 트리는 자료구조에서 범용적으로 쓰이는 데이터 구조다.

인덱스의 내부 작동 원리

인덱스의 내부 작동 원리를 이해하면, 인덱스를 사용해야 할 경우와 사용하지 말아야 할 경우를 판단하는 데 도움이 된다. 인덱스가 늘 좋은 것은 아니므로 정확한 판단이 중요하다.

균형 트리의 개념

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

• 루트 노드(root node): 노드 중 가장 상위 노드. 모든 출발은 루트 노드에서 시작된다.
• 리프 노드(leaf node): 제일 마지막에 존재하는 노드.
• 중간 노드: 루트 노드와 리프 노드 사이에 끼인 노드들.

노드라는 용어는 개념적 설명에서 주로 쓰이며, MySQL에서는 **페이지(page)**라고 부른다. 페이지는 최소한의 저장 단위로, 16KB(16,384byte) 크기를 지닌다.

예를 들어 데이터 1건만 입력해도 1개 페이지(16KB)가 필요하다. (페이지는 비어 있는 노트 1장으로 생각해도 된다. 노트에 단어 1개만 적고 싶어도 1장이 필요한 것처럼, 데이터 1건만 입력해도 1페이지가 필요하다.)

균형 트리는 데이터를 검색할 때(SELECT 구문을 사용할 때) 아주 뛰어난 성능을 발휘한다. 다음 그림에서 MMM이라는 데이터를 검색한다고 가정해보자. 만약 모두 리프 페이지만 있다면, MMM을 찾는 방법은 처음부터 차례로 검색하는 것밖에 없다.

이 경우 AAA에서 MMM까지 8건의 데이터(페이지 3개)를 검색해야 결과를 알 수 있다.

반면 균형 트리에서 검색하면 무조건 루트 페이지에서 시작한다. 모든 데이터는 정렬되어 있고, MMM은 AAA, FFF, LLL 중 LLL 다음 범위에 있으므로 세 번째 리프 페이지로 직접 이동하면 된다.

세 번째 리프 페이지에서 LLL, MMM 2개를 읽어 MMM을 찾는다.

결국 루트 페이지에서 AAA, FFF, LLL 3개와 리프 페이지에서 LLL, MMM 2개를 합쳐 총 5건의 데이터를 검색해서 원하는 결과를 찾았으며, 읽은 페이지는 2개다.

균형 트리가 아닌 구조에서는 3페이지를 읽었지만, 균형 트리는 2페이지만 읽어 결과를 얻을 수 있다.

지금은 중간 단계 페이지 없이 루트 페이지, 리프 페이지만 있는 2단계뿐이라 효용성이 크게 느껴지지 않을 수 있다. 하지만 실무에서 다루는 훨씬 많은 데이터(많은 단계)에서는 균형 트리 구성 여부에 따라 읽어야 하는 페이지 수의 차이가 상당히 커진다.

균형 트리의 페이지 분할

앞서 데이터 검색 시 균형 트리가 효과적인 것을 봤다. 인덱스는 균형 트리로 구성되어 있으므로, 인덱스를 만들면 SELECT 속도를 향상시킬 수 있다. 2페이지를 읽어 데이터를 찾는 것이 3페이지를 읽어 찾는 것보다 빠르기 때문이다.

그런데 인덱스를 구성하면 데이터 변경 작업(INSERT, UPDATE, DELETE) 시 성능이 나빠진다. 특히 INSERT 작업이 일어날 때 더 느리게 입력될 수 있다.

이유는 페이지 분할이라는 작업이 발생하기 때문이다. 페이지 분할이란 새로운 페이지를 준비해서 데이터를 나누는 작업이다. 페이지 분할이 일어나면 MySQL이 느려지고, 너무 자주 일어나면 성능에 큰 영향을 끼친다.

앞 그림에서 III 데이터가 새로 INSERT되었다고 가정하면 균형 트리는 다음과 같이 변경된다.

두 번째 리프 페이지에는 빈 공간이 있어 JJJ가 아래로 한 칸 이동하고 III가 그 자리에 삽입된다. 정렬되어야 하므로 JJJ가 한 칸 이동했을 뿐 큰 변화는 없다. 즉, III를 입력하는 작업은 순식간에 처리된다.

그렇다면 GGG를 입력하려면? 두 번째 리프 페이지는 더 이상 빈 공간이 없다. 이럴 때 페이지 분할 작업이 일어난다.

데이터를 1개밖에 추가하지 않았지만 많은 변화가 생긴다. 새 페이지를 확보한 뒤 페이지 분할 작업이 1회 일어나고, 루트 페이지에도 새로 등록된 페이지의 제일 위에 있는 데이터(III)가 등록된다.

이번에는 PPP와 QQQ를 연속해서 입력해보자. 먼저 PPP를 입력하면 네 번째 리프 페이지에 빈칸이 있으므로 제일 마지막에 추가되며 별다른 일이 일어나지 않는다.

이번엔 QQQ를 입력해보자. 그런데 네 번째 리프 페이지에는 빈칸이 없으므로 페이지 분할 작업이 일어난다.

페이지 분할 후 추가된 다섯 번째 리프 페이지를 루트 페이지에 등록하려고 하니, 루트 페이지도 이미 꽉 차서 등록할 곳이 없다.

그래서 루트 페이지도 다시 페이지 분할을 해야 한다. 원래 루트 페이지가 있던 곳은 2개의 페이지가 되어 더 이상 루트 페이지가 아니라 중간 페이지가 된다.

마지막으로 새 페이지를 준비해서 중간 노드를 가리키는 새로운 루트 페이지로 구성된다.

결국 QQQ 하나를 입력하기 위해 3개의 새로운 페이지가 할당되고 2회의 페이지 분할이 일어났다. 데이터 하나를 입력하기 위해 너무 많은 작업이 발생한 것이다. 즉, 입력 작업이 엄청 오래 걸린다.

이를 통해 인덱스를 구성하면 데이터 변경(특히 INSERT) 작업이 느려지는 것을 확인할 수 있다.

---

인덱스 구조

인덱스 구조를 통해 인덱스를 생성하면 왜 데이터가 정렬되는지, 어떤 인덱스가 더 효율적인지 살펴보자.

클러스터형 인덱스 구성하기

클러스터형 인덱스와 보조 인덱스의 구조가 어떻게 다른지 살펴보자. 우선 인덱스 없이 테이블을 생성하고 다음과 같이 데이터를 입력해보자.

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', '마마무');

SELECT * FROM cluster;

1 페이지에 4개 행이 입력된다고 가정하면 아래 그림과 같이 나온다.

여기서 표현한 데이터 페이지는 실제 데이터가 들어 있는 부분이다. 아직은 인덱스가 없는 상태이며, 각 페이지 위에 써진 숫자는 임의로 부여한 페이지 번호다.

정렬된 순서를 보면 입력된 순서와 동일하게 보인다.

이제 테이블의 mem_id에 클러스터형 인덱스를 구성해보자. 앞서 배웠듯 mem_id를 Primary Key로 지정하면 클러스터형 인덱스로 구성된다.

ALTER TABLE cluster
    ADD CONSTRAINT
    PRIMARY KEY (mem_id);

결과를 보면 mem_id를 기준으로 오름차순 정렬된다. 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', '마마무');

앞서 고유 키 제약조건이 보조 인덱스를 생성한다는 것을 확인했다. mem_id 열에 UNIQUE를 지정하고 데이터를 확인해보자.

ALTER TABLE second
    ADD CONSTRAINT
    UNIQUE (mem_id);

SELECT * FROM second;

보조 인덱스가 생성되었는데도 순서가 입력한 것과 동일하다. 내부적으로 보조 인덱스가 데이터 페이지를 건드리지 않았기 때문이다. 대신 그림 위쪽을 보면 별도의 장소에 인덱스 페이지를 생성한 것을 알 수 있다.

보조 인덱스는 일반 책과 같다. 찾아보기가 없던 일반 책에 찾아보기를 만들어도 본문이 바뀌지 않듯, 보조 인덱스를 생성해도 데이터 페이지는 변경되지 않는다. 대신 책 뒷부분 같은 별도의 공간에 만들어지는 것처럼, 보조 인덱스도 별도 공간에 만들어진다.

인덱스에서 데이터 검색하기

클러스터형 인덱스에서 데이터를 찾는 방법은 앞서 본 것과 동일하다.

---

**인덱스 검색(Index Scan)**을 통해 클러스터형 인덱스는 2페이지만 읽어 원하는 결과를 검색했고, 보조 인덱스는 3페이지를 읽어 검색했다. 두 인덱스 모두 검색이 빠르지만, 클러스터형 인덱스가 조금 더 빠르다.

---

정리

• 인덱스는 내부적으로 균형 트리, 즉 나무를 거꾸로 표현한 자료구조로 구성된다.
• 노드는 트리 구조에서 데이터가 저장되는 공간을 말하며, MySQL에서는 노드를 페이지라고 부른다.
• **전체 테이블 검색(Full Table Scan)**은 데이터를 처음부터 끝까지 검색하는 것이다. 인덱스가 없으면 전체 페이지를 검색하는 방법밖에 없다.
• 페이지 분할은 데이터를 입력할 때 입력할 페이지에 공간이 없어 2개 페이지로 데이터가 나뉘는 것이다.
• 인덱스 검색은 클러스터형 또는 보조 인덱스를 이용해 데이터를 검색하는 것이다. 인덱스를 사용하지 않았을 때보다 속도가 빠르다.

---

인덱스의 실제 사용

인덱스 생성

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

• CREATE INDEX로 생성되는 인덱스는 보조 인덱스다.
• UNIQUE는 중복이 안 되는 고유 인덱스를 만드는 옵션이며, 생략하면 중복이 허용된다. UNIQUE로 인덱스를 생성하려면 기존 입력값 중 중복이 없어야 한다.
• 정렬 기본값은 ASC(오름차순)다.

인덱스 제거

DROP INDEX로 인덱스를 제거한다.

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

주의할 점은, 기본 키나 고유 키로 자동 생성된 인덱스는 DROP INDEX로 제거할 수 없다는 것이다. 이 경우 ALTER TABLE 문으로 기본 키나 고유 키를 제거하면 자동으로 생성된 인덱스도 함께 제거된다.

---

인덱스를 효과적으로 사용하는 법

1. 인덱스는 열 단위에 생성된다.
2. WHERE 절에서 사용되는 열에 인덱스를 만들어야 한다.
3. WHERE 절에 사용되더라도 자주 사용해야 가치가 있다.
4. 데이터의 중복이 높은 열은 인덱스를 만들어도 별 효과가 없다.
5. 클러스터형 인덱스는 테이블당 하나만 생성할 수 있다.
6. 사용하지 않는 인덱스는 제거한다.

---

정리

• CREATE INDEX 문으로 인덱스를 직접 생성한다.
• 기본 키 및 고유 키로 자동 생성된 인덱스는 ALTER TABLE로 제거하고, CREATE INDEX 문으로 생성한 인덱스는 DROP INDEX 문으로 제거한다.
• 단순 보조 인덱스는 중복을 허용하는 보조 인덱스이며, CREATE INDEX 문을 사용한다.
• 고유 보조 인덱스는 중복을 허용하지 않는 보조 인덱스이며, CREATE UNIQUE INDEX 문을 사용한다.
• MySQL 워크벤치에서 SQL을 실행한 후, 실행 계획에서 인덱스 사용 여부를 알 수 있다.