[MySQL] 인덱스(Index): 종류, 설정 방법

 

데이터베이스 성능을 최적화하려면 '인덱스'를 이해하는 것이 중요하다고 한다.

수업시간에 짧게 다뤘는데, 잘 이해가 안가서 다시 정리하는 글~ 

 

 

1. 인덱스란 ?

인덱스는 말 그대로 데이터베이스의 '목차'라고 할 수 있다.

데이터가 저장된 물리적 위치를 알려주고, 데이터를 검색할 때 효율적으로 접근할 수 있게 도와주는 것.

 

2. 예시 테이블: employees

직원 정보를 관리하는 employees라는 테이블을 설정하고, 이를 바탕으로 인덱스 종류를 살펴볼 예정.

CREATE TABLE employees (
    employee_id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    department_id INT,
    PRIMARY KEY (employee_id)
);

• employee_id: 각 직원의 고유한 ID
• first_name, last_name: 직원의 이름
• email: 직원의 이메일 주소 (유니크)
• department_id: 직원이 소속된 부서

 

3. MySQL 인덱스의 종류

3.1 Clustered Index (클러스터형 인덱스)

• 테이블의 데이터가 물리적으로 정렬된 순서를 결정하는 인덱스
• 하나의 테이블에는 하나의 클러스터 인덱스만 설정 가능
• Primary Key가 설정되면 MySQL은 자동으로 해당 컬럼에 대해 클러스터 인덱스를 생성

PRIMARY KEY (employee_id)

- employee_id 컬럼이 Primary Key로 설정되어 있으며, 이 컬럼은 클러스터 인덱스로 사용된다.

 

 

3.2 Secondary Index (보조 인덱스)

• 검색 성능을 높이기 위한 추가적인 인덱스로, 테이블의 물리적 정렬에 영향을 미치지 않음
• 여러 개의 보조 인덱스 설정 가능
• 클러스터 인덱스가 아닌 다른 컬럼에 대해 인덱스를 설정할 때 사용

CREATE INDEX idx_department_id ON employees (department_id);

부서 ID(department_id)로 직원 데이터를 자주 검색해야 한다면, department_id 컬럼에 보조인덱스를 추가하는 것이 좋다.

인덱스를 설정하면 부서별로 직원을 검색할 때 성능을 개선할 수 있다.

 

예를 들어, 부서 ID가 5인 직원을 찾는 쿼리는 아래와 같이 작성되는데, 이 경우 보조 인덱스를 사용해 검색 성능이 향상된다.

SELECT * FROM employees WHERE department_id = 5;

 

 

3.3 Unique Index

• 보조 인덱스(Secondary Index)로 사용: 여러 개 설정 가능
• 중복되지 않는 값을 보장, NULL 값은 허용

CREATE UNIQUE INDEX idx_email_unique ON employees (email);

- email 컬럼은 Unique Index로 설정되어, 중복된 이메일이 테이블에 저장되지 않도록 한다.

 

 

3.4 Fulltext Index (전체 텍스트 인덱스)

• 긴 텍스트 필드에서 단어 기반으로 빠르게 검색할 수 있게 해주는 인덱스
• 주로 문서나 긴 텍스트 데이터를 다룰 때 사용

CREATE FULLTEXT INDEX idx_fulltext_name ON employees (first_name, last_name);

- 직원 이름을 검색할 때 first_name과 last_name 필드에 Fulltext Index를 설정할 수 있다.

 

 

4. 인덱스 설정 및 확인하는 방법

4.1 인덱스 생성

CREATE INDEX idx_column_name ON table_name (column_name);
CREATE INDEX idx_department_id ON employees (department_id);

 

4.2 인덱스 확인

SHOW INDEX FROM employees;

 

4.3 인덱스 삭제

DROP INDEX idx_department_id ON employees;

 

5. 인덱스의 장점

• 검색 성능 향상: 필요한 데이터를 빠르게 검색할 수 있도록 도와준다.
• 정렬 속도 개선: 데이터 정렬 시 성능이 향상된다.
• 성능 최적화: 대규모 데이터베이스에서는 성능 최적화에 중요한 역할을 한다.

 

6. 인덱스를 사용할 때 주의할 점

• 쓰기 성능 저하: 데이터를 삽입하거나 수정할 때 성능이 저하된다.
• 디스크 공간 사용: 인덱스는 추가적인 저장 공간이 필요하므로, 불필요한 인덱스는 성능 저하를 유발한다.
• 과도한 인덱스 설정 자제: 꼭 필요한 컬럼에만 설정해야 한다.

 

7. 성능 향상 확인

EXPLAIN 명령어를 통해 쿼리가 인덱스를 사용하고 있는지, 어느 정도 성능 향상을 기대할 수 있는지 확인할 수 있다.

EXPLAIN SELECT * FROM employees WHERE department_id = 5;