MySQL 인덱스 커버링 기법과 실제 성능 개선 사례

안녕하세요! 오늘은 MySQL에서 쿼리 성능을 크게 향상시킬 수 있는 '인덱스 커버링 기법'에 대해 알아보겠습니다. 복잡한 이론보다는 실제 성능이 어떻게 개선되는지 실습 위주로 설명해 드릴게요.

인덱스와 커버링 인덱스란?

데이터베이스에서 인덱스는 책의 색인과 같은 역할을 합니다. 특정 정보를 빠르게 찾을 수 있도록 도와주죠. MySQL에서 인덱스는 데이터를 정렬된 상태로 유지하여 검색 속도를 높여줍니다.

커버링 인덱스(Covering Index)는 쿼리에서 필요한 모든 데이터가 인덱스 내에 포함되어 있어 실제 테이블 데이터에 접근할 필요 없이 인덱스만으로 쿼리를 처리할 수 있는 상황을 말합니다. 이 방식은 다음과 같은 이유로 성능이 크게 향상됩니다:

1. 디스크 I/O 감소: 테이블 데이터를 읽지 않아도 됨
2. 메모리 효율성: 인덱스는 테이블보다 크기가 작아 메모리에 더 많이 캐시될 수 있음
3. 데이터 접근 속도 향상: 정렬된 인덱스에서의 검색은 훨씬 빠름

실습 환경 준비하기

이제 실습을 통해 커버링 인덱스의 효과를 직접 확인해봅시다. MySQL 서버가 설치되어 있다고 가정하고 진행하겠습니다.

-- 데이터베이스 생성
CREATE DATABASE IF NOT EXISTS covering_index_demo;
USE covering_index_demo;

-- 테스트용 테이블 생성
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    department VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL,
    hire_date DATE NOT NULL
);

테스트 데이터 생성하기

의미 있는 테스트를 위해 충분한 데이터를 생성합시다. 아래 코드를 실행하면 10만 건의 테스트 데이터가 생성됩니다.

-- 테스트 데이터 생성
DELIMITER //
CREATE PROCEDURE generate_test_data()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 100000 DO
        INSERT INTO employees (
            first_name, 
            last_name, 
            email, 
            phone, 
            department, 
            salary, 
            hire_date
        ) 
        VALUES (
            CONCAT('First', i),
            CONCAT('Last', i),
            CONCAT('employee', i, '@example.com'),
            CONCAT('010-', FLOOR(1000 + RAND() * 9000), '-', FLOOR(1000 + RAND() * 9000)),
            ELT(1 + FLOOR(RAND() * 5), '개발', '마케팅', '영업', '인사', '재무'),
            ROUND(3000000 + RAND() * 5000000, 2),
            DATE_ADD('2015-01-01', INTERVAL FLOOR(RAND() * 2500) DAY)
        );
        SET i = i + 1;
    END WHILE;
END//
DELIMITER ;

-- 프로시저 실행
CALL generate_test_data();

성능 비교 1: 인덱스 없는 경우

먼저 인덱스가 없는 상태에서 부서별 평균 급여를 조회해 봅시다.

-- 실행 계획 확인
EXPLAIN SELECT department, AVG(salary) AS avg_salary 
FROM employees 
WHERE hire_date BETWEEN '2018-01-01' AND '2020-12-31'
GROUP BY department;

-- 실제 쿼리 실행 및 시간 측정
SET profiling = 1;
SELECT department, AVG(salary) AS avg_salary 
FROM employees 
WHERE hire_date BETWEEN '2018-01-01' AND '2020-12-31'
GROUP BY department;
SHOW PROFILES;

위 쿼리를 실행하면 전체 테이블을 스캔(Full Table Scan)하기 때문에 상당한 시간이 소요됩니다. EXPLAIN 결과에서 'type' 컬럼이 'ALL'로 표시되는 것을 확인할 수 있습니다. 이는 전체 테이블을 검색한다는 의미입니다.

성능 비교 2: 일반 인덱스 추가

이제 hire_date 필드에 인덱스를 추가해 봅시다.

-- hire_date에 인덱스 생성
CREATE INDEX idx_hire_date ON employees(hire_date);

-- 실행 계획 다시 확인
EXPLAIN SELECT department, AVG(salary) AS avg_salary 
FROM employees 
WHERE hire_date BETWEEN '2018-01-01' AND '2020-12-31'
GROUP BY department;

-- 실제 쿼리 실행 및 시간 측정
SELECT department, AVG(salary) AS avg_salary 
FROM employees 
WHERE hire_date BETWEEN '2018-01-01' AND '2020-12-31'
GROUP BY department;
SHOW PROFILES;

인덱스를 추가한 후에는 hire_date 조건에 맞는 레코드를 빠르게 찾을 수 있게 되었습니다. 그러나 여전히 각 레코드에 대해 테이블을 조회해야 하므로, 성능 향상은 제한적입니다.

성능 비교 3: 커버링 인덱스 적용

이제 커버링 인덱스를 적용해 봅시다. 쿼리에서 필요한 모든 컬럼(hire_date, department, salary)을 포함하는 복합 인덱스를 생성합니다.

-- 커버링 인덱스 생성 (기존 인덱스 삭제 후)
DROP INDEX idx_hire_date ON employees;
CREATE INDEX idx_covering ON employees(hire_date, department, salary);

-- 실행 계획 확인
EXPLAIN SELECT department, AVG(salary) AS avg_salary 
FROM employees 
WHERE hire_date BETWEEN '2018-01-01' AND '2020-12-31'
GROUP BY department;

-- 실제 쿼리 실행 및 시간 측정
SELECT department, AVG(salary) AS avg_salary 
FROM employees 
WHERE hire_date BETWEEN '2018-01-01' AND '2020-12-31'
GROUP BY department;
SHOW PROFILES;

EXPLAIN 결과에서 중요한 부분을 확인해보세요! 'Using index'라는 문구가 Extra 컬럼에 표시됩니다. 이는 쿼리가 인덱스만으로 해결되었다는 의미입니다. 실행 시간도 크게 단축된 것을 확인할 수 있습니다.

성능 비교 4: 다양한 쿼리에서의 커버링 인덱스

이번에는 직원 수 기준으로 부서를 조회하는 쿼리로 테스트해 봅시다.

-- 새로운 커버링 인덱스 생성
CREATE INDEX idx_dept_hire ON employees(department, hire_date);

-- 실행 계획 확인
EXPLAIN SELECT department, COUNT(*) AS employee_count 
FROM employees 
WHERE department IN ('개발', '마케팅') 
AND hire_date > '2019-01-01'
GROUP BY department;

-- 실제 쿼리 실행 및 시간 측정
SELECT department, COUNT(*) AS employee_count 
FROM employees 
WHERE department IN ('개발', '마케팅') 
AND hire_date > '2019-01-01'
GROUP BY department;
SHOW PROFILES;

이 쿼리에서도 'Using index' 표시를 확인할 수 있습니다. 이는 커버링 인덱스가 제대로 활용되고 있다는 증거입니다.

커버링 인덱스 사용 시 주의사항

커버링 인덱스는 강력하지만, 몇 가지 주의해야 할 점이 있습니다:

1. 인덱스 크기: 너무 많은 컬럼을 인덱스에 포함하면 인덱스 자체가 커져 성능이 저하될 수 있습니다.
2. 쓰기 성능: 인덱스가 많을수록 INSERT, UPDATE, DELETE 작업이 느려집니다.
3. 인덱스 선택: MySQL 옵티마이저가 항상 최적의, 인덱스를 선택한다고 보장할 수 없습니다. FORCE INDEX 힌트를 사용해야 할 수도 있습니다.
4. 복합 인덱스 순서: 복합 인덱스에서 컬럼 순서는 매우 중요합니다. 자주 필터링되는 컬럼을 앞쪽에 배치하는 것이 좋습니다.

커버링 인덱스 확인 방법

쿼리가 커버링 인덱스를 사용하는지 확인하는 가장 쉬운 방법은 EXPLAIN 명령 결과의 Extra 컬럼에서 "Using index"를 확인하는 것입니다:

EXPLAIN SELECT department, COUNT(*) 
FROM employees 
WHERE hire_date > '2019-01-01' 
GROUP BY department;

실전 팁: 커버링 인덱스 설계하기

효과적인 커버링 인덱스를 설계하는 방법은 다음과 같습니다:

1. 쿼리 패턴 분석: 자주 실행되는 쿼리를 분석하여 어떤 컬럼이 WHERE, GROUP BY, ORDER BY, SELECT 절에서 사용되는지 파악합니다.
2. 선택성 고려: WHERE 절에서 사용되는 컬럼 중 선택성이 높은 컬럼(중복이 적은 컬럼)을 인덱스 앞쪽에 배치합니다.
3. 인덱스 크기 최소화: 필요한 컬럼만 인덱스에 포함하여 크기를 최소화합니다.
4. 기존 인덱스 활용: 이미 있는 인덱스를 확장하는 방식으로 커버링 인덱스를 구성하면 효율적입니다.

요약

지금까지 MySQL 인덱스 커버링 기법과 실제 성능 개선 사례에 대해 알아보았습니다. 정리하자면:

1. 커버링 인덱스는 쿼리에 필요한 모든 데이터가 인덱스 내에 포함되어, 테이블 접근 없이 쿼리를 처리하는 기법입니다.
2. 성능 향상 원리는 디스크 I/O 감소, 메모리 효율성 향상, 데이터 접근 속도 향상입니다.
3. 실제 적용 시에는 자주 사용하는 쿼리 패턴을 분석하고, 필요한 컬럼만을 포함하는 인덱스를 생성하는 것이 중요합니다.
4. 'Using index' 표시는 커버링 인덱스가 제대로 활용되고 있다는 신호입니다.

초급 개발자분들도 이 기법을 적용하면 데이터베이스 쿼리 성능을 크게 개선할 수 있습니다. 특히 대용량 데이터를 다루는 애플리케이션에서는 더욱 효과적입니다.

여러분의 프로젝트에서도 커버링 인덱스를 시도해 보시고, 성능 개선 효과를 경험해 보세요!