Sieun Kim

안녕하세요 :)

3 분 소요

인덱스

인덱스 개념

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

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

인덱스의 개념

인덱스의 문제점

필요 없는 인덱스를 만들면 데이터베이스가 차지하는 공간만 더 늘어나고, 인덱스를 이용해서 데이터를 찾는 것이 전체 테이블을 찾아보는 것보다 느려진다.

똑똑한 MySQL
데이터베이스에 인덱스를 생성해 놓아도, 인덱스를 사용해서 검색하는 것이 빠를지 아니면 전체 테이블을 검색하는 것이 빠를지 MySQL이 알아서 판단한다.
만약 인덱스를 사용하지 않는다면 쓸데없이 공간을 낭비한 셈이다.

인덱스의 장점과 단점

인덱스의 장점은 다음과 같다.

  • SELECT 문으로 검색하는 속도가 매우 빨라진다.

  • 그 결과 컴퓨터의 부담이 줄어들어서 결국 전체 시스템의 성능이 향상된다.

인덱스의 단점은 다음과 같다.

  • 인덱스도 공간을 차지해서 데이터베이스 안에 추가적인 공간이 필요하다.

  • 처음에 인덱스를 만드는 데 시간이 오래 걸릴 수 있다.

  • SELECT가 아닌 데이터의 변경 작업(INSERT, UPDATE, DELETE)이 자주 일어나면 오히려 성능이 나빠질 수도 있다.

인덱스의 종류

MySQL에서 사용되는 인덱스의 종류는 크게 두 가지로 나뉘는데, 클러스터형 인덱스(Clustered Index)와 보조 인덱스(Secondary Index)이다.
이 두 개를 쉽게 비교하면 클러스터형 인덱스는 영어사전과 같고, 보조 인덱스는 책의 뒤에 찾아보기가 있는 일반적인 책과 같다.

보조 인덱스는 찾아보기가 별도로 있고, 찾아보기에 해당 단어를 찾은 후에 옆에 표시된 페이지를 펼쳐야 실제 찾는 내용이 있는 것을 말한다.
클러스터형 인덱스는 영어사전처럼 책의 내용이 이미 알파벳 순서대로 정렬되어 있는 것으로 별도의 찾아보기가 없다.

자동으로 생성되는 인덱스

인덱스는 테이블의 열(칼럼) 단위에 생성되며, 하나의 열에는 하나의 인덱스를 생성할 수 있다.

market_db의 회원 테이블(member)을 예로 인덱스를 살펴보겠다.

스크린샷 2024-06-03 121859

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

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

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

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

이제 테이블의 인덱스를 확인해보겠다.
SHOW INDEX 문을 사용하면 인덱스 정보가 확인된다.

SHOW INDEX FROM table1;

스크린샷 2024-06-03 122605

먼저 Key_name 부분을 보면 PRIMARY라고 써 있다.
이것은 기본 키로 설정해서 ‘자동으로 생성된 인덱스’라는 의미로 클러스터형 인덱스이다.
Column_name이 col1로 설정되어 있다는 것은 col1 열에 인덱스가 만들어져 있다는 말이다.
마지막으로 Non_Unique는 ‘고유하지 않다’라는 뜻으로 중복이 허용되냐는 의미이다.
Non_Unique가 0이라는 것은 False, 1은 True의 의미이다.
결론적으로 이 인덱스는 중복이 허용되지 않는 인덱스이다.

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

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

두 번째와 세 번째 열을 UNIQUE로 지정하여 간단히 확인해보겠다.

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

스크린샷 2024-06-03 152636

이번에는 Key_name에 col2, col3이라고 열 이름이 써 있다.
이렇게 Key_name에 열 이름이 써 있는 것은 보조 인덱스라고 보면 된다.
고유 키 역시 중복값을 허용하지 않기 때문에 Non_unique가 0으로 되어 있다.
또 고유 키를 여러 개 지정할 수 있듯이 보조 인덱스도 여러 개 만들 수 있다.

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

어떤 열을 기본 키로 지정하면(클러스터형 인덱스가 생성되면) 그 열을 기준으로 자동 정렬된다.

실습을 통해 확인해보겠다.

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;

스크린샷 2024-06-03 154053

이제 mem_id 열을 기본 키로 설정하고 내용을 확인해보겠다.

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

스크린샷 2024-06-03 154258

아이디 기준으로 정렬 순서가 바뀐 것을 확인할 수 있다.
mem_id 열을 기본 키로 지정했으므로 mem_id 열에 클러스터형 인덱스가 생성되어 mem_id 열을 기준으로 정렬되었다.

이번에는 mem_id 열의 Primary Key를 제거하고, mem_name 열을 Primary Key로 지정해보겠다.

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

스크린샷 2024-06-03 154654

결과를 보면 데이터의 내용 변경을 없으나, mem_name 열을 기준으로 다시 정렬되었다.
mem_name 열에 클러스터형 인덱스가 생성되었기 때문이다.

지금부터는 추가로 데이터를 입력하면 알아서 기준에 맞춰 정렬된다.

정렬되지 않는 보조 인덱스

열을 고유 키로 설정해도 데이터의 순서에는 변화가 없다.
즉, 보조 인덱스를 생성해도 데이터의 순서는 변경되지 않고 별도로 인덱스를 만드는 것이다.

데이터를 추가로 입력하면 제일 마지막에 추가된다.

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

참고