[MySQL] 쿼리 튜닝을 위한 인덱스 활용의 모든 것

오늘은 백엔드 API 성능 개선을 위해 핵심적인 요소 중 하나인 인덱스를 통한 쿼리 튜닝 방법에 대하여, 학습한 내용들을 정리해보겠습니다. 먼저 기본적인 내용부터 다뤄보겠습니다.

인덱스

인덱스의 정의

여러분은 색인에 대해 들어보셨나요? 색인(index)이란 책이나 학술서, 연구 재료가 될 서적의 내용 중에서 중요한 항목, 술어, 인명, 지명 등을 뽑아 본문 어느 쪽에 있는지 쉽게 찾아볼 수 있도록 쪽 번호와 함께 별도로 배열하여 놓은 목록입니다.

인덱스란 마치 책의 색인(index)처럼, 데이터를 빠르게 찾기 위해 데이터가 위치한 위치를 나열한 별도의 데이터 구조입니다. 보통 데이터베이스 테이블의 특정 열(Column)에 대한 검색 속도를 향상시키기 위해 사용하죠.

인덱스의 종류

이러한 인덱스에도 여러 종류가 있습니다. 각각의 목적과 용도에 따라, 활용되는 것이 다릅니다.

• B-Tree 인덱스 : 가장 대표적인 인덱스입니다. 자료구조를 공부해보신 분이라면 아시겠지만, B-Tree에서 탐색 시간복잡도는 O(log t​n) 입니다. 다른 자료구조에 비해, 상대적으로 빠르게 탐색할 수 있죠. 관계형 데이터베이스 시스템(RDB)에서는 보통 인덱스의 자료구조로 B-Tree를 활용하여, 데이터를 정렬하고 검색합니다.
• Hash 인덱스: 특정 값에 대해 매우 빠른 검색이 가능하며, 해시 테이블을 기반으로 합니다. 일반적으로 등가 비교(=)에 최적화되어 있습니다.
• Bitmap 인덱스: 데이터가 카디널리티가 낮은 경우(예: 성별, 상태) 효과적입니다. 각 값에 대한 비트맵을 생성하여 빠른 필터링을 제공합니다.
• Full-text 인덱스: 텍스트 데이터의 검색에 최적화된 인덱스로, 텍스트 내용에서 특정 단어나 구문을 빠르게 찾을 수 있습니다.

인덱스의 장점

• 빠른 검색 속도: 인덱스를 사용하면 데이터를 빠르게 검색할 수 있어 쿼리 성능이 향상됩니다.

인덱스가 없는 경우와 인덱스가 있는 경우, 어떠한 성능 차이가 있는지 예시를 통해 설명드리겠습니다.

SELECT * FROM users WHERE last_name = 'Smith';

첫번째 쿼리는 테이블 users의 모든 행을 순차적으로 검색하여 last_name이 'Smith'인 행을 찾습니다. 만약 테이블에 1,000,000개의 행이 있다면, 최악의 경우 1,000,000번의 비교를 해야 합니다.

이번엔 인덱스를 추가해보겠습니다.

CREATE INDEX idx_last_name ON users(last_name);

인덱스를 생성한 후 동일한 쿼리를 실행했을 때, B-Tree 인덱스를 사용한다고 가정하면, 1,000,000개의 행이 있는 테이블에서도 검색 속도는 log₂(1,000,000) ≈ 20 번의 비교만 필요하게 됩니다.

SELECT * FROM users WHERE last_name = 'Smith';

• 정렬된 데이터: 인덱스는 데이터를 정렬된 상태로 유지하므로, 정렬된 데이터가 필요한 쿼리에 유리합니다.

인덱스의 단점

인덱스가 항상 좋은 것은 아닙니다. 아래와 같은 단점도 존재합니다.

• 추가 저장 공간 필요: 인덱스를 저장하기 위한 추가 저장 공간이 필요합니다.
• 데이터 수정 시 성능 저하: 인덱스가 있는 테이블에 데이터를 삽입, 수정, 삭제할 때 인덱스도 업데이트해야 하므로 성능이 저하될 수 있습니다.

인덱스의 사용 사례

인덱스를 전혀 모르더라도 CRUD 기능을 구현해본 적 있다면, 인덱스를 사용하고 있다는 사실 알고 계셨나요? 기본키 설정을 했다면,

• Primary Key 인덱스: 각 테이블의 기본 키에 자동으로 생성되는 인덱스로, 데이터의 고유성을 보장합니다. 또한 (MySQL InnoDB 기준으로) 생성 될 때 클러스터드 인덱스로 함께 지정되어, 데이터 조회에 기준이 됩니다.
• Unique 인덱스: 특정 열의 값이 고유하도록 강제합니다.
• Composite 인덱스: 여러 열을 결합하여 인덱스를 생성할 수 있으며, 다중 열 검색에 효과적입니다.

데이터베이스의 성능을 최적화를 위한 인덱스 설계 방법

데이터베이스의 성능을 최적화하기 위해 인덱스를 설계할 때 고려해야 할 사항들은 다양하며, 각 사항을 신중하게 검토해야 최적의 성능을 달성할 수 있는데, 다음은 주요 고려 사항들입니다.

쿼리 패턴 분석

• 자주 사용되는 쿼리 파악: 인덱스를 설계하기 전에, 어떤 쿼리가 자주 사용되는지 분석해야 합니다. 이를 통해 특정 컬럼에 인덱스를 적용할지 결정할 수 있습니다. 만약 자주 사용되는 쿼리가 느리다면, 1순위로 튜닝되어야 할 대상입니다.
• 조건절 확인: WHERE 절, JOIN 조건, ORDER BY, GROUP BY에 사용되는 컬럼을 중심으로 인덱스를 설계합니다.

인덱스 활용

어떤 쿼리가 자주 사용되는지, 어떤 쿼리가 느린지, 어떤 칼럼을 기준으로 조건 검색이 되는지 확인이 됐다면, 아래 방법들을 적용해볼 수 있습니다.
더 쉬운 이해를 위해, 아래와 같이 employees을 예시로 설명드리겠습니다.

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50),
    position VARCHAR(50),
    department VARCHAR(50)
);

• 단일 인덱스 : 특정 컬럼을 기준으로 단순한 검색 쿼리에 적합하며, 자주 조회되는 단일 컬럼에 대해 성능 최적화를 할 때 사용합니다.

-- 단일 인덱스 생성
CREATE INDEX idx_name ON employees (name);

• 복합 인덱스 : 여러 조건을 결합한 검색 쿼리에 적합하며, 인덱스를 구성하는 컬럼의 순서가 중요합니다. 첫 번째 컬럼이 조건에 포함되지 않으면 인덱스가 효과적이지 않습니다.

-- 복합 인덱스 생성
CREATE INDEX idx_name_position ON employees (name, position);

• 커버링 인덱스: 특정 쿼리에 필요한 모든 컬럼을 포함하는 인덱스입니다. 이는 쿼리를 실행할 때 인덱스만으로 데이터를 반환할 수 있기 때문에 성능 향상에 매우 유용합니다. 많은 조회 쿼리가 특정 컬럼들만 자주 읽는 경우, 커버링 인덱스를 사용하면 테이블을 조회할 필요 없이 인덱스만으로 데이터를 반환할 수 있습니다. 이는 디스크 I/O를 크게 줄이고 쿼리 성능을 향상시킵니다.

-- 커버링 인덱스 생성
CREATE INDEX idx_covering ON employees (name, position, department);

아래는 예시입니다.

SELECT name, position, department FROM employees WHERE name = 'John' AND position = 'Manager';

기타 고려사항

1. 인덱스의 수와 종류
   • 적절한 인덱스 수: 너무 많은 인덱스는 데이터 삽입, 업데이트, 삭제 시 성능 저하를 초래합니다. 반면, 너무 적은 인덱스는 검색 속도 저하를 유발할 수 있습니다.
   • Composite 인덱스: 여러 컬럼을 포함하는 인덱스를 통해 복합 조건 검색의 성능을 높일 수 있습니다. 단, 컬럼 순서가 중요합니다.
2. 데이터 분포와 카디널리티
   • 데이터 분포와 카디널리티 고려: 인덱스를 설계할 때 컬럼의 데이터 분포를 분석해야 합니다. 예를 들어, 매우 많은 중복값이 있는 컬럼은 인덱스 효율이 낮을 수 있습니다. 반면, 고유한 값이 많은 컬럼은 인덱스 효율이 높습니다. 카디널리티가 낮은 컬럼에는 인덱스를 적용을 피하는 것이 좋습니다.
3. 인덱스 유지 비용
   • 인덱스 유지 관리: 데이터 삽입, 수정, 삭제 시 인덱스도 함께 업데이트되어야 하므로 유지 비용을 고려해야 합니다.
   • 성능 테스트: 인덱스를 추가하거나 변경한 후에는 실제 환경에서 성능 테스트를 통해 그 효용성을 검증해야 합니다.
4. 스토리지와 성능 트레이드오프
   • 저장 공간: 인덱스는 추가적인 저장 공간을 필요로 합니다. 스토리지 비용과 성능 간의 균형을 고려해야 합니다.
   • 읽기/쓰기 성능 균형: 읽기 성능을 최적화하기 위한 인덱스가 쓰기 성능을 저하시킬 수 있습니다. 시스템의 주요 작업(읽기 vs 쓰기)에 따라 인덱스 전략을 조정합니다.