MariaDB 고급 인덱스 유형 및 성능 최적화 방법

인덱스는 데이터베이스 성능을 최적화하는 중요한 도구입니다. 이 글에서는 MariaDB의 고급 인덱스 유형인 UNIQUE, FULLTEXT, SPATIAL 인덱스와 인덱스 최적화 및 성능 튜닝 방법에 대해 알아봅니다.

고급 인덱스 유형

UNIQUE 인덱스는 인덱스가 적용된 열에 고유한 값만 허용합니다. 이는 데이터 무결성을 유지하는 데 유용합니다.

CREATE UNIQUE INDEX unique_index_name
ON table_name (column_name);

FULLTEXT 인덱스는 대량의 텍스트 데이터를 빠르게 검색하는 데 사용됩니다. 주로 텍스트 검색에 적합합니다.

CREATE FULLTEXT INDEX fulltext_index_name
ON table_name (column_name);

SPATIAL 인덱스는 지리적 데이터를 효율적으로 저장하고 검색하는 데 사용됩니다. 주로 GIS(지리 정보 시스템) 애플리케이션에 사용됩니다.

CREATE SPATIAL INDEX spatial_index_name
ON table_name (spatial_column);

인덱스를 효과적으로 사용하려면 자주 검색되는 열에 인덱스를 생성하고, 중복되지 않는 값에 UNIQUE 인덱스를 사용하는 것이 좋습니다. 인덱스는 읽기 성능을 향상시키지만, 쓰기 성능에는 영향을 줄 수 있으므로 주의해야 합니다.

EXPLAIN 명령어를 사용하여 쿼리의 실행 계획을 확인하고, 쿼리가 인덱스를 사용하는지 분석할 수 있습니다.

EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';

예를 들어, employees 테이블에서 employee_name 열에 인덱스를 생성하고, EXPLAIN을 사용하여 쿼리 성능을 분석합니다.

CREATE INDEX idx_employee_name
ON employees (employee_name);

EXPLAIN SELECT * FROM employees WHERE employee_name = 'John Doe';

▼ 실행 결과 화면

EXPLAIN 명령어의 결과를 통해 쿼리 성능을 분석할 때 주로 확인하는 필드는 다음과 같습니다.

select_type: 쿼리의 종류를 나타냅니다. 예를 들어, SIMPLE은 서브쿼리가 없는 단일 쿼리를 의미합니다.
table: 쿼리에서 사용된 테이블의 이름입니다.
type: 조인의 유형을 나타냅니다. 이 값이 성능을 결정하는 데 중요한데, 가능한 값들로는 ALL, index, range, ref, eq_ref, const, system, NULL 등이 있습니다. 이 중 const와 eq_ref가 가장 효율적입니다.
possible_keys: 쿼리에 사용될 수 있는 인덱스 목록입니다.
key: 실제로 사용된 인덱스를 나타냅니다.
key_len: 사용된 인덱스 키의 길이입니다.
ref: 조인에서 사용된 컬럼 또는 상수입니다.
rows: 쿼리 실행 시 예상되는 처리할 행의 수입니다.
Extra: 추가적인 정보를 제공합니다. 예를 들어, Using index, Using where, Using temporary, Using filesort 등이 있습니다.

더 자세한 내용은 다음 사이트들을 참고하세요.