MySQL 데이터베이스 인덱스 내부 구조와 성능 최적화 가이드

인덱스의 기본 개념과 역할

데이터베이스에서 인덱스는 대용량 데이터를 신속하게 검색하고 조회하기 위해 설계된 데이터 조직 방식입니다. 기술적인 관점에서 인덱스는 정렬된 상태의 자료구조로 간주할 수 있으며, 그 핵심 목적은 무작위 탐색을 최소화하고 데이터 접근 경로를 단축하는 것입니다.

이를 이해하기 위한 예시로 도서를 찾는 상황을 들 수 있습니다. 목차가 없는 상태에서 특정 페이지를 찾으려면 모든 내용을 순차적으로 확인해야 하지만, 목차 (인덱스) 가 존재한다면 원하는 장과 페이지 번호를 즉시 파악하여 해당 위치로 이동할 수 있습니다. 이러한 메커니즘이 데이터베이스 쿼리 속도 향상의 기반이 됩니다.

일반적으로 인덱스를 구축하면 데이터 조회 속도가 극적으로 개선되고 입출력 (IO) 횟수가 줄어들지만, 인덱스 자체도 공간과 유지보수 비용이 발생합니다.

인덱스 구조의 선택 과정

데이터베이스 엔진은 여러 가지 자료구조 중에서 특정 환경에 가장 적합한 것을 선택합니다. 주요 후보로는 해시 테이블, 이진 검색 트리 (BST), AV L 트리, 레드 블랙 트리, 그리고 B 트리 계열 등이 있습니다.

해시 (Hash) 기반 접근법

해시 테이블은 키 (key) 를 통해 값을 상수 시간 O(1) 에 접근할 수 있는 효율성을 제공합니다. 그러나 데이터베이스 환경에서는 범위를 지정하거나 정렬한 결과를 반환해야 하는 요구사항이 빈번합니다. 예를 들어, '특정 값보다 작은 모든 데이터'를 조회하려 할 때, 해시 알고리즘으로는 연속된 구간을 찾기 어렵고 각 행마다 개별 계산이 필요해 비효율적입니다. MySQL 의 InnoDB 엔진은 일반적인 해시 인덱스를 기본으로 지원하지 않으며, 범위 검색과 정렬의 안정성 때문에 B+ 트리를 채택했습니다.

트리 구조들의 비교

이진 검색 트리는 균형 잡혀 있을 경우 O(log n) 의 성능을 보이지만, 데이터 입력 순서에 따라 트리가 한쪽으로 쏠려 선형 리스트처럼 변할 경우 성능이 급격히 저하됩니다. 이를 해결하기 위해 고안된 AVL 트리나 레드 블랙 트리는 자발적 균형 유지를 통해 최악의 상황을 방지하지만, 노드 당 저장되는 데이터 양이 적어 디스크 I/O 발생 빈도가 높은 문제점이 있습니다.

비균형 트리结构示意图

반면, B+ 트리는 다중 분기 구조를 통해 트리의 높이를 낮추고, 데이터가 모두 리프 노드에 집중되어 있어 연쇄 연결이 가능합니다. 이로 인해 범위 스캔 시 효율이 뛰어나며, 디스크 블록 읽기 단위와 잘 맞도록 최적화되어 있습니다. 따라서 대부분의 관계형 데이터베이스는 인덱스의 물리적 구조로 B+ 트리를 사용합니다.

InnoDB 와 MyISAM 의 인덱스 구현 차이

MySQL 은 두 가지 주요 스토리지 엔진을 제공하며, 이들이 인덱스를 처리하는 방식에는 근본적인 차이가 있습니다.

클러스터링 인덱스 (Clustered Index)

InnoDB 엔진에서는 실제 데이터 레코드가 인덱스 구조 내부에 함께 저장됩니다. 이를 클러스터링 인덱스라고 하며, 보통 테이블의 기본키 (Primary Key) 가 이 역할을 담당합니다. B+ 트리의 리프 노드 영역이 곧 실제 데이터가 위치한 영역이기 때문에, 주소를 찾는 과정에서 추가적인 조회 (회표) 없이 직접 데이터를 얻을 수 있다는 장점이 있습니다.

비클러스터링 인덱스 (Secondary Index)

기본키 외의 다른 열에 대해 생성되는 인덱스는 일반적으로 보조 인덱스로 불립니다. InnoDB 에서 보조 인덱스의 리프 노드는 실제 데이터 전체가 아니라 기본키 값을 저장합니다. 따라서 보조 인덱스를 통해 데이터를 찾았다면, 이후 기본키 인덱스를 다시 조회하여 실제 레코드를 가져와야 하는 과정 (회표) 이 필요합니다. 반면, MyISAM 엔진은 모든 인덱스를 비클러스터링 방식으로 처리하며, 데이터 파일과 인덱스 파일을 분리하여 관리합니다.

고급 인덱스 활용 전략

커버링 인덱스 (Covering Index)

쿼리에서 요청된 모든 컬럼 값이 인덱스 내에 포함되어 있으면, 별도의 회표 작업 없이 인덱스에서만 모든 데이터를 추출할 수 있습니다. 이는 I/O 비용을 획기적으로 줄여주는 중요한 최적화 기법입니다.

예를 들어, 주문 정보를 조회하는 시나리오를 상상해 보겠습니다.

-- 샘플 테이블 생성
CREATE TABLE transaction_log (
    id BIGINT NOT NULL AUTO_INCREMENT,
    amount DECIMAL(10, 2) NOT NULL,
    customer_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
);

-- 테스트 데이터 로드 프로시저
DELIMITER $$
CREATE PROCEDURE populate_test_data(IN start_idx INT, IN end_idx INT)
BEGIN
    DECLARE i INT DEFAULT start_idx;
    WHILE i <= end_idx DO
        INSERT INTO transaction_log (id, amount, customer_name)
        VALUES (i, RAND() * 1000000, CONCAT('buyer', i));
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

CALL populate_test_data(1, 1000000);

-- 복합 인덱스 적용
ALTER TABLE transaction_log ADD INDEX idx_amount_name (amount, customer_name);

-- 쿼리 분석
EXPLAIN SELECT amount, customer_name FROM transaction_log ORDER BY amount DESC;

위와 같이 `amount` 와 `customer_name` 에 대한 복합 인덱스가 존재하면, 서버는 인덱스 트리만 스캔하여 결과를 반환하므로 `Extra` 열에 `Using index` 메시지가 나타납니다. 이는 데이터를 별도로 가져올 필요가 없음을 의미합니다.

복합 인덱스와 최좌측 접두사 원칙

여러 열을 조합하여 만든 복합 인덱스를 사용할 때는 정의된 순서가 중요합니다. MySQL 옵티마이저는 인덱스를 정의한 왼쪽부터 차례대로 조건을 매칭합니다.

예컨대 `(col_a, col_b, col_c)` 순서로 인덱스가 있다면:

col_a = ?: 인덱스 사용 가능
col_a = ? AND col_b = ?: 인덱스 사용 가능
col_b = ?: 인덱스 미사용 (최좌측 열이 생략됨)
col_a = ? AND col_c = ?: col_a 만 인덱스 사용, col_c 는 필터링 후 걸러짐

복합 인덱스 매칭 시나리오

따라서 선택성 (Distinctness) 이 높은 열을 인덱스의 왼쪽에 배치하는 것이 효율적입니다. 또한 MySQL 8.0 이상 버전에서는 일부 상황에서 인덱스 스킵 스캔 기능을 지원하여 엄격한 순서 제한에서 벗어날 수 있기도 하나, 여전히 기본 원칙을 따르는 것이 안전합니다.

인덱스 조건 내보내기 (ICP)

MySQL 5.6 부터 도입된 인덱스 조건 내보내기 (Index Condition Pushdown) 는 스토리지 엔진 단계에서 WHERE 절의 조건을 미리 평가하도록 하여, 서버 계층으로 보내는 데이터 양을 줄이는 기술입니다.

다음 예시를 참조하겠습니다.

CREATE TABLE sys_account (
    account_id INT AUTO_INCREMENT,
    postal_code VARCHAR(20),
    join_date DATE,
    PRIMARY KEY (account_id),
    INDEX idx_zip_date (postal_code, join_date)
) ENGINE=InnoDB;

SELECT * FROM sys_account 
WHERE postal_code = '12345' 
AND MONTH(join_date) = 3;

ICP 이전에는 `postal_code` 로 인덱스를 타고 해당하는 모든 행의 데이터를 먼저 읽어오던 서버에게 넘긴 뒤, 월 (MONTH) 조건을 적용했습니다. ICP 활성화 후엔 인덱스를 스캔하던 스토리지 엔진이 바로 날짜 함수 조건도 체크하고, 충족된 행의 키만 서버로 전달합니다. 이렇게 되면 불필요한 네트워크 전송과 메모리 복사 작업이 감소합니다.

실전 인덱스 설계 가이드라인

지양해야 할 상황

빈번한 업데이트 열: 인덱스는 데이터 변경 시 함께 갱신되어야 하므로 쓰기 성능을 저하시킵니다.
NULL 값 많은 열: 인덱스에 NULL 을 처리하는 오버헤드가 발생할 수 있습니다.
중복성 높은 열: 성별 등 구분도가 낮은 열에 인덱스를 생성하면 오히려 전체 스캔보다 느릴 수 있습니다.
함수 연산 포함: 인덱스 열에 함수를 적용하거나 타입 변환이 발생하면 인덱스가 작동하지 않습니다.

EXPLAIN 항목	의미 설명
id	쿼리 구문의 순서 식별자
select_type	선언 형태 (SIMPLE, PRIMARY, SUBQUERY 등)
type	접근 방법 (ALL: 전체 스캔, ref: 인덱스 등, 시스템 순으로 성능 향상)
key	실제로 선택된 인덱스 이름
rows	읽어온 행 개수 추정치
Extra	추가 정보 (Using filesort, Using temporary 등 주의 필요)

괴물 클럽