[MySQL] 인덱스

 

인덱스

인덱스(Index)는 MySQL 등의 데이터베이스 관리 시스템에서 데이터 검색 속도를 향상시키기 위해 사용하는 데이터 구조입니다. 인덱스를 이용하면 데이터베이스 엔진은 테이블 전체를 검색(풀 테이블 스캔)하지 않고, 빠르게 데이터를 찾을 수 있습니다.

 

인덱스가 필요한 이유

• 데이터 검색 속도 향상: 큰 데이터 집합에서 특정 레코드를 빠르게 찾아낼 수 있습니다.
• 정렬된 데이터 접근 향상: 인덱스는 정렬된 순서로 데이터를 저장하여 정렬된 데이터에 빠르게 접근할 수 있게 해줍니다.
• 집계 연산 최적화: GROUP BY나 DISTINCT 같은 집계 연산의 성능을 개선할 수 있습니다.

 

기본 인덱스 타입과 속성

• B-TREE 인덱스: 가장 일반적인 인덱스 타입으로, 범위 검색과 정확한 값을 찾는 검색에 유용합니다.
• HASH 인덱스: 메모리 기반의 테이블에서 주로 사용되며, 정확한 값에 대한 빠른 접근을 제공합니다. 순차적인 데이터 접근에는 적합하지 않습니다.
• FULLTEXT 인덱스: 텍스트 기반의 검색, 예를 들어 검색 엔진에서 사용되는 인덱스 타입입니다.
• SPATIAL 인덱스: 공간 데이터 처리를 위해 사용되며, 주로 GIS(지리 정보 시스템) 데이터에 사용됩니다.

 

인덱스 생성하기

 

CREATE INDEX 명령어 소개

CREATE INDEX 명령어를 사용하여 테이블의 특정 컬럼에 인덱스를 생성할 수 있습니다. 기본 형식은 다음과 같습니다:

CREATE INDEX 인덱스_이름 ON 테이블_이름 (컬럼_이름);

 

단일 컬럼 인덱스 생성

단일 컬럼에 인덱스를 생성하는 예는 다음과 같습니다:

CREATE INDEX idx_name ON users (name);

 

복합 컬럼 인덱스 생성

복수의 컬럼에 대해 인덱스를 생성하는 방법입니다. 예를 들어, name과 email 컬럼에 대한 인덱스는 다음과 같이 생성합니다:

CREATE INDEX idx_name_email ON users (name, email);

 

UNIQUE 인덱스와 PRIMARY KEY 인덱스

• UNIQUE 인덱스: 컬럼의 모든 값이 유일해야 함을 보장합니다. 중복된 값을 입력하려고 하면 오류가 발생합니다.

CREATE UNIQUE INDEX idx_email ON users (email);

• PRIMARY KEY 인덱스: 각 행의 고유한 식별자 역할을 합니다. 자동으로 UNIQUE 인덱스를 가지며, 테이블 당 하나만 설정할 수 있습니다.

ALTER TABLE users ADD PRIMARY KEY (id);

 

인덱스 관리와 최적화

 

인덱스 활용 전략

• 적절한 컬럼 선택: 자주 조회되고, WHERE 절 등에서 사용되는 컬럼에 인덱스를 생성합니다.
• 복합 인덱스 활용: 여러 컬럼을 함께 자주 조회하는 경우, 복합 인덱스를 고려합니다.

 

인덱스의 영향 분석 및 모니터링

• EXPLAIN 명령어 사용: SQL 쿼리의 실행 계획을 분석하고, 인덱스 사용 여부를 확인할 수 있습니다.
• 성능 모니터링: 인덱스 생성 후, 실제 쿼리 성능이 개선되었는지 모니터링합니다.

 

인덱스 조정과 삭제

• 인덱스 삭제: 더 이상 사용하지 않는 인덱스는 삭제하여 관리 오버헤드를 줄일 수 있습니다.

DROP INDEX idx_name ON users;

• 인덱스 조정: 쿼리 패턴 변경에 따라 인덱스를 추가하거나 조정합니다.

 

인덱스 사용 시 주의사항

 

인덱스 오버헤드

인덱스는 검색 속도를 향상시키지만, 추가적인 저장 공간을 사용하며, 데이터 삽입, 수정, 삭제 작업 시 성능 저하를 일으킬 수 있습니다.

 

쿼리 성능 저하 원인 분석

인덱스가 올바르게 사용되지 않는 경우, 예상과 다르게 쿼리 성능이 저하될 수 있습니다. 따라서, 쿼리 실행 계획을 분석하여 인덱스의 효율성을 평가해야 합니다.

 

인덱스 선택의 중요성

올바른 컬럼에 인덱스를 생성하는 것이 중요합니다. 모든 컬럼에 인덱스를 생성하는 것은 바람직하지 않으며, 사용 패턴을 분석하여 적절한 인덱스를 선택해야 합니다.