MySQL 쿼리 최적화 비법: 수천만 건의 데이터를 190초에서 1초로!

안녕하세요 Devloo 입니다. 🙂 이번 시간에는 MySQL 의 쿼리를 최적화하는 방법에 대해 알아보고자 합니다.

먼저, 수천만 건의 데이터를 MySQL로 처리하는 것은 일반적으로 최적의 사용 사례가 아님을 분명히 해두어야 합니다.

수천만 건의 레코드를 MySQL에서 최적화하기 위한 몇 가지 전략이 있습니다:

샤딩 (테이블 및 데이터베이스 분할)
요약 테이블 생성
여러 하위 쿼리를 사용하는 쿼리 수정

이번 논의에서는 하나의 MySQL 테이블에 수천만 건의 레코드가 포함된 상황을 살펴보겠습니다.

테이블 설계가 부실하고 비즈니스 규칙상 SQL 쿼리를 여러 하위 쿼리로 분할할 수 없는 경우, 개발자는 SQL을 최적화하여 쿼리 목표를 달성해야 합니다.

하나의 MySQL 테이블에 수천만 건의 레코드가 포함될 때는 특별한 고려가 필요합니다. 이 글에서는 주로 이러한 극단적인 상황에서 사용할 수 있는 SQL 최적화 전략에 대해 다루어 보도록 하겠습니다.

MySQL 쿼리 최적화 비법 — 사진: Unsplash의Isaac Smith

>> 목차

수천만 건의 데이터 생성하기

저장 프로시저를 사용하여 1,000만 건의 레코드를 생성해보겠습니다.

테이블 구조는 다음과 같습니다:

CREATE TABLE `orders` (
  `order_id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `order_date` date NOT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  PRIMARY KEY (`order_id`),
  KEY `idx_user_id` (`user_id`) USING BTREE,
  KEY `idx_user_amount` (`user_id`,`total_amount`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `users` (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `email` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  KEY `idx_user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

데이터를 생성하는 저장 프로시저는 다음과 같습니다.

사용자 데이터 생성:

-- 1000명의 사용자를 생성하는 저장 프로시저
CREATE DEFINER=`root`@`localhost` PROCEDURE `create_users`()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE total_users INT DEFAULT 1000; -- 사용자 수 조정
    DECLARE rnd_username VARCHAR(50);
    DECLARE rnd_email VARCHAR(100);

    WHILE i < total_users DO
        -- 랜덤 사용자 이름과 이메일 생성
        SET rnd_username = CONCAT('User', FLOOR(1 + RAND() * 10000000)); -- 고유한 사용자 이름 가정
        SET rnd_email = CONCAT(rnd_username, '@example.com'); -- 고유한 이메일 가정
        -- 사용자 테이블에 데이터 삽입
        INSERT INTO users (username, email) VALUES (rnd_username, rnd_email);

        SET i = i + 1;
    END WHILE;
END;

주문 데이터 생성 저장 프로시저:

CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_orders`()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE total_users INT DEFAULT 1000; -- 사용자 수
    DECLARE total_orders_per_user INT DEFAULT 1000; -- 사용자당 주문 수
    DECLARE rnd_user_id INT;
    DECLARE rnd_order_date DATE;
    DECLARE rnd_total_amount DECIMAL(10, 2);
    DECLARE j INT DEFAULT 0;

    WHILE i < total_users DO
        -- 사용자 ID 가져오기
        SELECT user_id INTO rnd_user_id FROM users LIMIT i, 1;

        WHILE j < total_orders_per_user DO
            -- 주문 날짜와 총 금액 생성
            SET rnd_order_date = DATE_ADD('2020-01-01', INTERVAL FLOOR(RAND() * 1096) DAY); -- 2020-01-01과 2022-12-31 사이의 랜덤 날짜
            SET rnd_total_amount = ROUND(RAND() * 1000, 2); -- 0과 1000 사이의 랜덤 총 금액
            -- 주문 테이블에 데이터 삽입
            INSERT INTO orders (user_id, order_date, total_amount) VALUES (rnd_user_id, rnd_order_date, rnd_total_amount);

            SET j = j + 1;
        END WHILE;
        SET j = 0;

        SET i = i + 1;
    END WHILE;
END;

사용자 데이터와 주문 데이터 생성을 분리하면 멀티스레딩을 활용할 수 있습니다.

먼저, call create_users()를 호출한 다음, 15개의 창을 열어 call generate_orders()를 호출하세요.

이 과정을 통해 1,000명의 사용자와 1,500만 개의 주문 레코드를 생성할 수 있습니다.

원본 SQL

이 SQL 쿼리는 각 사용자의 총 주문 금액을 계산하는 매우 간단한 쿼리입니다.

기본적으로 인덱스가 생성되지 않은 경우, 이 쿼리를 실행하는 데 190초 이상 걸립니다.

-- 첫 번째 버전
SELECT a.*, sum(b.total_amount) as total 
FROM users a 
LEFT JOIN orders b ON a.user_id = b.user_id
GROUP BY a.user_id;

Explain 분석 결과는 다음과 같습니다 (쿼리 앞에 explain 명령어를 붙여주시면 됩니다):

인덱스가 사용되지 않았으며, “ALL” 타입으로 전체 테이블을 스캔합니다.

실행 시간: 191초

첫 번째 최적화: 일반 인덱스

SQL 조건에 사용된 모든 열에 인덱스를 생성합니다. 여기에는 WHERE, JOIN, SUM이 포함됩니다.

CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_total_amount ON orders (total_amount);
CREATE INDEX idx_users_user_id ON users (user_id);

SQL 쿼리는 동일합니다:

-- 첫 번째 버전
SELECT a.*, sum(b.total_amount) as total 
FROM users a 
LEFT JOIN orders b ON a.user_id = b.user_id
GROUP BY a.user_id;

Explain 결과를 확인해 봅시다:

타입이 “index” 또는 “ref”로 변경되었으며, 모든 쿼리가 인덱스를 사용합니다.

그러나 결과는 실망스럽습니다. 실행 시간이 460초 이상으로 증가했습니다. 이는 MySQL이 기본 키를 사용하여 관련 필드를 찾는 메커니즘 때문일 수 있습니다.

두 번째 최적화: 커버링 인덱스

커버링 인덱스는 쿼리를 만족시키기 위해 필요한 모든 열을 포함하는 인덱스로, 실제 데이터 행에 접근하지 않고도 쿼리를 직접 실행할 수 있게 해줍니다.

먼저 기존 인덱스는 삭제하지 말고 다음과 같이 커버링 인덱스를 생성해봅시다:

CREATE INDEX idx_orders_total_amount_user_id ON orders (total_amount, user_id);
CREATE INDEX idx_orders_user_id_total_amount ON orders (user_id, total_amount);

1500만 건의 레코드에 인덱스를 생성하는 데 300초 이상 걸리므로 인덱스 생성은 신중하게 해야 합니다.

SQL 쿼리는 다음과 같이 동일하게 유지됩니다:

SELECT a.*, sum(b.total_amount) as total 
FROM users a 
LEFT JOIN orders b ON a.user_id = b.user_id
GROUP BY a.user_id;

이제 EXPLAIN 명령어를 통해 결과를 확인해보겠습니다:

주문 테이블의 타입이 “index”에서 “ref”로 변경된 것을 볼 수 있습니다.

쿼리 시간이 460초에서 10초로 감소했습니다. 이 결과는 커버링 인덱스가 쿼리 성능을 향상시킬 수 있음을 증명합니다.

여기서 주목할 점은 idx_orders_user_id_total_amount 인덱스만이 쿼리 시간을 줄였다는 것입니다. idx_orders_total_amount_user_id 인덱스는 그렇지 않았습니다. 이는 MySQL 키워드의 실행 순서와 관련이 있을 수 있습니다(가설적인 설명으로 명확한 출처는 없습니다).

MySQL 실행 순서:

from
on
join
where
group by
having
select
distinct
union (all)
order by
limit

커버링 인덱스는 WHERE 절을 SELECT의 SUM 함수 앞에 사용하며, 이는 idx_orders_user_id_total_amount 인덱스 생성 순서와 일치합니다.

이제 불필요한 인덱스를 삭제해봅시다:

drop INDEX idx_orders_user_id ON orders;
drop INDEX idx_orders_total_amount ON orders;
drop INDEX idx_orders_total_amount_user_id ON orders;

불필요한 인덱스를 삭제한 후에도 실행 시간은 10초로 동일하게 유지됩니다.

따라서, 이 최적화 전략은 커버링 인덱스를 사용하여 인덱스를 효과적으로 활용하는 것입니다.

세 번째 최적화: 데이터 볼륨 줄이기

데이터 볼륨을 줄이는 것은 불필요한 데이터를 제거하거나 아키텍처를 조정하는 것을 포함합니다. 그 중 한 가지 방법은 테이블을 분할하는 것입니다.

이 방법을 사용하면 데이터 볼륨을 수천만 건에서 수백만 건, 심지어 수십만 건으로 줄일 수 있어 쿼리 속도를 크게 개선할 수 있습니다.

다음은 데이터 볼륨을 줄이는 쿼리입니다:

SELECT a.*, sum(b.total_amount) as total 
FROM users a 
LEFT JOIN orders b ON a.user_id = b.user_id
WHERE a.user_id > 1033
GROUP BY a.user_id;

EXPLAIN 결과는 다음과 같습니다:

보시다시피, users 테이블의 타입이 “range”로 표시되어 데이터의 일부를 필터링하고 있습니다.

쿼리 시간이 10초에서 7초로 줄어들어 데이터 볼륨을 줄이는 것이 효과적임을 증명했습니다.

네 번째 최적화: 작은 테이블로 큰 테이블 구동하기

MySQL에서는 옵티마이저가 쿼리 조건과 테이블 크기를 바탕으로 가장 적합한 구동 테이블을 선택합니다.

‘작은 테이블로 큰 테이블 구동하기’ 전략은 조인 쿼리에서 작은 테이블을 구동 테이블로 선택하여 메모리 사용량과 처리 시간을 줄이는 방법입니다.

세 번째 최적화 결과를 바탕으로 이 전략을 시도해 보겠습니다.

SELECT a.*, sum(b.total_amount) as total 
FROM users a 
LEFT JOIN (SELECT user_id, total_amount FROM orders c WHERE c.user_id > 1033) b ON a.user_id = b.user_id
WHERE a.user_id > 1033
GROUP BY a.user_id;

이 쿼리는 LEFT JOIN 테이블을 서브쿼리로 변경하여 일부 데이터를 미리 필터링합니다.

EXPLAIN 결과는 다음과 같습니다:

보시다시피, EXPLAIN 결과는 크게 변하지 않았으며 실제 실행 성능도 동일하게 유지되었습니다.

‘작은 테이블로 큰 테이블 구동하기’ 전략은 여기서는 효과적이지 않았지만, 특정 비즈니스 로직에 따라 여전히 유효한 최적화 전략이 될 수 있습니다.

다섯 번째 최적화: 인덱스 강제 사용

수천만 건의 레코드가 포함된 쿼리에서 IN 절을 사용할 때, 인덱스가 잘 설계되지 않으면 인덱스 비효율성이 발생하여 쿼리 성능이 저하될 수 있습니다.

보통 MySQL 옵티마이저는 최적의 실행 계획과 적절한 인덱스를 선택합니다. 하지만 대용량 데이터에서 IN 절을 사용하는 경우 MySQL이 인덱스를 효과적으로 사용하지 못해 전체 테이블 스캔이나 인덱스 비효율성이 발생할 수 있습니다.

다음 SQL은 인덱스를 강제 사용한 경우와 그렇지 않은 경우를 보여줍니다. 이 예시에서는 IN 절의 데이터가 밀집되어 있어 두 경우 모두 비슷한 결과를 얻을 수 있습니다:

-- 다섯 번째 버전, 인덱스 강제 사용
SELECT a.*, sum(b.total_amount) as total 
FROM users a 
LEFT JOIN orders b FORCE INDEX (idx_orders_user_id_total_amount) ON a.user_id = b.user_id
WHERE b.user_id IN (1033, 1034, 1035, 1036, 1037, 1038)
GROUP BY a.user_id;

-- 다섯 번째 버전, 인덱스 강제 사용하지 않음
SELECT a.*, sum(b.total_amount) as total 
FROM users a 
LEFT JOIN orders b ON a.user_id = b.user_id
WHERE b.user_id IN (1033, 1034, 1035, 1036, 1037, 1038)
GROUP BY a.user_id;

두 경우 모두 쿼리 시간은 1초 미만입니다.

실제 비즈니스 시나리오에서는 쿼리가 설명 결과대로 인덱스를 사용하더라도 여전히 느릴 수 있습니다. 이러한 경우 인덱스를 강제 사용해 보는 것이 유효한 방법이 될 수 있습니다.

최적화 전략

인덱스를 조기에 활용하고, 작은 테이블로 큰 테이블을 구동합니다.
IN 절의 비효율성을 인덱스 강제로 해결합니다.
커버링 인덱스를 사용하여 테이블 조회를 피합니다.

다음 SQL 최적화 방법

데이터 볼륨이 수천만 건에 달할 때는 테이블 파티셔닝을 사용하세요. 예를 들어, 사용자 ID의 나머지를 기준으로 분할할 수 있습니다.
하위 쿼리를 요약 테이블로 대체하여 인덱스를 활용합니다. 예를 들어, 일별 또는 월별 요약 테이블을 생성하세요.
중복된 조인 필드를 단일 테이블에 직접 배치하여 단일 테이블 쿼리를 수행합니다.
인덱스를 최대한 활용하며, 공간을 시간으로 교환하는 방식의 최적화를 시도하세요. 이는 이 글에서 분석한 핵심 시나리오입니다.

인덱스를 효과적으로 활용하는 데 중요한 포인트는 커버링 인덱스입니다. 데이터 볼륨이 수천만 건에 달할 경우, 인덱스를 강제로 사용하는 것이 필요할 수 있습니다.

추가 팁

EXPLAIN 결과의 타입 필드 의미

MySQL의 EXPLAIN 쿼리 결과에서 타입(type) 필드는 쿼리가 데이터를 조회하는 방식을 나타냅니다. 이는 쿼리 실행 중 사용되는 접근 방법을 설명해줍니다.

MySQL 쿼리 옵티마이저는 접근 방식에 따라 다양한 실행 계획을 선택합니다. 아래는 타입 필드의 가능한 값과 그 의미입니다:

system: 최상의 시나리오로, PRIMARY KEY나 고유 인덱스를 통해 단일 행 결과를 반환하는 경우입니다.
const: MySQL이 쿼리에서 상수 값을 찾았음을 나타내며, 주로 조인의 첫 번째 테이블에서 사용됩니다. 상수 조건이므로 MySQL은 테이블에서 한 행만 읽습니다. 예를 들어, 기본 키를 통해 한 행의 데이터를 읽는 경우입니다.
eq_ref: const와 유사하지만 인덱스를 사용하는 경우입니다. 이 유형의 쿼리는 고유 인덱스를 사용하여 테이블에 접근하며, 각 인덱스 키 값에 대해 한 행만 일치합니다. 주로 기본 키나 고유 인덱스를 사용하는 조인에서 나타납니다.
ref: 쿼리가 값을 찾기 위해 비고유 인덱스를 사용했음을 나타냅니다. 단일 값과 일치하는 모든 행을 반환하며, 주로 비고유 인덱스나 인덱스 접두사를 사용하는 조인에서 사용됩니다.
range: 쿼리가 범위 스캔을 위해 인덱스를 사용했음을 나타내며, BETWEEN, IN(), >, < 등의 범위 조건이 있는 쿼리에서 일반적입니다.
index: 필요한 행을 찾기 위해 전체 인덱스를 스캔함을 의미하며, 적절한 인덱스가 없을 때 나타납니다.
all: 필요한 행을 찾기 위해 전체 테이블을 스캔함을 의미하며, 최악의 시나리오입니다.

일반적으로, 타입 필드 값은 system, const, eq_ref, ref, range, index, all 순으로 가장 좋은 것에서 최악의 순으로 나열됩니다. 그러나 실제 상황은 특정 쿼리, 테이블 구조 및 인덱스 사용에 따라 달라질 수 있습니다. 더 나은 쿼리 성능은 일반적으로 더 나은 타입 값에 해당합니다.

MySQL의 테이블 조회 메커니즘

MySQL에서 테이블 조회(또는 “ref”)는 먼저 인덱스를 사용하여 조건에 맞는 행의 위치를 찾고, 그 다음에 실제 데이터 행을 메인 테이블에서 조회하는 과정을 의미합니다.

이 과정은 커버링 인덱스가 모든 쿼리 요구사항을 충족하지 못할 때 주로 발생합니다. 쿼리가 인덱스만으로는 완전히 해결되지 않을 경우, MySQL은 추가 정보를 얻기 위해 메인 테이블을 조회해야 합니다. 이는 다음과 같은 상황에서 주로 발생합니다:

커버링 인덱스가 없는 쿼리: 쿼리가 인덱스에 포함되지 않은 추가 열을 반환해야 할 때, MySQL은 이러한 열을 얻기 위해 메인 테이블을 조회해야 합니다.
인덱스 범위 조건 사용: 범위 조건(BETWEEN, >, < 등)을 사용하는 경우, 인덱스는 범위의 시작점만 찾을 수 있으며, MySQL은 범위 조건을 만족하는 전체 행을 찾기 위해 메인 테이블을 조회해야 합니다.
클러스터 인덱스를 사용하지만 필요한 열이 인덱스에 포함되지 않은 경우: 클러스터 인덱스가 있는 테이블에서 필요한 열이 클러스터 인덱스에 포함되지 않은 경우, MySQL은 이러한 열을 얻기 위해 메인 테이블을 조회해야 합니다.

MySQL이 테이블 조회를 수행할 때는 메인 테이블에서 데이터를 읽어야 하므로 추가 디스크 액세스가 발생하며, 이는 특히 대용량 테이블이나 고부하 환경에서 성능을 저하시킬 수 있습니다.

테이블 조회를 최소화하기 위해 다음을 고려해보세요:

커버링 인덱스 생성: 쿼리에 필요한 모든 열이 인덱스에 포함되도록 하여 테이블 조회를 피합니다.
쿼리 최적화: 가능한 경우 범위 조건 사용을 피하거나 모든 필터링 조건이 인덱스로 완전히 매칭되도록 합니다.
테이블 설계 고려: 자주 조회되는 열을 인덱스에 포함시켜 테이블 조회의 필요성을 줄입니다.

마무리

지금까지 알아본 다양한 최적화 전략을 통해 MySQL 쿼리 성능을 크게 향상시킬 수 있습니다. 특히 커버링 인덱스를 사용하여 쿼리의 효율성을 높이고, 데이터 볼륨을 줄여 전체적인 성능을 개선할 수 있음을 확인했습니다. 이번 글이 여러분의 데이터베이스 최적화에 도움이 되었기를 바랍니다.

궁금한 점이나 추가로 논의하고 싶은 내용이 있다면 언제든지 댓글로 남겨주세요. 끝까지 읽어주셔서 정말 감사합니다 🙂