인덱스(Index)

---

인덱스(Index)란 데이터베이스에서 데이터를 빠르게 검색하기 위한 데이터 구조이다. 이러한 인덱스에는 클러스터형 인덱스(Clustered Index)와 보조 인덱스(Secondary Index)가 존재한다.

클러스터형 인덱스(Clustered Index)는 특정 열을 기본 키로 지정하면 자동 생성되는 인덱스로, 테이블에 1개만 생성 가능하고 기본 키로 지정한 열을 기준으로 자동 정렬된다.

보조 인덱스(Secondary Index)는 특정 열을 고유 키로 지정하면 자동 생성되는 인덱스로, 클러스터형 인덱스와 다르게 여러개를 생성할 수 있으나 자동 정렬되지 않는다. 여기서 자동 정렬된다는 개념은 후에 알아보도록 하자.

그렇다면 인덱스는 왜 존재하는 걸까? 궁극적으로 인덱스를 만들면 SELECT문의 속도가 증가된다.

인덱스는 책의 찾아보기와 비슷한데, 실무에서 운영하는 테이블에서는 인덱스의 사용 여부에 따라 성능 차이가 발생한다. 하지만 성능 차이만 있을 뿐 데이터를 찾을 때 인덱스의 사용 여부에 따른 결과값에는 차이가 없다.

문제점

필요 없는 인덱스를 만들면 데이터베이스가 차지하는 공간이 증가하고, 인덱스를 이용해서 데이터를 찾는 것이 전체 테이블에서 데이터를 찾는 것보다 속도가 오히려 감소된다.

예를 들어 SQL 책 찾아보기에 SELECT를 만들면 SQL에는 수 없이 많은 SELECT가 만들어지므로 SELECT에 수많은 페이지 번호가 표기된다. 따라서 본문에 있는 SELECT를 찾아보는 과정에서 찾아보기 참조 → 본문 페이지 참조 → 찾아보기 참조 → 본문 페이지 참조와 같은 불필요한 추가 프로세스가 발생하는 것과 같다.

데이터베이스에서는 인덱스를 생성할때 인덱스를 사용하는 것이 빠른지 전체 테이블 검색이 빠른지 MySQL이 자동으로 판단한다. 인덱스를 사용하지 않는다면 인덱스를 생성하는 것이 공간 낭비이니 불필요한 인덱스 생성은 삼가하도록 하자.

인덱스의 장점과 단점

장점

• 적절한 인덱스의 생성과 사용은 아주 빠른 응답 속도를 기대할 수 있다.
• 빠른 처리 속도로 컴퓨터 자원에 여유가 생기고 더 많은 일을 수행할 수 있다. 따라서 전체 시스템 성능이 향상된다.
• SELECT문으로 검색하는 속도가 매우 빨라진다.

단점

• 인덱스가 공간을 차지하여 데이터베이스 내에 추가 공간 필요하게 된다. 인덱스는 테이블 크기의 10% 정도 추가 공간을 필요로 한다.
• 초기 인덱스 생성에 많은 시간이 걸린다.
• SELECT가 아닌 데이터의 변경 작업(INSERT, UPDATE, DELETE)가 자주 발생할 시 오히려 성능이 저하된다.

인덱스의 종류

인덱스의 종류에는 앞서 말했듯이 클러스터형 인덱스와 보조 인덱스가 있는데, 비유하자면 클러스터형 인덱스는 영어사전, 보조 인덱스는 책 뒤의 찾아보기가 있는 일반 책과 같다.

클러스터형 인덱스는 영어사전과 같이 책의 내용이 이미 알파벳 순서대로 정렬되어 있어서 별도의 찾아보기가 필요 없다.

보조 인덱스는 SQL 책과 같이 찾아보기가 별도로 있고, 찾아보기에서 해당 단어를 찾은 후 표시된 페이지를 펼쳐야 실제 내용 발견하는 것과 같다.

인덱스 알아보기

---

자동으로 생성되는 인덱스

인덱스는 테이블의 열 단위로 생성할 수 있다. 하나의 열에 여러 개의 인덱스, 또는 여러 개의 열에 하나의 인덱스를 생성할 수 있으나 일반적으로 하나의 열에 하나의 인덱스를 생성한다.

CREATE TABLE member
(
	mem_id CHAR(8) NOT NULL PRIMARY KEY,
	mem_name VARCHAR(10) NOT NULL,
	mem_number INT NOT NULL,
	....
)

mem_id를 기본키로 정의했으므로 mem_id 열에 클러스터형 인덱스가 생성된다. 기본 키로 지정하면 자동으로 해당 열에 클러스터형 인덱스가 생성된다.

기본 키는 테이블 당 하나만 지정할 수 있으므로, 클러스터형 인덱스는 테이블에 한 개만 생성 가능하다.

<실행>

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

SHOW INDEX FROM table1;

<결과>

SHOW INDEX FROM (테이블명)을 사용하면 해당 테이블의 인덱스 정보를 확인할 수 있다.

여기서 Key_name 열에서 PRIMARY라는 것을 확인할 수 있는데, 이는 기본 키로 설정했기에 ‘자동으로 생성된 인덱스’라는 의미이고 이것이 클러스터형 인덱스이다.

Column_name이 col1인데 이는 col1 열에 인덱스가 생성되었다는 것을 해당 인덱스가 생성되었다는 것을 의미한다.

Non_Unique는 고유하지 않다는 의미로 중복을 허용하냐는 의미인데 0은 False, 1은 True를 의미한다.

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

<실행>

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

SHOW INDEX FROM table2;

<결과>

고유 키(UNIQUE KEY)도 인덱스가 자동 생성되는데 이를 보조 인덱스라고 한다. 고유 키도 중복값 비허용이므로 Non_unique가 0으로 설정되어있다.

고유 키를 여러 개 지정할 수 있듯 보조 인덱스도 여러 개 생성할 수 있다.

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

어떤 열을 기본 키로 지정하면(=클러스터형 인덱스가 생성되면) 그 열을 기준으로 자동 정렬된다. 아까 클러스터형 인덱스를 영어사전에 비유했는데, 클러스터형 인덱스가 생성되면 실제 DB의 데이터들이 자동으로 정렬된다.

그렇다면 진짜 정렬이 되는지 확인해보도록 하자.

<실행>

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)
);

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;

<결과>

위 테이블 생성문에서 기본 키 지정하지 않고 데이터를 삽입했다. 그리고 데이터를 조회하니 입력한 순서대로 결과가 출력되는 것을 확인할 수 있다.

이번에는 테이블에 기본 키를 지정해보자.

<실행>

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

<결과>

기본 키를 지정할 시 해당 열에 클러스터형 인덱스 생성되고 해당 열을 기준으로 자동 정렬된다. 따라서 데이터를 조회했을 때 기본 키인 mem_id의 오름차순에 맞게 조회되는 것을 확인할 수 있다.

추가로 데이터를 입력할 시 자동으로 기준에 맞춰 정렬되고, mem_id를 기본 키로 지정했으므로 기본 키 기준으로 자동 정렬된다.

<실행>

ALTER TABLE member DROP PRIMARY KEY;
ALTER TABLE member ADD CONSTRAINT PRIMARY KEY(mem_name);
SELECT * FROM member;

<결과>

만약 기본 키를 변경하면 변경한 열에 맞춰서 정렬된다.

정렬되지 않는 보조 인덱스

고유 키로 지정할 시 보조 인덱스가 생성된다. 보조 인덱스는 자동 정렬이 되지 않는다.

<실행>

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, '서울');

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

<결과>

고유 키로 지정하지 않을 시에는 입력한 그대로 데이터가 출력되는데, 고유 키를 지정하여 보조 인덱스를 생성해도 데이터 순서가 변경되지 않는다. 보조 인덱스를 추가로 생성해도(고유 키를 추가로 지정해도) 데이터 순서가 변경되지 않는다.

보조 인덱스는 여러 개를 생성할 수 있으나 만들 때마다 데이터베이스 공간을 차지하고 및 전반적인 시스템에 부정적 영향을 끼칠 수 있는다.

출처

• 혼자 공부하는 SQL(우재남 저, 한빛미디어)